如何在 Ubuntu 16.04 上为 PostgreSQL 9.6 设置主从复制

本教程适用于这些操作系统版本

Ubuntu 16.04（Xenial Xerus）
Ubuntu 15.04（Vivid Vervet）

在此页

第 1 步 - 安装 PostgreSQL 9.6
第 2 步 - 配置 UFW 防火墙
第 3 步 - 配置 PostgreSQL 主服务器
第 4 步 - 配置从属服务器
第 5 步 - 将 PostgreSQL 数据从 MASTER 复制到 SLAVE
第 6 步 - 测试
参考资料

PostgreSQL 或 Postgres 是一个开源对象关系数据库管理系统 (ORDBMS)，拥有超过 15 年的积极发展。它是一个功能强大的数据库服务器，可以处理高工作负载。 PostgreSQL 可用于 Linux、Unix、BSD 和 Windows 服务器。

主从数据库复制是将数据从一台服务器（主）上的数据库复制（同步）到另一台服务器（从）上的数据库的过程。这个过程的主要好处是将数据库分布到多台机器上，所以当主服务器出现问题时，有一台具有相同数据的备份机器可以不间断地处理请求。

PostgreSQL 提供了几种复制数据库的方法。它可用于备份目的并提供高可用性数据库服务器。在本教程中，我们将向您展示如何在 Ubuntu 16.04 服务器上安装和配置 PostgreSQL 9.6 主从复制。我们将使用热备模式，这是深入学习 PostgreSQL 的一个很好的起点。

我们将要做什么：

安装 PostgreSQL 9.6。
配置 UFW 防火墙。
配置主服务器。
配置从属服务器。
将 PostgreSQL 数据从 MASTER 复制到 SLAVE。
测试。

先决条件：

