搭建1主2从的流复制环境
详细搭建过程可以参考：PG12高可用之1主2从流复制环境搭建及切换测试：https://www.xmmup.com/dbbao60pg12gaokeyongzhi1zhu2congliufuzhihuanjingdajianjiqiehuanceshi.html
主库操作
配置主库pg_hba.conf
| cat << EOF > /var/lib/pgsql/13/data/pg_hba.conf # TYPE DATABASE USER ADDRESS METHOD local all all trust host all all 127.0.0.1/32 trust host all all 0.0.0.0/0 md5 host replication all 0.0.0.0/0 md5 EOF |
本人提供Oracle(OCP、OCM)、MySQL(OCP)、PostgreSQL(PGCA、PGCE、PGCM)等数据库的培训和考证业务，私聊QQ646634621或微信db_bao，谢谢！
配置主库参数
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 | -- 登陆主库环境 docker exec -it lhrpgpatroni71 bash -- 该路径也需要在从库创建 mkdir -p /postgresql/archive chown -R postgres.postgres /postgresql/archive -- 修改参数 cat >> /var/lib/pgsql/13/data/postgresql.conf <<"EOF" wal_level='replica' archive_mode='on' archive_command='test ! -f /postgresql/archive/%f && cp %p /postgresql/archive/%f' max_wal_senders=10 EOF -- 重启 systemctl restart postgresql-13.service -- 查询参数 select * from pg_settings where name in ('wal_level','archive_mode','archive_command'); |
主库创建用户
| create role replhr login encrypted password 'lhr' replication; |
从库操作
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 | mkdir -p /bk chown postgres:postgres /bk su - postgres pg_basebackup -h 172.72.6.71 -p 5432 -U replhr -l bk20220120 -F p -P -R -D /bk cat >> /bk/postgresql.conf <<"EOF" primary_conninfo = 'host=172.72.6.71 port=5432 user=replhr password=lhr' EOF -- 关闭从库，删除从库的数据文件，并且将备份文件覆盖从库的数据文件 systemctl stop postgresql-13.service rm -rf /var/lib/pgsql/13/data/* cp -r /bk/* /var/lib/pgsql/13/data/ systemctl start postgresql-13.service |
检查主从复制
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 | -- 主库查看wal日志发送状态 select * from pg_stat_replication; -- 从库查看wal日志接收状态 select * from pg_stat_wal_receiver; -- 也可以通过该命令查看 pg_controldata | grep state -- 也可以查看这个，主库是f代表false ；备库是t，代表true select pg_is_in_recovery(); postgres=# select * from pg_stat_replication; pid | usesysid | usename | application_name | client_addr | client_hostname | client_port | backend_start | backend_xmin | state | sent_lsn | write_lsn | flush_lsn | replay_lsn | write_lag | flush_lag | replay_lag | sync_priority | sync_state | reply_time ------+----------+---------+------------------+-------------+-----------------+-------------+-------------------------------+--------------+-----------+-----------+-----------+-----------+------------+-----------+-----------+------------+---------------+------------+------------------------------- 3170 | 16430 | replhr | walreceiver | 172.72.6.73 | | 45000 | 2022-01-20 14:58:13.769779+08 | | streaming | 0/7000060 | 0/7000060 | 0/7000060 | 0/7000060 | | | | 0 | async | 2022-01-20 15:13:14.383888+08 3980 | 16430 | replhr | walreceiver | 172.72.6.72 | | 41278 | 2022-01-20 15:12:09.147278+08 | | streaming | 0/7000060 | 0/7000060 | 0/7000060 | 0/7000060 | | | | 0 | async | 2022-01-20 15:13:09.266891+08 (2 rows) |
笔耕不辍，我辈学习楷模。