[ADMIN] 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: [EMAIL PROTECTED] Yahoo!: yscrappy ICQ: 7615664 ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [ADMIN] PITR Based replication ...
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) ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [ADMIN] PITR Based replication ...
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 <[EMAIL PROTECTED]> Frost Consulting, LLC http://www.frostconsultingllc.com/ Phone: 650-780-7908 FAX: 650-649-1954 ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
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
Re: [ADMIN] PITR Based replication ...
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: [EMAIL PROTECTED] [mailto:pgsql-admin- > [EMAIL PROTECTED] 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! > ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [ADMIN] PITR Based replication ...
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
Re: [ADMIN] PITR Based replication ...
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: [EMAIL PROTECTED] Yahoo!: yscrappy ICQ: 7615664 ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [ADMIN] PITR Based replication ...
On 4/5/06, Robin Iddon <[EMAIL PROTECTED]> 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 listabout how best to replicate the WAL files between two machines - I haveno 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 pgbackup.sh Description: Bourne shell script ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [ADMIN] PITR Based replication ...
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
Re: [ADMIN] PITR Based replication ...
On 4/5/06, Robin Iddon <[EMAIL PROTECTED]> wrote:[-l $LOGFILE] Hope this helps,It did, thanks./rls-- :wq
