PostgreSQL建立Hot Standby的Replication机制

PostgreSQL的Replication机制，大概有三种。

这里用的是第一种。

安装PostgreSQL

sudo apt install postgresql

Ubuntu 16.04上，PostgreSQL版本为9.5，配置文件的目录为 /etc/postgresql/9.5/main/ 。 以下的配置文件都在此目录下。

当然，用 Docker 来部署也是更灵活的，需要去 library/postgres - Docker Hub 寻找合适的镜像。 配置文件的位置，可以在运行时指定。

master机

先去创建一个同步专用账户 repl ：

$ sudo su - postgres
$ psql
postgres=# create user repl replication password '******';

然后修改两个配置文件。 其一是 postgresql.conf ：

#------------------------------------------------------------------------------
# CONNECTIONS AND AUTHENTICATION
#------------------------------------------------------------------------------

# - Connection Settings -
listen_addresses = '*'                  # what IP address(es) to listen on;
port = 5432                             # (change requires restart)
max_connections = 10000                 # (change requires restart)

#------------------------------------------------------------------------------
# WRITE AHEAD LOG
#------------------------------------------------------------------------------

# - Settings -
wal_level = hot_standby                 # minimal, archive, hot_standby, or logical
synchronous_commit = local              # synchronization level;

#------------------------------------------------------------------------------
# REPLICATION
#------------------------------------------------------------------------------

# - Sending Server(s) -
max_wal_senders = 32            # max number of walsender processes
wal_keep_segments = 64          # in logfile segments, 16MB each; 0 disables

# - Master Server -
synchronous_standby_names = 'pg_standby'        # standby servers that provide sync rep
hot_standby = on                        # "on" allows queries during recovery

以上配置仅是和热备份相关的片段。 其中， 'pg_standby' 需要在 /etc/hosts 中配置为slave的IP。

其二是 pg_hba.conf 文件：

# TYPE  DATABASE        USER            ADDRESS                 METHOD

# IPv4 local connections:
host    all             all             127.0.0.1/32            trust
# IPv6 local connections:
host    all             all             ::1/128                 trust
# Allow replication connections from localhost, by a user with the
# replication privilege.
host    replication     repl        0.0.0.0/0        md5

最后一行就是允许 repl 的访问连接。

完成修改后，重启服务。

sudo systemctl restart postgresql.service

slave机

修改前，先停止服务。

sudo systemctl stop postgresql.service

先做首次备份（需要输入前面 repl 用户的密码）：

rm -rf /var/lib/postgresql/9.5/main/
pg_basebackup -h pg_master -U repl -p 5432 -F p -P -x -R -D /var/lib/postgresql/9.5/main/ -l 20180907

然后修改一个配置， postgresql.conf ：

listen_addresses = 'localhost,pg_master'
port = 5432
max_connections = 10000
wal_level = hot_standby
max_wal_senders = 8
wal_keep_segments = 16
hot_standby = on

最后新增一个配置， recovery.conf ：

standby_mode = 'on'
primary_conninfo = 'host=pg_master port=5432 user=repl password=******** sslmode=disable sslcompression=1 application_name=pg_standby'
recovery_target_timeline = 'latest'

完成修改后，启动服务。

sudo systemctl start postgresql.service

测试

在master上建表：

$ sudo su - postgres
$ psql
postgres=# create database test;
CREATE DATABASE

然后，在slave上查看结果：

postgres=# \l
                                  List of databases
   Name    |  Owner   | Encoding |   Collate   |    Ctype    |   Access privileges
-----------+----------+----------+-------------+-------------+-----------------------
 postgres  | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 |
 template0 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres          +
           |          |          |             |             | postgres=CTc/postgres
 template1 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres          +
           |          |          |             |             | postgres=CTc/postgres
 test      | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 |
(4 rows)

一些问题

端口

如果在配置时修改了默认端口 5432 ，比如改成 65432 ， 则需要在 pg_basebackup 同步时添加 -p 65432 参数，否则备份失败。 也需要在所有使用的地方注意，否则会找不到数据库服务。

一个错误

FATAL:  hot standby is not possible because max_connections = 100 is a lower setting than on the master server (its value was 10000)

以上是导致slave的PostgreSQL起不来的一个原因。 在 postgresql.conf 中， max_connections 不应该小于master。 由于master设为 10000 ，这里至少需要设为 10000 。

一个警告

postgres=# create database test;
^CCancel request sent
WARNING:  canceling wait for synchronous replication due to user request
DETAIL:  The transaction has already committed locally, but might not have been replicated to the standby.

执行写操作时，总是无法返回； Ctrl + c 取消后，又收到警告。 而slave上，明明已经同步完成。

可以通过配置 postgresql.conf ，修改一行解决。

synchronous_commit = local                # synchronization level;

这表示每次commit设置为仅保证本地完成，不等待远程同步。 这相当于在 CAP 中取 AP ，比较适合当前的使用场景。

参考

• postgresql 同步流复制以及主备切换 - 简书
• PostgreSQL 同步流复制原理和代码浅析-博客-云栖社区-阿里云
• How to Install and Configure PostgreSQL Replication with Hot Standby on Ubuntu 15.04
• Three Approaches to PostgreSQL Replication and Backup