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
Views: Raw Message | Whole Thread | Download mbox | Resend email
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

Browse sfpug by date

  From Date Subject
Next Message Steven Ericsson-Zenith 2005-04-16 01:26:48 Re: Schema visualization tools?
Previous Message Javier Soltero 2005-04-16 00:10:32 Re: Schema visualization tools?