Pgsql Master-slave replication

System version: Ubuntu12.04
database version:postgresql-9.1.3

:http://ftp.postgresql.org/pub/source/v9.1.3/postgresql-9.1.3.tar.gz

Master :192.168.1.10

Slave : 192.168.1.108

Master PostgreSQL Installation:

sudo apt-get  install  lib64readline-gplv2-devlib64readline6-dev libreadline-dev readline-common  libghc-readline-devsudo apt-get install zlib1g-devsudo mkdir -p /datatar  Zxvf postgresql-9.1.3.tar.gzcd postgresql-9.1.3/sudo ./configure --prefix=/data/pqsqlsudo  makesudo make installsudo mkdir -p /data/pqsql/data0sudo chown -r  postgres:postgres /data/pqsql/data0/sudo su – postgres/data/pqsql/bin/initdb   -D /data/pqsql/data0//data/pqsql/bin/pg_ctl   -D /data/pqsql/data0/  startcd /data/pqsql/data0/vim pg_hba.confhost   replication      REPUSER192.168.1.108/32        MD5 

# The last line adds the slave's IP and the user name password used to replicate

Vim postgresql.conflisten_addresses = ' * ' max_wal_senders = 1wal_level = Hot_standbyarchive_mode = Onarchive_command = ' CD ./' wal_keep_segments = 64

Note:max_wal_senders is the number of nodes in the Slave library, how many Slave libraries are set ,

Wal_level is the write ahead log parameter value , set stream replication Be sure to update this value to Hot_standby

Wal_keep_segments Default value is , is the log file size under Pg_xlog archive can also choose to shut down, the archive is scheduled for recovery, stream replication is not required

To create a user for replication:

Cd/data/pqsql/bin/[email protected]:/data/pqsql/bin$./psqlpsql (9.1.3) Type ' help ' for help. postgres=# CREATE USER repuser replicationlogin CONNECTION LIMIT 2 ENCRYPTED PASSWORD ' Repuser ';p ostgres=#\q

Restart the master database

./pg_ctl-d. /data0/restartslave installing sudo apt-get install Lib64readline-gplv2-devlib64readline6-dev libreadline-dev Readline-common Libghc-readline-devsudo apt-get Install Zlib1g-devsudo mkdir-p/datatar zxvf POSTGRESQL-9.1.3.TAR.GZCD Postgresql-9.1.3/sudo./configure--prefix=/data/pqsqlsudo makesudo make Installsudo mkdir-p/data/pqsql/data0sudo Chown-r Postgres:postgres/data/pqsql/data0/sudo su–postgres/data/pqsql/bin/initdb-d/data/pqsql/data0//data/pqsql /bin/pg_ctl-d/data/pqsql/data0/start

Make sure that you can start it properly Ok

To begin the synchronization operation:

backing up the main library (master)

Login Master Main Library Execution:

postgres=# selectpg_start_backup (' Replication work ');

open another terminal and switch to root for a packaged backup:

Cd/data/pqsql/tar ZCVF data0.tar.gz--exclude=data0/pg_xlog data0/scpdata0.tar.gz [email protected]:/tmp

go back to the master Library to execute and stop the backup operation:

postgres=# selectpg_stop_backup (), Current_timestamp;

To operate from the library:

To use a user with sudo permissions:

SUDP cp/tmp/data0.tar.gz/data/pqsql/

then use the Postgres user to stop the database service

/data/pqsql/bin/pg_ctl-d/data/pqsql/data0/stopmv data0 Data0.old

Users using sudo to decompress:

Tar zxvf data0.tar.gz

Modify with postgres user

Cd/data/pgsql/data0vim pg_hba.confhost replication Repuser192.168.1.10/32 md5vim Postgresql.confhot_standby = Oncp/data/pgsql/share/recovery.conf.sample/data/pgsql/data0/recovery.confstandby_mode = ' on ' Primary_conninfo = ' host=192.168.1.10port=5432 user=repuser password=repuser keepalives_idle=60 ' trigger_file = '/data/pgsql/data0/ postgresql.trigger.1949 '

Configure the. Pgpass file

192.168.1.10:5432:POSTGRES:REPUSER:REPUSERRM–RF PG_XLOGRM–RF Postmaster.pidmkdir Pg_xlog

start The slave database

[Email protected]:/data/pgsql/bin$./pg_ctl-d. /DATA0 startserver starting[email protected]:/data/pgsql/bin$log:database system was interrupted;last known up at 201 4-05-21 11:40:05 cstlog:creating missing WAL directory "Pg_xlog/archive_status" log:entering standby modelog:streaming R Eplication successfully connected to Primarylog:redo starts @ 0/3000020log:consistent recovery state reached at 0/40000 00log:database system is ready to accept read onlyconnections

650) this.width=650; "src=" http://s3.51cto.com/wyfs02/M02/52/7B/wKiom1RkfjHhWASkAANceSJS-Fg110.jpg "title=" 1.png " alt= "Wkiom1rkfjhhwaskaancesjs-fg110.jpg"/>

Test is normal:

on Master:

CREATE TABLE rep_test (test varchar), insert into rep_test values (' Data one '), insert into rep_test values (' some morew Ords '), insert into rep_test values (' Lalala '), insert into rep_test values (' Hellothere '), insert into rep_test values (' Bla Hblah ');

query on slave:

postgres=# select * from Rep_test; Test-----------------dataone somemore words lalala Hello there Blahblah (5 rows) postgres=#

The database can see that it has been synced over.

Primary and standby server judgment:

650) this.width=650; "src=" http://s3.51cto.com/wyfs02/M02/52/7A/wKioL1RkfvSDdrHnAANfdpeyb2k860.jpg "title=" 2.png " alt= "Wkiol1rkfvsddrhnaanfdpeyb2k860.jpg"/>

