Migrating to PG 9.2 (LONG, SORRY)

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!