本教程适用于以下操作系统版本

• Debian 11 (Bullseye)
• Debian 10 (Buster)

本页内容

• 先决条件
• 配置 /etc/hosts 和 PostgreSQL 软件源
• 安装 PostgreSQL 和 pglogical 扩展
• 配置 UFW 防火墙
• 配置 PostgreSQL 以启用复制
• 创建用户和数据库
• 通过 pglogical 启用表复制 将 pgdg02 设置为订阅者节点
• 测试复制
• 结论

• 将 pgdg02 设置为订阅者节点

PostgreSQL 是一个免费的开源数据库管理系统，专注于可扩展性和 SQL 合规性。PostgreSQL 是一个高级的企业级 RDBMS（关系型数据库管理系统），同时支持 SQL（关系型）和 JSON（非关系型）查询。它是一个高度稳定且企业级的数据库管理系统，提供多种解决方案，使你能够构建高可用性和故障转移。PostgreSQL 被用作 Web 应用、移动应用和分析应用的主数据库。它以可靠性、灵活性、功能健壮性和性能而享有盛誉。

在本教程中，我将在两台 Debian 11 服务器上通过 pglogical2 安装并配置 PostgreSQL 数据库服务器的逻辑复制（Logical Replication）。你将安装 PostgreSQL 服务器及其 pglogical 扩展包，然后在两台或多台 PostgreSQL 服务器之间配置复制。

pglogical 是 PostgreSQL 的一个扩展，使你能够在数据库级别配置逻辑流复制（logical streaming replication）。它使用发布/订阅（publish/subscribe）模型进行复制。借助 pglogical 扩展，你不复制 PostgreSQL 上的全部可用数据，而是将现有数据库中的特定表复制并同步到其他 PostgreSQL 服务器。

先决条件

要完成本指南，你必须满足以下要求：

• 两台或更多台 Debian 11 服务器。
• 具有 sudo/root 管理员权限的非 root 用户。

本示例使用了两台 Debian 服务器，其详细信息如下：

Hostname    IP Address      Used as
------------------------------------------
pgdg01      192.168.5.25    Publisher Node
pgdg02      192.168.5.26    Subscriber Node

当服务器准备就绪后，你就可以开始通过 pglogical 安装和配置 PostgreSQL 复制。

配置 /etc/hosts 和 PostgreSQL 软件源

本示例假设你使用的是全新的 Debian 11 服务器，因此你要做的第一件事就是为所有 Debian 系统配置 PostgreSQL 软件源并安装 PostgreSQL。

你必须在所有 Debian 服务器上执行以下步骤。

首先，使用以下 nano 编辑器命令打开配置文件 '/etc/hosts'。

sudo nano /etc/hosts

将以下行添加到文件中，并确保修改 IP 地址和主机名的详细信息。

192.168.5.25    pgdg01
192.168.5.26    pgdg02

完成后保存并关闭 '/etc/hosts' 文件。

接下来，输入以下 apt 命令安装一些基础依赖。当提示时输入 y 并按 ENTER 继续。

sudo apt install wget gnupg2 lsb-release curl apt-transport-https ca-certificates

现在运行以下命令，为 Debian 服务器添加 PostgreSQL 的 GPG 密钥和软件源。

curl https://www.postgresql.org/media/keys/ACCC4CF8.asc | gpg --dearmor | sudo tee /usr/share/keyrings/pgdg.gpg > /dev/null 2>&1
sudo sh -c 'echo "deb [signed-by=/usr/share/keyrings/pgdg.gpg] http://apt.postgresql.org/pub/repos/apt $(lsb_release -cs)-pgdg main" > /etc/apt/sources.list.d/pgdg.list'

然后，更新并刷新你的软件包索引以应用更改。

sudo apt update

添加后，你应该在列表中看到 PostgreSQL 软件源。

添加 PostgreSQL 软件源后，接下来你将安装 PostgreSQL 服务器和 pglogical 扩展。

安装 PostgreSQL 和 pglogical 扩展

默认情况下，官方 PostgreSQL 软件源提供多个版本的 PostgreSQL，例如 PostgreSQL 13、14 和 15。在本示例中，你将使用最新版本的 PostgreSQL 15 来安装和配置 PostgreSQL 复制。

