Thread: question about Postgresql and rsync
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? Thanks, Tony Caduto
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: > 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.
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
==========================================================================