Erik Jones wrote:
On Jan 31, 2008, at 10:04 AM, Steve Clark wrote:
Hello List,
I am going to be setting up a warm standby postgresql 8.2.5 high
availability 2 server system. I was
wondering if anybody that has done this can share some scripts,
pertinent postgresql.conf entries,
etc so I don't have to reinvent the wheel. I have read the manual a
couple of times and it is a lot to
pull together.
Anything would be greatly appreciated.
The complexity in the docs comes from explaining what everything is
and how it all works. There are a couple available options to you:
use the walmgr.py portion of the Skype's SkyTools package with will
handle PITR backups from a primary to a single slave or manually,
I'll cover manually here. To actually get a warm standby up is
actually a pretty simple process.
Pre-process recommendations:
a.) Use pg_standby for your restore_command in the recovery.conf file
on the standby
b.) Set up your standby host's environment and directory structure
exactly the same as your primary. Otherwise you'll need to spend
time changing any symlinks you've created on the primary for xlogs,
tablespaces, or whatnot which is really just opportunity for error.
c.) Pre-configure both the postgresql.conf and recovery.conf files
for your standby. I usually keep all of my different config files
for all of my different servers in a single, version-controlled
directory that I can then check out and symlink to. Again,
consistent environment & directory setups make symlinks your best
friend.
d.) Use ssh keys for simply, and safely, transferring files between
hosts.
e.) Follow all of the advice in the manual wrt handling errors.
1. Set archive_command in your postgresql.conf, rysnc is a popular
choice or you can just use one of the examples from the docs. I use:
rsync -a %p [EMAIL PROTECTED]:/path/to/wal_archive/%f
2. Reload your config -- either: SELECT pg_reload_conf(); from psql
or: pg_ctl reload -D data_dir/
3. Verify that the WALs are being shipped to their destination.
4. In psql, SELECT pg_start_backup('some_label');
5. Run your base backup. Again, rsync is good for this with
something as simple as: rsync -a --progress /path/to/data_dir/*
[EMAIL PROTECTED]:/path/to/data_dir/
I'd suggest running this in a screen term window, the --progress
flag will let you watch to see how far along the rsync is. The -a
flag will preserve symlinks as well as all file permissions & ownership.
6. In psql, SELECT pg_stop_backup();
-- this drops a file to be archived that will have the same name as
the first WAL shipped after the call to pg_start_backup() with
a .backup suffix. Inside will be the start & stop WAL records
defining the range of WAL files needed to be replayed before you can
consider bringing the standby out of recovery.
7. Drop in, or symlink, your recovery.conf file in the standby's
data_dir.
-- The restore command should use pg_standby (it's help/README are
simple and to the point). I'd recommend redirecting all output from
pg_standby to a log file that you can then watch to verify that
everything is working correctly once you've started things.
8. Drop in, or symlink, your standby's postgresql.conf file.
8 a.) If you don't symlink your pg_xlog directory to write WALs to a
separate drive, you can safely delete everything under data_dir/
pg_xlog on the standby host.
9. Start the standby db server with a normal: pg_ctl start -D /path/
to/data_dir/
10. run a: tail -f on your standby log and watch to make sure that
it's replaying logs. If everything's cool you'll see some info on
each WAL file, in order, that the standby looks for along with
'success' messages. If it can't find the files for some reason,
you'll see repeated messages like: 'WAL file not present yet.
Checking for trigger file...' (assuming you set up pg_standby to look
for a trigger file in your recovery_command).
Execute this entire process at least a couple times, bringing up the
standby into normal operations mode once it's played through all of
the necessary WAL files (as noted in the .backup file) so that you
can connect to it and verify that everything looks good, before doing
all of this and leaving it running indefinitely. Once you do it a
couple times, it becomes dirt simple. If you have any questions
about any of this, don't hesitate to ask.
Erik Jones
DBA | Emma®
[EMAIL PROTECTED]
800.595.4401 or 615.292.5888
615.292.0777 (fax)
Emma helps organizations everywhere communicate & market in style.
Visit us online at http://www.myemma.com
Thanks much Erik - this is exactly what I was looking for.
---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?
http://archives.postgresql.org/
