PostgreSQL复制与集群管理指南

### PostgreSQL 复制与集群管理指南 #### 1. 使用 Londiste3 进行复制 在 PostgreSQL 中，Londiste3 是一个强大的工具，可用于复制数据。以下是使用 Londiste3 复制 pgbench 表的详细步骤： 1. **在主 PostgreSQL 服务器上注册 pgbench 表**： ```bash londiste3 primary.ini add-table pgbench_accounts londiste3 primary.ini add-table pgbench_branches londiste3 primary.ini add-table pgbench_tellers ``` 2. **在订阅者 PostgreSQL 服务器上注册 pgbench 表**： ```bash londiste3 subscriber.ini add-table pgbench_accounts londiste3 subscriber.ini add-table pgbench_branches londiste3 subscriber.ini add-table pgbench_tellers ``` 3. **比较两个节点上的数据**： ```bash londiste3 subscriber.ini compare ``` 其工作原理如下： - 首先，需要将表结构复制到订阅者。Londiste 仅复制数据，因此假设源表和目标表具有完全相同的列。可以使用 `pg_dump` 来获取以 `pgbench` 开头的表的仅模式（`-s`）提取，然后使用 `-h` 参数在订阅者数据库上执行生成的 SQL，创建所有 `pgbench` 表作为空壳。 - 接着，建议进入 `/etc/skytools` 目录，因为配置文件始终是 `londiste3` 的第一个参数，这样可以避免每次都输入完整的文件路径。 - 为了在主服务器上注册每个表，需要指定其配置文件、`add-table` 参数以及要注册的表。与 Slony 和 Bucardo 一样，需要添加具有主键的三个 `pgbench` 表。对于订阅者，使用其配置文件重复此过程。 - 完成上述操作后，Londiste 将开始检查每个服务器上的表内容，并复制订阅者上缺失的数据。所有未来的修改也将复制到订阅者。 Londiste3 还提供了一个有趣的功能，即通过执行校验和比较来确认数据是否同步。如果等待片刻让数据同步，然后使用 `compare` 参数执行 `londiste3`，应该会看到每个表的相关信息。 #### 2. 设置 pglogical PostgreSQL 9.4 引入了复制插槽功能，这使得在逻辑级别解码事务日志并提取数据库流量以进行远程重放成为可能。与标准复制不同，复制插槽可以挖掘与用户需求相关的特定信息。 2ndQuadrant 的 pglogical 是最早利用复制插槽的 PostgreSQL 扩展之一。与 Slony、Bucardo 和 Londiste 一样，pglogical 可以将单个表从一个数据库复制到另一个数据库。但不同的是，它不会使用影响性能的触发器来处理表，也不依赖外部守护进程来协调数据复制流。 以下是安装 pglogical 的步骤： 1. **准备工作**： - 获取 pglogical 的最新版本（撰写本文时为 1.2.1）的源代码，可从以下 URL 下载： ``` https://github.com/2ndQuadrant/pglogical ``` - 根据系统类型安装必要的库： - 在基于 Debian 的系统上，使用以下 `apt-get` 命令： ```bash sudo apt-get install libselinux1-dev libxslt1-dev libpam0g-dev libedit-dev ``` - 在基于 Red-Hat 的系统上，使用以下 `yum` 命令： ```bash sudo yum install libselinux-devel libxslt-devel pam-devel libedit-devel ``` 2. **安装步骤**： - 从以下链接获取最新的源代码分发： ``` https://github.com/2ndQuadrant/pglogical/releases ``` - 提取源代码并进入相应目录： ```bash tar -xzf REL1_2_1.tar.gz cd pglogical-REL1_2_1 ``` - 以具有 root 权限的用户身份构建并安装 pglogical： ```bash export PATH=/usr/lib/postgresql/9.6/bin:/usr/pgsql-9.6/bin:$PATH make USE_PGXS=1 clean all sudo -E make install ``` - 确保主服务器和订阅者服务器上的 `pg_hba.conf` 文件包含以下行： ```plaintext host all rep_user 192.168.56.1/24 md5 host replication rep_user 192.168.56.1/24 md5 ``` - 确保主服务器上的 `postgresql.conf` 中的 `wal_level`、`max_replication_slots` 和 `shared_preload_libraries` 设置如下： ```plaintext wal_level = logical max_replication_slots = 5 shared_preload_libraries = 'pg_stat_statements, pglogical' ``` - 确保订阅者服务器上的 `shared_preload_libraries` 设置如下： ```plaintext shared_preload_libraries = 'pg_stat_statements, pglogical' ``` - 重启两个服务器上的 PostgreSQL 服务： ```bash pg_ctl -D $PGDATA restart -m fast ``` - 如果复制用户 `rep_user` 不存在，则在两个节点上创建该用户： ```sql CREATE USER rep_user WITH REPLICATION SUPERUSER PASSWORD 'newpass'; ``` - 在两个服务器上的 `postgres` 用户主目录中创建一个名为 `.pgpass` 的文件，内容如下： ```plaintext *:*:*:rep_user:newpass ``` - 修改 `.pgpass` 文件的权限： ```bash chmod 600 ~/.pgpass ``` - 在主服务器的 `postgres` 数据库中执行以下语句： ```sql CREATE EXTENSION pglogical; SELECT pglogical.create_node( node_name := 'origin', dsn := 'host=192.168.56.10 dbname=postgres user=rep_user' ); ``` - 最后，在订阅者节点的 `postgres` 数据库中执行以下语句： ```sql CREATE EXTENSION pglogical; SELECT pglogical.create_node( node_name := 'target', dsn := 'host=192.168.56.30 dbname=postgres user=rep_user' ); ``` 其工作原理如下： 大部分工作实际上是可选的，但展示了如何确保特定用户安全地操作 pglogical 复制流。选择的用户必须是超级用户，并且能够访问 `postgres` 复制流以解码其中的逻辑指令。 与本章中的其他操作类似，首先进行标准的提取、`make` 和 `make install` 操作。为了确保 pglogical 构建所需的二进制文件可用，还添加了一个通用的 `PATH` 导出。 接下来，更改了几个配置设置。`pg_hba.conf` 中的两行确保 `rep_user` 用户可以连接到主服务器上的复制流以及任何可能用作表复制源的数据库。将 `wal_level` 设置为 `logical`，因为这是在 PostgreSQL 中