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 INS