输入以下 'apt install' 命令来安装 PostgreSQL 服务器。输入 y 确认安装并按 ENTER 继续。

sudo apt install postgresql

安装完成后，输入以下 systemctl 命令工具来验证 PostgreSQL 服务器，并确保该服务已启用且正在运行。

sudo systemctl is-enabled postgresql
sudo systemctl status postgresql

输出 'enabled' 确认 PostgreSQL 服务已启用，并将在系统启动时自动启动。而输出 'active (running)' 确认当前 PostgreSQL 服务正在运行。

接下来，输入以下 apt 命令安装 pglogical 扩展包。如果你使用的是不同版本的 PostgreSQL，应替换对应的包号，例如 'postgresql-version-pglogical'。

sudo apt install postgresql-15-pglogical

当提示时输入 y 并按 ENTER 继续。

PostgreSQL 已启动并运行，且 pglogical 扩展也已安装。你已准备好开始通过 pglogical 配置逻辑流复制。

配置 UFW 防火墙

在配置 PostgreSQL 服务器之前，你现在必须配置 ufw 防火墙，以保护并限制到 PostgreSQL 服务器的连接。这样，你只会接受来自可信网络的连接，这些连接由你的 PostgreSQL 部署进行保护。

要安装 ufw，请输入以下 apt 命令。当提示时，输入 y 确认并按 ENTER 继续。

sudo apt install ufw

接下来，输入以下 ufw 命令开放 OpenSSH 服务。输出 'Rule added' 确认新规则已添加到 ufw。

sudo ufw allow OpenSSH

现在输入以下 ufw 命令，允许你的可信网络访问 PostgreSQL 服务器的 5432 端口。请务必修改以下命令中的 IP 地址。

sudo ufw allow from 192.168.5.0/24 to any port 5432

接下来，输入以下命令启动并启用 ufw 防火墙。当提示确认时，输入 y 并按 ENTER 继续。

sudo ufw enable

输出 'Firewall is running and enabled' 确认 ufw 正在运行且已启用，这意味着 ufw 防火墙将在系统启动时自动启动。

你现在可以通过输入以下命令来验证 ufw 的状态和已启用端口列表。

sudo ufw status

你应该看到 ufw 防火墙 'Status: Active'，并且 OpenSSH 服务和 PostgreSQL 服务器端口 '5432' 已添加并在 ufw 防火墙上可用。

配置 PostgreSQL 以启用复制

WAL（Write-Ahead Logging，预写日志）是一种确保 PostgreSQL 服务器数据完整性的方法。对表和索引的任何更改都必须先被记录，之后才能写入。

在 PostgreSQL 中，你可以使用三种 WAL 方法来启用复制。

• 默认的 WAL 设置为 'replica'（副本），它允许你配置归档和复制，并允许你在备用服务器（standby server）上运行只读查询。
• minimal（最小）允许你移除除部分重要信息之外的所有日志记录，这些重要信息用于从崩溃或立即关机中恢复。
• logical（逻辑）方法允许你向 WAL 中添加一些支持逻辑解码（logical decoding）的信息。

在本节中，你将配置 PostgreSQL 在本地网络 IP 地址上运行，通过 'pglogical' 扩展设置 'logical'（逻辑）wal 级别，然后通过 pg_hba.conf 文件启用复制用户。

首先，输入以下命令登录 PostgreSQL shell。

sudo -u postgres psql

然后，验证 PostgreSQL 服务器上默认的 'wal_level' 配置。

select name,setting,unit from pg_settings where name in ('wal_level','max_worker_processes','max_replication_slots','max_wal_senders','shared_preload_libraries','track_commit_timestamp');

下面的输出确认 PostgreSQL 服务器上的默认 wal 级别为 'replica'（副本），这是当今 PostgreSQL 版本的标准设置。

现在输入 '\q' 退出 PostgreSQL shell。

接下来，使用以下 nano 编辑器命令打开默认的 PostgreSQL 配置文件 '/etc/postgresql/15/main/postgresql.conf'。

sudo nano /etc/postgresql/15/main/postgresql.conf

取消注释 'listen_addresses' 参数，并将你的内部服务器 IP 地址添加到其中。

