PostgreSQL使用 pg_basebackup 搭建流復制環(huán)境

1、一 環(huán)境信息系統(tǒng)：   Red Hat Enterprise Linux Server release 6.7版本：   PostgreSQL 9.4主庫IP： 10.3.2.101  主機名：postgres1備庫IP： 10.3.2.102  主機名:postgres2備注： PostgreSQL 安裝略。備庫可以只裝數(shù)據(jù)庫，不初始化數(shù)據(jù)庫二 主庫上操作-2.1 創(chuàng)建流復制用戶CREATE USER repuser replication LOGIN CONNECTION LIMIT 5 ENCRYPTED PASSWORD '888888&

2、#39;-2.2 設置 pg_hba.conf，添加以下host all all 0.0.0.0/0 md5host   replication     repuser        10.3.2.102/32         md5  -2.3 設置主庫 postgresql.conf #checkpoint_segments = 16archive_mode = onarchive_command = '/bin/date'max_wa

3、l_senders = 3#wal_keep_segments = 16 wal_level = hot_standbyhot_standby = on 備注：僅列出主要參數(shù)，其它參數(shù)根據(jù)實際情況設置。max_wal_senders是Slave庫的節(jié)點數(shù)，有多少個slave庫就設多少。wal_level是write ahead log參數(shù)值,設置流復制務必將此值更新成hot_standby。max_wal_senders是Slave庫的節(jié)點數(shù)，有多少個slave庫就設多少,wal_level是write ahead log參數(shù)值,設置流復制務必將此值更新成hot_stand

4、bywal_keep_segments默認值是16,是PG_XLOG下的日志文件數(shù)相關(guān)參數(shù)archive也可以選擇關(guān)閉，歸檔是定時恢復用的，流復制不是必須的修改后記得重啟數(shù)據(jù)庫$ pg_ctl stop -m fast  $pg_ctl start -D $PGDATA 或 pg_ctl -D $PGDATA -l /rasdis/pgdata/pg.log start #指定輸出日志到pg.log-2.4 重載配置文件(如果數(shù)據(jù)庫服務已啟動可以使用該命令) pg93redhatB $ pg_ctl reload -D $PGDATAserver signaled三 備庫上

5、操作-3.1基礎備份復制到備庫服務器登陸備庫服務器，運行如下命令從主庫復制文件。如果備庫/rasdis/pgdata目錄有文件先需要刪除。pg_basebackup -D $PGDATA -F p -h masterdb -p masterdb_port -U repuser W如：pg_basebackup -D $PGDATA -F p -h 10.3.2.101 -p 5432 -U repuser W注：以上命令會遇到wal日志無法重定向問題，需要執(zhí)行$pg_resetxlog -f /opt/postgres9-1/data重置預寫日志下回重新配置時使用以下命令試下：pg_

6、basebackup -D /rasdis/pgdata -F p -X s -v -P -h 10.3.2.101 -p 5432 -U repuser 或pg_basebackup -D $PGDATA -F p -X stream -v -P -h 10.3.2.101 -p 5432 -U repuser-3.2：修改備庫配置信息添加recovery.conf文件。$cp /rasdis/postgresql/share/recovery.conf.sample /rasdis/pgdata/recovery.conf $ vi recovery.conf  修改以下參數(shù)st

7、andby_mode = onprimary_conninfo = 'host=10.3.2.101 port=5432 user=repuser password=888888 keepalives_idle=60 '#trigger_file = '/rasdis/pgdata/postgresql.trigger.5432' 配置.pgpass文件。在home/rasdb下創(chuàng)建 .pgpassrasdbpostgres2 $ touch .pgpassrasdbpostgres2 $ vi .pgpasspg93redhat6 $ cat .pgpass1

8、0.3.2.101:5432:replication:repuser:888888pg93redhat6 $ chmod 0600 .pgpass備注：注意 .pgpass文件權(quán)限為 0600。-3.3 啟服務  pg93redhat6 pg_root$ pg_ctl start -D $PGDATA 或pg_ctl -D $PGDATA -l /rasdis/pgdata/pg.log startserver starting-3.4 查看備庫進程         查看備庫是否有類似這個進程：-3.5 查看主庫進程查看主庫是否有類似

