提取命令流_PostgreSQL 12 流复制

最新推荐文章于 2024-08-21 22:04:00 发布

陈语岚

最新推荐文章于 2024-08-21 22:04:00 发布

阅读量290

点赞数

文章标签：提取命令流

本文链接：https://blog.csdn.net/weixin_35089515/article/details/112650554

版权

继续PostgreSQL在CentOS Docker里的安装练习

PostgreSQL数据库支持多种复制解决方案，以构建高可用性，可伸缩，容错的应用程序，其中之一是预写日志(WAL)传送。该解决方案允许使用基于文件的日志传送或流复制，或者在可能的情况下，将两种方法结合使用来实现备用服务器。

通过流复制，备用(复制从属)数据库服务器配置为连接到主服务器/主服务器，该主服务器/主服务器在生成WAL记录时将其流传输到备用数据库，而无需等待WAL文件被填充。

默认情况下，流复制是异步的，其中在将事务提交到主服务器后将数据写入备用服务器。这意味着在主服务器中提交事务与更改在备用服务器中变得可见之间存在很小的延迟。这种方法的一个缺点是，如果主服务器崩溃，则可能无法复制任何未提交的事务，这可能导致数据丢失。

本指南显示了如何在CentOS 7上设置Postgresql 12主从流复制。我们将使用复制插槽(replication slots)作为备用解决方案，以避免主服务器在旧WAL段收到备用WAL段之前对其进行回收。

测试环境

Docker里独立部署2个基于CentOS 的PostgreSQL 12 容器

名称	容器名	IP	端口	映射端口
主服务器	master	172.17.0.3	5432	5433
从服务器	standby	172.17.0.4	5432	5434

第0步：准备工作

Jupyter的别名魔法

%alias mrun docker exec  -ti master $*%store mrun%alias srun docker exec  -ti standby $*%store srun%alias mpsql docker exec  -ti master runuser -l postgres -c 'psql -c "\x auto" -c "$0"'%store mpsql%alias spsql docker exec  -ti standby runuser -l postgres -c 'psql  -c "\x auto" -c "$0"'%store spsql

第1步：主从服务器安装

#主服务器name_var = 'master'port_var = 5433%run -i PostgreSQL_安装.ipynb#从服务器name_var = 'standby'port_var = 5434%run -i PostgreSQL_安装.ipynb

第2步：配置主服务器

1. 主服务器允许来自所有IP的访问

# su - postgres#$ psql -c "ALTER SYSTEM SET listen_addresses TO '*';"mpsql "ALTER SYSTEM SET listen_addresses TO '*';"

ALTER SYSTEM SET SQL命令是一项功能强大的命令，可直接通过SQL更改服务器的配置参数。配置保存在$PGDATA/postgresql.conf.auto文件中(例如/var/lib/ pgsql/12/data/)，并读取存储在postgresql.conf中的配置文件。但是，前者中的配置优先于后者和其他相关文件中的配置。

2. 创建复制角色replicator

# su - postgres#$ psql -c "CREATE USER replicator WITH REPLICATION ENCRYPTED PASSWORD 'secret'"mpsql "CREATE USER replicator WITH REPLICATION ENCRYPTED PASSWORD 'secret'"

3. 赋予新创建的复制角色在指定IP段复制权限

#在/var/lib/pgsql/12/data/pg_hba.conf中追加#host replication replicator 172.17.0.1/24 md5mrun sh -c  "echo 'host replication replicator 172.17.0.1/24 md5' >>/var/lib/pgsql/12/data/pg_hba.conf"

4. 重启PostgreSQL主服务器

# systemctl restart postgresql-12.servicemrun systemctl reload postgresql-12.service

第3步：配置从服务器

5. 备份从服务器数据目录

#关服务器#systemctl stop postgresql-12.servicesrun systemctl stop postgresql-12.service#移库# su - postgres# mv /var/lib/pgsql/12/data /var/lib/pgsql/12/data2srun runuser -l postgres -c "mv /var/lib/pgsql/12/data /var/lib/pgsql/12/data2"

6. 核心命令pg_basebackup闪亮登场

#su - postgres#pg_basebackup -h 172.17.0.3 -U replicator -p 5432 -D /var/lib/pgsql/12/data -Fp -Xs -P -R -C -S pgstandby1srun sh -c "echo secret|runuser -l postgres -c 'pg_basebackup -h 172.17.0.3 -U replicator -p 5432 -D /var/lib/pgsql/12/data -Fp -Xs -P -R -C -S pgstandby1'"

-h – 主服务器IP
-D – 从服务器PGDATA
-U – 连接主服务器的用户名
-P – 允许进度报告
-v – 允许verbose模式
-R – 允许创建复制配置:
- 自动创建standby.signal 文件
- 配置postgresql.auto.conf，
- 省去touch standby.signal
- 省去手动修改postgresql.auto.conf
-X – 在备份中包括所需的预写式日志文件(WAL文件)。这包括所有在备份期间产生的预写式日志。除非指定了方法none，可以直接在提取出的目录中启动postmaster而无需参考日志归档，所以这样得到的是一种完整的独立备份
-C – 允许在开始备份前创建 replication slot，名字由 -S 参数指定
-S – 指定replication slot 名

7. 检查从服务器data目录

#发现standby.signal自动生成# ls -l /var/lib/pgsql/12/data/srun ls -l /var/lib/pgsql/12/data/

