Skip site navigation (1) Skip section navigation (2)

Warm standby architecture opinions

From: Jeff Frost <jeff(at)frostconsultingllc(dot)com>
To: sfpug(at)postgresql(dot)org
Subject: Warm standby architecture opinions
Date: 2005-04-16 00:53:04
Message-ID: Pine.LNX.4.62.0504151743100.27293@discord.dyndns.org (view raw or flat)
Thread:
Lists: sfpug
We are currently working on a warm standby architecture for a Postgresql 8.0.1 
system.  I'm interested in soliciting opinions from the list on which way you 
might go.   The size of the DATA dir is about 4.4GB and the db is constantly 
writing, though I would not necessarily call it write intensive.  We intend to 
use heartbeat to automate the failover and failback, but what I am having a 
hard time deciding on is the method of keeping the backup server in sync.

The way I see it, we have the following options:

* pg_dump | psql on the backup server
* slony
* SELECT pg_start_backup('label'); rsync -aP /usr/local/pgsql/data
 	backup-server:/usr/local/pgsql/data; SELECT pg_stop_backup();
* setup wal archiving, SELECT pg_start_backup('label'); rsync -aP
 	/usr/local/pgsql/data backup-server:/usr/local/pgsql/data; SELECT
 	pg_stop_backup(); rsync or scp wal archives
* storing the data dir on shared media
* some hybrid method that we have yet to think of

The servers will have Gb connections with one another.

pg_dump | psql would be awful as it dumps the entire db every time.

Slony seems too cumbersome as it is table based and you have to reteach 
developers how to change schema and they tend to change the schema on this DB 
semi-often.

The rsync and wal methods appear the best, though I'm not sure which should be 
preferred.  It probably depends on how helpful rsync's block checksuming 
algorithm turns out to be with a postgres data dir.

Please let me know your opinions and experiences with this in the past.  Also, 
let me know if I should solicit opinions for this on another postgres forum.

-- 
Jeff Frost, Owner 	<jeff(at)frostconsultingllc(dot)com>
Frost Consulting, LLC 	http://www.frostconsultingllc.com/
Phone: 650-780-7908	FAX: 650-649-1954

Responses

sfpug by date

Next:From: Steven Ericsson-ZenithDate: 2005-04-16 01:26:48
Subject: Re: Schema visualization tools?
Previous:From: Javier SolteroDate: 2005-04-16 00:10:32
Subject: Re: Schema visualization tools?

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group