流复制-pg_basebackup (有自定义表空间)
一.组成部分
- 1.walsender进程是用来发送WAL日志记录的
- 2.walreceiver进程是用来接收WAL日志记录的
- 3.startup进程是用来apply日志的
二.主库配置
- 1.授权账号，用于远程连接复制，类似mysql的复制账号
CREATE ROLE replica login replication encrypted password 'replica';
- 2.
pg_hba.conf新增策略
host replication replica 172.16.10.0/24 md5
- 3.
postgresql修改主配置文件
wal_level = hot_standby # 备用服务器上增加了运行只读查询所需的信息
max_wal_senders = 5 # 这个设置了可以最多有几个流复制连接，即几个slave
wal_keep_segments = 128 # 设置流复制保留的最多的xlog数目，默认每个16M，总大小128*16
wal_sender_timeout = 60s # 设置流复制主机发送数据的超时时间
- 4.启动primary主库
pg_ctl start -D $PGDATA
pg_ctl start -D /data/pgsql_data
/etc/init.d/pgsqld start
三.slave配置
- 1.复制主库的pgsql_data目录
pg_basebackup -D . -Fp -Xs -v -P -h 172.16.10.100 -U repl -p 5432 --format=p -T "/data/pgsql_data/pgdata01"=/data/pg_data/tb/
# 复制到本地备份目录，再移动到/data/pgsql_data (如果数据库很大很浪费时间做二次移动)
mv ./* /data/pgsql_data
cd /data/pgsql_data/pg_tblspc
ln -s /data/pgsql_data/pgdata01 16428
- 2.修改slave的
postgresql.conf
max_connections = 200 # 比主库稍大即可
hot_standby = on
max_standby_streaming_delay = 30s
wal_receiver_status_interval = 10s
hot_standby_feedback = on
- 3.在
$PGDATA目录下创建recovery.conf文件
standby_mode = on # 指定为从库
primary_conninfo = 'host=172.16.10.100 port=5432 user=repl password=repl' # 对应的主库信息
recovery_target_timeline = 'latest' # 这个说明这个流复制同步到最新的数据
- 4.启动slave
pg_ctl start -D $PGDATA
pg_ctl start -D /data/pgsql_data
/etc/init.d/pgsqld start
- 5.在主库上查询同步状态
postgres=# \x
postgres=# select * from pg_stat_replication;
-[ RECORD 1 ]----+------------------------------
pid | 1664
usesysid | 16426
usename | repl
application_name | walreceiver
client_addr | 172.16.10.142
client_hostname |
client_port | 48645
backend_start | 2018-11-27 13:22:53.316104+08
backend_xmin | 1854
state | streaming
sent_location | 0/12000138
write_location | 0/12000138
flush_location | 0/12000138
replay_location | 0/12000138
sync_priority | 0
sync_state | async
# 有三个值
async: 异步
sync: 同步
potential: 虽然现在是异步模式，但是有可能升级到同步模式
sent_location--发送wal的位置
write_location--备库接收到的wal的位置
flush_location—备库写wal日志到磁盘的位置
replay_location—备库应用日志的位置
查看备库落后主库多少字节
select pg_xlog_location_diff(pg_current_xlog_location(),replay_location)/1024/1024 as MB from pg_stat_replication;
select pg_xlog_location_diff(pg_current_xlog_location(),replay_location)/1024/1024/1024 as GB from pg_stat_replication;
- 6.slave的log
[root@mycat01 pg_log]# cat postgresql-2018-11-27_132255.csv
2018-11-27 13:22:55.938 CST,,,29116,,5bfcd4af.71bc,1,,2018-11-27 13:22:55 CST,,0,LOG,00000,"ending log output to stderr",,"Future log output will go to log destination ""csvlog"".",,,,,,,""
2018-11-27 13:22:55.953 CST,,,29118,,5bfcd4af.71be,1,,2018-11-27 13:22:55 CST,,0,LOG,00000,"database system was interrupted; last known up at 2018-11-27 13:17:47 CST",,,,,,,,,""
2018-11-27 13:22:55.965 CST,,,29118,,5bfcd4af.71be,2,,2018-11-27 13:22:55 CST,,0,LOG,00000,"entering standby mode",,,,,,,,,""
2018-11-27 13:22:56.014 CST,,,29119,,5bfcd4b0.71bf,1,,2018-11-27 13:22:56 CST,,0,LOG,00000,"started streaming WAL from primary at 0/11000000 on timeline 1",,,,,,,,,""
2018-11-27 13:22:56.046 CST,,,29118,,5bfcd4af.71be,3,,2018-11-27 13:22:55 CST,1/0,0,LOG,00000,"redo starts at 0/11000028",,,,,,,,,""
2018-11-27 13:22:56.047 CST,,,29118,,5bfcd4af.71be,4,,2018-11-27 13:22:55 CST,1/0,0,LOG,00000,"consistent recovery state reached at 0/11000128",,,,,,,,,""
2018-11-27 13:22:56.047 CST,,,29116,,5bfcd4af.71bc,2,,2018-11-27 13:22:55 CST,,0,LOG,00000,"database system is ready to accept read only connections",,,,,,,,,""
- 7.进程
[root@mysql56 pgsql_data]# ps -ef |grep postgres
postgres 1621 1 0 13:14 ? 00:00:00 /usr/local/pgsql/bin/postmaster -D /data/pgsql_data
postgres 1622 1621 0 13:14 ? 00:00:00 postgres: logger process
postgres 1624 1621 0 13:14 ? 00:00:00 postgres: checkpointer process
postgres 1625 1621 0 13:14 ? 00:00:00 postgres: writer process
postgres 1626 1621 0 13:14 ? 00:00:00 postgres: wal writer process
postgres 1627 1621 0 13:14 ? 00:00:00 postgres: autovacuum launcher process
postgres 1628 1621 0 13:14 ? 00:00:00 postgres: archiver process last was 000000010000000000000011.00000028.backup
postgres 1629 1621 0 13:14 ? 00:00:00 postgres: stats collector process
postgres 1664 1621 0 13:22 ? 00:00:00 postgres: wal sender process repl 172.16.10.142(48645) streaming 0/12000570 #用于发送log
postgres 1669 1621 0 13:23 ? 00:00:00 postgres: postgres postgres [local] idle
postgres 29116 1 0 13:22 pts/6 00:00:00 /usr/local/postgresql-9.4.7/bin/postgres -D /data/pgsql_data
postgres 29117 29116 0 13:22 ? 00:00:00 postgres: logger process
postgres 29118 29116 0 13:22 ? 00:00:00 postgres: startup process recovering 000000010000000000000012
postgres 29119 29116 0 13:22 ? 00:00:00 postgres: wal receiver process streaming 0/12000570
postgres 29120 29116 0 13:22 ? 00:00:00 postgres: checkpointer process
postgres 29121 29116 0 13:22 ? 00:00:00 postgres: writer process
postgres 29122 29116 0 13:22 ? 00:00:00 postgres: stats collector process
https://www.postgresql.org/docs/9.4/app-pgbasebackup.html
https://my.oschina.net/u/2254487/blog/783904
