【PG】PostgreSQL13主从流复制部署(详细可用)

版本

部署主从注意点

1 主库上创建复制用户

2 主库上修改pg_hba.conf文件

3 修改文件后重新加载配置使其生效

4 主库上修改配置文件

5 重启主库pg使参数生效

6 部署从库

7 备份主库数据至从库

停止从库

备份从库的数据库目录

新建数据库数据目录data

创建和主库相同表空间目录

进行备份

8 修改从库参数

9 查看主从状态

1 通过查看服务器进程

2 主库插入数据验证在从库是否同步

3 查看状态表

主库查看pg_stat_replication表

从库查看pg_stat_wal_receiver

从库 pg_is_in_recovery()

参数解释

版本

数据库：PostgreSQL 13.12

服务器：CentOS 7

部署主从注意点

表空间的挂载路径相同；与表空间相关的路径名将被未经修改地传递，因此如果该特性被使用主、备服务器必须对表空间具有完全相同的挂载路径，记住如果CREATE TABLESPACE在主服务器上被执行，在命令被执行前，它所需要的任何新挂载点必须在主服务器和所有后备服务器上先创建好
硬件不需要完全相同；但是经验显示，在应用和系统的生命期内维护两个相同的系统比维护两个不相似的系统更容易
操作系统位数相同；在任何情况下硬件架构必须相同 — 从一个 32 位系统传送到一个 64 位系统将不会工作。
PG主版本相同；不能在两个运行着不同主版本PostgreSQL的服务器之间传送日志

1 主库上创建复制用户

在主库上创建复制用的账号，需要有属性 replication 才能进行复制

CREATE ROLE repl login replication password 'repl';

2 主库上修改pg_hba.conf文件

允许从库IP可以通过repl用户连接主库

# TYPE  DATABASE        USER            ADDRESS                 METHOD
# 网络段 10.79.21.0
host    replication     repl            10.79.21.0/24            trust

3 修改文件后重新加载配置使其生效

pg_ctl -D /home/storage/pgsql/data -l /home/storage/pgsql/data/server.log reload

4 主库上修改配置文件

listen_addresses = '*' # 允许所有IP访问
wal_level = replica # 可选值 minimal ，replica ，logical。要支持复制和归档，必须设置为replica 或logical
archive_mode = on # 开启归档模式，配合archive_command参数将WAL发送到归档目录 
archive_command = 'cp %p /home/storage/pgsql/archive/%f' # 将WAL日志拷贝到归档目录
max_wal_senders = 10  # 指定来自后备服务器或流式基础备份客户端的并发连接的最大数量

5 重启主库pg使参数生效

# 停止
pg_ctl -D /home/storage/pgsql/data -l /home/storage/pgsql/data/server.log stop

# 启动
pg_ctl -D /home/storage/pgsql/data -l /home/storage/pgsql/data/server.log start

6 部署从库

参考我的博客【PG】PostgreSQL单机部署（简洁命令版）-CSDN博客

7 备份主库数据至从库

停止从库

pg_ctl stop -D /home/storage/pgsql/data

备份从库的数据库目录

mv data data_bak

新建数据库数据目录data

使用root

mkdir data

chown postgres:postgres /home/storage/pgsql/data

创建和主库相同表空间目录

mkdir mytablespace

chown postgres:postgres mytablespace

进行备份

备份时注意要添加以下参数

-R 或者 --write-recovery-conf

创建一个standby.signal文件，并将连接设置附加到目标目录的postgresql.auto.conf文件中。这样可以简化使用备份结果设置备用服务器的过程。

postgresql.auto.conf文件将记录连接设置（如果有）以及pg_basebackup所使用的复制槽，这样流复制后面就会使用相同的设置。

pg_basebackup -h 10.79.21.30 -p 5432 -U repl -D /home/storage/pgsql/data -P --wal-method=stream -R

备份之后的目录

8 修改从库参数

listen_addresses = '*' # 允许所有IP访问
hot_standby = on
primary_conninfo = 'host=10.79.21.30 port=5432 user=repl password=repl application_name=standby2129'

9 查看主从状态

1 通过查看服务器进程

主库多了进程 postgres: walsender repl 10.79.21.29(32704) streaming 0/B000060

从库多了进程 postgres: walreceiver streaming 0/B000148

2 主库插入数据验证在从库是否同步

INSERT INTO weather (date, city, temp_hi, temp_lo)
    VALUES ('2023-11-08', 'BeiJing', 54, 37);

3 查看状态表

主库查看pg_stat_replication表

postgres-# \x
Expanded display is on.
postgres=#  select * from pg_stat_replication;
-[ RECORD 1 ]----+------------------------------
pid              | 161877
usesysid         | 16528
usename          | repl
application_name | walreceiver
client_addr      | 10.79.21.29
client_hostname  |
client_port      | 32704
backend_start    | 2023-11-08 10:38:46.379612+08
backend_xmin     |
state            | streaming
sent_lsn         | 0/B0003B8
write_lsn        | 0/B0003B8
flush_lsn        | 0/B0003B8
replay_lsn       | 0/B0003B8
write_lag        |
flush_lag        |
replay_lag       |
sync_priority    | 0
sync_state       | async
reply_time       | 2023-11-08 11:00:51.902209+08

从库查看pg_stat_wal_receiver

postgres=# select * from pg_stat_wal_receiver;
-[ RECORD 1 ]---------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
pid                   | 54324
status                | streaming
receive_start_lsn     | 0/B000000
receive_start_tli     | 1
written_lsn           | 0/B0003B8
flushed_lsn           | 0/B0003B8
received_tli          | 1
last_msg_send_time    | 2023-11-08 11:03:42.159233+08
last_msg_receipt_time | 2023-11-08 11:03:42.159264+08
latest_end_lsn        | 0/B0003B8
latest_end_time       | 2023-11-08 10:52:11.163215+08
slot_name             |
sender_host           | 10.79.21.30
sender_port           | 5432
conninfo              | user=repl passfile=/home/postgres/.pgpass channel_binding=disable dbname=replication host=10.79.21.30 port=5432 fallback_application_name=walreceiver sslmode=disable sslcompression=0 ssl_min_protocol_version=TLSv1.2 gssencmode=disable krbsrvname=postgres target_session_attrs=any

从库 pg_is_in_recovery()

select pg_is_in_recovery();

postgres=# select pg_is_in_recovery();
 pg_is_in_recovery
-------------------
 t
(1 row)

参数解释

listen_addresses (string)

指定服务器在哪些 TCP/IP 地址上监听客户端连接。值的形式是一个逗号分隔的主机名和/或数字 IP 地址列表。

特殊项*对应所有可用 IP 接口。
项0.0.0.0允许监听所有 IPv4 地址并且::允许监听所有 IPv6 地址。
如果列表为空，服务器将根本不会监听任何 IP 接口，在这种情况中只能使用 Unix 域套接字来连接它。
默认值是localhost，它只允许建立本地 TCP/IP “环回”连接。
虽然客户端认证允许细粒度地控制谁能访问服务器，listen_addresses控制哪些接口接受连接尝试，这能帮助在不安全网络接口上阻止重复的恶意连接请求。这个参数只能在服务器启动时设置。

wal_level (enum)

wal_level决定多少信息写入到 WAL 中。默认值是replica，它会写入足够的数据以支持WAL归档和复制，包括在后备服务器上运行只读查询。minimal会去掉除从崩溃或者立即关机中进行恢复所需的信息之外的所有记录。最后，logical会增加支持逻辑解码所需的信息。每个层次包括所有更低层次记录的信息。这个参数只能在服务器启动时设置。

archive_mode (enum)

当启用archive_mode时，可以通过设置 archive_command命令将完成的 WAL 段发送到归档存储。除用于禁用的off之外，还有两种模式： on和always。在普通操作期间，这两种模式之间没有区别，但是当设置为always时，WAL 归档器在归档恢复或者后备模式下也会被启用。在always模式下，所有从归档恢复的或者用流复制传来的文件将被（再次）归档。

archive_mode和archive_command是独立的变量，这样可以在不影响归档模式的前提下修改archive_command。这个参数只能在服务器启动时设置。当wal_level被设置为minimal时，archive_mode不能被启用。

archive_command (string)

本地 shell 命令被执行来归档一个完成的 WAL 文件段。字符串中的任何%p被替换成要被归档的文件的路径名，而%f只被文件名替换（路径名是相对于服务器的工作目录，即集簇的数据目录）。如果要在命令里嵌入一个真正的%字符，可以使用%%。有一点很重要，该命令只在成功时返回一个零作为退出状态。

这个参数只能在postgresql.conf文件中或在服务器命令行上设置。除非服务器启动时启用了archive_mode，否则它会被忽略。如果archive_mode被启用时，archive_command是一个空字符串（默认），WAL 归档会被临时禁用，但服务器仍会继续累计 WAL 段文件，期待着一个命令被提供。将archive_command设置为一个只返回真但不做任何事的命令（例如/bin/true或 Windows 上的REM）实际上会禁用归档，也会打破归档恢复所需的 WAL 文件链，因此只有在极少数情况下才能用。

max_wal_senders (integer)

指定来自后备服务器或流式基础备份客户端的并发连接的最大数量（即同时运行 WAL 发送进程的最大数）。默认值是10。值0意味着禁用复制。