8. 检查postgresql.auto.conf的改动

#cat /var/lib/pgsql/12/data/postgresql.auto.confsrun  cat /var/lib/pgsql/12/data/postgresql.auto.conf

内容如下，

# Do not edit this file manually!# It will be overwritten by the ALTER SYSTEM command.listen_addresses = '*'primary_conninfo = 'user=replicator password=secret host=172.17.0.3 port=5432 sslmode=prefer sslcompression=0 gssencmode=prefer krbsrvname=postgres target_session_attrs=any'primary_slot_name = 'pgstandby1'

9. 启动从服务器

#systemctl start postgresql-12.servicesrun systemctl start postgresql-12.service

10. 主服务器上检查replication slot

# su - postgres# psql -c "SELECT * FROM pg_replication_slots;"mpsql  "SELECT * FROM pg_replication_slots;"

Expanded display is used automatically.-[ RECORD 1 ]-------+-----------slot_name           | pgstandby1plugin              | slot_type           | physicaldatoid              | database            | temporary           | factive              | tactive_pid          | 2032xmin                | catalog_xmin        | restart_lsn         | 0/A2F3818confirmed_flush_lsn |

第4步，测试PostgerSQL流复制

10 .一旦成功建立了主服务器和备用服务器之间的连接，您将在备用服务器中看到WAL接收器进程的流状态，您可以使用pg_stat_wal_receiver视图进行检查。

#su - postgres# psql -x -c "SELECT * FROM pg_stat_wal_receiver;"spsql "SELECT * FROM pg_stat_wal_receiver;"

Expanded display is used automatically.-[ RECORD 1 ]---------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------pid                   | 1613status                | streamingreceive_start_lsn     | 0/8000000receive_start_tli     | 1received_lsn          | 0/80078C8received_tli          | 1last_msg_send_time    | 2020-04-20 16:33:47.051854+00last_msg_receipt_time | 2020-04-20 16:33:47.052842+00latest_end_lsn        | 0/80078C8latest_end_time       | 2020-04-20 16:33:47.051854+00slot_name             | pgstandby1sender_host           | 172.17.0.3sender_port           | 5432conninfo              | user=replicator password=******** dbname=replication host=172.17.0.3 port=5432 fallback_application_name=walreceiver sslmode=prefer sslcompression=0 gssencmode=prefer krbsrvname=postgres target_session_attrs=any

通过在主服务器检查pg_stat_replication，可以发现状态为streaming，sync_state为sync。

#su - postgres# psql -c "\x" -c "SELECT * FROM pg_stat_replication;"mpsql "SELECT * FROM pg_stat_replication;"

Expanded display is used automatically.-[ RECORD 1 ]----+------------------------------pid              | 2032usesysid         | 16384usename          | replicatorapplication_name | walreceiverclient_addr      | 172.17.0.4client_hostname  | client_port      | 32768backend_start    | 2020-04-20 22:50:08.105645+00backend_xmin     | state            | streamingsent_lsn         | 0/A2F3818write_lsn        | 0/A2F3818flush_lsn        | 0/A2F3818replay_lsn       | 0/A2F3818write_lag        | flush_lag        | replay_lag       | sync_priority    | 0sync_state       | asyncreply_time       | 2020-04-21 00:10:07.218241+00

11. 测试代码

# 主服务器建表，并插入数据mpsql "create table t(id int); insert into t values (1),(2),(3),(4),(5),(6);"#从服务器检查主服务器刚建的b表spsql  "select * from t "

第5步，允许同步复制

PostgreSQL默认流复制为异步。

同步复制提供了同时向主数据库和备用数据库/副本提交事务(或写入数据)的功能。仅当事务所做的所有更改都已转移到一个或多个同步备用服务器上时，才确认事务成功。

要启用同步复制，还必须将ynchronous_commit设置为on(这是默认值，因此不需要进行任何更改)，并且还需要将synchronous_standby_names参数设置为非空值。对于本指南，我们将其全部设置。

# su - postgres# psql -c "ALTER SYSTEM SET synchronous_standby_names TO  '*';"# systemctl reload postgresql-12.servicempsql -c "ALTER SYSTEM SET synchronous_standby_names TO  '*';"mrun systemctl reload postgresql-12.service

再查下sync_sate

#su -p postgres# psql -c "SELECT sync_state, reply_time FROM pg_stat_replication;"mpsql "SELECT sync_state, reply_time FROM pg_stat_replication;"

Expanded display is used automatically. sync_state |          reply_time           ------------+------------------------------- sync       | 2020-04-21 00:17:58.126462+00(1 row)

总结

pg_hba IP访问权限部分做了更新(相比上篇笔记)
基于Docker 容器安装PostgreSQL的代码进一步简化，并经封装后可重复使用
使用Jupyter的alias魔法，让代码更简洁，易读
越发觉得Jupyter Notebook适合于DBA教学

安装PostgreSQL

流复制

‍

参考

https://www.percona.com/blog/2019/10/11/how-to-set-up-streaming-replication-in-postgresql-12/
https://www.tecmint.com/configure-postgresql-streaming-replication-in-centos-8/
https://www.percona.com/blog/2018/11/30/postgresql-streaming-physical-replication-with-slots/
http://www.postgres.cn/docs/11/app-pgbasebackup.html

如果有什么建议和意见，也欢迎留言，或者加我个人微信，