Re: Warm standby architecture opinions

Jeff,

> The way I see it, we have the following options:
>
> * pg_dump | psql on the backup server

This would be I/O, transaction, and CPU-intensive.

> * slony

Slony is an excellent option.

> * 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.

> * storing the data dir on shared media

This works pretty good too, provided that your shared media is extremely
redundant. Otherwise you've just moved your single point of failure.

> * 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?

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?

--
Josh Berkus
Aglio Database Solutions
San Francisco