默认
Python3.8 安装
Nuitka使用
自检工具常见问题
python 提权
Git 打补丁-- patch 和 diff 的使用
ssh 端口转发
Ubuntu常用操作
gitea安装
ubuntu添加pycharm快捷方式
常用链接
DTHouse测试
test
GIT 常用操作
groovyScript 使用
Kafka 常用操作
Postgres 日期操作
硬盘读写测试
安装nodejs
dthouse打包
postgresql 主从复制
Bandit 代码安全检测
odoo安全
License相关
人民币大写
unison使用
supervisor 使用记录
如何修改 Ubuntu 时区
ubuntu 中 history 命令显示执行时间
Ubuntu 修改 SSH 登录欢迎界面
git仅拉取仓库内的指定目录
本文档使用 MrDoc 发布
-
+
首页
postgresql 主从复制
> 本文由 [简悦 SimpRead](http://ksria.com/simpread/) 转码， 原文地址 [blog.csdn.net](https://blog.csdn.net/m0_52544877/article/details/125449202) ## 一、部署和配置环境 | 主机 | IP 地址 | 版本 | | --- | --- | --- | | psql-master（主库） | 10.10.140.113 | psql-14 | | psql-slave（备库） | 10.10.140.112 | psql-14 | #### 1 修改主机名 ``` [root@localhost ~]# hostnamectl set-hostname pgsql-master [root@localhost ~]# bash [root@localhost ~]# hostnamectl set-hostname pgsql-slave [root@localhost ~]# bash ``` #### 2 安装 postgresql-14 ``` #安装pgsql apt install postgresql #查看服务是否开启 [root@pgsql-master ~]# ss -antl State Recv-Q Send-Q Local Address:Port Peer Address:Port Process LISTEN 0 244 127.0.0.1:5432 0.0.0.0:* ``` #### 3 修改数据存放路径 ``` # 建目录/赋权限 root@node1:/home/user# mkdir -p /data/postgres_data root@node1:/home/user# chown -R postgres:postgres /data/postgres_data root@node1:/home/user# ll /data/ total 12 drwxr-xr-x 3 root root 4096 Oct 12 01:46 ./ drwxr-xr-x 20 root root 4096 Oct 12 01:46 ../ drwxr-xr-x 2 postgres postgres 4096 Oct 12 01:46 postgres_data/ # 修改数据库配置 root@node1:/home/user# vim /etc/postgresql/14/main/postgresql.conf data_directory = '/data/postgres_data' # 停止数据库服务 root@node1:/home/user# systemctl stop postgresql root@node1:/home/user# systemctl status postgresql # 同步数据 root@node1:/home/user# rsync -av /var/lib/postgresql/14/main/ /data/postgres_data/ #重启数据库 root@node1:/home/user# systemctl start postgresql ``` **也可以使用默认数据存放路径：/var/lib/postgresql/14/main/** ## 二、执行 stream 主备配置流程 主体思路跟 PostgreSQL 11 及以前版本的配置流程大同小异，甚至是更简单一些了。 ### 1 主库修改监听地址 ``` root@node1:/home/user# vim /etc/postgresql/14/main/postgresql.conf ··· #listen_addresses = 'localhost' # what IP address(es) to listen on; listen_addresses = '*' ··· ``` ### 2 主库创建流复制的用户 连接主库后, 创建复制用户replicator。 ``` root@node1:/home/user# su postgres postgres@node1:/home/user$ psql psql (14.5 (Ubuntu 14.5-0ubuntu0.22.04.1)) Type "help" for help. postgres=# CREATE USER replicator REPLICATION LOGIN ENCRYPTED PASSWORD '******'; CREATE ROLE ``` 设置 pg_hba.conf，添加以下内容，允许 replicaor 用户从备库 IP 10.10.140.112 访问主库。 ``` host replication repuser 10.10.140.112/32 scram-sha-256 ``` ### 4 备库删掉旧数据 ``` root@node2:/var/lib/postgresql/14# cd /var/lib/postgresql/14/main root@node2:/var/lib/postgresql/14# rm -rf * ``` ### 5 备库执行对于主库的基础备份 [pg_basebackup 文档](http://www.postgres.cn/docs/14/app-pgbasebackup.html "pg_basebackup 文档") > 注意，备份选项上带有 - R 选项。 ``` -bash-4.2$ pg_basebackup -D /data/postgres_data -h 10.10.140.113 -U replicator -P -v -R -X stream -C -S pgstandby1 口令: pg_basebackup: 开始基础备份，等待检查点完成 pg_basebackup: 已完成检查点 pg_basebackup: 预写日志起始于时间点: 0/2000028, 基于时间轴1 pg_basebackup: 启动后台 WAL 接收进程 pg_basebackup: 已创建复制槽"pgstandby1" 26952/26952 kB (100%), 1/1 表空间 pg_basebackup: 预写日志结束点: 0/2000100 pg_basebackup: 等待后台进程结束流操作... pg_basebackup: 同步数据到磁盘... pg_basebackup: 将backup_manifest.tmp重命名为backup_manifest pg_basebackup: 基础备份已完成 ``` ### 6 备库启动 postgresql 服务 这时，就可以看到备库服务器上自动生成了 standby.signal 文件。同时，也看到在 $PGDATA 路径下，数据库自动帮我们配置了关于流复制的主库的信息： ``` root@node2:/data# service postgresql start root@node2:/data# cat /data/postgres_data/postgresql.auto.conf # Do not edit this file manually! # It will be overwritten by the ALTER SYSTEM command. primary_conninfo = 'user=replicator password=replicatorPWD channel_binding=prefer host=10.10.140.113 port=5432 sslmode=prefer sslcompression=0 sslsni=1 ssl_min_protocol_version=TLSv1.2 gssencmode=prefer krbsrvname=postgres target_session_attrs=any' primary_slot_name = 'pgstandby1' ``` 当然了，如果我们没有使用 - R 来备份主库的话。我们完全可以在备库上手工创建 standby.signal 文件，然后手工编辑 postgresql.auto.conf，并在其内容中配置主库的信息。 ## 三、信息查看 ##### 1 备库数据库进程信息 ``` -bash-4.2$ ps -ef|grep postgres postgres 8911 1 0 Oct20 ? 00:00:00 /postgres/pg12.8/bin/postgres postgres 8912 8911 0 Oct20 ? 00:00:00 postgres: startup recovering 000000010000000000000003 postgres 8913 8911 0 Oct20 ? 00:00:00 postgres: checkpointer postgres 8914 8911 0 Oct20 ? 00:00:00 postgres: background writer postgres 8915 8911 0 Oct20 ? 00:00:00 postgres: stats collector postgres 8916 8911 0 Oct20 ? 00:00:32 postgres: walreceiver streaming 0/3000148 root 18015 18014 0 16:13 pts/0 00:00:00 su - postgres postgres 18016 18015 0 16:13 pts/0 00:00:00 -bash postgres 18055 18016 0 16:13 pts/0 00:00:00 ps -ef postgres 18056 18016 0 16:13 pts/0 00:00:00 grep --color=auto postgres ``` 备库上，可以看到 walreceiver 进程，正在读取日志 streaming 0/3000148，执行恢复 recovering 000000010000000000000003。 ##### 2 主库数据库进程信息 ``` -bash-4.2$ ps -ef|grep postgres postgres 3708 1 0 Oct20 ? 00:00:00 /postgres/pg12.8/bin/postgres postgres 3710 3708 0 Oct20 ? 00:00:00 postgres: checkpointer postgres 3711 3708 0 Oct20 ? 00:00:00 postgres: background writer postgres 3712 3708 0 Oct20 ? 00:00:00 postgres: walwriter postgres 3713 3708 0 Oct20 ? 00:00:00 postgres: autovacuum launcher postgres 3714 3708 0 Oct20 ? 00:00:00 postgres: stats collector postgres 3715 3708 0 Oct20 ? 00:00:00 postgres: logical replication launcher postgres 4174 3708 0 Oct20 ? 00:00:00 postgres: walsender replicator 192.168.40.147(57926) streaming 0/3000148 root 30410 30409 0 16:11 pts/1 00:00:00 su - postgres postgres 30411 30410 0 16:11 pts/1 00:00:00 -bash postgres 30868 30411 0 16:13 pts/1 00:00:00 ps -ef postgres 30869 30411 0 16:13 pts/1 00:00:00 grep --color=auto postgres ``` 主库上看到，后台进程 walsender，正在向 replicator 192.168.40.147(57926) streaming 0/3000148 推送日志信息。 ##### 3 主库查看数据库复制信息 ``` -bash-4.2$ psql -xc "select * from pg_stat_replication" -[ RECORD 1 ]----+------------------------------ pid | 4174 usesysid | 16384 usename | replicator application_name | walreceiver client_addr | 192.168.40.147 client_hostname | client_port | 57926 backend_start | 2021-10-20 15:45:37.817863+08 backend_xmin | state | streaming sent_lsn | 0/3000148 write_lsn | 0/3000148 flush_lsn | 0/3000148 replay_lsn | 0/3000148 write_lag | flush_lag | replay_lag | sync_priority | 0 sync_state | async reply_time | 2021-10-21 16:11:58.438541+08 ``` ## 六、 主备切换及注意事项 如果因为意外或故障导致主库不可用的情况下，可以直接将备库提升为主库对外提供服务。然后视具体情况看原来的主库是否需要重建，或者是否待故障恢复之后，可以直接作为新的备库，然后从新的主库 (原备库) 同步数据。 下面是模拟切换步骤： ##### 1 主库停止，模拟故障 ``` -bash-4.2$ ps -ef|grep postgres postgres 3708 1 0 Oct20 ? 00:00:00 /postgres/pg12.8/bin/postgres postgres 3710 3708 0 Oct20 ? 00:00:00 postgres: checkpointer postgres 3711 3708 0 Oct20 ? 00:00:00 postgres: background writer postgres 3712 3708 0 Oct20 ? 00:00:00 postgres: walwriter postgres 3713 3708 0 Oct20 ? 00:00:00 postgres: autovacuum launcher postgres 3714 3708 0 Oct20 ? 00:00:00 postgres: stats collector postgres 3715 3708 0 Oct20 ? 00:00:00 postgres: logical replication launcher postgres 4174 3708 0 Oct20 ? 00:00:00 postgres: walsender replicator 192.168.40.147(57926) streaming 0/3000148 postgres 4747 30411 0 16:42 pts/1 00:00:00 ps -ef postgres 4748 30411 0 16:42 pts/1 00:00:00 grep --color=auto postgres root 30410 30409 0 16:11 pts/1 00:00:00 su - postgres postgres 30411 30410 0 16:11 pts/1 00:00:00 -bash -bash-4.2$ pg_ctl status pg_ctl: server is running (PID: 3708) /postgres/pg12.8/bin/postgres -bash-4.2$ pg_ctl stop -m fast waiting for server to shut down.... done server stopped -bash-4.2$ ps -ef|grep postgres postgres 4843 30411 0 16:43 pts/1 00:00:00 ps -ef postgres 4844 30411 0 16:43 pts/1 00:00:00 grep --color=auto postgres root 30410 30409 0 16:11 pts/1 00:00:00 su - postgres postgres 30411 30410 0 16:11 pts/1 00:00:00 -bash -bash-4.2$ pg_ctl status pg_ctl: no server running ``` 通过 pg_ctl stop -m fast 停止原来的主库之后，数据库后台进程都没有了。 ##### 2 备库提升为新主库，对外提供服务 ``` -bash-4.2$ ps -ef|grep postgres postgres 8911 1 0 Oct20 ? 00:00:00 /postgres/pg12.8/bin/postgres postgres 8912 8911 0 Oct20 ? 00:00:00 postgres: startup recovering 000000010000000000000003 postgres 8913 8911 0 Oct20 ? 00:00:00 postgres: checkpointer postgres 8914 8911 0 Oct20 ? 00:00:00 postgres: background writer postgres 8915 8911 0 Oct20 ? 00:00:00 postgres: stats collector root 18015 18014 0 16:13 pts/0 00:00:00 su - postgres postgres 18016 18015 0 16:13 pts/0 00:00:00 -bash postgres 25404 18016 0 16:43 pts/0 00:00:00 ps -ef postgres 25405 18016 0 16:43 pts/0 00:00:00 grep --color=auto postgres -bash-4.2$ pg_ctl status pg_ctl: server is running (PID: 8911) /postgres/pg12.8/bin/postgres -bash-4.2$ pg_ctl promote waiting for server to promote.... done server promoted -bash-4.2$ ps -ef|grep postgres postgres 8911 1 0 Oct20 ? 00:00:00 /postgres/pg12.8/bin/postgres postgres 8913 8911 0 Oct20 ? 00:00:00 postgres: checkpointer postgres 8914 8911 0 Oct20 ? 00:00:00 postgres: background writer postgres 8915 8911 0 Oct20 ? 00:00:00 postgres: stats collector root 18015 18014 0 16:13 pts/0 00:00:00 su - postgres postgres 18016 18015 0 16:13 pts/0 00:00:00 -bash postgres 25622 8911 0 16:44 ? 00:00:00 postgres: walwriter postgres 25623 8911 0 16:44 ? 00:00:00 postgres: autovacuum launcher postgres 25624 8911 0 16:44 ? 00:00:00 postgres: logical replication launcher postgres 25628 18016 0 16:44 pts/0 00:00:00 ps -ef postgres 25629 18016 0 16:44 pts/0 00:00:00 grep --color=auto postgres -bash-4.2$ ``` 重要 1：启动备库为新主库的命令是 pg_ctl promote。 提升备库为主库之后，可以看到，后台进程中不再有 startup recovering，以及 walreceiver streaming 进程了。同时，多了 postgres: walwriter 写进程。 重要 2：$PGDATA/standby.signal 文件自动消失了。这是告诉 PostgreSQL，我现在不再是备库了，我的身份是主库了。 ##### 3 新主库修改 pg_hba.conf 文件 修改新主库 (原备库 192.168.40.147) 的 $PGDATA/pg_hba.conf 文件，在其中添加允许新备库 (原主库 192.168.40.133) 可以通过 replica 用户访问数据库的条目信息。 ``` host replication replicaotr 192.168.40.133/24 md5 ``` 如果不做这一步配置的话，将来启动原主库为新备库的时候，可能会遇到下述错误。 ``` 2021-10-21 17:13:20.464 CST [11394] FATAL: could not connect to the primary server: FATAL: no pg_hba.conf entry for replication connection from host "192.168.40.133", user "replica", SSL off 2021-10-21 17:13:20.466 CST [11395] FATAL: could not connect to the primary server: FATAL: no pg_hba.conf entry for replication connection from host "192.168.40.133", user "replica", SSL off ``` 注意：如果主从环境的数据库没有配置浮动 IP 的话，则这里的 IP 地址，应该直接填原主库的实际 IP 地址。 ##### 4 原主库新建 $PGDATA/standby.signal 文件 ``` -bash-4.2$ pwd /var/lib/pgsql/14/data -bash-4.2$ touch standby.signal -bash-4.2$ ll standby.signal -rw-rw-r-- 1 postgres postgres 0 Oct 21 16:54 standby.signal ``` 注意：这一步骤非常非常重要，如果不配置该文件的话，那么原来的主库一旦重新启动话，就将成为了 1 个新的独立主库，脱离了主从数据库环境。 ##### 5 原主库修改 $PGDATA/postgresql.auto.conf 文件 注意，应该用单引号，而不是双引号。否则遇到下述错误。 ``` -bash-4.2$ cat postgresql.auto.conf # Do not edit this file manually! # It will be overwritten by the ALTER SYSTEM command. primary_conninfo="user=replica password=replica host=192.168.40.147 port=5432" -bash-4.2$ -bash-4.2$ pg_ctl start -l ~/pg.log waiting for server to start.... stopped waiting pg_ctl: could not start server Examine the log output. -bash-4.2$ tailf ~/pg.log 数据库启动日志 2021-10-21 09:07:06.512 GMT [10045] LOG: syntax error in file "/postgres/pg12.8/data/postgresql.auto.conf" line 3, near token """ 2021-10-21 09:07:06.512 GMT [10045] FATAL: configuration file "postgresql.auto.conf" contains errors ``` 修改 $PGDATA/postgresql.auto.conf 配置文件为下述正确的格式： ``` -bash-4.2$ cat postgresql.auto.conf # Do not edit this file manually! # It will be overwritten by the ALTER SYSTEM command. primary_conninfo='user=replica password=replica host=192.168.40.147 port=5432' ``` ##### 6 启动原主库，变为新备库 ``` -bash-4.2$ pg_ctl start -l ~/pg.log waiting for server to start.... done server started -bash-4.2$ ps -ef|grep postgres root 8116 8115 0 16:58 pts/0 00:00:00 su - postgres postgres 8118 8116 0 16:58 pts/0 00:00:00 -bash root 8598 8597 0 17:00 pts/2 00:00:00 su - postgres postgres 8600 8598 0 17:00 pts/2 00:00:00 -bash postgres 11368 8118 0 17:13 pts/0 00:00:00 tailf pg.log postgres 11389 1 0 17:13 ? 00:00:00 /postgres/pg12.8/bin/postgres postgres 11390 11389 0 17:13 ? 00:00:00 postgres: startup recovering 000000020000000000000003 postgres 11391 11389 0 17:13 ? 00:00:00 postgres: checkpointer postgres 11392 11389 0 17:13 ? 00:00:00 postgres: background writer postgres 11393 11389 0 17:13 ? 00:00:00 postgres: stats collector postgres 11440 11389 0 17:13 ? 00:00:00 postgres: walreceiver streaming 0/3013AC8 postgres 12545 30411 0 17:18 pts/1 00:00:00 ps -ef postgres 12546 30411 0 17:18 pts/1 00:00:00 grep --color=auto postgres root 30410 30409 0 16:11 pts/1 00:00:00 su - postgres postgres 30411 30410 0 16:11 pts/1 00:00:00 -bash -bash-4.2$ tailf pg.log 2021-10-21 17:13:45.488 CST [11440] LOG: fetching timeline history file for timeline 2 from primary server 2021-10-21 17:13:45.493 CST [11440] LOG: started streaming WAL from primary at 0/3000000 on timeline 1 2021-10-21 17:13:45.493 CST [11440] LOG: replication terminated by primary server 2021-10-21 17:13:45.493 CST [11440] DETAIL: End of WAL reached on timeline 1 at 0/30001C0. 2021-10-21 17:13:45.494 CST [11390] LOG: new target timeline is 2 2021-10-21 17:13:45.494 CST [11440] LOG: restarted WAL streaming at 0/3000000 on timeline 2 2021-10-21 17:13:45.539 CST [11390] LOG: redo starts at 0/30001C0 ``` 这样，就完成了一次主从数据库环境的切换操作了。 #### 七、小结 1. 随着新版本的发行，数据库的配置和使用也越来越简单顺手了。 2. 备库提升为主库的命令：pg_ctl promote; 3. 新主库 (原备库) 的 pg_hba.conf 文件，要开放允许流复制访问数据库的信息给原主库的 IP 地址； 4. 原主库配置为新备库的时候，务必要创建 $PGDATA/standby.signal 文件； 5. 原主库配置为新备库的时候，务必要修改 $PGDATA/postgresql.auto.conf 文件，添加主库 primary_conninfo 的信息；
幻翼
2022年10月12日 11:04
转发文档
收藏文档
上一篇
下一篇
手机扫码
复制链接
手机扫一扫转发分享
复制链接
Markdown文件
分享
链接
类型
密码
更新密码