Re: PITR Based replication ...

On 4/5/06, Robin Iddon <robin(at)edesix(dot)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