AI 助理
备案控制台
文档
输入文档关键字查找
首页 云服务器 ECS 实践教程 搭建应用 部署数据库 搭建PostgreSQL主从架构

搭建PostgreSQL主从架构

更新时间：
产品详情
我的收藏

PostgreSQL是一个开源数据库，遵循SQL标准，支持NoSQL数据类型（JSON/XML/hstore）、递归查询、全文搜索、GIS地理信息处理等，能够应对各种复杂数据分析需求。广泛应用于互联网网站、企业级应用后端、数据仓库与分析平台、位置应用系统、复杂数据对象处理等应用场景。本文介绍了如何在内网环境中，基于Linux操作系统的ECS实例搭建PostgreSQL主从架构。

准备工作

  • 创建两台ECS实例，一台实例作为主节点，另一台ECS实例作为从节点。具体参数说明，请参见自定义购买实例。

  • 安全组：已在安全组内添加入方向规则放行22端口。具体操作，请参见添加安全组规则。

步骤一：部署PostgreSQL主节点

本教程以部署PostgreSQL 15版本为例。您可以根据业务需求选择合适的版本。

Alibaba Cloud Linux 3/CentOS 8

  1. 添加PostgreSQL镜像源并配置文件。

    #添加PostgreSQL镜像源
sudo rpm -Uvh http://mirrors.cloud.aliyuncs.com/postgresql/repos/yum/reporpms/EL-8-x86_64/pgdg-redhat-repo-latest.noarch.rpm
#使用 sed 工具编辑 /etc/yum.repos.d/pgdg-redhat-all.repo文件
sudo sed -i "s@https://download.postgresql.org/pub@http://mirrors.cloud.aliyuncs.com/postgresql@g" /etc/yum.repos.d/pgdg-redhat-all.repo
sudo sed -i "s@\$releasever@8@g" /etc/yum.repos.d/pgdg-redhat-all.repo
#创建/var/lib/pgsql/15/data/目录
sudo mkdir -p /var/lib/pgsql/15/data/

  2. （可选）CentOS 8系统安装时需执行该命令禁用默认的模块流。

    sudo dnf --assumeyes module disable postgresql

  3. 安装PostgreSQL 15。

    sudo dnf install -y postgresql15-server

  4. 初始化PostgreSQL数据库。

    sudo /usr/pgsql-15/bin/postgresql-15-setup initdb

    返回结果如下图所示，表示PostgreSQL数据库已初始化完成。

    image

  5. 配置主服务器。

    作为主服务器需要修改如下配置。

    #记录足够的信息以支持常见的备库用途，包括流复制和热备。
sudo sed -i "s@#wal_level = replica@wal_level = replica@g" /var/lib/pgsql/15/data/postgresql.conf
#max_wal_senders 设置最大WAL发送进程数量
sudo sed -i "s@#max_wal_senders = 10@max_wal_senders = 10@g" /var/lib/pgsql/15/data/postgresql.conf
#wal_keep_size 设置主节点上保留的WAL日志的最小大小
sudo sed -i "s@#wal_keep_size = 0@wal_keep_size = 128MB@g" /var/lib/pgsql/15/data/postgresql.conf
#archive_mode = on，以支持WAL日志的归档
sudo sed -i "s@#archive_mode = off@archive_mode = on@g" /var/lib/pgsql/15/data/postgresql.conf
#监听的IP地址
sudo sed -i "s@#listen_addresses = 'localhost'@listen_addresses = '*'@g" /var/lib/pgsql/15/data/postgresql.conf

  6. 修改pg_hba.conf文件，配置从节点连接主节点的权限。

    • <YOUR_USER>替换为从节点账号。

    • <从节点私网IP/网段>需替换为从节点的私网IP/网段。

    echo "host replication <YOUR_USER> <从节点私网IP/网段> md5" | sudo tee -a /var/lib/pgsql/15/data/pg_hba.conf

  7. 依次运行以下命令，启动服务并设置开机自启动。

    sudo systemctl enable postgresql-15.service
sudo systemctl start postgresql-15.service

  8. 创建复制用户。

    • <YOUR_USER>替换为从节点账号。

    • <YOUR_PASSWORD>替换为要设置的密码。

    cd /