'pgdg01' 服务器上将使用的配置如下。

listen_addresses = 'localhost, 192.168.5.25'

而 'pgdg02' 服务器上将使用的配置如下。

listen_addresses = 'localhost, 192.168.5.26'

现在添加以下参数以通过 'pglogical' 扩展启用 'logical'（逻辑）wal 级别。两台服务器的 wal 级别设置配置相似。参数 'shared_preload_libraries = 'pglogical'' 将通过 'pglogical' 扩展启用逻辑 wal 级别。

max_worker_processes = 10
shared_preload_libraries = 'pglogical'
track_commit_timestamp = 'on'
wal_level = 'logical'

完成后保存并关闭 '/etc/postgresql/15/main/postgresql.conf' 文件。

接下来，使用以下 nano 编辑器命令打开 PostgreSQL 认证文件 '/etc/postgresql/15/main/pg_hba.conf'。

sudo nano /etc/postgresql/15/main/pg_hba.conf

在文件末尾添加以下行。这将允许来自主机 '192.168.5.1/24' 的任意用户进行认证，包括普通 PostgreSQL 用户和复制用户。

# IPv4 local connections:
host    all             all             192.168.5.1/24          scram-sha-256
# replication privilege.
host    replication     all             192.168.5.1/24          scram-sha-256

完成后保存并关闭文件 '/etc/postgresql/15/main/pg_hba.conf'。

当 '/etc/postgresql/15/main/postgresql.conf' 和 '/etc/postgresql/15/main/pg_hba.conf' 的配置完成后，输入以下 systemctl 命令工具重启 PostgreSQL 服务并应用更改。

sudo systemctl restart postgresql

现在输入以下 ss 命令，确保 PostgreSQL 正在你的本地 IP 地址上运行。

ss -tulpn | grep 5432

你应该收到类似以下的输出 —— 在 pgdg01 服务器上，PostgreSQL 服务器应运行在 '192.168.5.25:5432'。在 pgdg02 服务器上，PostgreSQL 服务器应运行在 '192.168.5.26:5432'。

最后，输入以下命令登录 PostgreSQL shell，并验证 PostgreSQL 服务器上已启用的 wal 级别。

sudo -u postgres psql

输入以下 PostgreSQL 查询来验证 PostgreSQL 服务器上已启用的 wal 级别。

select name,setting,unit from pg_settings where name in ('wal_level','max_worker_processes','max_replication_slots','max_wal_senders','shared_preload_libraries','track_commit_timestamp');

输出 'wal_level | logical' 确认 wal 级别 'logical'（逻辑）已启用，这是通过 'shared_preload_library | pglogical' 实现的。

至此，你已经为通过 'pglogical' 进行逻辑流复制配置了具有 'logical'（逻辑）wal 级别的 PostgreSQL 服务器。此外，两台 PostgreSQL 服务器都运行在内部网络 IP 地址上。在下一步中，你将创建一个新 PostgreSQL 用户和数据库，然后启用 'pglogical' 扩展。

创建用户和数据库

pglogical 是一个在数据库级别工作的 PostgreSQL 扩展。因此，你不是复制整个 PostgreSQL 系统，而是复制 PostgreSQL 上可用的特定表或数据库。

要配置 pglogical，你必须拥有具有 SUPERUSER 权限/角色（privilege/role）的 PostgreSQL。此外，你还必须在要复制的目标数据库上启用 'pglogical' 扩展。

输入以下命令登录 PostgreSQL shell。

sudo -u postgres psql

使用以下 PostgreSQL 查询创建一个新用户和数据库。

在本示例中，你将创建一个名为 'appuser'、具有 'SUPERUSER' 权限的新用户。在撰写本文时，pglogical 需要 SUPERUSER 权限才能正常工作。你还将创建一个名为 'appdb' 的新数据库，这是你要在其上启用复制的目标数据库。

CREATE ROLE appuser WITH LOGIN SUPERUSER ENCRYPTED PASSWORD 'Str0ngP4ssw0rd';
CREATE DATABASE appdb WITH OWNER appuser;

使用以下查询验证 PostgreSQL 服务器上的用户和数据库列表。你应该看到具有 SUPERUSER 权限的新用户 'appuser' 和新数据库 'appdb'。

