pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedMigrating to PG 9.2 (LONG, SORRY)
2+ messages / 2 participants
[nested] [flat]
* Migrating to PG 9.2 (LONG, SORRY)
@ 2012-10-03 16:55 Scott Whitney <scott@journyx.com>
0 siblings, 1 reply; 2+ messages in thread
From: Scott Whitney @ 2012-10-03 16:55 UTC (permalink / raw)
To: 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 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!
^ permalink raw reply [nested|flat] 2+ messages in thread
* Re: Migrating to PG 9.2 (LONG, SORRY)
@ 2012-10-04 01:18 Stephen Frost <sfrost@snowman.net>
parent: Scott Whitney <scott@journyx.com>
0 siblings, 0 replies; 2+ messages in thread
From: Stephen Frost @ 2012-10-04 01:18 UTC (permalink / raw)
To: Scott Whitney <swhitney@journyx.com>; +Cc: pgsql-admin
* Scott Whitney (scott@journyx.com) wrote:
> 1)Add to pg_hba.conf on master:
> host replication postgres my IP/32 trust
Never use 'trust'. At least use 'md5', all that requires is putting a
password into your replication config.
> 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'
You should definitely have a mechanism for the WALs to be transferred to
the replica(s) on a regular basis. Don't just rely on the replication
connection. With this, you don't have to worry as much about the
wal_keep_segments option above...
> 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'
In primary_conninfo is where you'd put the password for md5 auth.
> 5)Do my backup from the STANDBY AT THE DATA CETNER
I think the typical approach is to use the same copy of the master that
you did for the intial slave when doing the cascaded slave..
Regardless, and with all of this, test, test, test.
Thanks,
Stephen
Attachments:
signature.asc (application/pgp-signature, 197B) download
^ permalink raw reply [nested|flat] 2+ messages in thread
end of thread, other threads:[~2012-10-04 01:18 UTC | newest]
Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2012-10-03 16:55 Migrating to PG 9.2 (LONG, SORRY) Scott Whitney <scott@journyx.com>
2012-10-04 01:18 ` Stephen Frost <sfrost@snowman.net>
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