Re: Backup/disaster recovery and bandwidth (long)

On 04/25/2012 09:11 AM, Scott Whitney wrote:
> ...
> 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