Thread: Automation/scheduling of Backup stratetgy

Automation/scheduling of Backup stratetgy

From
"Matthew H. North"
Date:
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.



RE: Automation/scheduling of Backup stratetgy

From
"Trewern, Ben"
Date:

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.
>
>

RE: Automation/scheduling of Backup stratetgy

From
"Matthew H. North"
Date:
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.
>
>


RE: Automation/scheduling of Backup stratetgy

From
Michael Ansley
Date:

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.
>>   >
>>   >
>>