Skip site navigation (1) Skip section navigation (2)

Re: warm standby examples.

From: Erik Jones <erik(at)myemma(dot)com>
To: Steve Clark <sclark(at)netwolves(dot)com>
Cc: pgsql <pgsql-general(at)postgresql(dot)org>
Subject: Re: warm standby examples.
Date: 2008-01-31 19:24:47
Message-ID: CCB3F647-4BEE-44E2-AC2C-69F92EE0252E@myemma.com (view raw or flat)
Thread:
Lists: pgsql-general
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(at)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(at)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(at)myemma(dot)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




In response to

Responses

pgsql-general by date

Next:From: Erik JonesDate: 2008-01-31 19:26:24
Subject: Re: Recover by Log
Previous:From: HugoDate: 2008-01-31 19:16:48
Subject: variable table names in trigger functions

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group