Andy Shellam wrote:

>Robin,
>
>On my part it's simply the fact that I currently have two servers in
>different geographical locations - and cost of new hardware is a huge issue.
>
>
>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.
>
>Andy
>
>

Andy - agreed that DRBD is not appropriate for WAN-type environments (at
least not unless the database is mostly read-only).

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.

Cheers,
Robin


---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match

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


---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faq

> Anyone have any suggestions on novel phrases to offer in my incantations for
> getting this script to do everything I need?
You need to add "-l $LOGFILE" where log is wherever you want to write
the stderr+stdout from the postmaster to. Then it will return once
starting the server.

> 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.
>
>
Not that I know of, but you can poll the server status using the same
pg_ctl command constructs as you're using already but with "status"
instead of "stop" or "start". pg_ctl (and hence ssh) will return 0 if
the server is running and 1 if the server is no running.

So you could try something like

while ssh $REMOTE pg_ctl -D $CLUSTER status
do
echo "Remote server still running - continuing to wait ..."
sleep 10
done

Hope this helps,

Robin


---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend

On 4/5/06, Robin Iddon <robin@edesix.com> wrote:

[-l $LOGFILE]

Hope this helps,


It did, thanks.

/rls

--
:wq