\du
\l

接下来，使用 '\c' 或 '\connect' 查询连接到新数据库 'appdb'。连接后，你的 PostgreSQL shell 提示符将变为类似 'appdb=#' 的形式。

\c appdb

输入以下查询在 'appdb' 数据库上启用 'pglogical' 扩展。

CREATE EXTENSION pglogical;

然后使用以下查询验证当前 'appdb' 数据库上已启用扩展的列表。你应该看到 'pglogical' 扩展已在 'appdb' 数据库上启用。

\dx

创建用户和数据库并启用 pglogical 后，接下来你将开始配置 pglogical，以在两台 PostgreSQL 服务器之间启用数据库复制。

通过 pglogical 启用表复制

在本节中，你将针对将在 'appdb' 数据库上创建的特定表启用逻辑复制。

### 将 pgdg01 设置为发布者节点（Publisher Node）

在 '发布者节点'（publisher node）—— 'pgdg01 服务器' 上输入以下 PostgreSQL 查询，创建一个名为 'teachers' 的新表。

CREATE TABLE teachers (
  id INT PRIMARY KEY,
  first_name VARCHAR,
  last_name VARCHAR,
  subject VARCHAR
);

通过输入以下查询向 'teachers' 表插入新数据。

INSERT INTO teachers(id, first_name, last_name, subject)
VALUES 
(1, 'Alice', 'Wonderland', 'Math'),
(2, 'Bob', 'Rista', 'Physics'),
(3, 'John', 'Bonas', 'English');

然后检索你的新数据，确保其在发布者节点 pgdg01 服务器上可用。

select count(*) from teachers;
select * from teachers;

下面的输出确认新数据已添加到 'teachers' 表。

接下来，要通过 pglogical 在 PostgreSQL 数据库/表上设置复制，你必须按照以下步骤操作：

• 在发布者服务器 pgdg01 上创建提供者节点（provider node）。
• 创建一个副本集（replication set），用于控制数据库中哪些表将被复制。
• 将表添加到副本集（replica set）。

输入以下命令创建一个名为 'provider1' 的新提供者节点（provider node）。请务必将 PostgreSQL 的详细信息修改为 pgdg01 服务器。

select pglogical.create_node(node_name := 'provider1', dsn := 'host=pgdg01 port=5432 dbname=appdb user=appuser');

接下来，输入以下查询创建一个名为 'replication_set' 的新副本集（replica set）。

select pglogical.create_replication_set('replication_set');

然后，通过输入以下查询将 'teachers' 表添加到副本集 'replication_set'。

select pglogical.replication_set_add_table(set_name := 'replication_set', relation := 'teachers', synchronize_data := true);

至此，你已使用 pgdg01 PostgreSQL 服务器创建了提供者节点，创建了副本集 'replication_set'，并将 'teachers' 表添加到了 'replication_set'。接下来，你将在 'pgdg02' 服务器上创建并配置一个订阅者节点（subscriber node）。

将 pgdg02 设置为订阅者节点（Subsciber Node）

切换到 'pgdg02' 服务器，输入以下 nano 编辑器命令创建一个新 PostgreSQL 密码文件 '/var/lib/postgresql/.pgpass'。该密码文件将用于你的

sudo -u postgres nano /var/lib/postgresql/.pgpass

将以下行添加到文件中，并务必将用户名和密码修改为你自己的信息。

pgdg01:5432:appdb:appuser:Str0ngP4ssw0rd
pgdg01:5432:replication:appuser:Str0ngP4ssw0rd
pgdg02:5432:appdb:appuser:Str0ngP4ssw0rd
pgdg02:5432:replication:appuser:Str0ngP4ssw0rd

完成后保存并关闭文件。

现在输入以下命令，将文件 '/var/lib/postgresql/.pgpass' 的权限更改为只读。

sudo chmod 0600 /var/lib/postgresql/.pgpass

接下来，使用以下命令登录 PostgreSQL。

sudo -u postgres psql

登录后，输入以下查询重新加载 PostgreSQL 服务器配置，然后退出以确保更改生效。

select pg_reload_conf();
\q

现在再次登录 pgdg02 服务器上的 PostgreSQL shell。

sudo -u postgres psql

连接到你已创建的数据库 'appdb'。

