Pgsql Warm Standby Server Setups

Setting Up a Warm StandBy Server Below is an example or just an introduction of how to setup a Warm Stand By Server for a Stand Alone Server on PostgreSQL 8.3 using pg_standby , postgres@ashokraj:~> ls total 8 drwxr-xr-x 3 postgres users 4096 2008-12-29 15:29 basebkp drwx------ 11 postgres users 4096 2008-12-29 16:29 data As shown above I am having my stand alone postgres server running at data directory “data” for which I am going to start a warm standby server on the same machine • • • • • • • •

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@ashokraj:~> 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@ashokraj:~> pg_ctl -D server starting

data start

3) Create some objects postgres@ashokraj:~> 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@ashokraj:~/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@ashokraj:~> cd basebkp postgres@ashokraj:~/basebkp> cp -r /home/postgres/data . postgres@ashokraj:~/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@ashokraj:~/basebkp> du -sh 369M data

data

postgres@ashokraj:~/basebkp> mv data/ standby postgres@ashokraj:~/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@ashokraj:~/basebkp> vi standby/recovery.conf postgres@ashokraj:~/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@ashokraj:~/basebkp> vi standby/postgresql.conf port = 5433 ##change port postgres@ashokraj:~/basebkp/standby> rm -f postmaster.pid

Start the Stand By Server postgres@ashokraj:~/basebkp> pg_ctl -D standby start server starting postgres@ashokraj:~/basebkp> psql -p 5433 -l psql: FATAL: the database system is starting up

View LOG files of the Stand By Server postgres@ashokraj:~> less ~/basebkp/standby/pg_log/postgresql2008-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 Trigger file : /tmp/pgsql.trigger.5432 Waiting for WAL file : 00000001000000000000000C.00621394.backup WAL file path : /d0/data/archive_backups/00000001000000000000000C.00621394.backup Restoring to... : pg_xlog/RECOVERYHISTORY Sleep interval : 2 seconds Max wait interval : 0 forever Command for restore : ln -s -f "/d0/data/archive_backups/00000001000000000000000C.00621394.backu p" "pg_xlog/RECOVERYHISTORY" Keep archive history : 000000000000000000000000 and later

WAL WAL WAL WAL WAL

file file file file file

not not not not not

present present present present present

yet. yet. yet. yet. yet.

Checking Checking Checking Checking Checking

for for for for for

trigger trigger trigger trigger trigger

file... file... file... file... file...

WAL file not present yet. Checking for trigger WAL file not present yet. Checking for trigger WAL file not present yet. Checking for trigger WAL file not present yet. Checking for trigger running restore : OK [3263 4958acc1.cbf 18]LOG: restored log file "000000010000000000000016" from archive

file... file... file... file...

Trigger file : /tmp/pgsql.trigger.5432 Waiting for WAL file : 000000010000000000000017 WAL file path : /d0/data/archive_backups/000000010000000000000017 Restoring to... : pg_xlog/RECOVERYXLOG Sleep interval : 2 seconds Max wait interval : 0 forever Command for restore : ln -s -f "/d0/data/archive_backups/000000010000000000000017" "pg_xlog/RECOVERYXLOG" Keep archive history : 00000001000000000000000C and later WAL file not present yet. Checking for trigger file... running restore : OK [3263 4958acc1.cbf 19]LOG: restored log file "000000010000000000000017" from archive Trigger file : /tmp/pgsql.trigger.5432 Waiting for WAL file : 000000010000000000000018 WAL file path : /d0/data/archive_backups/000000010000000000000018 Restoring to... : pg_xlog/RECOVERYXLOG Sleep interval : 2 seconds Max wait interval : 0 forever Command for restore : ln -s -f "/d0/data/archive_backups/000000010000000000000018" "pg_xlog/RECOVERYXLOG" Keep archive history : 00000001000000000000000C and later WAL file not present yet. Checking for trigger file... running restore : OK [3263 4958acc1.cbf 20]LOG: restored log file "000000010000000000000018" from archive Trigger file Waiting for WAL file WAL file path

: /tmp/pgsql.trigger.5432 : 000000010000000000000019 :

/d0/data/archive_backups/000000010000000000000019 Restoring to... : pg_xlog/RECOVERYXLOG Sleep interval : 2 seconds Max wait interval : 0 forever Command for restore : ln -s -f "/d0/data/archive_backups/000000010000000000000019" "pg_xlog/RECOVERYXLOG" Keep archive history : 00000001000000000000000C and later WAL file not present yet. Checking for trigger file... WAL file not present yet. Checking for trigger file... WAL file not present yet. Checking for trigger file...

Stop Stand Alone Server and Use the server as a Live DB server postgres@ashokraj:~> touch /tmp/pgsql.trigger.5432 postgres@ashokraj:~/basebkp/standby/pg_log> less postgresql2008-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 Sleep interval : 2 seconds Max wait interval : 0 forever Command for restore : ln -s -f "/d0/data/archive_backups/000000010000000000000019" "pg_xlog/RECOVERYXLOG" Keep archive history : 000000000000000000000000 and later running restore : OK [12107 4958afea.2f4b 25]LOG: restored log file "000000010000000000000019" from archive Trigger file Waiting for WAL file

: /tmp/pgsql.trigger.5432 : 00000002.history

WAL file path : /d0/data/archive_backups/00000002.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/00000002.history" "pg_xlog/RECOVERYHISTORY" Keep archive history : 000000000000000000000000 and later running restore : OK[12107 4958afea.2f4b 26]LOG: selected new timeline ID: 2 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[12107 4958afea.2f4b 27]LOG: archive recovery complete [13433 4958b05b.3479 1]LOG: autovacuum launcher started [12100 4958afe9.2f44 1]LOG: database system is ready to accept connections [13667 4958b077.3563 3]LOG: statement: SELECT pg_catalog.quote_ident(datname) FROM pg_catalog.pg_database WHERE substring(pg_catalog.quote_ident(datname),1,3)='new' LIMIT 1000 [13667 4958b077.3563 4]LOG: duration: 95.414 ms

postgres@ashokraj:~> psql -p 5432 -c "select count(1) from chk12" -U postgres count ------11270 (1 row) postgres@ashokraj:~> psql -p 5433 -c "select count(1) from chk12" -U postgres count ------11270

(1 row) postgres@ashokraj:~> psql -p 5432 -c "select count(1) from chktable" -U postgres count ------48 (1 row) postgres@ashokraj:~> psql -p 5433 -c "select count(1) from chktable" -U postgres count ------48 (1 row) postgres@ashokraj:~> psql -p 5433 -c "select count(1) from classproc" -U postgres count -------484318 (1 row) postgres@ashokraj:~> psql -p 5432 -c "select count(1) from classproc" -U postgres count -------484318 (1 row) postgres@ashokraj:~> psql -p 5432 -c "select count(1) from large" -U postgres count ------8572 (1 row) postgres@ashokraj:~> psql -p 5433 -c "select count(1) from large" -U postgres count ------8572 (1 row)