配置 PostgreSQL 数据库主从
后知后觉 暂无评论

PostgreSQL

为 PostgreSQL 配置主从关系，提高系统冗余。

概述

PostgreSQL 官方宣传语 The World's Most Advanced Open Source Relational Database 全世界最高级的开源关系型数据库，安装过程就不再赘述了，可以参考其官方文档 - 点击跳转，没有特殊需要的话还是建议使用官方的包管理器仓库进行安装，后期维护和更新都比较方便。

本文只列举 Debian 及其衍生发行版的安装过程

创建仓库配置

sudo sh -c 'echo "deb https://apt.postgresql.org/pub/repos/apt $(lsb_release -cs)-pgdg main" > /etc/apt/sources.list.d/pgdg.list'

导入官方密钥

wget --quiet -O - https://www.postgresql.org/media/keys/ACCC4CF8.asc | sudo apt-key add -

更新仓库索引

sudo apt update

安装数据库

sudo apt -y install postgresql

规划

主机

主机名CPU内存IP
pgsql148G192.168.122.11
pgsql248G192.168.122.12

为机器配置域和主机名

# A 机器
sudo hostnamectl set-hostname pgsql1
sudo echo "192.168.122.11 pgsql1" >> /etc/hosts
# B 机器
sudo hostnamectl set-hostname pgsql2
sudo echo "192.168.122.12 pgsql2" >> /etc/hosts

配置时间同步

sudo apt install chrony

或者可以使用 ntp 进行时间同步

sudo apt install ntpdate
sudo ntpdate ntp.ntsc.ac.cn

搭建

检查数据库版本（本文在 UOS V20 和 Debian 10 上使用 PGSQL 11 12 和 13 版本测试通过）

$ sudo -u postgres psql -c "SELECT version();"
version
------------------------------------------------------------------------------------------------------------------
 PostgreSQL 11.11 (Debian 11.11-0.deb10u1) on x86_64-pc-linux-gnu, compiled by gcc (Uos 8.3.0.6-1+dde) 8.3.0, 64-bit
(1 row)
$ sudo -u postgres psql -c "SELECT version();"
version
------------------------------------------------------------------------------------------------------------------
 PostgreSQL 12.7 (Debian 12.7-1.pgdg100+1) on x86_64-pc-linux-gnu, compiled by gcc (Debian 8.3.0-6) 8.3.0, 64-bit
(1 row)
$ sudo -u postgres psql -c "SELECT version();"
version
------------------------------------------------------------------------------------------------------------------
 PostgreSQL 13.3 (Debian 13.3-1.pgdg100+1) on x86_64-pc-linux-gnu, compiled by gcc (Debian 8.3.0-6) 8.3.0, 64-bit
(1 row)

主节点操作

以下步骤仅在主节点进行操作

  1. 首先为数据库专属用户改密码，方便后续操作

    sudo passwd postgres

  2. 然后进入数据库创建用于构建主从关系的专属用户

    sudo -u postgres psql

  3. 输入以下 SQL 创建从库连接用户

    CREATE USER replication REPLICATION LOGIN CONNECTION LIMIT 1 ENCRYPTED PASSWORD 'MY_PASSWD';

  4. 修改配置文件 /etc/postgresql/1x/main/pg_hba.conf ，（1x 为版本号，请根据实际版本进行修改，下省略）找到关键字

    # DO NOT DISABLE!

    在此行上添加以下内容，注意修改倒数第二个字段为从库的IP地址或主机。

    host    replication    replication    192.168.122.12/24    md5

  5. 修改主配置文件 /etc/postgresql/1x/main/postgresql.conf 找到以下字段取消注释并按下文进行修改

    listen_addresses = 'localhost,192.168.122.11'    # 此处的地址为本机IP
wal_level = replica
max_wal_senders = 10
wal_keep_segments = 64   # 此参数仅适用于 PGSQL 11 12

  6. 修改完毕重启服务

    sudo systemctl restart postgresql

