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 Name | Owner | Encoding -----------+----------+---------- postgres | postgres | UTF8 template0 | postgres | UTF8 template1 | postgres | UTF8 (3 rows) [local]:5432:postgres# create database newdb ; CREATE DATABASE [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 ; SELECT [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 ; SELECT as select * from
Start the Backup Mode
[local]:5432:postgres# select pg_start_backup('L1') ; pg_start_backup ----------------- 0/C621394 (1 row)
Take the Base Backup
postgres@px:~> cd basebkp postgres@px:~/basebkp> cp -r /home/postgres/data . postgres@px:~/basebkp> total 12 drwxr-xr-x 3 postgres users drwxr-xr-x 5 postgres users drwx------ 11 postgres users l 4096 2008-12-29 15:28 ./ 4096 2008-12-29 15:21 ../ 4096 2008-12-29 15:28 data/ postgres@px:~/basebkp> du -sh 369M data data postgres@px:~/basebkp> mv data/ standby postgres@px:~/basebkp> ls standby
Stop the Backup Mode
[local]:5432:postgres# CREATE TABLE chktable as select * from pg_tables ; SELECT [local]:5432:postgres# CREATE TABLE chk12 as select * from pg_tables natural join pg_class ; SELECT [local]:5432:postgres# select pg_stop_backup() ; pg_stop_backup ---------------- 0/1547E284 (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 postmaster.pid <strong>Start the Stand By Server</strong> postgres@px:~/basebkp> pg_ctl -D standby start server starting postgres@px:~/basebkp> psql -p 5433 -l psql: FATAL: the database system is starting up
View LOG files of the Stand By Server
postgres@px:~> less ~/basebkp/standby/pg_log/postgresql- 2008-12-29_162601.log [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 : /d0/data/archive_backups/00000001.history Restoring to... : pg_xlog/RECOVERYHISTORY Sleep interval : 2 seconds Max wait interval : 0 forever Command for restore : ln -s -f "/d0/data/archive_backups/00000001.history" "pg_xlog/RECOVERYHISTORY" Keep archive history : 000000000000000000000000 and later running restore : OK
Stop Stand Alone Server and Use the server as a Live DB server
postgres@px:~> touch /tmp/pgsql.trigger.5432 postgres@px:~/basebkp/standby/pg_log> less postgresql- 2008-12-29_163930.log 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 : /d0/data/archive_backups/000000010000000000000019 Restoring to... : pg_xlog/RECOVERYXLOG
Now lets check if everything is in place and you are all set