9、這個進程四 測試-4.1 主庫postgres=# create table test_2 (id int4,create_time timestamp(0) without time zone);CREATE TABLEpostgres=# dt List of relations Schema | Name | Type | Owner -+-+-+- public | kenyon_rep | table | rasdb public | test_2 | table | rasdb(2 rows)postgres=# insert into test_2 values(1,now();

10、INSERT 0 1postgres=# select * from test_2; id | create_time -+- 1 | 2016-07-18 19:19:36(1 row)-4.2 備庫注：在備庫中進行查詢,備庫可查詢，但不可更新數(shù)據(jù)：五主備機區(qū)別主備的區(qū)別有多種辦法去判斷 1. 通過pg_controldata輸出主機：rasdbpostgres1 pgdata$ pg_controldatapg_control version number: 942Catalog version number: 201409291Database system identifier: 63

11、06486576830085248Database cluster state: in production或用pg_controldata |grep clusterrasdbpostgres1 etc$ pg_controldata |grep clusterDatabase cluster state: in production備機：rasdbpostgres2 $ pg_controldatapg_control version number: 942Catalog version number: 201409291Database system identifier: 630648

12、6576830085248Database cluster state: in archive recovery或用pg_controldata |grep clusterrasdbpostgres2 pgdata$ pg_controldata |grep clusterDatabase cluster state: in archive recovery2.通過數(shù)據(jù)字典表pg_stat_replication 主機表中能查到記錄，備機表中是沒有的select pid,usename,application_name,client_addr,client_port,state,sync_st

13、ate from pg_stat_replication;主機：postgres=# select pid,usename,application_name,client_addr,client_port,state,sync_state from pg_stat_replication; pid | usename | application_name | client_addr | client_port | state | sync_state -+-+-+-+-+-+- 19301 | repuser | walreceiver | 10.3.2.102 | 53322 | strea

14、ming | async(1 row)備機：postgres=# select pid,usename,application_name,client_addr,client_port,state,sync_state from pg_stat_replication; pid | usename | application_name | client_addr | client_port | state | sync_state -+-+-+-+-+-+-(0 rows)-9.2版本,procpid已經(jīng)改為pid3.通過進程查看，顯示wal sender的是主機，顯示wal receiver

15、的是備機主機：rasdbpostgres1 pgdata$ ps -ef |grep postgresrasdb 19293 1 0 18:12 pts/1 00:00:00 /rasdis/postgresql/bin/postgres -D /rasdis/pgdatarasdb 19295 19293 0 18:12 ? 00:00:00 postgres: checkpointer process rasdb 19296 19293 0 18:12 ? 00:00:00 postgres: writer process rasdb 19297 19293 0 18:12 ? 00:00

16、:00 postgres: wal writer process rasdb 19298 19293 0 18:12 ? 00:00:00 postgres: autovacuum launcher process rasdb 19299 19293 0 18:12 ? 00:00:00 postgres: archiver process rasdb 19300 19293 0 18:12 ? 00:00:00 postgres: stats collector process rasdb 19301 19293 0 18:12 ? 00:00:00 postgres: wal sender

17、 process repuser 10.3.2.102(53322) streaming 0/80009B0rasdb 19345 19001 0 18:24 pts/1 00:00:00 grep postgres備機：rasdbpostgres2 $ ps -ef |grep postgresrasdb 1844 1 0 18:11 pts/0 00:00:00 /rasdis/postgresql/bin/postgres -D /rasdis/pgdatarasdb 1845 1844 0 18:11 ? 00:00:00 postgres: logger process rasdb

18、1846 1844 0 18:11 ? 00:00:00 postgres: startup process recovering 000000010000000000000008rasdb 1847 1844 0 18:11 ? 00:00:00 postgres: checkpointer process rasdb 1848 1844 0 18:11 ? 00:00:00 postgres: writer process rasdb 1849 1844 0 18:11 ? 00:00:00 postgres: stats collector process rasdb 1858 1844

19、 0 18:12 ? 00:00:00 postgres: wal receiver process streaming 0/80009B0rasdb 1892 1602 0 18:23 pts/0 00:00:00 grep postgres4.通過自帶的函數(shù)，是備機則是true主機：postgres=# select pg_is_in_recovery(); pg_is_in_recovery - f(1 row)備機：postgres=# select pg_is_in_recovery(); pg_is_in_recovery - t(1 row)六、模擬主機宕機，備機切換到主機1.主

