pg.ddx.io  pgsql-admin@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Rosser Schwarz <rosser.schwarz@gmail.com>
To: pgsql-admin@postgresql.org
Subject: Re: PITR Based replication ...
Date: Wed, 5 Apr 2006 13:26:13 -0500
Message-ID: <37d451f70604051126s2bd2b322m1ebf76051bdcf02d@mail.gmail.com> (raw)
In-Reply-To: <4433AB17.7060705@edesix.com>
References: <4433AB17.7060705@edesix.com>

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

Attachments:

  pgbackup.sh (application/x-sh, 896B) download

view thread (10+ messages)  latest in thread

Message-ID: <37d451f70604051126s2bd2b322m1ebf76051bdcf02d@mail.gmail.com>
Permalink:  ../37d451f70604051126s2bd2b322m1ebf76051bdcf02d@mail.gmail.com/
Also on:    postgresql.org/message-id/37d451f70604051126s2bd2b322m1ebf76051bdcf02d@mail.gmail.com

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients from your mail client; the link fills in
  the headers below: reply via email
  Some clients, webmail especially, drop In-Reply-To and References from
  a link, and the reply then starts a new thread: use the mbox below.

  To: pgsql-admin@postgresql.org
  Cc: rosser.schwarz@gmail.com
  Subject: Re: PITR Based replication ...
  In-Reply-To: <37d451f70604051126s2bd2b322m1ebf76051bdcf02d@mail.gmail.com>
  References: <4433AB17.7060705@edesix.com> <37d451f70604051126s2bd2b322m1ebf76051bdcf02d@mail.gmail.com>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox