X-Original-To: pgsql-admin-postgresql.org@localhost.postgresql.org Received: from localhost (av.hub.org [200.46.204.144]) by postgresql.org (Postfix) with ESMTP id CEE5A11F6750 for ; Wed, 5 Apr 2006 15:26:22 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 26319-05 for ; Wed, 5 Apr 2006 15:26:20 -0300 (ADT) X-Greylist: domain auto-whitelisted by SQLgrey- Received: from wproxy.gmail.com (wproxy.gmail.com [64.233.184.231]) by postgresql.org (Postfix) with ESMTP id 9B51211F662D for ; Wed, 5 Apr 2006 15:26:13 -0300 (ADT) Received: by wproxy.gmail.com with SMTP id i2so544963wra for ; Wed, 05 Apr 2006 11:26:13 -0700 (PDT) DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=beta; d=gmail.com; h=received:message-id:date:from:to:subject:in-reply-to:mime-version:content-type:references; b=cmsnrRm72tnNsf4DbFYGQcRK96DGgOoMtBfgOrVyDzjrMLvCsGJGQxR05RWQ3Sam0UPlS0bcG2CgDgdrGwUdZMcLo4/HhmwhUHUWxZ9KxpRi2CF9AfL6onaLJ3oHHf2tp7dybTQFN6JmS5C0doZFGX6Aa0sX/NuKGLNis6nh6gY= Received: by 10.54.121.18 with SMTP id t18mr1073684wrc; Wed, 05 Apr 2006 11:26:13 -0700 (PDT) Received: by 10.54.102.9 with HTTP; Wed, 5 Apr 2006 11:26:13 -0700 (PDT) Message-ID: <37d451f70604051126s2bd2b322m1ebf76051bdcf02d@mail.gmail.com> Date: Wed, 5 Apr 2006 13:26:13 -0500 From: "Rosser Schwarz" To: pgsql-admin@postgresql.org Subject: Re: PITR Based replication ... In-Reply-To: <4433AB17.7060705@edesix.com> MIME-Version: 1.0 Content-Type: multipart/mixed; boundary="----=_Part_8016_7952947.1144261573222" References: <4433AB17.7060705@edesix.com> X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, score=0.001 required=5 tests=[HTML_MESSAGE=0.001] X-Spam-Score: 0.001 X-Spam-Level: X-Archive-Number: 200604/58 X-Sequence-Number: 21227 ------=_Part_8016_7952947.1144261573222 Content-Type: multipart/alternative; boundary="----=_Part_8017_17893654.1144261573222" ------=_Part_8017_17893654.1144261573222 Content-Type: text/plain; charset=ISO-8859-1 Content-Transfer-Encoding: quoted-printable Content-Disposition: inline On 4/5/06, Robin Iddon 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. M= y 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 shoul= d 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, productio= n 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 doe= s 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 jus= t 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 ten= d not to be consistent. Anyone have any suggestions on novel phrases to offer in my incantations fo= r getting this script to do everything I need? Should I just use two separat= e 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 th= e 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 ------=_Part_8017_17893654.1144261573222 Content-Type: text/html; charset=ISO-8859-1 Content-Transfer-Encoding: quoted-printable Content-Disposition: inline On 4/5/06, Robin Iddon <robin@edesix.com> wrote:
Andy Shellam wrote:

>I have, however, recently developed an in= terest in rsync but I'm unsure as
>to how PG on the standby server would handle a complete rsync'd dat= a
>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 successf= ully rsyncing between two machines, including WAL, nightly for some time no= w; our only problem is fully automating the job.  My simple shell scri= pt for a two-pass rsync backup of one PostgreSQL instance to another is att= ached; hopefully it's useful. It's modeled on the scenario described at <= ; http://www.postgresql.org/docs/8.1/interactive/backup-file.html>, a= nd 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 guessi= ng it has something to do with what pg_ctl does with STDIN/OUT/ERR -- the s= cript never continues beyond that point to start up the backup instance.&nb= sp;=20

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 t= his 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 i= nstances tend not to be consistent.

Anyone have any suggestions on novel phrases to offer in my incanta= tions 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 sh= utting down the production instance for the second pass, to have the shutdo= wn command wait indefinitely?  "-m smart" will give up after= waiting so long, and I'd like neither to interrupt any running jobs, nor e= nd up not taking a backup in the event a running job outlasts pg_ctl's time= out.

We'd ultimately be interested in migrating this setup towards PITR-= based replication, too.  We have two identical hosts running PostgreSQ= L that, short of full-on clustering, we'd like to have as close to real-tim= e fail-over as possible. For now, these nightly snapshots are "good en= ough" 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 ------=_Part_8017_17893654.1144261573222-- ------=_Part_8016_7952947.1144261573222 Content-Type: application/x-sh; name=pgbackup.sh Content-Transfer-Encoding: 7bit X-Attachment-Id: f_elnzfkmb Content-Disposition: attachment; filename="pgbackup.sh" #!/bin/sh # perform a two-pass rsync backup of production PostgreSQL host # to backup host # remote (production) host IP REMOTE= # database cluster path CLUSTER= # log path LOGDIR= # stop local (backup) backend pg_ctl -D $CLUSTER stop -m smart # sync remote data cluster to local (first pass) rsync -avz --delete --exclude='*.conf' $REMOTE:$CLUSTER $CLUSTER # sync remote wal to local (first pass) rsync -avz --delete $REMOTE:$LOGDIR $LOGDIR # stop remote (production) backend ssh $REMOTE pg_ctl -D $CLUSTER stop -m smart # sync remote data cluster to local (second pass) rsync -avz --delete --exclude='*.conf' $REMOTE:$CLUSTER $CLUSTER # sync remote wal to local (first pass) rsync -avz --delete $REMOTE:$LOGDIR $LOGDIR # restart remote (production) backend ssh $REMOTE pg_ctl -D $CLUSTER start # restart local (backup) backend pg_ctl -D $CLUSTER start # fin ------=_Part_8016_7952947.1144261573222--