环境配置信息:
CentOS7.6/postgres12
需求描述:
用户已经自己做好主从库pg软件安装以及主库和从库的初始化,需要配置主从同步
问题分析:
需要完成:
1、检查主库配置文件参数配置情况
2、配置主从同步
3、主从同步测试
解决方案:
1.创建流复制角色修改管理员密码(主节点)
$ psql
postgres=# create role replica login replication encrypted password ‘pg123456’;
postgres=# ALTER USER postgres WITH PASSWORD ‘pg123456’;
postgres=# \q
##设置replica复制用户免密登陆,避免将密码写在配置文件recovery.done中
$ cd /home/postgres
$ touch .pgpass
$ chmod 0600 .pgpass
$ cat >>.pgpass<<EOF
172.25.29.3:5432:replication:replica:pg123456
172.25.29.17:5432:replication:replica:pg123456
EOF
2.配置recovery.done (主节点)
$ cp $PGHOME/share/recovery.conf.sample $PGDATA/
$ cd $PGDATA
$ mv recovery.conf.sample recovery.done
$ cat >>recovery.done<<EOF
recovery_target_timeline = ‘latest’
standby_mode = on
primary_conninfo = ‘host=172.25.29.17 port=5432 user=replica’
EOF
3.备份主库恢复生成从库(从节点)
在备库服务器的postgres用户上执行
su - postgres
在备库上直接备份主库到备库/paic/postgres目录下
修改如下红色字体标识ip地址为主库地址
$ pg_basebackup -D /paic/postgres -F p -X stream -h 172.25.29.3 -p 5432 -U replica
输入主库创建的replica角色的密码
4.配置从库postgresql.conf,从库的conf文件是从主库拷贝过来的,根据需要修改。本次环境主备配置环境一致,不用修改。
5.配置从库的recovery.conf (从节点)
##设置replica复制用户免密登陆,避免将密码写在配置文件recovery.conf中
$cd /home/postgres
$touch .pgpass
$chmod 0600 .pgpass
$cat >>.pgpass<<EOF
172.25.29.3:5432:replication:replica:pg123456
172.25.29.17:5432:replication:replica:pg123456
EOF
$cd /paic/postgres
$mv recovery.done recovery.conf
$sed -i “s/172.25.29.17/172.25.29.3/g” recovery.conf
6.启动备库
$chmod 0700 /paic/postgres
$pg_ctl start -D /paic/postgres -l logfile
7.主从数据复制测试
主库插入数据:
su - postgres
$ psql
postgres=# create table test(id int);
postgres=# insert into test values(10);
postgres=# \q
备库查询数据:
su - postgres
$ psql
postgres=#
postgres=# \dt
postgres=# select * from test;
id
10
(1 row)
测试备库只读:
postgres=# create table test(id int);
ERROR: cannot execute CREATE TABLE in a read-only transaction
postgres=#
8.主从复制状态监控
主库:
su - postgres
$ pg_controldata #查看database cluster state:in production 表示主库
$ top -c -u postgres #查看进程,确认有sender进程
$ postgres=# select * from pg_stat_replication;
备库:
su - postgres
$ pg_controldata #查看database cluster state:in archive recovery 表示备库
$ top -c -u postgres #查看状态,确认有reciever进程
9.主库查询复制延迟
$ psql
postgres=# select pid,usename,client_addr,state,
pg_size_pretty(pg_wal_lsn_diff(pg_current_wal_lsn(), sent_lsn)) as sent_delay,
pg_size_pretty(pg_wal_lsn_diff(pg_current_wal_lsn(), write_lsn)) as write_delay,
pg_size_pretty(pg_wal_lsn_diff(pg_current_wal_lsn(), flush_lsn)) as flush_delay,
pg_size_pretty(pg_wal_lsn_diff(pg_current_wal_lsn(), replay_lsn)) as replay_delay
from pg_stat_replication;
10.备库查询复制状态
$psql
– 查看当前WAL应用是否暂停了
postgres=# select pg_is_wal_replay_paused();
– 查看WAL接收到的位点
postgres=# select pg_last_wal_receive_lsn();
– 查看WAL的应用位点
postgres=# select pg_last_wal_replay_lsn();
– 查看wal receiver的统计信息
postgres=# select * from pg_stat_get_wal_receiver();