至此主库配置完毕，检查一下端口，正常应该可以看到 PGSQL 监听了两个 5432 端口，一个为本地环回，一个为本机地址。

$ ss -lnt
State     Recv-Q    Send-Q        Local Address:Port       Peer Address:Port
LISTEN    0         128                 0.0.0.0:22              0.0.0.0:*
LISTEN    0         128          192.168.122.11:5432            0.0.0.0:*
LISTEN    0         128               127.0.0.1:5432            0.0.0.0:*
LISTEN    0         128                   [::1]:5432               [::]:*

从节点操作

以下步骤仅在从节点进行操作

  1. 与主库配置方式相同，也需要先为数据库专属用户进行修改密码。

    sudo passwd postgres

  2. 密码修改完成后，停止数据库服务。

    sudo systemctl stop postgresql

  3. 然后修改主配置文件 /etc/postgresql/1x/main/postgresql.conf 找到以下字段取消注释并按下文进行修改。

    listen_addresses = 'localhost,192.168.122.12'    # 此处的地址为本机IP
wal_level = replica
max_wal_senders = 10
wal_keep_segments = 64   # 此参数仅适用于 PGSQL 11 12

  4. 修改完毕后切换到 postgres 用户下继续操作，删除数据库存储文件夹。

    sudo su - postgres
