pg高可用

数据库服务器可以协同工作，以便在主服务器发生故障时让第二台服务器快速接管（高可用性），或者允许多台计算机提供相同的数据（负载平衡）。

大多数数据库服务器都有读/写混合请求，而读/写服务器很难组合。这是因为虽然只读数据只需要在每个服务器上放置一次，但对任何服务器的写入都必须传播到所有服务器，以便将来对这些服务器的读取请求返回一致的结果。这就是多服务器同步问题。

同步问题是服务器协同工作的根本难题。由于没有一种解决方案可以消除同步问题对所有用例的影响，因此存在多种解决方案。

一些解决方案通过仅允许一台服务器修改数据来处理同步。可以修改数据的服务器称为读/写服务器或主服务器。跟踪主服务器数据变动的服务器称为备用服务器或辅助服务器。在升级为主服务器之前无法连接的备用服务器称为温备用服务器，可以接受连接并提供只读查询的服务器称为热备用服务器。

某些解决方案是同步的，这意味着修改数据的事务只有在所有服务器都提交该事务后才被视为已提交。相比之下，异步解决方案允许在提交和传播到其他服务器之间有一些延迟，这可能会导致：1. 在备用服务器切主时丢失某些事务， 2. 负载平衡的服务器可能会返回略微过时的结果。

1. 不同解决方案的比较

1. 共享磁盘故障转移

多台服务器共享一个网络存储设备。

此方法的一个重大限制是，如果共享磁盘阵列发生故障或损坏，主服务器和备用服务器都将无法运行。

另一个问题是，在主服务器运行时，备用服务器永远不应访问共享存储。

2. DRBD

使用linux内核的DRBD模块，对文件系统的所有更改都镜像到另一台计算机的文件系统上。

唯一的限制是对备用服务器的写入必须按照与主服务器相同的顺序进行。

所以，这个方案，主服务器正常运行的时候，备用服务器的数据库需要关掉，但不能关机，因为DRBD还需要运行。

这个方案只能实现温备

3. 预写日志传送（物理复制）

通过读取预写日志 ( WAL ) 记录流，温备用服务器和热备用服务器可以保持最新状态。如果主服务器发生故障，备用服务器将包含主服务器的几乎所有数据，并且可以快速成为新的主数据库服务器。这可以是同步的，也可以是异步的，并且只能针对整个数据库服务器执行。

备用服务器可以使用基于文件的日志传送或流复制或两者结合来实现。

这个方案，可以实现热备

4. 逻辑复制

逻辑复制允许数据库服务器将数据修改流发送到另一台服务器。

PostgreSQL逻辑复制从 WAL 构建逻辑数据修改流。

逻辑复制允许按表复制数据更改。

此外，发布自身更改的服务器还可以订阅来自另一台服务器的更改，从而允许数据在多个方向上流动。

5. 基于触发器的主备复制

只支持单主。以表为单位进行操作，主服务器（通常）异步地将数据更改发送到备用服务器。在主服务器运行的同时，备用服务器可以响应查询（热备），并且可能允许一些本地数据更改或写入活动。这种形式的复制通常用于将数据分流给(offloading)大型分析或数据仓库查询。

Slony-I是此类复制的一个示例，具有表级粒度，并支持多个备用服务器。由于它以异步方式（批量）更新备用服务器，因此在故障转移期间可能会丢失数据。

6. 基于 SQL 的复制中间件

使用基于 SQL 的复制中间件，程序会拦截每个 SQL 查询并将其发送到一台或所有服务器。每台服务器独立运行。读写查询必须发送到所有服务器，以便每台服务器都能收到任何更改。但只读查询可以只发送到一台服务器，从而允许读取工作负载在它们之间分配。

如果查询未经修改就被简单广播，那么像 random()、CURRENT_TIMESTAMP 和序列这样的函数在不同的服务器上可能会有不同的值。这是因为每个服务器都是独立运行的，而且广播的是SQL查询而不是实际的数据变更。

还必须小心确保所有事务都在所有服务器上要么提交要么回滚，可能需要使用两阶段提交（PREPARE TRANSACTION 和 COMMIT PREPARED）。

Pgpool-II就是这种类型复制的例子。

2. 逻辑复制和物理复制

无论逻辑复制还是物理复制，都包含两个阶段，刚开始的全量复制和后面的增量复制。

增量复制，百分百使用的是sql statement的方式，所以区分逻辑复制和物理复制，就看全量复制阶段是怎么做的。

物理复制，在全量复制阶段，直接复制的数据文件。逻辑复制，在全量复制阶段，还是执行的sql statement。

2.1 postgresql如何实现逻辑复制