sudo -u postgres psql -c "CREATE ROLE <YOUR_USER> REPLICATION LOGIN PASSWORD '<YOUR_PASSWORD>';"

  9. 运行以下命令，重启PostgreSQL服务。

    sudo systemctl restart postgresql-15.service

Alibaba Cloud Linux 2/CentOS 7

  1. 安装PostgreSQL 15。

    #添加PostgreSQL镜像源
sudo rpm -Uvh http://mirrors.cloud.aliyuncs.com/postgresql/repos/yum/reporpms/EL-7-x86_64/pgdg-redhat-repo-latest.noarch.rpm
#使用 sed 工具编辑 /etc/yum.repos.d/pgdg-redhat-all.repo文件
sudo sed -i "s@https://download.postgresql.org/pub@http://mirrors.cloud.aliyuncs.com/postgresql@g" /etc/yum.repos.d/pgdg-redhat-all.repo
sudo sed -i "s@\$releasever@7@g" /etc/yum.repos.d/pgdg-redhat-all.repo
#创建/var/lib/pgsql/15/data/目录
sudo mkdir -p /var/lib/pgsql/15/data/
#安装PostgreSQL 15
sudo yum install -y postgresql15-server

  2. 初始化PostgreSQL数据库。

    sudo /usr/pgsql-15/bin/postgresql-15-setup initdb

    返回结果如下图所示，表示PostgreSQL数据库已初始化完成。

    image

  3. 配置主服务器。

    作为主服务器需要修改如下配置。

    #记录足够的信息以支持常见的备库用途，包括流复制和热备。
sudo sed -i "s@#wal_level = replica@wal_level = replica@g" /var/lib/pgsql/15/data/postgresql.conf
#max_wal_senders 设置最大WAL发送进程数量
sudo sed -i "s@#max_wal_senders = 10@max_wal_senders = 10@g" /var/lib/pgsql/15/data/postgresql.conf
#wal_keep_size 设置主节点上保留的WAL日志的最小大小
sudo sed -i "s@#wal_keep_size = 0@wal_keep_size = 128MB@g" /var/lib/pgsql/15/data/postgresql.conf
#archive_mode = on，以支持WAL日志的归档
sudo sed -i "s@#archive_mode = off@archive_mode = on@g" /var/lib/pgsql/15/data/postgresql.conf
#监听的IP地址
sudo sed -i "s@#listen_addresses = 'localhost'@listen_addresses = '*'@g" /var/lib/pgsql/15/data/postgresql.conf

  4. 修改pg_hba.conf文件，配置从节点连接主节点的权限。

    • <YOUR_USER>替换为从节点账号。

    • <从节点私网IP/网段>需替换为从节点的私网IP/网段。

    echo "host replication <YOUR_USER> <从节点私网IP/网段> md5" | sudo tee -a /var/lib/pgsql/15/data/pg_hba.conf

  5. 依次运行以下命令，启动服务并设置开机自启动。

    sudo systemctl enable postgresql-15.service
sudo systemctl start postgresql-15.service

  6. 创建复制用户。

    • <YOUR_USER>替换为从节点账号。

    • <YOUR_PASSWORD>替换为要设置的密码。

    cd /
sudo -u postgres psql -c "CREATE ROLE <YOUR_USER> REPLICATION LOGIN PASSWORD '<YOUR_PASSWORD>';"

  7. 运行以下命令，重启PostgreSQL服务。

    sudo systemctl restart postgresql-15.service

Ubuntu/Debian

  1. 安装PostgreSQL 15。

    #添加PostgreSQL镜像源。
sudo sh -c 'echo "deb [signed-by=/etc/apt/trusted.gpg.d/postgresql.gpg] http://mirrors.cloud.aliyuncs.com/postgresql/repos/apt/ $(lsb_release -sc)-pgdg main" > /etc/apt/sources.list.d/pgdg.list'
#下载GPG密钥并导出公钥，并将其转换为适合APT使用的格式。
wget -qO - http://mirrors.cloud.aliyuncs.com/postgresql/repos/apt/ACCC4CF8.asc | sudo apt-key add -
sudo apt-key export ACCC4CF8 | sudo gpg --dearmor -o /etc/apt/trusted.gpg.d/postgresql.gpg
#更新软件包列表。
sudo apt-get update
#安装PostgreSQL 15
sudo apt-get install -y postgresql-15

  2. 配置主服务器。修改pg_hba.conf文件，配置从节点连接主节点的权限。

    作为主服务器需要修改如下配置。

    sudo sed -i "s@#listen_addresses = 'localhost'@listen_addresses = '*'@g" /etc/postgresql/15/main/postgresql.conf
