Thread: Incremental Backups in postgres
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
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. On Nov 9, 2009, at 6:48 PM, 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 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
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 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
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 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 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
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. On Nov 9, 2009, at 6:48 PM, 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 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
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:
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
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:
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.
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 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
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? On Tue, Nov 10, 2009 at 4:56 PM, Alan Hodgson <ahodgson@simkin.ca> 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." > > -- > Sent via pgsql-general mailing list (pgsql-general@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-general >
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:
How about using replication instead of incremental backups?
On Tue, Nov 10, 2009 at 4:56 PM, Alan Hodgson <ahodgson@simkin.ca> 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."
>
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
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 Tue, Nov 10, 2009 at 4:56 PM, Alan Hodgson <ahodgson@simkin.ca> 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." > > -- > Sent via pgsql-general mailing list (pgsql-general@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-general
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:
>
> 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
>
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