반응형
/****************************************************************************************************************
-- 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

-- Set WAL Mode on
    o Ref. : http://dbrang.tistory.com/756

-- Create Tablespace (on Master)
create tablespace ts_data0
location '/databases/data';

-- Create Table and Insert Data (on Master)

create table ttt (a int, b int) tablespace ts_data0;
insert into ttt values (1,1);
insert into ttt values (2,2);

 
-- Modify postgresql.conf (on Master)

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  

 
반응형

+ Recent posts