Archive for category Standby_server


For users that are on previous versions of Postgres 8.x there is an option for warm standby that is available. In case there is an issue with the primary server the standby server can be activated to be used as primary and data loss is prevented. In order to setup warm standby follow the steps below:

Steps to setup warm standby

–> Configure the Standalone Db server
–> Start the Back Up Mode
–> Take The Base Back Up
–> Stop The Back Up Mode
–> Configure the Stand By Db server
–> Start the Start By Server
–> View The Logs
–> Switching to The Stand by server


Configure the Stand Alone DB server
1) Edit the postgresql.conf file to activate the archive mode of the server and define
where to archive the transactional logs

postgres@px:~> vi data/postgresql.conf
archive_mode = on
archive_command = 'cp -i %p /d0/data/archive_backups/%f'
archive_timeout = 300s

2) Start the DB server

postgres@px:~> pg_ctl -D
server starting
data start

3) Create some objects

postgres@px:~> psql
Welcome to psql 8.3.4, the PostgreSQL interactive terminal.
[local]:5432:postgres# \l
List of databases
| Owner
| Encoding
postgres | postgres | UTF8
template0 | postgres | UTF8
template1 | postgres | UTF8
(3 rows)
[local]:5432:postgres# create database newdb ;
[local]:5432:postgres# \c newdb
You are now connected to database "newdb".
[local]:5432:newdb# CREATE TABLE sample as select * from
pg_class,pg_namespace ;
[local]:5432:newdb# \dt
List of relations
Schema | Name | Type | Owner
public | sample | table | postgres
(1 row)
postgres@px:~/data> psql
[local]:5432:postgres# CREATE TABLE classproc
pg_class natural join pg_proc ;
as select * from


Start the Backup Mode

[local]:5432:postgres# select pg_start_backup('L1') ;
(1 row)

Take the Base Backup

postgres@px:~> cd basebkp
postgres@px:~/basebkp> cp -r /home/postgres/data .
total 12
drwxr-xr-x 3 postgres users
drwxr-xr-x 5 postgres users
drwx------ 11 postgres users
4096 2008-12-29 15:28 ./
4096 2008-12-29 15:21 ../
4096 2008-12-29 15:28 data/
postgres@px:~/basebkp> du -sh
postgres@px:~/basebkp> mv data/ standby
postgres@px:~/basebkp> ls


Stop the Backup Mode

[local]:5432:postgres# CREATE TABLE chktable as select *
from pg_tables ;
[local]:5432:postgres# CREATE TABLE chk12 as select * from
pg_tables natural join pg_class ;
[local]:5432:postgres# select pg_stop_backup() ;
(1 row)


Configuring the Stand By Server

postgres@px:~/basebkp> vi standby/recovery.conf
postgres@px:~/basebkp> cat basebkp/standby/recovery.conf
restore_command = 'pg_standby -l -d -s 2 -t
/tmp/pgsql.trigger.5432 /d0/data/archive_backups %f %p %r'
postgres@px:~/basebkp> vi standby/postgresql.conf
port = 5433 ##change port
postgres@px:~/basebkp/standby> rm -f
<strong>Start the Stand By Server</strong>
postgres@px:~/basebkp&gt; pg_ctl -D standby start
server starting
postgres@px:~/basebkp&gt; psql -p 5433 -l
psql: FATAL: the database system is starting up


View LOG files of the Stand By Server

postgres@px:~&gt; less ~/basebkp/standby/pg_log/postgresql-
[3263 4958acc1.cbf 1]LOG: database system was interrupted; last
known up at 2008-12-29 15:27:28 IST
[3263 4958acc1.cbf 2]LOG: starting archive recovery
[3263 4958acc1.cbf 3]LOG: restore_command = 'pg_standby -l -d -s
2 -t /tmp/pgsql.trigger.5432 /d0/data/archive_backups %f %p %r'
Trigger file
: /tmp/pgsql.trigger.5432
Waiting for WAL file
: 00000001.history
WAL file path
Restoring to...
Sleep interval
: 2 seconds
Max wait interval
: 0 forever
Command for restore
: ln -s -f
Keep archive history
: 000000000000000000000000 and later
running restore
: OK


Stop Stand Alone Server and Use the server as a Live DB server

postgres@px:~&gt; touch /tmp/pgsql.trigger.5432
postgres@px:~/basebkp/standby/pg_log&gt; less postgresql-
WAL file not present yet. Checking for trigger file...
WAL file not present yet. Checking for trigger file...trigger
file found
[12107 4958afea.2f4b 22]LOG: could not open file
"pg_xlog/00000001000000000000001A" (log file 0, segment 26): No
such file or directory
[12107 4958afea.2f4b 23]LOG: redo done at 0/1960BAB8
[12107 4958afea.2f4b 24]LOG: last completed transaction was at
log time 2008-12-29 16:32:41.142195+05:30
Trigger file
: /tmp/pgsql.trigger.5432
Waiting for WAL file
: 000000010000000000000019
WAL file path
Restoring to...

Now lets check if everything is in place and you are all set



Leave a comment

%d bloggers like this: