Обсуждение: PITR Based replication ...
I know ppl are using it to do replication, but has anyone documented what is involved in doing so? thanks ... ---- Marc G. Fournier Hub.Org Networking Services (http://www.hub.org) Email: scrappy@hub.org Yahoo!: yscrappy ICQ: 7615664
On Apr 4, 2006, at 4:05 PM, Marc G. Fournier wrote: > I know ppl are using it to do replication, but has anyone > documented what is involved in doing so? > > thanks ... I'll be curious to hear stories of people using it for replication. The way I interpret replication, there's an available database (even if read-only) on both ends. With PITR/on-line backups, the way I understand it, there's no way to provide availability to the recovery database because it's in a process of continuous recovery. It qualifies as high availability in terms of a failover solution, but the recovery database is not actually available until something triggers it to recover, at which point any writing done to it causes it to cease to be a replicant of the base database. -- Thomas F. O'Connell Database Architecture and Programming Co-Founder Sitening, LLC http://www.sitening.com/ 3004 B Poston Avenue Nashville, TN 37203-1314 615-260-0005 (cell) 615-469-5150 (office) 615-469-5151 (fax)
On Tue, 4 Apr 2006, Thomas F. O'Connell wrote: > On Apr 4, 2006, at 4:05 PM, Marc G. Fournier wrote: > > I'll be curious to hear stories of people using it for replication. The way I > interpret replication, there's an available database (even if read-only) on > both ends. With PITR/on-line backups, the way I understand it, there's no way > to provide availability to the recovery database because it's in a process of > continuous recovery. It qualifies as high availability in terms of a failover > solution, but the recovery database is not actually available until something > triggers it to recover, at which point any writing done to it causes it to > cease to be a replicant of the base database. We started a project on it here: http://pgpitrha.projects.postgresql.org/ So far we have a working version of it in CVS which we are using at travelpost.com. You're correct, the secondary system is only available after you complete the PITR recovery, but it works well for us currently. Right now we just make base backups 3 times daily and restore all the way from the base when we cutover. The first thing we'll be changing is that methodology (i.e. we'll be going to a continuous recovery methodology). Hopefully we'll get some interest from more folks soon and get some good ideas flowing. -- Jeff Frost, Owner <jeff@frostconsultingllc.com> Frost Consulting, LLC http://www.frostconsultingllc.com/ Phone: 650-780-7908 FAX: 650-649-1954
Marc G. Fournier wrote: > > I know ppl are using it to do replication, but has anyone documented > what is involved in doing so? > > thanks ... We use linux HA and linux DRBD (~RAID1 mirror between disks across a LAN) to provide a similar replication mechanism that runs "underneath" the database rather than PITR between the database servers. I see a lot of interest on this list for the WAL copying solution, and I am wondering if that is because people have discounted a DRBD solution or that it has been overlooked? Thanks, Robin
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 > -----Original Message----- > From: pgsql-admin-owner@postgresql.org [mailto:pgsql-admin- > owner@postgresql.org] On Behalf Of Robin Iddon > Sent: 05 April 2006 9:10 am > Cc: pgsql-admin@postgresql.org > Subject: Re: [ADMIN] PITR Based replication ... > > Marc G. Fournier wrote: > > > > I know ppl are using it to do replication, but has anyone documented > > what is involved in doing so? > > > > thanks ... > We use linux HA and linux DRBD (~RAID1 mirror between disks across a > LAN) to provide a similar replication mechanism that runs "underneath" > the database rather than PITR between the database servers. > > I see a lot of interest on this list for the WAL copying solution, and I > am wondering if that is because people have discounted a DRBD solution > or that it has been overlooked? > > Thanks, > 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 > > !DSPAM:14,44337b9c35048018610585! >
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
On Wed, 5 Apr 2006, Robin Iddon wrote: > Marc G. Fournier wrote: >> >> I know ppl are using it to do replication, but has anyone documented what >> is involved in doing so? >> >> thanks ... > We use linux HA and linux DRBD (~RAID1 mirror between disks across a LAN) to > provide a similar replication mechanism that runs "underneath" the database > rather than PITR between the database servers. > > I see a lot of interest on this list for the WAL copying solution, and I am > wondering if that is because people have discounted a DRBD solution or that > it has been overlooked? IN my case, we don't run Linux, so any Linux solution is discounted :) But, thx ... ---- Marc G. Fournier Hub.Org Networking Services (http://www.hub.org) Email: scrappy@hub.org Yahoo!: yscrappy ICQ: 7615664
On 4/5/06, Robin Iddon <robin@edesix.com> wrote:
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
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.
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
Вложения
> 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
On 4/5/06, Robin Iddon <robin@edesix.com> wrote:
[-l $LOGFILE]
It did, thanks.
/rls
--
:wq
[-l $LOGFILE]
Hope this helps,
It did, thanks.
/rls
--
:wq