It must be the master server to see this information

650) this.width=650; "src=" http://s3.51cto.com/wyfs02/M00/52/7C/wKiom1RkftbS75xpAALlJA_WsBA785.jpg "title=" 3.png " alt= "Wkiom1rkftbs75xpaallja_wsba785.jpg"/>

It must be slave server to see this information

analog Master down ,slave switch to Master

Standby process before the host stops:

650) this.width=650; "src=" http://s3.51cto.com/wyfs02/M00/52/7C/wKiom1Rkfv-DQQH-AARFCFGi8hk055.jpg "title=" 4.png " alt= "Wkiom1rkfv-dqqh-aarfcfgi8hk055.jpg"/>

Master Downtime

[Email protected]:/data/pgsql/bin$./pg_ctl-d. /DATA0 stopwaiting for server shut down .... doneserver stopped[email protected]:/data/pgsql/bin$

650) this.width=650; "src=" http://s3.51cto.com/wyfs02/M00/52/7A/wKioL1Rkf67DAzD1AAPA2kBWjbI140.jpg "title=" 5.png " alt= "Wkiol1rkf67dazd1aapa2kbwjbi140.jpg"/>

View standby status

650) this.width=650; "src=" http://s3.51cto.com/wyfs02/M01/52/7C/wKiom1Rkf2Th4QDoAAIZGLfCrq8552.jpg "title=" 6.png " alt= "Wkiom1rkf2th4qdoaaizglfcrq8552.jpg"/>

Standby machine Switch to host

Trigger_file = '/database/pgdata/trigger.kenyon ' is configured in the recovery.conf on the standby machine to switch the standby machine into a host, just create a trigger file Trigger.kenyon , this name can be written casually.

touch/data/pgsql/data0/postgresql.trigger.1949

650) this.width=650; "src=" http://s3.51cto.com/wyfs02/M00/52/7A/wKioL1RkgBjDm_2GAAE9gLPq6AI500.jpg "title=" 7.png " alt= "Wkiol1rkgbjdm_2gaae9glpq6ai500.jpg"/>

has become production , yes, standby machine cutting mainframe is so simple.

Another obvious change is that the name of the recovery.conf file on the host now becomes recovery.done.

when the standby machine is switched to the main machine, it can be connected and used normally. At this point there is time to deal with the original master side problem.

650) this.width=650; "src=" http://s3.51cto.com/wyfs02/M02/52/7A/wKioL1RkgEDAw2RTAAVhDSj5eDU669.jpg "title=" 8.png " alt= "Wkiol1rkgedaw2rtaavhdsj5edu669.jpg"/>650) this.width=650; src= http://s3.51cto.com/wyfs02/M00/52/7C/ Wkiom1rkf_eixgelaalxy_7abxa416.jpg "title=" 9.png "alt=" Wkiom1rkf_eixgelaalxy_7abxa416.jpg "/>

now the standby machine can serve the normal, down to the original Master switch to Slave

do some things on the master now :

reate TABLE Slave (test varchar), INSERT into slave VALUES (' Welcome to Beijing ');

To prepare the recovery file on the current standby machine:

Cp/data/pqsql/share/postgresql/recovery.conf.sample/data/pqsql/data0/recovery.confrecovery_target_timeline = ' Latest ' Primary_conninfo = ' host=192.168.1.108port=5432 user=repuser password=repuser ' trigger_file = '/data/pgsql/ data0/postgresql.trigger.1949 ' Standby_mode = Onvim. /data0/postgresql.confhot_standby = Onvim. /data0/pg_hba.confhost Replication Repuser192.168.1.10/32 MD5

On The current slave, create:

Vim. Pgpass192.168.1.11:5432:postgres:repuser:repuser

then restart the first slave server

If you encounter an issue where this timeline is inconsistent:

650) this.width=650; "src=" http://s3.51cto.com/wyfs02/M01/52/7D/wKiom1RkgHeD_mgrAAHDrTHnmu0075.jpg "title=" 10.png "alt=" Wkiom1rkghed_mgraahdrthnmu0075.jpg "/>

perform the following actions on the existing master:

SCP 00000002.history [Email protected]:/data/pqsql/data0/pg_xlog

then start the existing slave host:

[Email protected]:/data/pqsql/bin$./pg_ctl-d. /DATA0 start

650) this.width=650; "src=" http://s3.51cto.com/wyfs02/M02/52/7B/wKioL1RkgUTwdqGZAAQiBy7vV5Q980.jpg "title=" 11.png "alt=" Wkiol1rkgutwdqgzaaqiby7vv5q980.jpg "/>

Successful switchover

650) this.width=650; "src=" http://s3.51cto.com/wyfs02/M02/52/7D/wKiom1RkgRaAaiqwAADlNkTOHPY785.jpg "title=" 12.png "alt=" Wkiom1rkgraaaiqwaadlnktohpy785.jpg "/>

INSERT into slave values (' Now Slave is OK ');

650) this.width=650; "src=" http://s3.51cto.com/wyfs02/M02/52/7B/wKioL1RkgbfRRjfnAAFIsju2JPo778.jpg "title=" 13.png "alt=" Wkiol1rkgbfrrjfnaafisju2jpo778.jpg "/>

To perform an insert action on an existing slave:

650) this.width=650; "src=" http://s3.51cto.com/wyfs02/M01/52/7D/wKiom1RkgWegK7vBAAEEQpOhhQw880.jpg "title=" 14.png "alt=" Wkiom1rkgwegk7vbaaeeqpohhqw880.jpg "/>

This article is from the "Sword Slave" blog, be sure to keep this source http://diudiu.blog.51cto.com/6371183/1576195

Pgsql Master-slave replication