cd /var/lib/postgresql/1x/main/
rm -rfv ./*

  5. 然后使用数据库自带的迁移工具迁移主库数据用于保持数据同步

    • 11 版本使用如下命令

      $ pg_basebackup -h 192.168.122.11 -D /var/lib/postgresql/11/main/ -P -U replication --wal-method=fetch
Password:
40612/40612 kB (100%), 1/1 tablespace

      看到上述提示后即为传输成功

    • 12 版本使用如下命令

      $ pg_basebackup -h 192.168.122.11 -D /var/lib/postgresql/12/main/ -U replication -Pv -R -Xs
Password:
pg_basebackup: initiating base backup, waiting for checkpoint to complete
pg_basebackup: checkpoint completed
pg_basebackup: write-ahead log start point: 0/7000028 on timeline 1
pg_basebackup: starting background WAL receiver
pg_basebackup: created temporary replication slot "pg_basebackup_983"
24530/24530 kB (100%), 1/1 tablespace
pg_basebackup: write-ahead log end point: 0/7000100
pg_basebackup: waiting for background process to finish streaming ...
pg_basebackup: syncing data to disk ...
pg_basebackup: base backup completed

    • 13 版本使用如下命令

      $ pg_basebackup -h 192.168.122.11 -D /var/lib/postgresql/13/main/ -U replication -Fp -Xs -Pv -R
Password:
pg_basebackup: initiating base backup, waiting for checkpoint to complete
pg_basebackup: checkpoint completed
pg_basebackup: write-ahead log start point: 0/8000028 on timeline 1
pg_basebackup: starting background WAL receiver
pg_basebackup: created temporary replication slot "pg_basebackup_544"
24227/24227 kB (100%), 1/1 tablespace
pg_basebackup: write-ahead log end point: 0/8000138
pg_basebackup: waiting for background process to finish streaming ...
pg_basebackup: syncing data to disk ...
pg_basebackup: renaming backup_manifest.tmp to backup_manifest
pg_basebackup: base backup completed

      12和13版本，看到上述提示后即为传输成功，可以看到传输完成后出现一个名为 standby.signal 的文件，写入内容 standby_mode = 'on' 后保存。

  6. 注意本步骤在不同的版本略有差异。

    • 在 PGSQL 11 版本上，需要在当前目录创建主从配置文件 /var/lib/postgresql/11/main/recovery.conf 并写入以下内容

      standby_mode     = 'on'
primary_conninfo = 'host=192.168.122.11 port=5432 user=replication password=MY_PASSWORD'
trigger_file     = '/tmp/MasterNow'
      小贴士：修改第二条的用户名密码为你设定的用户名和密码。

    • 在 PGSQL 12 和 13 版本上，此配置文件不支持，如果使用上述方法会见到如下报错

      FATAL:  using recovery command file "recovery.conf" is not supported

      这是因为新版本的 recovery.conf 配置合并进了主配置文件 postgresql.conf ，修改从库配置文件 /etc/postgresql/1x/main/postgresql.conf 约315行，找到以下字段并修改。

      primary_conninfo = 'host=192.168.122.11 port=5432 user=replication password=MY_PASSWORD'
primary_slot_name = 'node_a_slot'

      然后进入主库数据库，创建 primary_slot_name 规则，此部分说明可参考官方文档。

      postgres=# SELECT * FROM pg_create_physical_replication_slot('node_a_slot');
slot_name  | lsn
-------------+-----
 node_a_slot |

postgres=# SELECT slot_name, slot_type, active FROM pg_replication_slots;
slot_name  | slot_type | active 
-------------+-----------+--------
 node_a_slot | physical  | f
(1 row)

  7. 操作完毕后重启数据库（主从都重启一下）

    sudo systemctl restart postgresql

验证

进入主库数据库

$ sudo -u postgres psql
psql (12.7 (Debian 12.7-1.pgdg100+1))
Type "help" for help.

postgres=# \x
Expanded display is on.
postgres=# select client_addr,sync_state from pg_stat_replication;
-[ RECORD 1 ]---------------
client_addr | 192.168.122.12
sync_state  | async
$ sudo -u postgres psql
psql (13.3 (Debian 13.3-1.pgdg100+1))
Type "help" for help.

postgres=# \x
Expanded display is on.

postgres=# select client_addr,sync_state from pg_stat_replication;
-[ RECORD 1 ]----------------
client_addr | 192.168.122.12
sync_state  | async

看到上述内容即为主从搭建成功

还可以使用

postgres=# select * from pg_stat_activity where usename = 'replication';
-[ RECORD 1 ]----+------------------------------
datid            |
datname          |
pid              | 576
leader_pid       |
usesysid         | 16384
usename          | replication
application_name | 13/main
client_addr      | 192.168.122.12
client_hostname  |
client_port      | 36496
backend_start    | 2021-05-01 04:53:44.307728-04
xact_start       |
query_start      |
state_change     | 2021-05-01 04:53:44.311936-04
wait_event_type  | Activity
wait_event       | WalSenderMain
state            | active
backend_xid      |
backend_xmin     |
query            |
backend_type     | walsender

可以看到 client_addr 字段已经出现从库地址，即为成功。

可以手动在主库添加几条记录，验证从库是否跟进即可。

CREATE TABLE users (
name VARCHAR(30),
country VARCHAR(2)
);
INSERT INTO users VALUES('Shahriar', 'BD');
INSERT INTO users VALUES('Shovon', 'BD');
INSERT INTO users VALUES('Kelly', 'US');
INSERT INTO users VALUES('Nina', 'IN');
INSERT INTO users VALUES('Kesha', 'CA');

在从库查看即可

postgres=# select * from users;
   name   | country
----------+---------
 Shahriar | BD
 Shovon   | BD
 Kelly    | US
 Nina     | IN
 Kesha    | CA
(5 rows)

错误处理

如果遇到 FATAL: too many connections for role "replication" 报错，是因为主从账户创建时限制了连接数，取消即可。

postgres=# ALTER ROLE replication connection limit -1;
ALTER ROLE

附录

参考文档

本文撰写于一年前，如出现图片失效或有任何问题，请在下方留言。博主看到后将及时修正，谢谢！

本文由 柒 创作，采用 知识共享署名4.0 国际许可协议进行许可。
转载本站文章前请注明出处，文章作者保留所有权限。
最后编辑时间： 2021-05-14 17:14 PM

← → ↓ ↑
禁用 / 当前已拒绝评论，仅可查看「历史评论」。