在PostgreSQL中，逻辑复制是通过发布（Publication）和订阅（Subscription）机制来实现的。

1. 配置主库（Publisher）

编辑postgresql.conf文件并设置如下参数：

wal_level = logical
max_replication_slots = [至少为订阅节点的数量]
max_wal_senders = [至少为主库上所有并发连接数的总和]

接下来，在主库上创建一个逻辑复制槽，并定义要发布的表。例如，如果你想发布一个名为t3的表，可以这样做：

1

CREATE PUBLICATION my_publication FOR TABLE t3;

如果需要包含更多的表，可以使用ALTER PUBLICATION命令添加它们。

为了安全起见，你应该创建一个专门用于逻辑复制的用户，并赋予其适当的权限：

1
2
3
4

CREATE ROLE replication_user WITH LOGIN PASSWORD 'your_password';
ALTER ROLE replication_user REPLICATION;
GRANT USAGE ON SCHEMA public TO replication_user;
GRANT SELECT ON t3 TO replication_user; -- 对于每个需要发布的表都要执行这个命令

2. 配置备库（Subscriber）

在备库上，你需要设置订阅来接收来自主库的数据变化。首先，确保备库上的postgresql.conf文件包含了足够的max_logical_replication_workers值以支持订阅。

然后，你可以创建一个订阅，指定主库的连接信息和发布的名称：

1
2
3

CREATE SUBSCRIPTION my_subscription \
CONNECTION 'host=主库IP port=5432 dbname=主库DB user=replication_user password=your_password' \
PUBLICATION my_publication;

这将启动从主库到备库的数据同步过程。

3. 监控和管理

你可以查询系统视图来监控逻辑复制的状态：

• pg_stat_replication：查看主库上的复制状态。
• pg_replication_slots：检查复制槽的状态。
• pg_subscription_rel：在备库上查看订阅关系的状态。

如果你想要停止或删除订阅，可以使用ALTER SUBSCRIPTION … DISABLE或者DROP SUBSCRIPTION命令。

2.2 postgresql如何实现物理复制

PostgreSQL的物理复制，也称为流复制（Streaming Replication），是一种基于WAL（Write-Ahead Logging）日志的实例级别复制方法。

它允许创建一个与主库完全相同的从库副本，适用于高可用性和灾难恢复场景。

1. 主库配置

编辑postgresql.conf文件，确保以下参数已正确设置：

wal_level = replica     # 设置为replica或者更详细的logical
max_wal_senders = N     # N是并发连接数，至少要等于从库数量
hot_standby = on        # 允许从库在恢复模式下执行只读查询
wal_keep_segments = M   # M是保留的WAL段数量，用于防止网络问题导致的数据丢失
archive_mode = on       # 如果使用归档备份
archive_command = 'cp %p /path/to/archive/%f'  # 归档命令

在主库上创建一个专门用于复制的用户，并赋予其权限：

1

CREATE USER replication_user REPLICATION LOGIN ENCRYPTED PASSWORD 'your_password';

修改pg_hba.conf文件，添加如下行以允许从库连接：

host replication replication_user IP_address/32 md5

2. 备份主库数据

接下来，你需要使用pg_basebackup工具来从主库创建一个基础备份并将其传输到从库。

1

pg_basebackup -h primary_host -U replication_user -D /path/to/data_directory -P -v -R

这里的参数解释如下：

• -h: 指定主库的主机名或IP地址。
• -U: 使用的用户名，这里使用之前创建的复制用户。
• -D: 备份数据的目标目录。
• -P: 显示进度信息。
• -v: 显示详细输出。
• -R: 自动创建必要的恢复配置文件。

3. 配置从库

在从库上，编辑postgresql.conf文件，设置以下参数：

hot_standby = on  # 允许只读查询
port = different_port_number  # 使用不同于主库的端口号

如果pg_basebackup没有自动生成recovery.conf文件，则手动创建一个，并添加以下内容：

standby_mode = 'on'
primary_conninfo = 'host=primary_host port=5432 user=replication_user password=your_password'
trigger_file = '/tmp/postgresql.trigger.5432'  # 可选，用于手动触发故障转移

完成上述步骤后，使用步骤2生成/path/to/data_directory数据文件来启动从库服务。如果一切配置正确，从库将开始从主库接收WAL日志并应用这些日志来保持数据同步。

4. 监控和管理

可以使用以下SQL命令监控复制状态：

• 在主库上：

  1	SELECT * FROM pg_stat_replication;

• 在从库上：

  1	SELECT * FROM pg_stat_wal_receiver;

上一篇 « pg的编译安装 下一篇 » pg为何需要HOT