\c appdb

然后输入以下 PostgreSQL 查询创建一个新表 'teachers'，该表必须与你在提供者节点 'pgdg01' 上的表一致。

CREATE TABLE teachers (
  id INT PRIMARY KEY,
  first_name VARCHAR,
  last_name VARCHAR,
  subject VARCHAR
);

如果你检查可用数据，应该会得到一个空表。

select count(*) from teachers;
select * from teachers;

创建 PostgreSQL 密码文件和 'teachers' 表后，接下来你将按照以下步骤将 'pgdg02' 服务器上的 PostgreSQL 设置为订阅者（subscriber）：

• 在 pgdg02 服务器上创建订阅者节点（Subscriber node）。
• 创建订阅（subscription），它将在后台启动同步和复制过程。
• 验证复制过程。

输入以下查询，使用 PostgreSQL 服务器 pgdg02 创建一个新订阅者节点（subscriber node）。请务必修改主机名、数据库名称和用户的详细信息。

select pglogical.create_node(node_name := 'subscriber1', dsn := 'host=pgdg02 port=5432 dbname=appdb user=appuser');

接下来，创建一个名为 'subscription1' 的新订阅（subscription）。请务必输入名为 'replication_set' 的副本集，且提供者（provider）是 pgdg01 服务器上的 PostgreSQL。这样，'teachers' 表的复制将在后台启动并运行。

select pglogical.create_subscription(subscription_name := 'subscription1', replication_sets := array['replication_set'], provider_dsn := 'host=pgdg01 port=5432 dbname=appdb user=appuser' );

输入以下查询验证订阅状态。

select *  FROM pglogical.show_subscription_status();

你应该收到类似以下的输出 —— 'subscription1' 的状态正在向提供者 'provider1' 和副本集 'replication_set' 进行复制。

现在输入以下查询，等待订阅异步开始复制并完成数据同步。

SELECT pglogical.wait_for_subscription_sync_complete('subscription1');

最后，你可以在 pgdg02 PostgreSQL 服务器上验证 'teachers' 表中的数据。

select count(*) from teachers;
select * from teachers;

如果复制成功，你的数据现在应该已经可用并复制到 PostgreSQL pgdg02 服务器。

测试复制

在本节中，你将通过从发布者节点 pgdg01 插入新数据来验证 PostgreSQL 服务器上的复制。然后，你将通过订阅者节点验证数据，确保数据已被复制。

首先，输入以下命令连接到 pgdg01 服务器上的数据库 'appdb'。

sudo -u postgres psql -U appuser -h pgdg01 -d appdb

输入以下查询，向 'teachers' 表插入新数据。

INSERT INTO teachers(id, first_name, last_name, subject)
VALUES 
(4, 'Ian', 'Gibson', 'Geography'),
(5, 'Tom', 'Riddle', 'Biology'),
(6, 'Jared', 'Dunn', 'Chemistry');

验证 'teachers' 表，确保新数据已添加。

select count(*) from teachers;
select * from teachers;

接下来，切换到 pgdg02 服务器，输入以下命令连接到数据库 'appdb'。

sudo -u postgres psql -U appuser -h pgdg02 -d appdb

输入以下查询验证 'teachers' 表上的可用数据。如果复制成功，你应该会在 pgdg02 服务器上看到新更新的数据。

select count(*) from teachers;
select * from teachers;

至此，你已完成在 PostgreSQL 服务器上通过 pglogical 配置逻辑流复制。

结论

在本教程中，你已在 Debian 11 服务器上通过 pglogical 安装并配置了逻辑流复制（logical stream replication）。同时，你还学习了 pglogical 扩展在数据库级别创建和管理 PostgreSQL 复制的基本用法。

此外，你还通过官方 PostgreSQL 软件源完成了在 Debian 服务器上安装最新的 PostgreSQL 15，并通过 UFW 防火墙保护了 PostgreSQL 服务器。

至此，你可以添加另一台 PostgreSQL 服务器并配置 pglogical 扩展，以启用到多台服务器的复制。你还可以借助 HAPROXY 配置负载均衡，或配置 PostgreSQL 服务器的故障转移（failover）。如需详细信息，请访问 PostgreSQL 官方文档。