On 04/25/2012 09:11 AM, Scott Whitney wrote:
p { margin: 0; }...
My current setup uses a single PG 8.x...
My _new_ setup will instead be 2 PG 9.x ...
It is best to specify actual major version. While 8.0.x or 9.1.x is sufficient to discuss features and capabilities, 9.1 is a different major release than 9.0, not a minor update to version 9.
I set up a 4th database server internally at my office. Each night I stop PG on my 3rd server (the local one replicating off of the master) and rsync my pg_data directory to this new 4th server. I bring up the 4th server NOT as a standby, but as a master....
Does this sound like a viable option? Or does someone have additional suggestions?
And speaking of major versions, what you really want is PostgreSQL version 9.2 with cascading replication:
http://www.postgresql.org/docs/devel/static/warm-standby.html#CASCADING-REPLICATION Unfortunately that version is currently in development/testing and will probably not be released till toward the end of the year (based on my totally uninformed guesstimate method).
With cascading replication you could maintain a constantly up-to-date local copy which you could cascade-replicate to other clusters as necessary. Whether you maintain one or more constantly updated local cascaded replicas off your primary local replica or just spin one off as necessary will be determined by how quickly you need to access the "test/dev/debug" replica. It's likely that you can come up with a pretty fast method of spinning off a cascaded replica as needed.
Note that there is no requirement for the various replicas to reside on different servers. You can run multiple clusters on a single machine and one cluster can replicate to one or more others. Not good as a backup strategy, obviously, but might be just the ticket for your needs - especially since the data copies necessary to bring up a replica are all on your local disks - no network transfer required.
You may want to experiment with a current development copy of 9.2 to see how it works (and provide bug reports to the developers). Perhaps 9.2 final will be released before your database grows too big for current solutions.
Cheers,
Steve