Postgres 9.2 Replication Configuration on CentOS 5

Master server configuration

Edit postgresql.conf

Add variables

archive_mode = on
archive_command = 'cp "%p" /data/pgsql/data/pg_xlog/archive_status/"%f"'
archive_timeout = 500
wal_level = 'hot_standby'
max_wal_senders=1
wal_keep_segments=50

Edit pg_hba.conf

Add the following line replace this ip with your slave host name

host     replication     postgres       192.168.1.15/32     trust

Next thing we need to do is take data snapshot of data from master and then move that to slave server

[postgres@zwc ~]$ psql -c "select pg_start_backup('replbackup');"
 pg_start_backup 
-----------------
 0/64000020
(1 row)
[postgres@zwc ~]$ tar -cfP pg_backup.tar data
[postgres@zwc ~]$ psql -c "select pg_stop_backup();"
NOTICE:  pg_stop_backup complete, all required WAL segments have been archived
 pg_stop_backup 
----------------
 0/640000E0
(1 row)

Move this data to slave server

[postgres@zwc ~]$ scp pg_backup.tar postgres@192.168.1.15:/tmp

Slave server configuration

Move the existing data directory to a new folder

[postgres@pgstandby ~]$ pgstop
[postgres@pgstandby ~]$ mv data data.old

Unzip master server data snapshot file that is copied into this server

[postgres@pgstandby ~]$ tar -xvf /tmp/pg_backup.tar 

Remove postmaster.pid so standby server does not see the primary server’s pid as its own

[postgres@pgstandby data]$ rm -f postmaster.pid 

Edit postgresql.conf

Add variables

hot_standby = on

Edit recovery.conf file

Add variables

[postgres@pgstandby data]$ cp -rp ../share/recovery.conf.sample recovery.conf

standby_mode = on
primary_conninfo = 'host=zwc port=1521'

Start slave database server

To test replication, simple insert into a table on master server and query the same from slave server

[postgres@zwc ~]$ psql -h zwc -U zwc -d testdb
Password for user zwc: 
psql (9.2.4)
Type "help" for help.

testdb=> \dt+
                       List of relations
 Schema |   Name    | Type  | Owner |    Size    | Description 
--------+-----------+-------+-------+------------+-------------
 public | orderinfo | table | zwc   | 8192 bytes | 
 public | t01       | table | zwc   | 8192 bytes | 
(2 rows)

testdb=> create table t02(id integer);
CREATE TABLE
testdb=> \dt+
                       List of relations
 Schema |   Name    | Type  | Owner |    Size    | Description 
--------+-----------+-------+-------+------------+-------------
 public | orderinfo | table | zwc   | 8192 bytes | 
 public | t01       | table | zwc   | 8192 bytes | 
 public | t02       | table | zwc   | 0 bytes    | 
(3 rows)

testdb=> \q
[postgres@zwc ~]$ 
[postgres@zwc ~]$ psql -h pgstandby -U zwc -d testdb
Password for user zwc: 
psql (9.2.4)
Type "help" for help.

testdb=> \dt+
                       List of relations
 Schema |   Name    | Type  | Owner |    Size    | Description 
--------+-----------+-------+-------+------------+-------------
 public | orderinfo | table | zwc   | 8192 bytes | 
 public | t01       | table | zwc   | 8192 bytes | 
 public | t02       | table | zwc   | 0 bytes    | 
(3 rows)

testdb=> insert into t02 values(1),(2);
ERROR:  cannot execute INSERT in a read-only transaction
testdb=> \q
[postgres@zwc ~]$ psql -h zwc -U zwc -d testdb
Password for user zwc: 
psql (9.2.4)
Type "help" for help.

testdb=> insert into t02 values(1),(2);
INSERT 0 2
testdb=> \q
[postgres@zwc ~]$ psql -h pgstandby -U zwc -d testdb
Password for user zwc: 
psql (9.2.4)
Type "help" for help.

testdb=> select * from t02;
 id 
----
  1
  2
(2 rows)

testdb=> 

Standby database log

STATEMENT:  insert into t02 values(1),(2);
LOG:  connection received: host=192.168.1.13 port=27595
LOG:  connection received: host=192.168.1.13 port=27596
LOG:  connection authorized: user=zwc database=testdb
LOG:  restartpoint starting: time
LOG:  restartpoint complete: wrote 21 buffers (0.5%); 0 transaction log file(s) added, 0 removed, 0 recycled; write=2.019 s, sync=0.009 s, total=2.031 s; sync files=17, longest=0.005 s, average=0.000 s
LOG:  recovery restart point at 0/7C015D50
DETAIL:  last completed transaction was at log time 2013-09-03 22:03:22.099392+08
ERROR:  cannot execute DELETE in a read-only transaction
STATEMENT:  delete from t02 where id=1;