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