PG数据库的备份方案
参考文章：
- 一文彻底弄懂PostgreSQL流复制(全网最详细)-CSDN博客
- PostgreSQL 12 流复制环境搭建 - andyAngelo - 博客园 (cnblogs.com)
- PostgreSQL12主从流复制 - 知乎 (zhihu.com)
- https://blog.csdn.net/zxfmamama/article/details/121008549
- https://www.bilibili.com/video/BV1qk4y197zL/?spm_id_from=333.880.my_history.page.click
概述
什么是流复制？
如果有人问你PostgreSQL的流复制究竟是什么？你大概会说通过wal日志来进行数据同步之类的，的确如此，流复制大概就是这么回事。
但是准确的来说：PostgreSQL通过wal日志来传送的方式有两种：基于文件的日志传送和流复制。
不同于基于文件的日志传送，流复制的关键在于“流”，所谓流，就是没有界限的一串数据，类似于河里的水流，是连成一片的。因此流复制允许一台后备服务器比使用基于文件的日志传送更能保持为最新的状态。
比如我们有一个大文件要从本地主机发送到远程主机，如果是按照“流”接收到的话，我们可以一边接收，一边将文本流存入文件系统。这样，等到“流”接收完了，硬盘写入操作也已经完成。
流复制发展历史
流复制之前的手段：
像我们上面说的，pg在流复制出现之前，使用的就是基于文件的日志传送：对wal日志进行拷贝，因此从库始终落后主库一个日志文件，并且使用rsync工具同步data目录。
而流复制出现是从2010年推出的pg9.0开始的，其历史大致为：
- 起源：pg9.0开始支持流式物理复制，用户可以通过流式复制，构建只读备库(主备物理复制，块级别一致)。流式物理复制可以做到极低的延迟(通常在1毫秒以内)。
- 同步流复制：pg9.1开始支持同步复制，但是当时只支持一个同步流复制备节点(例如配置了3个备，只有一个是同步模式的，其他都是异步模式)。同步流复制的出现，保证了数据的0丢失。
- 级联流复制：pg9.2支持级联流复制。即备库还可以再连备库。
- 流式虚拟备库：pg9.2还支持虚拟备库，即就是只有WAL，没有数据文件的备库。
- 逻辑复制：pg9.4开始可以实现逻辑复制，逻辑复制可以做到对主库的部分复制，例如表级复制，而不是整个集群的块级一致复制。
- 增加多种同步级别：pg9.6版本开始可以通过synchronous_commit参数，来配置事务的同步级别。
流复制概述
流复制其原理为：备库不断的从主库同步相应的数据，并在备库apply每个WAL record，这里的流复制每次传输单位是WAL日志的record。
PostgreSQL物理流复制按照同步方式分为两类：
- 异步流复制
- 同步流复制
物理流复制具有以下特点： - 延迟极低，不怕大事务
- 支持断点续传
- 支持多副本
- 配置简单
- 备库与主库物理完全一致，并支持只读
流复制基础
在学习流复制之前，我们先来了解一些相关的基础知识。
WAL日志介绍
在这篇文章中我曾经详细介绍过PostgreSQL中的wal日志：PostgreSQL WAL日志详解
这里再简单介绍下：
- WAL日志机制保证了事务的持久性和数据完整性，同时又避免了频繁IO对性能的影响。
- 为了保证数据库中数据的持久性，即事务提交后，即使数据库出现故障也能保证数据的可靠性。最简单的方法就是：数据一提交就刷到磁盘上。但是这样对于事务非常频繁的系统，一有事务提交就去刷新脏数据，会对数据库性能产生非常不好的影响。因此使用wal日志来记录数据的更改，这样每当发生事务提交，只需要通过提交wal日志即可，而且wal日志的提交是顺序的，性能也很高。
WAL日志解读
对数据库操作会以record为单位首先记录到wal日志中，在checkpoint时才对数据进行刷
盘(background writer会定时刷脏数据，但最终还是都由checkpoint确认都刷盘成功)。
聊了这么久wal日志，我们都还不知道wal日志在哪？长啥样。。。
wal日志位置：
$PGDATA/pg_wal(pg10之前叫pg_xlog)
wal日志文件命名规则：
我们看到的wal日志是这样的：000000010000000100000092
其中前8位:00000001表示timeline；
中间8位：00000001表示logid；
最后8位：00000092表示logseg
wal日志LSN编号规则:
1/920001F8(高32位/低32位)
对照关系：
1、wal日志的logseg前6位始终是0，后两位是LSN低32位/16MB(2*24)，即LSN的前两位。如上例中logseg最后两位是92，LSN低32前两位也是92。
2、LSN在wal日志中的偏移量即LSN低32位中后24位对应的十进制值。
例如当前wal日志偏移量为504
bill=# select pg_walfile_NAME_OFFSET(pg_current_wal_lsn());
pg_walfile_name_offset
--------------------------------
(000000010000000100000092,504)
(1 row)
bill=# select x'1F8'::int;
int4
------
504
(1 row)
流复制原理
日志提交过程
图中可以看到流复制中日志提交的大致流程为:
- 事务commit后，日志在主库写入wal日志，还需要根据配置的日志同步级别，等待从库反馈的接收结果。
- 主库通过日志传输进程将日志块传给从库，从库接收进程收到日志开始回放，最终保证主从数据一致性。
流复制同步级别
vim /var/lib/pgsql/12/data/postgresql.conf
通过在postgresql.conf配置synchronous_commit参数来设置同步级别
synchronous_commit = off # synchronization level;
# off, local, remote_write, or on
- remote_apply：事务commit或rollback时，等待其redo在primary、以及同步standby(s)已持久化，并且其redo在同步standby*(s)已apply。
- on：事务commit或rollback时，等待其redo在primary、以及同步standby(s)已持久化。
- remote_write：事务commit或rollback时，等待其redo在primary已持久化; 其redo在同步standby(s)已调用write接口(写到 OS, 但是还没有调用持久化接口如fsync)。
- local：事务commit或rollback时，等待其redo在primary已持久化;
- off：事务commit或rollback时，等待其redo在primary已写入wal buffer，不需要等待其持久化；
不同的事务同步级别对应的数据安全级别越高，对应的对性能影响也就越大。上述从上至下安全级别越来越低。
配置时需要注意的点:
- postgresql-12版本不再支持通过recovery.conf的方式进行主备切换，如果数据目录中存在recovery.conf，则数据库无法启动；
- 新增 recovery.signal 标识文件，表示数据库处于 recovery 模式；
- 新增加 standby.signal 标识文件，表示数据库处于 standby 模式(这个需要重点关注一下)；
- 以前版本中 standby_mode 参数不再支持；
- ecovery.conf文件取消, 合并到了postgresql.conf文件中；
- 配置中war_level存储级别, postgresql-9.6以后有改变:
| 等级 | 说明 |
|---|---|
| minimal | 不能通过基础备份和wal日志恢复数据库 |
| replica | 9.6新增,将之前版本的 archive 和 hot_standby合并, 该级别支持wal归档和复制 |
| logical | 在replica级别的基础上添加了支持逻辑解码所需的信息 |
异步流复制主从库配置
在PostgreSQL 12中配置流复制，主库和从库的配置步骤相对明确，且可以使用一个已存在的PostgreSQL数据库作为从库，但需要进行适当的准备和配置。
前置条件
准备两PG数据库，如果使用Docker搭建，可以参考【Docker搭建PG数据库】文章
一、主库配置
修改postgresql.conf文件
在主库上，需要修改PostgreSQL的配置文件（通常位于 /var/lib/postgresql/12/data/ 或 /usr/local/pgsql/data/ 等路径下，具体取决于安装方式和操作系统）。主要需要调整的参数包括：
# 允许所有IP地址连接。
listen_addresses = '*'
# 设置WAL（Write-Ahead Logging）级别为replica，这是流复制所必需的
wal_level = replica
# 流复制允许连接进程，主备库这个参数值必须一样
max_wal_senders = 20
# 设置保留的WAL文件段数，确保从库有足够的时间来复制数据
wal_keep_segments = 64
# 当设置为 `on` 时，PostgreSQL 将把 WAL 日志文件复制到指定的归档位置，以便在需要时可以用于数据恢复
archive_mode = on
# `cp` 命令被用来将 WAL 日志文件复制到 `/home/postgres/arch/` 目录下，并保留原始文件名
archive_command = 'cp %p /home/postgres/arch/%f'
# `cp` 命令被用来从 `/home/postgres/arch/` 目录下复制 WAL 日志文件到 PostgreSQL 期望的路径
restore_command = 'cp /home/postgres/arch/%f %p'
# 当设置为 `'latest'` 时，PostgreSQL 将尝试恢复到最新的可用时间线
recovery_target_timeline = 'latest'
# 当设置为 `on` 时，PostgreSQL 会在每次修改页面时写入完整的页面到磁盘，这有助于防止部分写入导致的数据损坏
full_page_writes = on
# 当设置为 `on` 时，WAL 日志将包含更多的恢复提示，这些提示在恢复过程中可以帮助 PostgreSQL 更准确地重建数据状态
wal_log_hints = on
[!NOTE] wal_keep_segments
wal_keep_segments配置为0和1的主要区别在于系统为流复制保留的 WAL 段数量。设置为0时，系统更加灵活地管理 WAL 空间，但可能增加流复制连接中断的风险；设置为1时，系统至少保留一个 WAL 段以提高复制稳定性，但会稍微增加磁盘空间占用。在实际应用中，应根据数据库的具体需求和资源状况来合理配置该参数。如果备用服务器的处理能力和网络条件良好，可以考虑将wal_keep_segments设置为较低的值以节省磁盘空间；如果备用服务器可能经常滞后于主服务器，或者对数据一致性和复制稳定性有较高要求，则应将wal_keep_segments设置为较高的值以确保备用服务器能够及时获取所需的 WAL 数据。
创建复制用户
create role repl login replication encrypted password 'oracle';
- create role repl:
create role是用于创建新角色的命令。repl是新角色的名称。
- login:
login是一个选项，表示创建的角色允许登录数据库。在 PostgreSQL 中，这通常通过CREATE USER或CREATE ROLE ... WITH LOGIN来实现，而不是直接在CREATE ROLE后面加login。
- replication:
replication在这里似乎被误解为一个角色属性或选项，但实际上在CREATE ROLE命令中并没有这样的选项。replication是一个权限，不是角色属性。正确的方式是通过GRANT REPLICATION来赋予角色这个权限。
- encrypted password ‘oracle’:
encrypted password是一个指定密码并要求其加密的选项。'oracle'是要设置的密码。
[!NOTE] REPLICATION 角色
在 PostgreSQL（简称 PG）数据库中，REPLICATION并不是一个默认角色，而是一个可以赋予用户的特殊权限。这个权限允许用户进行数据库的复制操作，通常用于设置流复制或逻辑复制的场景。REPLICATION 权限的用途：
REPLICATION权限允许用户连接到数据库以进行复制操作，如读取 WAL（Write-Ahead Logging）文件以在备用服务器上应用更改，从而保持与主服务器的数据同步。
修改pg_hba.conf文件
配置认证文件（通常位于与postgresql.conf相同的目录下），允许从库连接到主库进行复制。添加类似以下的行：
host replication repl 192.168.164.0/24 trust
重启主库
修改配置文件后，需要重启主库以使更改生效。使用系统服务管理工具（如systemctl或service）来重启PostgreSQL服务。
pg_ctl -D /var/lib/postgresql/data -l ~/pglog.log restart
或者
docker restart postgres
二、从库配置
准备从库环境
如果使用一个已存在的PostgreSQL数据库作为从库，需要确保该数据库是干净的（即不包含需要复制的数据），或者你有计划删除现有数据并用主库的数据替换。
执行pg_basebackup
在从库上，使用pg_basebackup工具从主库获取基础备份。例如：
pg_basebackup -h 主库IP -p 5434 -U [复制用户名] -Fp -Xs -Pv -R -D [数据库文件目录：/var/lib/postgresql/data]
pg_basebackup -h 192.168.164.137 -p 5434 -U repl -Fp -Xs -Pv -R -D /var/lib/postgresql/data
- -h 192.168.164.137:
-h指定远程数据库服务器的主机名或 IP 地址。- 在这里，
192.168.164.137是远程 PostgreSQL 服务器的主机地址。
- -p 5434:
-p指定远程数据库服务器的端口号。5434是 PostgreSQL 服务器监听的端口（默认端口是5432，但这里使用了一个非默认端口）。
- -U repl:
-U指定用于连接数据库的用户名。repl是用于连接远程 PostgreSQL 服务器的用户名，这个用户需要有足够的权限来执行备份操作。
- -Fp:
-F指定备份文件格式。p表示使用普通文件格式（plain format），这是默认格式，意味着备份数据将以与原始数据库文件相同的格式存储。
- -Xs:
-X指定如何处理备份中的事务日志（WAL，Write-Ahead Logging）。s表示使用流复制（streaming replication）来传输 WAL 日志。这允许在备份过程中持续接收和应用 WAL 日志，从而得到一个更加一致的备份。
- -Pv:
-P显示备份过程的进度信息。v（verbose）表示输出详细的进度信息，这有助于监控备份的进展。
- -R:
-R表示在备份中包括恢复所需的配置信息（如recovery.conf，在 PostgreSQL 12 及以后版本中被postgresql.conf中的相关设置所取代）。- 这允许在备份完成后，使用备份数据直接启动一个恢复中的 PostgreSQL 实例。
- -D /var/lib/postgresql/data:
-D指定备份数据的目标目录。/var/lib/postgresql/data是备份数据将被存储的本地目录路径。
==注意==：有 standby.signal 文件数据库才会认为当前自己是备库
配置standby.signal
# 这个参数指定了备用服务器如何连接到主服务器以获取 WAL（Write-Ahead Logging）日志
primary_conninfo = 'host=192.168.164.137 port=5434 user=repl password=oracle options="-c wal_sender_timeout=5000"'
# 这个参数指定了在恢复过程中如何从归档中检索 WAL 日志文件的命令
restore_command ='cp /home/postgres/arch/%f %p'
# 用于在归档目录中清理不再需要的 WAL 日志文件
archive_cleanup_command = 'pg_archivecleanup /home/postgres/arch %r'
# 这个参数决定了 PostgreSQL 实例是否以备用模式运行
standby_mode = on
正常情况下pg_basebackup执行成功后，主库和从库就已经关联起来了，此时可以通过在主库上执行SQL查询来检查从库的连接和复制状态：
SELECT client_addr, sync_state FROM pg_stat_replication;
启动从库
启动从库服务，使流复制开始工作。使用系统服务管理工具来启动 PostgreSQL 服务。
三、验证配置
- 在主库和从库上，使用
ps命令或系统服务管理工具查看PostgreSQL服务的状态。 - 在主库上执行一些数据操作（如插入、更新），然后检查这些更改是否已同步到从库。
- 可以使用
select pg_is_in_recovery()命令来检查从库是否处于恢复模式（在主库上返回f，在从库上返回t）。
通过以上步骤，你可以成功配置PostgreSQL 12的流复制，并使用一个已存在的PostgreSQL数据库作为从库。不过，请确保在操作过程中备份重要数据，以防万一出现数据丢失或损坏的情况。
四、验证流复制是否成功
- 在主库中创建库、创建表、向表中插入数据
主库和备库进程情况
ps -ef | grep postgres
主库进程
备库进程
[!NOTE] 小知识：为什么PG和Oracle那么像
Oracle有很多工程师跳槽到了EDB，EDB是把PG数据库商业化做的最好的公司，EDB也维护开源的postgresql
主备切换
先查看主库和备库的状态
主库
备库
第一步：关闭主库
- 日志切换
- 把日志同步到备库
- 主备完全一致
第二步：原备库升级成主库
在备库中执行 pg_ctl promote，然后在执行 pg_controldata | grep cluster，就发现已经升级成主库了。
注意不要在root用户下执行
存在的问题：在主库存活时也可指进行这个操作，这就会导致出现两个主库。
此时备库中的 standby.signal 文件已经不存在了，执行命令 select pg_is_in_recovery() 检查，结果是 f。此时备库可以进行写的操作了。
建议：由于备库变成主库时 standby.signal 文件会被删除，后续，这个数据库在想改回备库就需要重新写一遍，很麻烦，所以先把这个文件拷贝到其他位置保存一份。
注意：
原来这个备库的 postgresql.auto.conf 文件中会自动添加下面这条数据
primary_conninfo = 'user=repl passfile=''/var/lib/postgresql/.pgpass'' channel_binding=prefer host=192.168.164.137 port=5434 sslmode=prefer sslcompression=0 sslcertmode=allow sslsni=1 ssl_min_protocol_vversion=TLSv1.2 gssencmode=prefer krbsrvname=postgres gssdelegation=0 target_session_attrs=any load_balance_hosts=disable'
会导致备库切换成主库以后，不在向新的备库发送日志进行同步。要把这一行给注释掉，否则虽然现在是主库了，但是配置还是当作备库，自相矛盾，且在跟踪日志中会报“background worker"logical replication launcher’"(PID6304)exited with exit code1"错误。这可能是PG12.2的bug。
重启数据库，查看后台进程，实验发现walsender进程要等备库正常启动后才会启动，备库关闭时该进程也自动中断。
第三步：原主库变成新的备库
配置文件 standby.signal
primary_conninfo = 'host=192.168.164.138 port=5434 user=repl password=oracle options="-c wal_sender_timeout=5000"'
restore_command ='cp /home/postgres/arch/%f %p'
archive_cleanup_command = 'pg_archivecleanup /home/postgres/arch %r'
standby_mode = on
配置文件 postgresql.auto.conf
primary_conninfo = 'user=repl passfile=''/var/lib/postgresql/.pgpass'' channel_binding=prefer host=192.168.164.138 port=5434 sslmode=prefer sslcompression=0 sslcertmode=allow sslsni=1 ssl_min_protocol__version=TLSv1.2 gssencmode=prefer krbsrvname=postgres gssdelegation=0 target_session_attrs=any load_balance_hosts=disable'
这个时候会报错：
经过实验，发现主备切换不太灵活和智能，需要后续进行手动修改，特别是 postgresql.auto.conf 文件中自动添加的一行，在主备切换的时候不会自动删除，没有相关文档，造成了隐性的问题，给DBA造成了很大的麻烦，不容易故障排除。
主库在正常运行中，备库可以随意切换为主库，没有一个制约机制，感觉不严谨，此时变成两个主库，数据无法同步。如果此时两边的数据库都各自发生变化，将来想把一台主库当作备库，则需要在备库上对当前的数据进行同步，然后就可以变成备库，用以下的命令进行同步：
在备库中执行以下脚本：
pg_rewind --target-pgdata=/var/lib/postgresql/data --source-server='host=192.168.164.138 port=5434 user=postgres password=abc123 dbname=postgres'
- 执行脚本的用户必须是postgres：
pg_ctl stop -l /var/log/postgresql/pglog.log - 备库的服务必须停止
重新启动备库
备库启动后主库的进程状态
经验
如果备库这样操作不可以，出现问题了，就重新按照异步复制主从配置的步骤操作一下。（需要保证新备库中旧数据没用了，新主库中的数据就是最新的）
实时同步
在异步同步配置的基础上，配置文件
# 这个参数指定一个支持同步复制的后备服务器的列表。'*' = all
synchronous_standby_names = 'testdb'
# 执行要配置的一组数据库
synchronous_standby_names = 'FIRST 1 (pg1,pg2)'
其中的pg1,pg2都是备库的名字，备库的名字需要再下面两个配置文件中进行配置，配置正确并且和主库一致后，主库启动时，日志中会提示。
standby.signal
# application_name 当前数据库的名称，同步时使用
primary_conninfo = 'application_name=pg3 host=192.168.164.138 application_name=pg3 port=5434 user=repl password=oracle options="-c wal_sender_timeout=5000"'
restore_command ='cp /home/postgres/arch/%f %p'
archive_cleanup_command = 'pg_archivecleanup /home/postgres/arch %r'
standby_mode = on
postgresql.auto.conf
# application_name 当前数据库的名称，同步时使用
primary_conninfo = 'application_name=pg3 user=repl passfile=''/var/lib/postgresql/.pgpass'' channel_binding=prefer host=192.168.164.138 port=5434 sslmode=prefer sslcompression=0 sslcertmode=allow sslsni=1 ssl_min_protocol_version=TLSv1.2 gssencmode=prefer krbsrvname=postgres gssdelegation=0 target_session_attrs=any load_balance_hosts=disable'
一主多从配置实时同步
synchronous_standby_names = 'FIRST 1 (pg1,pg2)'
synchronous_standby_names = 'FIRST 2 (pg1,pg2)'
synchronous_standby_names = 'any 1 (pg1,pg2)'
说明：
- FIRST 1：第一个数据库实时同步
- FIRST 2：前两个数据库实时同步
- any 1：找到任意一个可用的数据库进行实时同步
