From: Steve Crawford <scrawford@pinpointresearch.com>
To: Scott Whitney <swhitney@journyx.com>
Cc: Scott Whitney <scott@journyx.com>
Cc: pgsql-admin@postgresql.org
Subject: Re: Backup/disaster recovery and bandwidth (long)
Date: Wed, 25 Apr 2012 10:41:38 -0700
Message-ID: <4F983752.6000506@pinpointresearch.com> (raw)
In-Reply-To: <da3b784d-86db-4b60-a6cd-149efaa58c0e@zimbra.int.journyx.com>
References: <da3b784d-86db-4b60-a6cd-149efaa58c0e@zimbra.int.journyx.com>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-admin@postgresql.org
Cc: scrawford@pinpointresearch.com, swhitney@journyx.com, scott@journyx.com
Subject: Re: Backup/disaster recovery and bandwidth (long)
In-Reply-To: <4F983752.6000506@pinpointresearch.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox