Migrating to PG 9.2 (LONG, SORRY) - Mailing list pgsql-admin

Hello, everyone. I've brought this up before, but it takes us quite a bit of time to orchestrate this on our back-end
fora 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
nottalking crazy talk (or, you know, to find out that I am, and ensure that I truly understand what I think I
understandbefore I screw over our infrastructure...). 

Background:
Single PG server hosts several hundred databases on a single cluster. Performance was hideous until I realized that
autovacuumnaptime 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
2separate postmasters on it. The one on 5432 will be replicating from the production server. The one on the other port
isserving up the demo/training data, and I don't care to replicate that. My reasoning on port 5432 for the replication
stuffis in case the production server goes down, I merely point to the promoted replicant, and all my configurations
lookingfor 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
offsitefor disaster purposes. Basically repeat the replication steps for the first set. Specifically, I do this via
stepslisted in "Offsite replication." 

Now I have a happy replicated environment which allows me to do individual pg_dump on each server without having to
movemany 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_dumpand restore said dumps. If anyone's interested, contact me directly. It drastically cuts down the time it takes
pgto 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!