在 Docker 部署主从复制 PostgreSQL

PostgreSQL 主从复制（也称为主备复制）的优点主要体现在高可用性、负载分担、数据保护和维护便捷性等方面。以下是 PostgreSQL 主从复制的具体优点：

高可用性和故障恢复：可以使用故障转移工具，实现自动故障转移。
负载分担：从节点用于查询操作，将读请求分担到从节点，减轻主节点负载，优化主节点性能。
数据备份和数据保护

一、配置一览

1、Docker Compose 文件

name: postgres-master-slave-demo

volumes:
    master-volume:
        name: postgres-master-slave-demo-master-volume
    slave-volume:
        name: postgres-master-slave-demo-slave-volume

networks:
    postgres-master-slave-net:
        name: postgres-master-slave-net
        driver: bridge
        ipam:
            driver: default
            config:
                - subnet: 172.54.0.0/24
                  ip_range: 172.54.0.0/24
                  gateway: 172.54.0.1

services:
    postgres-master:
        image: ${image}
        restart: ${restart}
        container_name: postgres-master
        hostname: postgres-master
        networks:
            postgres-master-slave-net:
                ipv4_address: 172.54.0.2
        ports:
            - 5400:5432
        env_file:
            - ./postgres.env
        volumes:
            - master-volume:/var/lib/postgresql/data
            - ./master.conf:/etc/postgresql/postgresql.conf
            - ./master-init.sql:/docker-entrypoint-initdb.d/init.sql
        command: ["postgres", "-c", "config_file=/etc/postgresql/postgresql.conf"]

    postgres-slave:
        image: ${image}
        restart: ${restart}
        container_name: postgres-slave
        hostname: postgres-slave
        networks:
            postgres-master-slave-net:
                ipv4_address: 172.54.0.3
        ports:
            - 5401:5432
        env_file:
            - ./postgres.env
        volumes:
            - slave-volume:/var/lib/postgresql/data
            - ./slave.conf:/etc/postgresql/postgresql.conf
        command: ["postgres", "-c", "config_file=/etc/postgresql/postgresql.conf"]

2、env 文件

.env 文件

1 2	image=z2huo/postgres-dev:16.2-bookworm restart=unless-stopped

postgres.env 文件

1 2	POSTGRES_USER=postgres POSTGRES_PASSWORD=yourpassword

3、dockerfile

FROM postgres:16.2-bookworm

LABEL maintainer="z2huo9994@163.com"

