PostgreSQL使用 pg_basebackup 搭建流复制环境

1、一 环境信息系统：   Red Hat Enterprise Linux Server release 6.7版本：   PostgreSQL 9.4主库IP： 10.3.2.101  主机名：postgres1备库IP： 10.3.2.102  主机名:postgres2备注： PostgreSQL 安装略。备库可以只装数据库，不初始化数据库二 主库上操作-2.1 创建流复制用户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 备注：仅列出主要参数，其它参数根据实际情况设置。max_wal_senders是Slave库的节点数，有多少个slave库就设多少。wal_level是write ahead log参数值,设置流复制务必将此值更新成hot_standby。max_wal_senders是Slave库的节点数，有多少个slave库就设多少,wal_level是write ahead log参数值,设置流复制务必将此值更新成hot_stand

4、bywal_keep_segments默认值是16,是PG_XLOG下的日志文件数相关参数archive也可以选择关闭，归档是定时恢复用的，流复制不是必须的修改后记得重启数据库$ pg_ctl stop -m fast  $pg_ctl start -D $PGDATA 或 pg_ctl -D $PGDATA -l /rasdis/pgdata/pg.log start #指定输出日志到pg.log-2.4 重载配置文件(如果数据库服务已启动可以使用该命令) 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日志无法重定向问题，需要执行$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  修改以下参数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下创建 .pgpassrasdbpostgres2 $ touch .pgpassrasdbpostgres2 $ vi .pgpasspg93redhat6 $ cat .pgpass1

8、0.3.2.101:5432:replication:repuser:888888pg93redhat6 $ chmod 0600 .pgpass备注：注意 .pgpass文件权限为 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 备库注：在备库中进行查询,备库可查询，但不可更新数据：五主备机区别主备的区别有多种办法去判断 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.通过数据字典表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已经改为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.通过自带的函数，是备机则是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主机停机前和停机后备机的进程可以发现原先的streaming进程(pid=1858)没了。 同时，备机中的日志，出现大量的错误信息tail -f /rasdis/pgdata/pg.log显示的错误信息很明显，主机服务器连不上了。 此时查看备机的pg_controldata状态信息，仍是备机状态2.备机切换成主机之前备机上的recovery.conf中配置了 trigger_file = '/rasdis/pgdata/ postgresql.trigger.5432' 要切换备机成主机，只要创建一个触发文件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",""日志里可以体现出来原来的备机已经切换为主机了。 再去看现在这台机子的pg_controldata的信息，再次确认一下：已经变成production了，对，备机切主机就这么简单。 还有一处明显的变化是现在的主机(52)上的recovery.conf文件名字变成了recovery.done。 备机切换为主机后，就可以正常连接使用了。此时就有时间去处理原master端问题了。3.宕机的主机切换成备机 先在现在的主机(52)上做一些数据的增删改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