-- Title : [PGS9.2] How to implement a Streaming Replication - ver.dBRang
-- Reference : opensourcedbms.com, splee75.tistory.com
-- Key word : postgresql streaming replication 스트리밍 복제 pg_basebackup pg_ctl remote basebackup
****************************************************************************************************************/
-- Physical Configuration
o Master IP : 192.168.0.10
o Slave IP : 192.168.0.20
o Version : PostgreSQL 9.2.4
o Install Directory : /home/postgres/pgsql
o Data Directory : /databases/data
o WAL Directory : /databases/ARCHIVE
o Backup Directory : /datbases/backup/cluster (base backup output)
-- Install PostgreSQL
o Ref. : http://dbrang.tistory.com/732
o Ref. : http://dbrang.tistory.com/756
-- Create Tablespace (on Master)
create table ttt (a int, b int) tablespace ts_data0;
insert into ttt values (1,1);
insert into ttt values (2,2);
wal_level = hot_standby
max_wal_senders = 2 ## Count of Slave Server + 1
wal_keep_segments = 50
-- Modify pg_hba.conf (on Master)
host replication postgres 192.168.0.10/32 trust ## append this line, IP is Master's one.
host replication postgres 192.168.0.20/32 trust ## append this line, IP is Slave's one.
-- Restart PostgreSQL (on Master)
$ pg_ctl restart
-- Execute Base Backup
## if you have a only default tablespace..
$ pg_basebackup -h 192.168.0.10 -U postgres -D /databases/backup/cluster -xlog -c fast -P
## if you have some user tablespace.
$ pg_basebackup -h 192.168.0.10 -U postgres -D /databases/backup/cluster -xlog -c fast -P -Ft
-- Move base backup output to Slave (on Master)
$ rsync -ra /databases/backup/cluster/* root@192.168.0.20:/databases/backup/cluster/
The authenticity of host '192.168.0.20 (192.168.0.21)' can't be established.
RSA key fingerprint is 53:1a:af:46:25:f4:2f:af:0d:28:50:2c:50:8f:34:e0.
Are you sure you want to continue connecting (yes/no)? yes
Warning: Permanently added '192.168.0.20' (RSA) to the list of known hosts.
root@192.168.0.20's password:
-- Confirm moved files (on Slave)
$ ll /databases/backup/cluster
-rw-rw-r-- 1 postgres postgres 10752 2013-12-02 10:25 16397.tar ## user tablespace
-rw-rw-r-- 1 postgres postgres 54147584 2013-12-02 10:25 base.tar ## base files
-- Shutdown (on Slave)
## Server Status is gonna be No Server Running.
$ pg_ctl stop
-- Replace files from Moved ones made by pg_basebackup
## Modify Data Directory name.
$ mv /home/postgres/pgsql/data /home/postgres/pgsql/data_old
## Make New Data Directory
$ mkdir /home/postgres/pgsql/data
## Ensure user tablespace and archive directory
$ ll /databases/data
$ ll /databases/ARCHIVE
## Untar
$ cd /home/postgres/pgsql/data
$ tar -xvf /databases/backup/cluster/base.tar
$ cd /databases/data
$ tar -xvf /databases/backup/cluster/16397.tar
-- Modify postgresql.conf (on Slave)
wal_level = archive ## or minimal(?)
max_wal_sender = 0
wal_keep_segments = 0
archive_mode = on
archive_command = 'cp %p /databases/archive/%f'
hot_standby = on
-- Make recovery.conf (on Slave)
standby_mode = on
primary_conninfo = 'host=192.168.0.20 port=5432'
-- Startup Slave (on Slave)
$ pg_ctl startup
## once some errors occur about directory privilege
## execute chmod command like below:
$ chmod 700 /home/postgres/pgsql/data
$ chmod 700 /databases/data
-- Ensure Syncronization
## select ttt on Master
select * from ttt;
## select ttt on Slave
select * from ttt;
## insert data on Master
insert into ttt values (3,3);
## ensure syncronization on Slave
select * from ttt;
-- Insert Data on Slave
## insert data on Slave
insert into ttt values (4,4);
STATEMENT: insert into ttt values (4,4);
ERROR: cannot execute INSERT in a read-only transaction
-- Implement Fail Over
## stop Master on Master
$ pg_ctl stop
## make DML on slave
insert into ttt values (5,5);
STATEMENT: insert into ttt values (4,4);
ERROR: cannot execute INSERT in a read-only transaction
## Promote Slave on Slave
$ pg_ctl promote
LOG: archive recovery complete
LOG: database system is ready to accept connections
LOG: autovacuum launcher started
## make DML on Slave
insert into ttt values (5,5);
INSERT 0 1
-- Confirm recovery.conf
$ ll recovery.*
-rw-rw-r-- 1 postgres postgres 70 2013-12-02 12:00 recovery.done