#wal_level = replica
sudo sed -i "s@#wal_level = replica@wal_level = replica@g" /etc/postgresql/15/main/postgresql.conf
#max_wal_senders 设置最大WAL发送进程数量
sudo sed -i "s@#max_wal_senders = 10@max_wal_senders = 10@g" /etc/postgresql/15/main/postgresql.conf
#wal_keep_size 设置主节点上保留的WAL日志的最小大小
sudo sed -i "s@#wal_keep_size = 0@wal_keep_size = 128MB@g" /etc/postgresql/15/main/postgresql.conf
#archive_mode = on，以支持WAL日志的归档
sudo sed -i "s@#archive_mode = off@archive_mode = on@g" /etc/postgresql/15/main/postgresql.conf

  3. 修改pg_hba.conf文件，配置从节点连接主节点的权限。

    • <YOUR_USER>替换为要设置的从节点账号。

    • <从节点私网IP/网段>需替换为从节点的私网IP/网段。

    echo "host replication <YOUR_USER> <从节点私网IP/网段> md5" | sudo tee -a /etc/postgresql/15/main/pg_hba.conf

  4. 创建复制用户。

    • <YOUR_USER>替换为从节点账号。

    • <YOUR_PASSWORD>替换为要设置的密码。

    cd /
sudo -u postgres psql -c "CREATE ROLE <YOUR_USER> REPLICATION LOGIN PASSWORD '<YOUR_PASSWORD>';"

  5. 运行以下命令，重启服务并设置为开机自启动。

    sudo systemctl restart postgresql.service
sudo systemctl enable postgresql.service

步骤二：部署PostgreSQL从节点

本教程以部署PostgreSQL 15版本为例。您可以根据业务需求选择合适的版本。

Alibaba Cloud Linux 3/CentOS 8

  1. 添加PostgreSQL镜像源并配置文件。

    #添加PostgreSQL镜像源
sudo rpm -Uvh http://mirrors.cloud.aliyuncs.com/postgresql/repos/yum/reporpms/EL-8-x86_64/pgdg-redhat-repo-latest.noarch.rpm
#使用 sed 工具编辑 /etc/yum.repos.d/pgdg-redhat-all.repo文件
sudo sed -i "s@https://download.postgresql.org/pub@http://mirrors.cloud.aliyuncs.com/postgresql@g" /etc/yum.repos.d/pgdg-redhat-all.repo
sudo sed -i "s@\$releasever@8@g" /etc/yum.repos.d/pgdg-redhat-all.repo

  2. （可选）CentOS 8系统安装时需执行该命令禁用默认的模块流。

    sudo dnf --assumeyes module disable postgresql

  3. 安装PostgreSQL 15。

    sudo dnf install -y postgresql15-server

  4. 使用pg_basebackup基础备份工具创建主库的基础备份。

    • <YOUR_USER>替换为主库账号。

    • <YOUR_PASSWORD>替换为主库密码。

    • <主节点私网IP>替换为主节点私网IP。

    cd /
export PGPASSWORD=<YOUR_PASSWORD>
sudo -E -u postgres pg_basebackup -h <主节点私网IP> -D /var/lib/pgsql/15/data/ -U <YOUR_USER> -P -w -v --wal-method=stream

  5. 配置从库的postgresql.conf文件。

    作为主服务器需要修改如下配置。

    #hot_standby = on 开启备用服务器的只读模式