1 台 Ubuntu 16.04 服务器
- MASTER - 允许读写 - IP：10.0.15.10
- 从属 - 只读 - IP：10.0.15.11
第 1 步 - 安装 PostgreSQL 9.6
在本教程中，我们将安装最新版本的 PostgreSQL 9.6。在官方的 Ubuntu 存储库中，他们只提供 PostgreSQL 9.5，因此我们需要直接从 PostgreSQL 存储库安装最新版本。
将 postgreSQL 9.6 存储库添加到 sources.list.d 目录。
```
echo 'deb http://apt.postgresql.org/pub/repos/apt/ xenial-pgdg main' | tee /etc/apt/sources.list.d/postgresql.list
```
并将 PostgreSQL 签名密钥导入系统。
```
wget -q https://www.postgresql.org/media/keys/ACCC4CF8.asc -O - | sudo apt-key add -
```
现在使用 apt 命令更新系统存储库。
```
apt-get update
```
接下来，使用下面的 apt 命令安装 PosgreSQL 9.6 包。
```
apt-get install -y postgresql-9.6 postgresql-contrib-9.6
```
如果已经安装完成，添加开机自动启动。
```
systemctl enable postgresql
```
默认情况下，PostgreSQL 在本地主机 (127.0.0.1) IP 地址上运行，在 Ubuntu 上端口为 5432，使用 netstat 命令检查它。
```
netstat -plntu
```
PostgreSQL 9.6 正在系统上运行。在下一步中，我们必须为 postgres 用户配置密码。
从 root 帐户，使用 su 命令登录到 postgres 用户，然后访问 postgres 前端终端 psql。
```
su - postgres
psql
```
请更改 postgres 用户的密码，并使用下面的 postgres 查询检查连接信息。
```
\password postgres
\conninfo
```
系统已安装PostgreSQL 9.6，运行无误，更新postgres用户密码。
第 2 步 - 配置 UFW 防火墙
UFW 或 Uncomplicated Firewall 是一个在 Ubuntu 上管理基于 iptables 的防火墙的应用程序。 UFW 是 Ubuntu Linux 默认的防火墙配置工具，提供了一种用户友好的方式来配置防火墙。
我们可以使用 apt 命令从 Ubuntu 主存储库安装 ufw。
```
apt-get install -y ufw
```
向 UFW 防火墙添加新服务：使用以下命令添加 SSH 和 PostgreSQL 服务。
```
ufw allow ssh
ufw allow postgresql
```
启用 UFW 防火墙并检查状态。
```
ufw enable
ufw status
```
UFW 防火墙已安装，并已添加 PostgreSQL 服务。
笔记：
在 MASTER 和 SLAVE 服务器上运行步骤 1 和步骤 2。
第 3 步 - 配置 PostgreSQL 主服务器
主服务器的 IP 地址为 10.0.15.10，postgres 服务将使用默认端口在该 IP 下运行。主服务器将拥有对数据库的读写权限，并对从服务器进行流式复制。
进入postgres配置目录/etc/postgresql/9.6/main，用vim编辑postgresql.conf文件。
```
cd /etc/postgresql/9.6/main/
vim postgresql.conf
```
取消注释 listen_addresses 行并将值更改为主服务器 IP 地址 10.0.15.10。
```
listen_addresses = '10.0.15.10'
```
取消注释 wal_level 行并将值更改为 hot_standby。
```
wal_level = hot_standby
```
对于同步级别，我们将使用本地同步。取消注释并更改值行，如下所示。
```
synchronous_commit = local
```
启用归档模式并将 archive_command 选项更改为命令 cp %p /var/lib/postgresql/9.6/main/archive/%f。
```
archive_mode = on
archive_command = 'cp %p /var/lib/postgresql/9.6/main/archive/%f'
```
对于 Replication 设置，在本教程中我们仅使用 2 个服务器，master 和 slave，取消 wal_sender 行的注释并将值更改为 2，wal_keep_segments 值为 10。
```
max_wal_senders = 2
wal_keep_segments = 10
```
对于应用程序名称，取消注释 synchronous_standby_names 行并将值更改为名称 pgslave001。
```
synchronous_standby_names = 'pgslave001'
```
保存文件并退出编辑器。
在postgresql.conf文件中，启用了归档模式，所以我们需要新建一个目录用于归档。新建一个存档目录，修改权限，修改属主为postgres用户。
```
mkdir -p /var/lib/postgresql/9.6/main/archive/
chmod 700 /var/lib/postgresql/9.6/main/archive/
chown -R postgres:postgres /var/lib/postgresql/9.6/main/archive/
```
接下来，编辑 pg_hba.conf 文件以进行身份验证配置。
```
vim pg_hba.conf
```
将下面的配置粘贴到行尾。
```
# Localhost
host    replication     replica          127.0.0.1/32            md5
 
# PostgreSQL Master IP address
host    replication     replica          10.0.15.10/32            md5
 
# PostgreSQL SLave IP address
host    replication     replica          10.0.15.11/32            md5
```
保存退出，然后重启 PostgreSQL。
```
systemctl restart postgresql
```
PostgreSQL 运行在 IP 地址 10.0.15.10 下，使用 netstat 命令查看。
```
netstat -plntu
```
接下来，为复制创建一个新用户。我们将创建一个名为 replica 的新用户，密码为 [email 。请在此处为您的设置选择一个安全密码！登录postgres用户，访问postgres前端终端psql。
```
su - postgres
psql
```
使用下面的 postgres 查询使用密码 [email 创建新的副本用户。
```
CREATE USER replica REPLICATION LOGIN ENCRYPTED PASSWORD '';
```
现在用下面的du查询检查新用户，你会看到有复制权限的replica用户。
```
\du
```
MASTER 服务器配置已经完成。
第 4 步 - 配置从属服务器
SLAVE 服务器的 IP 地址为 10.0.15.11。而这台服务器将只有对数据库的READ权限。 Postgres 数据库服务器将在服务器的 IP 地址下运行，而不是本地主机 IP。
使用下面的 systemctl 命令停止从属服务器上的 postgres 服务。
```
systemctl stop postgresql
```
进入Postgres配置目录/etc/postgresql/9.6/main，然后编辑配置文件postgresql.conf。
```
cd /etc/postgresql/9.6/main/
vim postgresql.conf
```
取消注释 listen_addresses 行并将值替换为从服务器 IP 地址 10.0.15.11。
```
listen_addresses = '10.0.15.11'
```
取消注释 wal_level 行并将值更改为 hot_standby。
```
wal_level = hot_standby
```
对于同步级别，取消注释 synchronous_commit 行并更改值，如下所示。
```
synchronous_commit = local
```
对于复制设置，取消注释 max_wal_senders 行并将值替换为 2，因为只使用 2 个服务器。对于 wal_keep_segments，将值更改为 10。
```
max_wal_senders = 2
wal_keep_segments = 10
```
取消注释应用程序名称的 synchronous_standby_names 行并将值更改为名称 pgslave001。
```
synchronous_standby_names = 'pgslave001'
```
通过取消注释以下行并将值更改为 on，为从属服务器启用 hot_standby。
```
hot_standby = on
```
保存文件并退出编辑器。
第 5 步 - 将 PostgreSQL 数据从 MASTER 复制到 SLAVE
接下来，我们要将 SLAVE 服务器上的 postgres 主目录替换为 MASTER 服务器上的主数据目录。
登录SLAVE服务器，访问postgres用户。
```
su - postgres
```
转到 postgres 数据目录 main 并通过重命名目录名称对其进行备份。
```
cd 9.6/
mv main main-bekup
```
以 postgres 用户身份创建新的主目录，并确保具有与 main-bekup 目录类似的权限。
```
mkdir main/
chmod 700 main/
```
接下来，使用 pg_basebackup 命令将主目录从 MASTER 服务器复制到 SLAVE 服务器，我们将使用 replica 用户执行此数据复制。
```
pg_basebackup -h 10.0.15.10 -U replica -D /var/lib/postgresql/9.6/main -P --xlog
Password:
```
数据传输完成后，转到主数据目录并创建一个新的 recovery.conf 文件。
```
cd /var/lib/postgresql/9.6/main/
vim recovery.conf
```
粘贴下面的配置：
```
standby_mode = 'on'
primary_conninfo = 'host=10.0.15.10 port=5432 user=replica  application_name=pgslave001'
restore_command = 'cp /var/lib/postgresql/9.6/main/archive/%f %p'
trigger_file = '/tmp/postgresql.trigger.5432'
```
保存退出，然后用chmod修改文件权限为600。
```
chmod 600 recovery.conf
```
现在在 SLAVE 服务器上启动 PostgreSQL 9.6，并使用 netstat 确保 postgres 服务在 IP 地址 10.0.15.11 上运行。
```
systemctl start postgresql
netstat -plntu
```
SLAVE 服务器的数据传输和配置已经完成。
第 6 步 - 测试
为了测试，我们将检查 PostgreSQL 9.6 的复制状态并尝试在 MASTER 服务器上创建一个新表，然后通过检查 SLAVE 服务器上的所有数据来检查复制。
登录MASTER服务器，以postgres用户登录。
```
su - postgres
```
运行下面的 psql 命令以查看复制状态。
```
psql -c "select application_name, state, sync_priority, sync_state from pg_stat_replication;"
psql -x -c "select * from pg_stat_replication;"
```
您将得到如下结果。
接下来，尝试从 MASTER 服务器创建一个新表。在主服务器上以 postgres 用户登录。
```
su - postgres
psql
```
并创建一个名为 replica_test 的新表，并通过运行下面的 postgres 查询将数据插入表中。
```
CREATE TABLE replica_test (hakase varchar(100));
INSERT INTO replica_test VALUES ('linux教程');
INSERT INTO replica_test VALUES ('This is from Master');
INSERT INTO replica_test VALUES ('pg replication by hakase-labs');
```
接下来，登录到 SLAVE 服务器上的 postgres 用户并访问 psql 终端。
```
su - postgres
psql
```
使用下面的 postgres 查询检查 replica_test 表上的数据。
```
select * from replica_test;
```
您将获得与 MASTER 上相同的数据，它从 MASTER 服务器复制到 SLAVE 服务器。
附加测试：
使用以下查询测试在 SLAVE 服务器上写入。
```
INSERT INTO replica_test VALUES ('this is SLAVE');
```
您将收到有关无法在 SLAVE 服务器上执行 INSERT 查询的错误消息。
在 Ubuntu 16.04 Xenial Xerus 上安装和配置带有主从复制的 PostgreSQL 9.6 已成功。
参考
- https://linux教程/tutorial/postgresql-replication-on-ubuntu-15-04/