Warm_standby

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&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:~> 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

Advertisements