sudo sed -i "s@#hot_standby = off@hot_standby = on@g" /var/lib/pgsql/15/data/postgresql.conf
#设置 hot_standby_feedback = on 控制备用服务器是否会向主服务器发送关于自己的复制状态和进度的信息
sudo sed -i "s@#hot_standby_feedback = off@hot_standby_feedback = on@g" /var/lib/pgsql/15/data/postgresql.conf

  6. 设置主库的连接信息。

    • <主节点私网IP>需替换为主库私网IP。

    • <YOUR_USER>替换为主库用户名。

    • <YOUR_PASSWORD>需替换为主库密码。

    sudo sed -i "s@#primary_conninfo = ''@primary_conninfo = 'host=<主节点私网IP> port=5432 user=<YOUR_USER> password=<YOUR_PASSWORD>'@g" /var/lib/pgsql/15/data/postgresql.conf

  7. 设置从库可以接管主库。

    sudo -u postgres touch /var/lib/pgsql/15/data/standby.signal

  8. 依次运行以下命令，启动服务并设置服务开机自启动。

    sudo systemctl enable postgresql-15.service
sudo systemctl start postgresql-15.service

Alibaba Cloud Linux 2/CentOS 7

  1. 安装PostgreSQL 15。

    #添加PostgreSQL镜像源
sudo rpm -Uvh http://mirrors.cloud.aliyuncs.com/postgresql/repos/yum/reporpms/EL-7-x86_64/pgdg-redhat-repo-latest.noarch.rpm
#使用 sed 工具编辑 /etc/yum.repos.d/pgdg-redhat-all.repo文件
sudo sed -i "s@https://download.postgresql.org/pub@http://mirrors.cloud.aliyuncs.com/postgresql@g" /etc/yum.repos.d/pgdg-redhat-all.repo
sudo sed -i "s@\$releasever@7@g" /etc/yum.repos.d/pgdg-redhat-all.repo
#安装PostgreSQL 15
sudo yum install -y postgresql15-server

  2. 使用pg_basebackup基础备份工具创建主库的基础备份。

    • <YOUR_USER>需替换为主库账号。

    • <YOUR_PASSWORD>替换为主库密码。

    • <主节点私网IP>替换为主节点私网IP。

    cd /
export PGPASSWORD=<YOUR_PASSWORD>
sudo -E -u postgres pg_basebackup -h <主节点私网IP> -D /var/lib/pgsql/15/data/ -U <YOUR_USER> -P -w -v --wal-method=stream

  3. 配置从节点服务器。

    作为主服务器需要修改如下配置。

    #hot_standby = on 开启备用服务器的只读模式
sudo sed -i "s@#hot_standby = off@hot_standby = on@g" /var/lib/pgsql/15/data/postgresql.conf
#设置 hot_standby_feedback = on 控制备用服务器是否会向主服务器发送关于自己的复制状态和进度的信息
sudo sed -i "s@#hot_standby_feedback = off@hot_standby_feedback = on@g" /var/lib/pgsql/15/data/postgresql.conf

  4. 设置主服务器的连接信息。

    • <主节点私网IP>需替换为主节点私网IP。

    • <YOUR_USER>替换为主节点用户名。

    • <YOUR_PASSWORD>需替换为主节点密码。

    sudo sed -i "s@#primary_conninfo = ''@primary_conninfo = 'host=<主节点私网IP> port=5432 user=<YOUR_USER> password=<YOUR_PASSWORD>'@g" /var/lib/pgsql/15/data/postgresql.conf

  5. 设置从库可以接管主库。

    sudo -u postgres touch /var/lib/pgsql/15/data/standby.signal

  6. 依次运行以下命令，启动服务并设置服务开机自启动。

    sudo systemctl enable postgresql-15.service
sudo systemctl start postgresql-15.service

Ubuntu/Debian

  1. 安装PostgreSQL 15。

    #添加PostgreSQL镜像源。
sudo sh -c 'echo "deb [signed-by=/etc/apt/trusted.gpg.d/postgresql.gpg] http://mirrors.cloud.aliyuncs.com/postgresql/repos/apt/ $(lsb_release -sc)-pgdg main" > /etc/apt/sources.list.d/pgdg.list'
#下载GPG密钥并导出公钥，并将其转换为适合APT使用的格式。
wget -qO - http://mirrors.cloud.aliyuncs.com/postgresql/repos/apt/ACCC4CF8.asc | sudo apt-key add -
sudo apt-key export ACCC4CF8 | sudo gpg --dearmor -o /etc/apt/trusted.gpg.d/postgresql.gpg
#更新软件包列表。
sudo apt-get update
#安装PostgreSQL 15
sudo apt-get install -y postgresql-15

  2. 停止服务器。

    sudo systemctl stop postgresql.service

  3. 删除备份服务器初始化数据。

    sudo rm -rf /var/lib/postgresql/15/main/

  4. 使用pg_basebackup基础备份工具创建主库的基础备份。

    • <YOUR_USER>替换为主库账号。

    • <YOUR_PASSWORD>替换为主库密码。

    • <主节点私网IP>替换为主节点私网IP。

    cd /
