PostgreSQL复制技术全解析

### PostgreSQL 复制技术全解析 #### 1. 异步复制升级 自 PostgreSQL 9.0 版本发布以来，数据库管理员可以使用异步流复制。与早期版本的热备方法不同，副本服务器可以连接到上游 PostgreSQL 服务器并直接获取数据修改。在低网络延迟和快速事务的情况下，流副本通常仅比主服务器落后几毫秒。 在高可用性方面，我们可以通过将数据库复制到多个服务器来进行水平扩展。这意味着需要将整个数据库复制到每个服务器，但对于中小型数据库实例来说，这是一个相对较小的要求。此外，还可以生成最新的备份，对实时数据进行临时查询，并在不影响主数据库的情况下将信息汇总到报告中。 ##### 1.1 准备工作 继续之前设置热备的工作，构建一个可用的热备环境，然后修改备用设置以包含流复制和增强安全性。 ##### 1.2 操作步骤 假设服务器 `192.168.56.10` 是主 PostgreSQL 服务器，`192.168.56.20` 是异步副本。按照以下步骤构建 PostgreSQL 异步副本： 1. 使用以下 SQL 语句为 `rep_user` 用户设置密码： ```sql ALTER USER rep_user WITH PASSWORD 'newpass'; ``` 2. 在主服务器上，修改 `pg_hba.conf` 文件，删除所有对 `rep_user` 用户的引用，然后添加以下行： ```plaintext host replication rep_user 192.168.56.20/32 md5 ``` 3. 以 `postgres` 用户身份在主服务器上重新加载配置文件： ```bash pg_ctl -D /db/pgdata reload ``` 4. 在副本服务器上，在 `postgres` 用户的主目录中创建一个名为 `.pgpass` 的文件，内容如下： ```plaintext 192.168.56.10:*:replication:rep_user:newpass ``` 5. 使用以下命令更改 `.pgpass` 文件的权限： ```bash chmod 600 ~/.pgpass ``` 6. 修改恢复服务器上的 `recovery.conf` 文件，使其内容如下： ```plaintext standby_mode = on primary_conninfo = 'host=192.168.56.10 user=rep_user' restore_command = 'cp /db/pg_archived/%f %p 2>/dev/null' ``` 7. 以 `postgres` 用户身份在流副本服务器上重新加载配置文件： ```bash pg_ctl -D /db/pgdata reload ``` 8. 在主 PostgreSQL 服务器上执行以下 SQL 语句，确认备用服务器已连接： ```sql SELECT client_addr, usename, state FROM pg_stat_replication; ``` ##### 1.3 工作原理 不建议使用信任认证，因为这会留下长期的安全漏洞。因此，需要确保 `rep_user` 用户有密码，并修改 `pg_hba.conf` 文件以使用常规的 `md5` 认证。 在副本服务器上创建 `.pgpass` 文件，当连接信息匹配时，会自动发送所需的密码。如果连接信息不同，PostgreSQL 客户端库将不会发送密码，客户端将收到错误。同时，需要确保 `.pgpass` 文件的权限正确，只有 `postgres` 用户可以访问。 修改 `recovery.conf` 文件以包含 `primary_conninfo`，用于指定建立流复制的连接信息。由于密码存储在 `.pgpass` 文件中，因此无需在此处输入。此外，移除了 `pg_standby`，改用 `cp` 命令并抑制错误。 重新加载备用服务器后，它将成为流副本而不是常规的热备。可以通过检查主服务器上的 `pg_stat_replication` 视图来确认。 #### 2. 异步复制的更多功能 当切换到异步复制时，会解锁许多新功能，随着 PostgreSQL 版本的不断发展，这些功能会越来越多。 ##### 2.1 级联复制 在有多个流副本的情况下，早期版本的 PostgreSQL 要求副本服务器直接连接到主服务器。从 9.3 版本及以上，PostgreSQL 允许流副本订阅其他副本，从而将复制任务卸载到备用服务器拓扑中，进一步减轻主数据库服务器的压力。 级联复制还包括备份功能。`pg_basebackup` 工具通过调用 `pg_start_backup()` 函数将 PostgreSQL 置于备份模式。由于该函数会写入数据库，通常不能在只读的流副本上使用，但级联复制使得在备用服务器上使用 `pg_basebackup` 成为可能，大大简化了备份过程并减少了主服务器的开销。 ##### 2.2 使用复制槽 依赖事务日志文件存在风险，如果主服务器在副本处理之前删除了某个日志文件，可能需要完全重建副本。如果使用 PostgreSQL 9.4 或更高版本，可以使用复制槽来避免这种情况。 - 在主服务器上创建复制槽： ```sql SELECT * FROM pg_create_physical_replication_slot('pg2_slot'); ``` - 在副本的 `recovery.conf` 文件中添加以下行，然后启动（或重启）实例： ```plaintext primary_slot_name = 'pg2_slot' ``` 使用复制槽可以确保副本不会落后，但需要注意限制副本的停机时间，否则主服务器可能会积累过多不必要的事务日志文件，导致存储空间不足。如果副本需要长时间离线，可以使用以下 SQL 删除未使用的复制槽： ```sql SELECT pg_drop_replication_slot('pg2_slot'); ``` ##### 2.3 在副本上查看复制状态 从 PostgreSQL 9.6