Re: Warm standby architecture opinions

On Mon, 18 Apr 2005, Josh Berkus wrote:

>> * 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
>
> PITR is an excellent option. If this is supposed to be just a warm standby
> server which replicates 100% of the parent server, and will not be used for
> (for example) a temporary master during an OS/PG/system upgrade, then PITR is
> probably the better option. Certainly it's simpler.

What type of warm standby would never become a temporary master? I was
thinking to script up which db server is the master at a particular time using
heartbeat and some things I've done in the past, then whichever is the master
creates the WAL archives.

>> * some hybrid method that we have yet to think of
> Such as using an advanced filesystem or storage device that allows
> transactional instant snapshots of filesystems for archive purposes?

That sounds neat and expensive! :-)

>
> I think you need to figure out your goals for this redundancy solution:
>
> 1) What risks are you trying to mitigate?
> 2) What equipment do you have/can you afford?
> 3) How is your database currently being used?

1) If the master DB server goes down, we want to be able to bring up the warm
standby in minutes, preferably seconds. We're running a db driven website
which has lots of reads and writes

2) We can afford a semi decent dual processor Dell with 6 disks in it and a
PERC RAID controller. The warm standby is likely to be two single disks in
another dual processor Dell. We will also have access to a Dell NAS device
with Gb connectivity.

3) It's used for reads and writes on a busy website. I would say it's a bit
more read intensive than write intensive, but that could change. As usual
with these things, management wants maximum uptime.

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