export PGPASSWORD=<YOUR_PASSWORD>
sudo -E -u postgres pg_basebackup -h <主节点私网IP> -D /var/lib/postgresql/15/main/ -U <YOUR_USER> -P -w -v --wal-method=stream

  5. 配置从库的postgresql.conf文件。

    作为主服务器需要修改如下配置。

    #hot_standby = on 开启备用服务器的只读模式
sudo sed -i "s@#hot_standby = off@hot_standby = on@g" /etc/postgresql/15/main/postgresql.conf
#设置 hot_standby_feedback = on 控制备用服务器是否会向主服务器发送关于自己的复制状态和进度的信息
sudo sed -i "s@#hot_standby_feedback = off@hot_standby_feedback = on@g" /etc/postgresql/15/main/postgresql.conf

  6. 设置主服务器的连接信息。

    • <主节点私网IP>需替换为主节点私网IP。

    • <YOUR_USER>替换为主节点用户名。

    • <YOUR_PASSWORD>需替换为主节点密码。

    sudo sed -i "s@#primary_conninfo = ''@primary_conninfo = 'host=<主节点私网IP> port=5432 user=<YOUR_USER> password=<YOUR_PASSWORD>'@g" /etc/postgresql/15/main/postgresql.conf

  7. 设置从库可以接管主库。

    sudo -u postgres touch /var/lib/postgresql/15/main/standby.signal

  8. 运行以下命令，重启服务并设置为开机自启动。

    sudo systemctl restart postgresql.service
sudo systemctl enable postgresql.service

验证PostgreSQL主从架构

检测验证要求主从节点之间存在数据交互。例如，在从节点备份目录时，进行检测能够获得预期的结果。

sudo pg_basebackup -D /var/lib/pgsql/15/data -h <主节点IP> -p 5432 -U replica -X stream -P

  1. 在主节点中运行以下命令，查看sender进程。 

    ps aux |grep sender

    返回结果如下，表示可以成功查看到sender进程。 

    postgres  2916  0.0  0.3 340388  3220 ?        Ss   15:38   0:00 postgres: walsender  replica 192.168.**.**(49640) streaming 0/F01C1A8

  2. 在从节点中运行以下命令，查看receiver进程。

    ps aux |grep receiver

    返回结果类似如下，表示可以成功查看到receiver进程。 

    postgres 23284  0.0  0.3 387100  3444 ?        Ss   16:04   0:00 postgres: walreceiver   streaming 0/F01C1A8

  3. 在主节点中进入PostgreSQL交互终端，输入以下SQL语句，在主库中查看从库状态。

    1. 运行以下命令，登录postgres用户。 

      sudo su - postgres

    2. 输入以下命令，进入PostgreSQL交互终端。 

      psql

    3. 运行以下命令，在主库中查看从库状态。

      select * from pg_stat_replication;

      返回结果类似如下，表示可成功查看到从库状态。

      pid | usesysid | usename | application_name | client_addr | client_hostname | client_port | backend_start | backend_xmin | state | sent_location | write_locati
on | flush_location | replay_location | sync_priority | sync_state 
------+----------+---------+------------------+---------------+-----------------+------------- +-------------------------------+--------------+-----------+---------------+-------------
---+----------------+-----------------+---------------+------------
2916 | 16393 | replica | walreceiver | 192.168.**.** | | 49640 | 2017-05-02 15:38:06.188988+08 | 1836 | streaming | 0/F01C0C8 | 0/F01C0C8 
| 0/F01C0C8 | 0/F01C0C8 | 0 | async
(1 rows)

    4. 输入以下命令，并按Enter退出SQL终端。

      \q

    5. 输入以下命令，并按Enter退出PostgreSQL。 

      exit
上一篇：管理ECS实例自建数据库下一篇：搭建VPN