PostgreSQL使用pg_basebackup搭建流复制环境

一环境信息系统：RedHatEnterpriseLinuxServerrelease6.7版本：PostgreSQL9.4主库IP：10.3.2.101主机名：postgres1备库IP：10.3.2.102主机名:postgres2备注：PostgreSQL安装略。备库可以只装数据库，不初始化数据库二主库上操作--2.1创建流复制用户CREATEUSERrepuserreplicationLOGINCONNECTIONLIMIT5ENCRYPTEDPASSWORD'888888';--2.2设置pg_hba.conf，添加以下hostallall0.0.0.0/0md5hostreplicationrepuser10.3.2.102/32md5--2.3设置主库postgresql.conf#checkpoint_segments=16archive_mode=onarchive_command='/bin/date'max_wal_senders=3#wal_keep_segments=16wal_level=hot_standbyhot_standby=on备注：仅列出主要参数，其它参数根据实际情况设置。max_wal_senders是Slave库的节点数，有多少个slave库就设多少。wal_level是writeaheadlog参数值,设置流复制务必将此值更新成hot_standby。max_wal_senders是Slave库的节点数，有多少个slave库就设多少,wal_level是writeaheadlog参数值,设置流复制务必将此值更新成hot_standbywal_keep_segments默认值是16,是PG_XLOG下的日志文件数相关参数archive也可以选择关闭，归档是定时恢复用的，流复制不是必须的修改后记得重启数据库$pg_ctlstop-mfast$pg_ctlstart-D$PGDATA或pg_ctl-D$PGDATA-l/rasdis/pgdata/pg.logstart##指定输出日志到pg.log--2.4重载配置文件(如果数据库服务已启动可以使用该命令)[pg93@redhatB~]$pg_ctlreload-D$PGDATAserversignaled三备库上操作--3.1基础备份复制到备库服务器登陆备库服务器，运行如下命令从主库复制文件。如果备库/rasdis/pgdata目录有文件先需要删除。pg_basebackup-D$PGDATA-Fp-hmasterdb-pmasterdb_port-Urepuser–W如：pg_basebackup-D$PGDATA-Fp-h10.3.2.101-p5432-Urepuser–W注：以上命令会遇到wal日志无法重定向问题，需要执行$pg_resetxlog-f/opt/postgres9-1/data重置预写日志下回重新配置时使用以下命令试下：pg_basebackup-D/rasdis/pgdata-Fp-Xs-v-P-h10.3.2.101-p5432-Urepuser或pg_basebackup-D$PGDATA-Fp-Xstream-v-P-h10.3.2.101-p5432-Urepuser--3.2：修改备库配置信息添加recovery.conf文件。$cp/rasdis/postgresql/share/recovery.conf.sample/rasdis/pgdata/recovery.conf$virecovery.conf修改以下参数standby_mode=onprimary_conninfo='host=10.3.2.101port=5432user=repuserpassword=888888keepalives_idle=60'#trigger_file='/rasdis/pgdata/postgresql.trigger.5432'配置.pgpass文件。在home/rasdb下创建.pgpass[rasdb@postgres2~]$touch.pgpass[rasdb@postgres2~]$vi.pgpass[pg93@redhat6~]$cat.pgpass10.3.2.101:5432:replication:repuser:888888[pg93@redhat6~]$chmod0600.pgpass备注：注意.pgpass文件权限为0600。--3.3启服务[pg93@redhat6pg_root]$pg_ctlstart-D$PGDATA或pg_ctl-D$PGDATA-l/rasdis/pgdata/pg.logstartserverstarting--3.4查看备库进程查看备库是否有类似这个进程：--3.5查看主库进程查看主库是否有类似这个进程四测试--4.1主库postgres=#createtabletest_2(idint4,create_timetimestamp(0)withouttimezone);CREATETABLEpostgres=#\dtListofrelationsSchema|Name|Type|Owner--------+------------+-------+-------public|kenyon_rep|table|rasdbpublic|test_2|table|rasdb(2rows)postgres=#insertintotest_2values(1,now());INSERT01postgres=#select*fromtest_2;id|create_time----+---------------------1|2016-07-1819:19:36(1row)--4.2备库注：在备库中进行查询,备库可查询，但不可更新数据：五．主备机区别主备的区别有多种办法去判断1.通过pg_controldata输出主机：[rasdb@postgres1pgdata]$pg_controldatapg_controlversionnumber:942Catalogversionnumber:201409291Databasesystemidentifier:6306486576830085248Databaseclusterstate:inproduction或用pg_controldata|grepcluster[rasdb@postgres1etc]$pg_controldata|grepclusterDatabaseclusterstate:inproduction备机：[rasdb@postgres2~]$pg_controldatapg_controlversionnumber:942Catalogversionnumber:201409291Databasesystemidentifier:6306486576830085248Databaseclusterstate:inarchiverecovery或用pg_controldata|grepcluster[rasdb@postgres2pgdata]$pg_controldata|grepclusterDatabaseclusterstate:inarchiverecovery2.通过数据字典表pg_stat_replication主机表中能查到记录，备机表中是没有的selectpid,usename,application_name,client_addr,client_port,state,sync_statefrompg_stat_replication;主机：postgres=#selectpid,usename,application_name,client_addr,client_port,state,sync_statefrompg_stat_replication;pid|usename|application_name|client_addr|client_port|state|sync_state-------+---------+------------------+--------------+-------------+-----------+------------19301|repuser|walreceiver|10.3.2.102|53322|streaming|async(1row)备机：postgres=#selectpid,usename,application_name,client_addr,client_port,state,sync_statefrompg_stat_replication;pid|usename|application_name|client_addr|client_port|state|sync_state-----+---------+------------------+-------------+-------------+-------+------------(0rows)--9.2版本,procpid已经改为pid3.通过进程查看，显示walsender的是主机，显示walreceiver的是备机主机：[rasdb@postgres1pgdata]$ps-ef|greppostgresrasdb192931018:12pts/100:00:00/rasdis/postgresql/bin/postgres-D/rasdis/pgdatarasdb1929519293018:12?00:00:00postgres:checkpointerprocessrasdb1929619293018:12?00:00:00postgres:writerprocessrasdb1929719293018:12?00:00:00postgres:walwriterprocessrasdb1929819293018:12?00:00:00postgres:autovacuumlauncherprocessrasdb1929919293018:12?00:00:00postgres:archiverprocessrasdb1930019293018:12?00:00:00postgres:statscollectorprocessrasdb1930119293018:12?00:00:00postgres:walsenderprocessrepuser10.3.2.102(53322)streaming0/80009B0rasdb1934519001018:24pts/100:00:00greppostgres备机：[rasdb@postgres2~]$ps-ef|greppostgresrasdb18441018:11pts/000:00:00/rasdis/postgresql/bin/postgres-D/rasdis/pgdatarasdb18451844018:11?00:00:00postgres:loggerprocessrasdb18461844018:11?00:00:00postgres:startupprocessrecovering000000010000000000000008rasdb18471844018:11?00:00:00postgres:checkpointerprocessrasdb18481844018:11?00:00:00postgres:writerprocessrasdb18491844018:11?00:00:00postgres:statscollectorprocessrasdb18581844018:12?00:00:00postgres:walreceiverprocessstreaming0/80009B0rasdb18921602018:23pts/000: