[GENERAL] Incremental Backups in postgres

[akp geek]

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

[Ben Chobot]

Saving off the transaction log WAL files is a good way to do this.
Read this part of the manual:

http://www.postgresql.org/docs/8.4/interactive/continuous-archiving.html

...and see if that answers your questions.

--
Sent via pgsql-general mailing list (pgsql-...@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[Richard Broersma]

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

[Jing Tan]

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 <akp...@gmail.com> ha escrito:

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

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

[Alban Hertroys]

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!

[Greg Stark]

On Tue, Nov 10, 2009 at 11:03 AM, Alban Hertroys
<dal...@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

[Scott Mead]

On Tue, Nov 10, 2009 at 9:52 AM, Greg Stark <gss...@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

[akp geek]

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

[akp geek]

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

 

Please advice

 

Regards

[Alan Hodgson]

On Tuesday 10 November 2009, akp geek <akp...@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."

[silly8888]

How about using replication instead of incremental backups?

[akp geek]

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

 

regards

[akp geek]

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

[Scott Mead]

On Wed, Nov 11, 2009 at 12:51 PM, akp geek <akp...@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

 

[akp geek]

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

--

[Greg Smith]

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
gr...@2ndQuadrant.com www.2ndQuadrant.com