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

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

2、#39;-2.2 設(shè)置 pg_hba.conf，添加以下host all all 0.0.0.0/0 md5host   replication     repuser        10.3.2.102/32         md5  -2.3 設(shè)置主庫(kù) 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ù)實(shí)際情況設(shè)置。max_wal_senders是Slave庫(kù)的節(jié)點(diǎn)數(shù)，有多少個(gè)slave庫(kù)就設(shè)多少。wal_level是write ahead log參數(shù)值,設(shè)置流復(fù)制務(wù)必將此值更新成hot_standby。max_wal_senders是Slave庫(kù)的節(jié)點(diǎn)數(shù)，有多少個(gè)slave庫(kù)就設(shè)多少,wal_level是write ahead log參數(shù)值,設(shè)置流復(fù)制務(wù)必將此值更新成hot_stand

4、bywal_keep_segments默認(rèn)值是16,是PG_XLOG下的日志文件數(shù)相關(guān)參數(shù)archive也可以選擇關(guān)閉，歸檔是定時(shí)恢復(fù)用的，流復(fù)制不是必須的修改后記得重啟數(shù)據(jù)庫(kù)$ 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ù)庫(kù)服務(wù)已啟動(dòng)可以使用該命令) pg93redhatB $ pg_ctl reload -D $PGDATAserver signaled三 備庫(kù)上

5、操作-3.1基礎(chǔ)備份復(fù)制到備庫(kù)服務(wù)器登陸備庫(kù)服務(wù)器，運(yùn)行如下命令從主庫(kù)復(fù)制文件。如果備庫(kù)/rasdis/pgdata目錄有文件先需要?jiǎng)h除。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注：以上命令會(huì)遇到wal日志無法重定向問題，需要執(zhí)行$pg_resetxlog -f /opt/postgres9-1/data重置預(yù)寫日志下回重新配置時(shí)使用以下命令試下：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：修改備庫(kù)配置信息添加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 啟服務(wù)  pg93redhat6 pg_root$ pg_ctl start -D $PGDATA 或pg_ctl -D $PGDATA -l /rasdis/pgdata/pg.log startserver starting-3.4 查看備庫(kù)進(jìn)程         查看備庫(kù)是否有類似這個(gè)進(jìn)程：-3.5 查看主庫(kù)進(jìn)程查看主庫(kù)是否有類似

9、這個(gè)進(jìn)程四 測(cè)試-4.1 主庫(kù)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 備庫(kù)注：在備庫(kù)中進(jìn)行查詢,備庫(kù)可查詢，但不可更新數(shù)據(jù)：五主備機(jī)區(qū)別主備的區(qū)別有多種辦法去判斷 1. 通過pg_controldata輸出主機(jī)：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備機(jī)：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 主機(jī)表中能查到記錄，備機(jī)表中是沒有的select pid,usename,application_name,client_addr,client_port,state,sync_st

13、ate from pg_stat_replication;主機(jī)：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)備機(jī)：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.通過進(jìn)程查看，顯示wal sender的是主機(jī)，顯示wal receiver

15、的是備機(jī)主機(jī)：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備機(jī)：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ù)，是備機(jī)則是true主機(jī)：postgres=# select pg_is_in_recovery(); pg_is_in_recovery - f(1 row)備機(jī)：postgres=# select pg_is_in_recovery(); pg_is_in_recovery - t(1 row)六、模擬主機(jī)宕機(jī)，備機(jī)切換到主機(jī)1.主

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

21、rigger.5432即可，這個(gè)名字可以隨便寫。 postgreslocalhost $ touch /rasdis/pgdata/ postgresql.trigger.5432此時(shí)查看備機(jī)上的日志,可以看到成功切換到主機(jī)了。 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ī)已經(jīng)切換為主機(jī)了。 再去看現(xiàn)在這臺(tái)機(jī)子的pg_controldata的信息，再次確認(rèn)一下：已經(jīng)變成production了，對(duì)，備機(jī)切主機(jī)就這么簡(jiǎn)單。 還有一處明顯的變化是現(xiàn)在的主機(jī)(52)上的recovery.conf文件名字變成了recovery.done。 備機(jī)切換為主機(jī)后，就可以正常連接使用了。此時(shí)就有時(shí)間去處理原master端問題了。3.宕機(jī)的主機(jī)切換成備機(jī) 先在現(xiàn)在的主機(jī)(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