20、機宕機：pg_ctl stop -m fast主機停機前和停機后備機的進程可以發(fā)現(xiàn)原先的streaming進程(pid=1858)沒了。 同時，備機中的日志，出現(xiàn)大量的錯誤信息tail -f /rasdis/pgdata/pg.log顯示的錯誤信息很明顯，主機服務器連不上了。 此時查看備機的pg_controldata狀態(tài)信息，仍是備機狀態(tài)2.備機切換成主機之前備機上的recovery.conf中配置了 trigger_file = '/rasdis/pgdata/ postgresql.trigger.5432' 要切換備機成主機，只要創(chuàng)建一個觸發(fā)文件postgresql.t

21、rigger.5432即可，這個名字可以隨便寫。 postgreslocalhost $ touch /rasdis/pgdata/ postgresql.trigger.5432此時查看備機上的日志,可以看到成功切換到主機了。 postgreslocalhost $tail -f postgresql-2012-12-24_190930.csv 2012-12-24 19:09:37.100 PST,5954,50d918f1.1742,1,2012-12-24 19:09:37 PST,0,FATAL,XX000,"could not connect to the primary

22、 server: could not connect to server: Connection refused Is the server running on host ""10.3.2.101"" and accepting TCP/IP connections on port 5432?",""2012-12-24 19:09:42.093 PST,5227,50d85726.146b,6,2012-12-24 05:22:46 PST,1/0,0,LOG,00000,"trigger file found

23、: /database/pgdata/trigger.kenyon",""2012-12-24 19:09:42.097 PST,5227,50d85726.146b,7,2012-12-24 05:22:46 PST,1/0,0,LOG,00000,"redo done at 0/F0000020",""2012-12-24 19:09:42.104 PST,5227,50d85726.146b,8,2012-12-24 05:22:46 PST,1/0,0,LOG,00000,"last completed t

24、ransaction was at log time 2012-12-24 05:29:38.526602-08",""2012-12-24 19:09:42.112 PST,5227,50d85726.146b,9,2012-12-24 05:22:46 PST,1/0,0,LOG,00000,"selected new timeline ID: 2",""2012-12-24 19:10:04.403 PST,5227,50d85726.146b,10,2012-12-24 05:22:46 PST,1/0,0,LOG,

25、00000,"archive recovery complete",""2012-12-24 19:10:04.705 PST,5224,50d8571c.1468,2,2012-12-24 05:22:36 PST,0,LOG,00000,"database system is ready to accept connections",""2012-12-24 19:10:04.710 PST,5964,50d9190c.174c,1,2012-12-24 19:10:04 PST,0,LOG,00000,&qu

26、ot;autovacuum launcher started",""日志里可以體現(xiàn)出來原來的備機已經(jīng)切換為主機了。 再去看現(xiàn)在這臺機子的pg_controldata的信息，再次確認一下：已經(jīng)變成production了，對，備機切主機就這么簡單。 還有一處明顯的變化是現(xiàn)在的主機(52)上的recovery.conf文件名字變成了recovery.done。 備機切換為主機后，就可以正常連接使用了。此時就有時間去處理原master端問題了。3.宕機的主機切換成備機 先在現(xiàn)在的主機(52)上做一些數(shù)據(jù)的增刪改rasdbpostgres2 pgdata$ psql -U

27、rasdb postgrespsql (9.4.0)Type "help" for help.postgres=# d List of relations Schema | Name | Type | Owner -+-+-+- public | test_1 | table | rasdb(1 row)postgres=# create table kenyon_rep(ir int,name varchar(10);CREATE TABLEpostgres=# d List of relations Schema | Name | Type | Owner -+-+-+

28、- public | kenyon_rep | table | rasdb public | test_1 | table | rasdb(2 rows)postgres=# insert into kenyon_rep select generate_series(1,100),repeat('kenyon_rep',1);INSERT 0 100postgres=# drop table test_1; DROP TABLEpostgres=# dt List of relations Schema | Name | Type | Owner -+-+-+- public

29、| kenyon_rep | table | rasdb(1 row)postgres=# create table test_2 (id int4,create_time timestamp(0) without time zone);CREATE TABLEpostgres=# dt List of relations Schema | Name | Type | Owner -+-+-+- public | kenyon_rep | table | rasdb public | test_2 | table | rasdb(2 rows)postgres=# insert into test_2 values(1,now();INSERT 0 1pos