Hello, everyone. I've brought this up before, but it takes us quite a bit of
time to orchestrate this on our back-end for a variety of very good reasons.
Now, we're going to be migrating to PG 9.2 in the near future, and I've been
asked to vet my plan to ensure that I'm not talking crazy talk (or, you know,
to find out that I am, and ensure that I truly understand what I think I
understand before I screw over our infrastructure...).
Background:
Single PG server hosts several hundred databases on a single cluster.
Performance was hideous until I realized that autovacuum naptime was too low.
Since I turned that up (from 3 seconds, 3 workers on a 16-proc box hosting
hundreds of databases), performance has increased drastically, to say the
least. We're running 8.4.4.
There's a 2nd PG server (for demo and training purposes) which has nothing to
do with the above, logically speaking.
Hopeful Future:
Single production PG server on v9.2.x (latest 9.2) with replication enabled.
That 2nd PG server I was talking about has 2 separate postmasters on it. The
one on 5432 will be replicating from the production server. The one on the
other port is serving up the demo/training data, and I don't care to replicate
that. My reasoning on port 5432 for the replication stuff is in case the
production server goes down, I merely point to the promoted replicant, and all
my configurations looking for 5432 do not need to be changed.
I do this via steps listed below in "Initial replication."
Ok. I now have 2 PG servers replicating my production data at my data center.
For one further step, I need to replicate offsite for disaster purposes.
Basically repeat the replication steps for the first set. Specifically, I do
this via steps listed in "Offsite replication."
Now I have a happy replicated environment which allows me to do individual
pg_dump on each server without having to move many gigabytes of PG dump files
offsite for disaster purposes.
QUESTIONS:
Do the steps below (specifically the cascading part) look accurate?
Anyone have recommendations for companies you would use to pay for additional
vetting?
Thanks in advance.
Scott Whitney
PS: I have written a multi-proc script (in Python, Linux specific at the
moment) for pg_dump that you can use to pg_dump and restore said dumps. If
anyone's interested, contact me directly. It drastically cuts down the time it
takes pg to back up my cluster.
Initial Replication:
1)Add to pg_hba.conf on master:
host replication postgres my IP/32 trust
2) Configure in postgresql.conf on master:
wal_level = hot_standby
max_wal_senders = 5
wal_keep_segments = SOME NUMBER I HAVE TO DETERMINE BASED ON HOW LONG THE
BACKUP ETC TAKES.
# Maybe do this if the above is not high enough
#archive_mode = on
#archive_command = 'cp %p /path_to/archive/%f'
3) Add to postgresql.conf on standby:
hot_standby = on
4) Create a recovery.conf on the standby (in $PGDATA):
standby_mode = 'on'
primary_conninfo = 'host=master IP port=5432 user=postgres'
# Set this up if I want auto-failover (and, of course, setup something to
create that file)
#trigger_file = '/path_to/trigger'
# Set this up if I setup the archive stuff above.
#restore_command = 'cp /path_to/archive/%f "%p"'
5)Do my backup from the master:
$ psql -c "SELECT pg_start_backup('label', true)"
$ rsync -a ${PGDATA}/ standby:/srv/pgsql/standby/ --exclude postmaster.pid
$ psql -c "SELECT pg_stop_backup()"
6) Start pg on the standby and watch replication goodness.
Offsite replication:
1)Add to pg_hba.conf on cascading standby at data center:
host replication postgres IP of offsite server/32 trust
2)Add to postgresql.conf on cascading standby at data center:
wal_level = hot_standby
max_wal_senders = 5
wal_keep_segments = SOME NUMBER I HAVE TO DETERMINE BASED ON HOW LONG THE
BACKUP ETC TAKES.
# Maybe do this if the above is not high enough
#archive_mode = on
#archive_command = 'cp %p /path_to/archive/%f'
3)Add to postgresql.conf on offsite standby:
hot_standby = on
4)Create a recovery.conf on the offsite standby (in $PGDATA):
standby_mode = 'on'
primary_conninfo = 'host=data center STANDBY IP port=5432 user=postgres'
# Set this up if I setup the archive stuff above.
#restore_command = 'cp /path_to/archive/%f "%p"'
5)Do my backup from the STANDBY AT THE DATA CETNER
$ psql -c "SELECT pg_start_backup('label', true)"
$ rsync -a ${PGDATA}/ /dev/myUSBstick/ --exclude postmaster.pid
$ psql -c "SELECT pg_stop_backup()"
6)Take the USB stick from the data center to my office and load data
7)Start pg on offsite standby and taste the sinfully chocolately replication
goodness!
--
Sent via pgsql-admin mailing list ([email protected])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin
