On 4/5/06,
Robin Iddon <
robin@edesix.com> wrote:
Andy Shellam wrote:
>I have, however, recently developed an interest in rsync but I'm unsure as
>to how PG on the standby server would handle a complete rsync'd data
>directory.
There has just recently been a fairly extensive discussion on this list
about how best to replicate the WAL files between two machines - I have
no direct experience of this myself so will not comment on whether or
not rsync is suitable.
We've been successfully rsyncing between two machines, including WAL, nightly for some time now; our only problem is fully automating the job. My simple shell script for a two-pass rsync backup of one PostgreSQL instance to another is attached; hopefully it's useful. It's modeled on the scenario described at <
http://www.postgresql.org/docs/8.1/interactive/backup-file.html>, and should be run on the backup box.
To address any specific concerns about whether or not it's reliable, the backup instance invariably starts up cleanly -- it just doesn't start up automatically. The command to restart the backend on the remote, production host on line 33 of the script never returns. The production instance starts, but -- and I'm guessing it has something to do with what pg_ctl does with STDIN/OUT/ERR -- the script never continues beyond that point to start up the backup instance.
This isn't an utter deal-breaker; we could let the script run as-is and just cron a re-start of the backup instance for some time well after this job runs. Doing that would also allow us to take a tape backup of the backup instance while it's down, since backups of running PostgreSQL instances tend not to be consistent.
Anyone have any suggestions on novel phrases to offer in my incantations for getting this script to do everything I need? Should I just use two separate cron jobs? Also, is there any way, in the case of shutting down the production instance for the second pass, to have the shutdown command wait indefinitely? "-m smart" will give up after waiting so long, and I'd like neither to interrupt any running jobs, nor end up not taking a backup in the event a running job outlasts pg_ctl's timeout.
We'd ultimately be interested in migrating this setup towards PITR-based replication, too. We have two identical hosts running PostgreSQL that, short of full-on clustering, we'd like to have as close to real-time fail-over as possible. For now, these nightly snapshots are "good enough" but per Murphy if nothing else, that can't last. I'm willing to work with interested parties to get the docs a/o any scripts to accomplish this whipped into existence, if not shape.
/rls
--
:wq