Incremental Backups in postgres

On Mon, Nov 9, 2009 at 6:48 PM, akp geek <akpgeek@gmail.com> wrote:

                  Is there way to create incremental backups in postgres. I
am currently using 8.4.1 on solaris. I am new to postgres. Can you please
share your thoughts

I've read more about continuous back-ups:
http://www.postgresql.org/docs/8.4/static/continuous-archiving.html

However, I see there is a section on incremental backups as well:
http://www.postgresql.org/docs/8.4/static/warm-standby.html#BACKUP-INCREMENTAL-UPDATED

--
Regards,
Richard Broersma Jr.

Visit the Los Angeles PostgreSQL Users Group (LAPUG)
http://pugs.postgresql.org/lapug

I wrote an article about PITR , incremental backups and multiple timelines.
check out. http://jinxter555.blogspot.com/
it should be an easy read.

akp geek <akpgeek@gmail.com> ha escrito:

Dear all -

Is there way to create incremental backups in postgres. I
am currently using 8.4.1 on solaris. I am new to postgres. Can you please
share your thoughts

Regards

----
iamastring: "i am a string, a super string"

----------------------------------------------------------------
This message was sent using IMP, the Internet Messaging Program.

On 10 Nov 2009, at 3:48, akp geek wrote:

Dear all -

Is there way to create incremental backups in
postgres. I am currently using 8.4.1 on solaris.. I am new to
postgres. Can you please share your thoughts

Regards

IMHO The simplest solution is to just write a dump to the same file
every now and then and have the backup software take care of storing
only the differences. It does have a few drawbacks; it means you'll
have a file about as large as your database on your filesystem just
for making backups and there is a risk that your backup software kicks
in before the dump has finished writing.

As others mentioned, you can also go with a PITR solution, which is
probably prettier but is a bit harder to set up.

Alban Hertroys

--
Screwing up is the best way to attach something to the ceiling.

!DSPAM:737,4af948a011071608518950!

On Tue, Nov 10, 2009 at 11:03 AM, Alban Hertroys
<dalroi@solfertje.student.utwente.nl> wrote:

IMHO The simplest solution is to just write a dump to the same file every
now and then and have the backup software take care of storing only the
differences. It does have a few drawbacks; it means you'll have a file about
as large as your database on your filesystem just for making backups and
there is a risk that your backup software kicks in before the dump has
finished writing.

As others mentioned, you can also go with a PITR solution, which is probably
prettier but is a bit harder to set up.

It's always worth having the dump, even if you also implement PITR.
The dump allows you to restore just specific tables or to restore onto
a different type of system. The PITR backup is a physical
byte-for-byte copy which only works if you restore the whole database
and only on the same type of system.

--
greg

On Tue, Nov 10, 2009 at 9:52 AM, Greg Stark <gsstark@mit.edu> wrote:

It's always worth having the dump, even if you also implement PITR.
The dump allows you to restore just specific tables or to restore onto
a different type of system. The PITR backup is a physical
byte-for-byte copy which only works if you restore the whole database
and only on the same type of system.

Good point here, you really should have a 'logical' copy of your
database around in case there is some kind of physical corruption in
addition to Greg's good points.

--Scott

I have tested the procedure in the URL and it worked fine. I have
accidentally deleted my PGDATA folder after the backup procedure is done. I
could able to restore it. But still have few questions

Thanks for the help

Regards

On Mon, Nov 9, 2009 at 11:01 PM, Jing Tan <jing@iheavy.com> wrote:

So Is it always good to have the backup using PG_dump instead of PITR or a
combination of both

Please advice

Regards

On Tue, Nov 10, 2009 at 11:24 AM, Scott Mead
<scott.lists@enterprisedb.com>wrote:

On Tuesday 10 November 2009, akp geek <akpgeek@gmail.com> wrote:

So Is it always good to have the backup using PG_dump instead of PITR or
a combination of both

I like to do both. Ongoing PITR, daily base backups (by updating an rsync
copy), and weekly pg_dumps that in turn go to tape.

PITR gives a very recent restore point in the event of server loss. As
previously mentioned, the full (custom) backups let you restore individual
tables. They're also a lot smaller than base backups + WAL logs.

--
"No animals were harmed in the recording of this episode. We tried but that
damn monkey was just too fast."

How about using replication instead of incremental backups?

I have set up the replication using Bucardo. This is just an additional set
up

regards

On Tue, Nov 10, 2009 at 5:09 PM, silly8888 <silly8888@gmail.com> wrote:

Hi All -
                  I have read the document got a reasonable
understanding of the WAL process. I have some confusion regarding the
process.

1. I have set up the archiving process. Now the archive file are going
to a different mount point.
2. I set up job to create a back up of the PGDATA directory

Are the 2 above steps enough for recovery. My confusion is why we need
to have  Making a Base Backup. When you have time can you please
clarify?

Regards

On Wed, Nov 11, 2009 at 12:51 PM, akp geek <akpgeek@gmail.com> wrote:

Hi All -
I have read the document got a reasonable
understanding of the WAL process. I have some confusion regarding the
process.

1. I have set up the archiving process. Now the archive file are going
to a different mount point.
2. I set up job to create a back up of the PGDATA directory

Before you backup PGDATA, you either need to stop the database or use
pg_start_backup('label');

After your backup, you would need to run: pg_stop_backup();

Are the 2 above steps enough for recovery. My confusion is why we need
to have Making a Base Backup. When you have time can you please
clarify?

Remember, an 'incremental' backup is an increment to an original full
backup. That's why you need to make a backup of the PGDATA.

--Scott

Got it almost. Thanks a lot. One final question, please bear with me.

1. select pg_start_backup('label') ==> 10 AM
2. PGDATA folder backup ==> 10:05 AM
3. select pg_stop_backup => 10.10AM
4. The archiving will start writing files
5. If the disc crashes at 11AM, what will happen to the data between
10:10AM and 11:00AM, since we issued a pg_stop_backup at 10:10AM

Appreciate your help

Regards

On Wed, Nov 11, 2009 at 2:58 PM, Scott Mead
<scott.lists@enterprisedb.com> wrote:

akp geek wrote:

Got it almost. Thanks a lot. One final question, please bear with me.

1. select pg_start_backup('label') ==> 10 AM
2. PGDATA folder backup ==> 10:05 AM
3. select pg_stop_backup => 10.10AM
4. The archiving will start writing files

You've got step (4) in the wrong place. The archiver will generate
files you are compelled to save as soon as you issue pg_start_backup in
(1). That's how you're able to recover from a failure at any time after
that. When you issue pg_stop_backup, it will tell you what files it
expects to be copied over to the slave system in order to recovery from
a failure, and that list will go back to when you started the backup.
Saving those is actually part of the base backup process, as documented
in the manual if you read that section more carefully.

--
Greg Smith 2ndQuadrant Baltimore, MD
PostgreSQL Training, Services and Support
greg@2ndQuadrant.com www.2ndQuadrant.com