Re: warm standby examples. - Mailing list pgsql-general

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 postgres@sbhost:/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/*
> postgres@standbyhost:/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®
> erik@myemma.com
> 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.