question about Postgresql and rsync

Tony Caduto wrote:

Hi,
Does anyone know if it would be safe to use rsync to mirror a Postgresql
setup to a backup server?

I need to create a exact duplicate for a disaster recovery server, the
disaster recovery server would not be in use until the production one
went down for
some reason.

It seems to me that if the postmaster is stopped it should be safe.
Comments?

Yes, it is safe if the postmaster is down. In fact our documention
recommends a trick of doing rsync while the postmaster is running, then
shutting down the postmaster just long enough to do another rsync to get
the changes into the backup.

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 359-1001
  +  If your life is a hard drive,     |  13 Roberts Road
  +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073

On Thursday 03 November 2005 07:28, Tony Caduto wrote:

Hi,
Does anyone know if it would be safe to use rsync to mirror a
Postgresql setup to a backup server?

I need to create a exact duplicate for a disaster recovery server,
the disaster recovery server would not be in use until the
production one went down for
some reason.

It seems to me that if the postmaster is stopped it should be safe.
Comments?

Yes, if the system is shut down you should be able to do a filesystem
copy. But I'm confused why this is supposedly better than using the
tools provided (pg_dump, pg_dumpall, pg_restore, psql...).

Also, unless this is a read-only server, keeping the DR machine
up-to-date will require you to stop your server whenever you want to
update your DR machine.

You can do a filesystem backup on a live machine and bring it
up-to-date on a second machine using the log files. You can even keep
shipping the log files to the backup server in order to keep it
up-to-date which seems like a better solution (and you could use
rsync to move the log files if you so desire). Check out 22.3 in the
manual for info on on-line backup and recovery:
http://www.postgresql.org/docs/8.0/interactive/backup-online.html

You may want to use rsync to keep your configuration files up-to-date.

Cheers,
Steve

You're better off doing a live backup with the PITR mechanism. That way its
not a trick (assuming you're running 8.0+). If you are archiving logs, do

select pg_start_backup(<label>);
(do rsync,tar,cp -r,whatever)
select pg_stop_backup();

Do an archive recovery with any saved logs, add 2 eggs, stir, and bake at
350 for 10 minutes. Instant hot backup.

I haven't figured out a microwave recipe yet...metal computer cases and what
not...:-)

On 11/3/05 10:39 AM, "Bruce Momjian" <pgman@candle.pha.pa.us> wrote:

Wouldn't DRBD be a better solution ? Or are you looking for a delay between
updates, so your other server is out by an hour's worth of stuff ? I guess this
is applicable if you use some Linux distro though. HTH.

Cheers,

Aly.

Steve Crawford wrote:

On Thursday 03 November 2005 07:28, Tony Caduto wrote:

Hi,
Does anyone know if it would be safe to use rsync to mirror a
Postgresql setup to a backup server?

I need to create a exact duplicate for a disaster recovery server,
the disaster recovery server would not be in use until the
production one went down for
some reason.

It seems to me that if the postmaster is stopped it should be safe.
Comments?

Yes, if the system is shut down you should be able to do a filesystem
copy. But I'm confused why this is supposedly better than using the
tools provided (pg_dump, pg_dumpall, pg_restore, psql...).

Also, unless this is a read-only server, keeping the DR machine
up-to-date will require you to stop your server whenever you want to
update your DR machine.

You can do a filesystem backup on a live machine and bring it
up-to-date on a second machine using the log files. You can even keep
shipping the log files to the backup server in order to keep it
up-to-date which seems like a better solution (and you could use
rsync to move the log files if you so desire). Check out 22.3 in the
manual for info on on-line backup and recovery:
http://www.postgresql.org/docs/8.0/interactive/backup-online.html

You may want to use rsync to keep your configuration files up-to-date.

Cheers,
Steve

---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster

--
Aly S.P Dharshi
aly.dharshi@telus.net

"A good speech is like a good dress
that's short enough to be interesting
and long enough to cover the subject"

Tony.....

I need to create a exact duplicate for a disaster recovery server, the
disaster recovery server would not be in use until the production one
went down for some reason.

While it's not what you asked about, copying the database files, what I do
is pg_dump nightly with a cronjob, then use rsync to copy it to my backup
machine and load it into the backup server with a cronjob there.

That way if the main server crashes or is down the backup server is never
more than a day out of date.

Obviously this is a server with relatively small databases that can stand
the chance of some loss of data (it's a website server). I could make it
better by doing this every hour or so.

brew

==========================================================================
Strange Brew (brew@theMode.com)
Check out my Stock Option Covered Call website http://www.callpix.com
and my Musician's Online Database Exchange http://www.TheMode.com
==========================================================================