RUN apt-get update && \
    apt install -y procps && \
    apt install -y iputils-ping && \
    apt install -y telnet && \
    apt install -y net-tools && \
    apt install -y vim && \
    apt clean && \
    rm -rf /var/lib/apt/lists/*

4、postgres 配置文件

4.1 主节点配置文件

# 允许数据库监听任何地址，可以通过任意网络接口连接
listen_addresses = '*'

# 设置为 replica，表示开启了逻辑复制以支持热备份
wal_level = replica
# 设置能够连接到主服务器进行 WAL 复制的最大连接数量，只支持有多个从服务器的场景
max_wal_senders = 10
# 设置 WAL 文件的保留大小，以 MB 为单位，超过这个大小的日志文件将不会被删除，以便后续从备份中还原
wal_keep_size = 1024
# 启用归档模式，允许数据库将 WAL 日志文件存档
archive_mode = on
# 禁用存档命令以便在 Docker 内部测试
archive_command = 'cd .'
# 设置 WAL 发送者（主服务器）发送数据的超时时间
wal_sender_timeout = 60s
# 设置数据库允许的最大连接数量。从库的最大连接数量必须大于主库，确保从库可以处理主库发送的所有连接
max_connections = 100

4.2 从节点配置文件

# 允许数据库监听任何地址，可以通过任意网络接口连接
listen_addresses = '*'

# 设置为 replica，表示开启了逻辑复制以支持热备份
wal_level = replica
# 在恢复期间允许查询。这是在流复制过程中，从库在进行 WAL 日志恢复的同时允许查询读取。
hot_standby = on
# 设置数据库允许的最大连接数量。从库的最大连接数量必须大于主库，确保从库可以处理主库发送的所有连接
max_connections = 100
# 设置恢复的目标时间线。在这里，设置为 latest 表示从库将一直尝试连接到主库的最新时间线上。
recovery_target_timeline = latest
# 配置主库 IP 端口 用于复制的用户和用户密码
primary_conninfo = 'host=postgres-master port=5432 user=sync_user password=yourpassword'

5、初始化文件

5.1 初始化 SQL 文件

主节点初始化 SQL，创建复制用户和创建数据库权限用户：

-- 主节点创建用于复制用户 syncuser
create user sync_user login replication encrypted password 'yourpassword';

create user z2huo with ENCRYPTED PASSWORD 'yourpassword' createdb;

参考：Postgres 镜像#三、初始化脚本 | z2huo

5.2 shell 脚本

这两个 shell 脚本不能作为容器初始化脚本来使用，原因是镜像提供的脚本初始化功能是在创建好数据文件之后才进行的。

主节点用到的 shell 脚本：

1	echo "host replication sync_user 172.54.0.0/24 md5" >> /var/lib/postgresql/data/pg_hba.conf

从节点用到的 shell 脚本：

1 2	rm -rf /var/lib/postgresql/data/* && \ pg_basebackup -h postgres-master -p 5432 -U sync_user -Fp -Xs -Pv -R -D /var/lib/postgresql/data

二、部署过程

使用 Docker Compose 中定义的服务，通过 docker compose up -d 命令启动服务成功之后，对主节点和从节点执行一系列操作。

1、主节点操作

1.1 创建用于复制的用户

创建用于复制的用户。可以在容器启动之后进入主节点容器，使用下面的命令来手动创建用于同步的用户。

$ psql -U postgres
$ create user sync_user login replication encrypted password 'z2huo@2024';

create user sync_user login replication encrypted password 'z2huo@2024';

当然替代操作是通过 postgres 镜像提供的初始化脚本功能。将 Docker 部署主从复制 PostgreSQL#5.1 初始化 SQL 文件这里的初始化 SQL 通过 Compose 提供的挂载功能，挂载到 /docker-entrypoint-initdb.d/ 目录中。

1.2 修改 pg_hba.conf 文件

修改主节点上的 /var/lib/postgresql/data/pg_hba.conf 文件，以允许从服务器使用 sync_user 用户进行连接。容器正常启动之后，会自动创建该文件，需要在该文件中追加如下内容：

1 2	# TYPE DATABASE USER ADDRESS METHOD host replication sync_user 172.54.0.0/24 md5

这行配置允许来自 172.54.0.0/24 子网的客户端使用 sync_user 用户进行复制连接，并且需要提供正确的密码（使用 MD5 加密）。

可以直接使用下面的命令来在该文件末尾追加内容：

1	echo "host replication sync_user 172.54.0.0/24 md5" >> /var/lib/postgresql/data/pg_hba.conf

TIP：

可能使用的 docker 镜像中没有安装 vim。
上面 echo 命令后面的 >> 是追加到文件末尾，而 > 表示替换文件中所有的内容。

修改好上面的文件之后，重启主节点容器即可。

2、从节点操作

2.1 删除数据并同步主节点数据

从节点容器启动之后，会自动创建单独属于从节点的数据目录文件。

需要删除从节点单独的数据文件，之后执行 pg_basebackup 命令同步来自主节点的数据，这里需要用到之前在主节点上创建的用于复制的用户 sync_user，需要输入该用户的密码。

$ rm -rf /var/lib/postgresql/data/* && pg_basebackup -h postgres-master -p 5432 -U sync_user -Fp -Xs -Pv -R -D /var/lib/postgresql/data
Password: 
pg_basebackup: initiating base backup, waiting for checkpoint to complete
pg_basebackup: checkpoint completed
pg_basebackup: write-ahead log start point: 0/4000028 on timeline 1
pg_basebackup: starting background WAL receiver
pg_basebackup: created temporary replication slot "pg_basebackup_58"
23174/23174 kB (100%), 1/1 tablespace
pg_basebackup: write-ahead log end point: 0/4000100
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

执行完上述命令之后，从节点容器会自动重启。

2.2 修改 standby.signal 文件

需要修改 /var/lib/postgresql/data/standby.signal 文件，添加如下内容，以声明当前库为从库。

1	standby_mode = 'on'

可以使用如下命令手动追加内容。

TIP：可能使用的 docker 镜像中没有安装 vim。

1	echo "standby_mode = 'on'" >> /var/lib/postgresql/data/standby.signal

3、日志

3.1 主节点日志

2024-10-29 21:41:16 The files belonging to this database system will be owned by user "postgres".
2024-10-29 21:41:16 This user must also own the server process.
2024-10-29 21:41:16 
2024-10-29 21:41:16 The database cluster will be initialized with locale "en_US.utf8".
2024-10-29 21:41:16 The default database encoding has accordingly been set to "UTF8".
2024-10-29 21:41:16 The default text search configuration will be set to "english".
2024-10-29 21:41:16 
2024-10-29 21:41:16 Data page checksums are disabled.
2024-10-29 21:41:16 
2024-10-29 21:41:16 fixing permissions on existing directory /var/lib/postgresql/data ... ok
2024-10-29 21:41:16 creating subdirectories ... ok
2024-10-29 21:41:16 selecting dynamic shared memory implementation ... posix
2024-10-29 21:41:16 selecting default max_connections ... 100
2024-10-29 21:41:16 selecting default shared_buffers ... 128MB
2024-10-29 21:41:16 selecting default time zone ... Etc/UTC
2024-10-29 21:41:16 creating configuration files ... ok
2024-10-29 21:41:16 running bootstrap script ... ok
2024-10-29 21:41:16 performing post-bootstrap initialization ... ok
2024-10-29 21:41:16 syncing data to disk ... ok
2024-10-29 21:41:16 
2024-10-29 21:41:16 
2024-10-29 21:41:16 Success. You can now start the database server using:
2024-10-29 21:41:16 
2024-10-29 21:41:16     pg_ctl -D /var/lib/postgresql/data -l logfile start
2024-10-29 21:41:16 
2024-10-29 21:41:16 waiting for server to start....2024-10-29 13:41:16.885 GMT [48] LOG:  starting PostgreSQL 16.2 (Debian 16.2-1.pgdg120+2) on aarch64-unknown-linux-gnu, compiled by gcc (Debian 12.2.0-14) 12.2.0, 64-bit
2024-10-29 21:41:16 2024-10-29 13:41:16.885 GMT [48] LOG:  listening on Unix socket "/var/run/postgresql/.s.PGSQL.5432"
2024-10-29 21:41:16 2024-10-29 13:41:16.889 GMT [51] LOG:  database system was shut down at 2024-10-29 13:41:16 GMT
2024-10-29 21:41:16 2024-10-29 13:41:16.891 GMT [48] LOG:  database system is ready to accept connections
2024-10-29 21:41:16  done
2024-10-29 21:41:16 server started
2024-10-29 21:41:17 
2024-10-29 21:41:17 /usr/local/bin/docker-entrypoint.sh: running /docker-entrypoint-initdb.d/init.sql
2024-10-29 21:41:17 CREATE ROLE
2024-10-29 21:41:17 CREATE ROLE
2024-10-29 21:41:17 
2024-10-29 21:41:17 
2024-10-29 21:41:17 waiting for server to shut down....2024-10-29 13:41:17.025 GMT [48] LOG:  received fast shutdown request
2024-10-29 21:41:17 2024-10-29 13:41:17.025 GMT [48] LOG:  aborting any active transactions
2024-10-29 21:41:17 2024-10-29 13:41:17.029 GMT [48] LOG:  background worker "logical replication launcher" (PID 55) exited with exit code 1
2024-10-29 21:41:17 2024-10-29 13:41:17.030 GMT [49] LOG:  shutting down
2024-10-29 21:41:17 2024-10-29 13:41:17.046 GMT [49] LOG:  checkpoint starting: shutdown immediate
2024-10-29 21:41:17 2024-10-29 13:41:17.066 GMT [49] LOG:  checkpoint complete: wrote 7 buffers (0.0%); 0 WAL file(s) added, 0 removed, 0 recycled; write=0.001 s, sync=0.001 s, total=0.024 s; sync files=6, longest=0.001 s, average=0.001 s; distance=11349 kB, estimate=11349 kB; lsn=0/2000028, redo lsn=0/2000028
2024-10-29 21:41:17 2024-10-29 13:41:17.068 GMT [48] LOG:  database system is shut down
2024-10-29 21:41:17  done
2024-10-29 21:41:17 server stopped
2024-10-29 21:41:17 
2024-10-29 21:41:17 PostgreSQL init process complete; ready for start up.
2024-10-29 21:41:17 
2024-10-29 21:41:16 initdb: warning: enabling "trust" authentication for local connections
2024-10-29 21:41:16 initdb: hint: You can change this by editing pg_hba.conf or using the option -A, or --auth-local and --auth-host, the next time you run initdb.
2024-10-29 21:41:17 2024-10-29 13:41:17.136 GMT [1] LOG:  starting PostgreSQL 16.2 (Debian 16.2-1.pgdg120+2) on aarch64-unknown-linux-gnu, compiled by gcc (Debian 12.2.0-14) 12.2.0, 64-bit
2024-10-29 21:41:17 2024-10-29 13:41:17.136 GMT [1] LOG:  listening on IPv4 address "0.0.0.0", port 5432
2024-10-29 21:41:17 2024-10-29 13:41:17.136 GMT [1] LOG:  listening on IPv6 address "::", port 5432
2024-10-29 21:41:17 2024-10-29 13:41:17.137 GMT [1] LOG:  listening on Unix socket "/var/run/postgresql/.s.PGSQL.5432"
2024-10-29 21:41:17 2024-10-29 13:41:17.139 GMT [66] LOG:  database system was shut down at 2024-10-29 13:41:17 GMT
2024-10-29 21:41:17 2024-10-29 13:41:17.142 GMT [1] LOG:  database system is ready to accept connections
2024-10-29 21:46:17 2024-10-29 13:46:17.153 GMT [64] LOG:  checkpoint starting: time
2024-10-29 21:46:21 2024-10-29 13:46:21.376 GMT [64] LOG:  checkpoint complete: wrote 44 buffers (0.3%); 0 WAL file(s) added, 0 removed, 0 recycled; write=4.207 s, sync=0.003 s, total=4.224 s; sync files=11, longest=0.002 s, average=0.001 s; distance=260 kB, estimate=260 kB; lsn=0/2041448, redo lsn=0/2041410

3.2 从节点日志

2024-10-29 22:53:21 2024-10-29 14:53:21.189 GMT [1] LOG:  starting PostgreSQL 16.2 (Debian 16.2-1.pgdg120+2) on aarch64-unknown-linux-gnu, compiled by gcc (Debian 12.2.0-14) 12.2.0, 64-bit
2024-10-29 22:53:21 2024-10-29 14:53:21.189 GMT [1] LOG:  listening on IPv4 address "0.0.0.0", port 5432
2024-10-29 22:53:21 2024-10-29 14:53:21.189 GMT [1] LOG:  listening on IPv6 address "::", port 5432
2024-10-29 22:53:21 2024-10-29 14:53:21.191 GMT [1] LOG:  listening on Unix socket "/var/run/postgresql/.s.PGSQL.5432"
2024-10-29 22:53:21 2024-10-29 14:53:21.194 GMT [30] LOG:  database system was interrupted; last known up at 2024-10-29 14:52:57 GMT
2024-10-29 22:53:21 2024-10-29 14:53:21.262 GMT [30] LOG:  entering standby mode
2024-10-29 22:53:21 2024-10-29 14:53:21.262 GMT [30] LOG:  starting backup recovery with redo LSN 0/4000028, checkpoint LSN 0/4000060, on timeline ID 1
2024-10-29 22:53:21 2024-10-29 14:53:21.263 GMT [30] LOG:  redo starts at 0/4000028
2024-10-29 22:53:21 2024-10-29 14:53:21.264 GMT [30] LOG:  completed backup recovery with redo LSN 0/4000028 and end LSN 0/4000100
2024-10-29 22:53:21 2024-10-29 14:53:21.264 GMT [30] LOG:  consistent recovery state reached at 0/4000100
2024-10-29 22:53:21 2024-10-29 14:53:21.264 GMT [1] LOG:  database system is ready to accept read-only connections
2024-10-29 22:53:21 2024-10-29 14:53:21.270 GMT [37] LOG:  started streaming WAL from primary at 0/5000000 on timeline 1

三、验证主从状态

进入主节点，查询副本。

$ docker exec -it postgres-master /bin/bash
$ psql -U postgres
psql (16.2 (Debian 16.2-1.pgdg120+2))
Type "help" for help.

postgres=# select client_addr,sync_state from pg_stat_replication;
 client_addr | sync_state
-------------+------------
 172.54.0.3  | async
(1 row)

在主节点中创建数据库。

$ psql -U postgres
psql (16.2 (Debian 16.2-1.pgdg120+2))
Type "help" for help.

postgres=# create database test_db;
CREATE DATABASE

postgres=# \l
                                                      List of databases
   Name    |  Owner   | Encoding | Locale Provider |  Collate   |   Ctype    | ICU Locale | ICU Rules |   Access privileges   
-----------+----------+----------+-----------------+------------+------------+------------+-----------+-----------------------
 postgres  | postgres | UTF8     | libc            | en_US.utf8 | en_US.utf8 |            |           |
 template0 | postgres | UTF8     | libc            | en_US.utf8 | en_US.utf8 |            |           | =c/postgres          +
           |          |          |                 |            |            |            |           | postgres=CTc/postgres
 template1 | postgres | UTF8     | libc            | en_US.utf8 | en_US.utf8 |            |           | =c/postgres          +
           |          |          |                 |            |            |            |           | postgres=CTc/postgres
 test_db   | postgres | UTF8     | libc            | en_US.utf8 | en_US.utf8 |            |           |
(4 rows)

从节点查看是否存在主节点创建的数据库。

$ docker exec -it postgres-slave /bin/bash
$ psql -U postgres
psql (16.2 (Debian 16.2-1.pgdg120+2))
Type "help" for help.

postgres=# \l
                                                      List of databases
   Name    |  Owner   | Encoding | Locale Provider |  Collate   |   Ctype    | ICU Locale | ICU Rules |   Access privileges   
-----------+----------+----------+-----------------+------------+------------+------------+-----------+-----------------------
 postgres  | postgres | UTF8     | libc            | en_US.utf8 | en_US.utf8 |            |           | 
 template0 | postgres | UTF8     | libc            | en_US.utf8 | en_US.utf8 |            |           | =c/postgres          +
           |          |          |                 |            |            |            |           | postgres=CTc/postgres
 template1 | postgres | UTF8     | libc            | en_US.utf8 | en_US.utf8 |            |           | =c/postgres          +
           |          |          |                 |            |            |            |           | postgres=CTc/postgres
 test_db   | postgres | UTF8     | libc            | en_US.utf8 | en_US.utf8 |            |           |
(4 rows)

四、其他

1、调试过程需要工具

$ apt update

$ apt install procps
$ apt install iputils-ping
$ apt install telnet
$ apt install net-tools
$ apt install vim

2、构建自己的镜像

在调试容器的过程中，需要用到一些列工具包，但是因为容器镜像精简化，初始镜像中没有需要的工具，构建自己的镜像，方便调试。

1 2	docker build -f ./dev-postgres.dockerfile -t z2huo/postgres-dev:16.2-bookworm . docker build -f ./dev-postgres.dockerfile -t z2huo/postgres-dev:latest .

OB links

[[Postgres 镜像#三、初始化脚本]]

[[Docker 部署主从复制 PostgreSQL#5.1 初始化 SQL 文件]]

OB tags

#PostgreSQL #Docker #数据库