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, 易做图 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;
