Thread: Automation/scheduling of Backup stratetgy
Any word on when (if?) live/hot backup will be available? Matthew H. North Software Engineer CTSnet Internet Services t (858) 637-3600 f (858) 637-3630 mailto:ctsmhn@cts.com -----Original Message----- From: pgsql-admin-owner@hub.org [mailto:pgsql-admin-owner@hub.org]On Behalf Of Michel Decima Sent: Thursday, October 19, 2000 6:04 AM To: pgsql-admin@postgresql.org; CScheepers@mweb.com Subject: Re: [ADMIN] Automation/scheduling of Backup stratetgy >Is it posible to schedule / automate a backup task and functions to execute >at a pre-defined time at a pre-defined recurrence rate? yes, using the cron daemon and the commands pg_dump or pg_dumpall. The following entry in the postgres user crontab will backup the database every day at 03:01 AM in /dev/null (very usefull) 1 3 * * * /usr/local/pgsql/bin/pg_dumpall > /dev/null just look at man cron man crontab man pg_dumpall MD.
It is already built in. Use pg_dump or pg_dumpall when online. Only problem is OIDs and large objects. pg_dump -o to keep OIDs not sure about large objects.
Hope this is what you mean by live/hot backup.
Ben
> -----Original Message-----
> From: Matthew H. North [mailto:ctsmhn@cts.com]
> Sent: 19 October 2000 16:41
> To: pgsql-admin@postgresql.org
> Subject: [ADMIN] Automation/scheduling of Backup stratetgy
>
>
>
> Any word on when (if?) live/hot backup will be available?
>
> Matthew H. North
> Software Engineer
> CTSnet Internet Services
> t (858) 637-3600
> f (858) 637-3630
> mailto:ctsmhn@cts.com
>
> -----Original Message-----
> From: pgsql-admin-owner@hub.org [mailto:pgsql-admin-owner@hub.org]On
> Behalf Of Michel Decima
> Sent: Thursday, October 19, 2000 6:04 AM
> To: pgsql-admin@postgresql.org; CScheepers@mweb.com
> Subject: Re: [ADMIN] Automation/scheduling of Backup stratetgy
>
>
> >Is it posible to schedule / automate a backup task and
> functions to execute
> >at a pre-defined time at a pre-defined recurrence rate?
>
> yes, using the cron daemon and the commands pg_dump or pg_dumpall.
>
> The following entry in the postgres user crontab will backup the
> database every day at 03:01 AM in /dev/null (very usefull)
>
> 1 3 * * * /usr/local/pgsql/bin/pg_dumpall > /dev/null
>
> just look at
> man cron
> man crontab
> man pg_dumpall
>
> MD.
>
>
Let me clarify: By 'hot backup' I mean some kind of method we can use to keep a live mirror of a PostgreSQL database as it's being updated. As it is, the pg_dump method has quite a large granularity - we currently have a cron job that runs pg_dumpall once per day, in the early morning. This means that if we had to revert to one of these daily backups, we have the potential of a day's worth of lost data.... Not good. We would like to be able to backup the database AS it's being worked on so that if the primary db server drops dead, we have a backup that's pretty much up-to-date as of the time of the crash. Again, any word from the developers on when we might be able to expect this? Matthew H. North Software Engineer CTSnet Internet Services t (858) 637-3600 f (858) 637-3630 mailto:ctsmhn@cts.com -----Original Message----- From: pgsql-admin-owner@hub.org [mailto:pgsql-admin-owner@hub.org]On Behalf Of Trewern, Ben Sent: Thursday, October 19, 2000 9:32 AM To: pgsql-admin@postgresql.org Subject: RE: [ADMIN] Automation/scheduling of Backup stratetgy It is already built in. Use pg_dump or pg_dumpall when online. Only problem is OIDs and large objects. pg_dump -o to keep OIDs not sure about large objects. Hope this is what you mean by live/hot backup. Ben > -----Original Message----- > From: Matthew H. North [mailto:ctsmhn@cts.com] > Sent: 19 October 2000 16:41 > To: pgsql-admin@postgresql.org > Subject: [ADMIN] Automation/scheduling of Backup stratetgy > > > > Any word on when (if?) live/hot backup will be available? > > Matthew H. North > Software Engineer > CTSnet Internet Services > t (858) 637-3600 > f (858) 637-3630 > mailto:ctsmhn@cts.com > > -----Original Message----- > From: pgsql-admin-owner@hub.org [mailto:pgsql-admin-owner@hub.org]On > Behalf Of Michel Decima > Sent: Thursday, October 19, 2000 6:04 AM > To: pgsql-admin@postgresql.org; CScheepers@mweb.com > Subject: Re: [ADMIN] Automation/scheduling of Backup stratetgy > > > >Is it posible to schedule / automate a backup task and > functions to execute > >at a pre-defined time at a pre-defined recurrence rate? > > yes, using the cron daemon and the commands pg_dump or pg_dumpall. > > The following entry in the postgres user crontab will backup the > database every day at 03:01 AM in /dev/null (very usefull) > > 1 3 * * * /usr/local/pgsql/bin/pg_dumpall > /dev/null > > just look at > man cron > man crontab > man pg_dumpall > > MD. > >
You mean WAL. When your server goes down, you can restore from the last backup, and then reapply the WAL to recreate all transactions that were in committed between the backup and the crash. WAL is due for 7.1, I believe.
Cheers...
MikeA
>> -----Original Message-----
>> From: Matthew H. North [mailto:ctsmhn@cts.com]
>> Sent: 19 October 2000 19:40
>> To: pgsql-admin@postgresql.org; pgsql-hackers@postgresql.org
>> Subject: RE: [ADMIN] Automation/scheduling of Backup stratetgy
>>
>>
>>
>> Let me clarify:
>>
>> By 'hot backup' I mean some kind of method we can use to
>> keep a live mirror
>> of a PostgreSQL database as it's being updated. As it is,
>> the pg_dump
>> method has quite a large granularity - we currently have a
>> cron job that
>> runs pg_dumpall once per day, in the early morning. This
>> means that if we
>> had to revert to one of these daily backups, we have the
>> potential of a
>> day's worth of lost data.... Not good.
>>
>> We would like to be able to backup the database AS it's
>> being worked on so
>> that if the primary db server drops dead, we have a backup
>> that's pretty
>> much up-to-date as of the time of the crash.
>>
>> Again, any word from the developers on when we might be
>> able to expect this?
>>
>> Matthew H. North
>> Software Engineer
>> CTSnet Internet Services
>> t (858) 637-3600
>> f (858) 637-3630
>> mailto:ctsmhn@cts.com
>>
>> -----Original Message-----
>> From: pgsql-admin-owner@hub.org
>> [mailto:pgsql-admin-owner@hub.org]On Behalf
>> Of Trewern, Ben
>> Sent: Thursday, October 19, 2000 9:32 AM
>> To: pgsql-admin@postgresql.org
>> Subject: RE: [ADMIN] Automation/scheduling of Backup stratetgy
>>
>>
>> It is already built in. Use pg_dump or pg_dumpall when
>> online. Only problem
>> is OIDs and large objects. pg_dump -o to keep OIDs not
>> sure about large
>> objects.
>> Hope this is what you mean by live/hot backup.
>> Ben
>> > -----Original Message-----
>> > From: Matthew H. North [mailto:ctsmhn@cts.com]
>> > Sent: 19 October 2000 16:41
>> > To: pgsql-admin@postgresql.org
>> > Subject: [ADMIN] Automation/scheduling of Backup stratetgy
>> >
>> >
>> >
>> > Any word on when (if?) live/hot backup will be available?
>> >
>> > Matthew H. North
>> > Software Engineer
>> > CTSnet Internet Services
>> > t (858) 637-3600
>> > f (858) 637-3630
>> > mailto:ctsmhn@cts.com
>> >
>> > -----Original Message-----
>> > From: pgsql-admin-owner@hub.org
>> [mailto:pgsql-admin-owner@hub.org]On
>> > Behalf Of Michel Decima
>> > Sent: Thursday, October 19, 2000 6:04 AM
>> > To: pgsql-admin@postgresql.org; CScheepers@mweb.com
>> > Subject: Re: [ADMIN] Automation/scheduling of Backup stratetgy
>> >
>> >
>> > >Is it posible to schedule / automate a backup task and
>> > functions to execute
>> > >at a pre-defined time at a pre-defined recurrence rate?
>> >
>> > yes, using the cron daemon and the commands pg_dump or
>> pg_dumpall.
>> >
>> > The following entry in the postgres user crontab will backup the
>> > database every day at 03:01 AM in /dev/null (very usefull)
>> >
>> > 1 3 * * * /usr/local/pgsql/bin/pg_dumpall > /dev/null
>> >
>> > just look at
>> > man cron
>> > man crontab
>> > man pg_dumpall
>> >
>> > MD.
>> >
>> >
>>