环境准备

系统版本IP地址image版本角色划分
Ubuntu 20.0410.84.3.123postgre:9.6主
Ubuntu 20.0410.84.3.124postgre:9.6备

主配置操作

创建docker-compose文件

cat > docker-compose.yaml << EOF
version: '3'
services:
  postgres-master:
    container_name: postgres-master
    image: postgres:9.6
    restart: always
    environment:
      POSTGRES_PASSWORD: Kevin_1128
      POSTGRES_USER: postgres
    ports:
      - "5432:5432"
    volumes:
      - /apps/postgresql/data:/var/lib/postgresql/data
      - /apps/postgresql/logs:/var/log/postgresql
      - /apps/postgresql/arch:/mnt
  init-chown:
    image: alpine:latest
    volumes:
      - /apps/postgresql/logs:/var/log/postgresql
    entrypoint: ["/bin/sh", "-c"]
    command: ["chown -R postgres:postgres /var/log/postgresql"]
    restart: "no"
    depends_on:
      - postgres-master
EOF

构建主容器

docker-compose up -d

创建复制账号并验证

1. 创建用户

docker exec -it postgres-master bash -c 'psql -U postgres -c "create role sync login replication encrypted password '\''Kevin_1128'\'';"'

2. 验证用户

docker exec -it postgres-master bash -c 'psql -U postgres -c "\du;"'
                               List of roles
Role name |                         Attributes                         | Member of 
-----------+------------------------------------------------------------+-----------
postgres  | Superuser, Create role, Create DB, Replication, Bypass RLS | {}
sync      | Replication                                                | {}

配置主PostgreSQL配置文件

cat > /apps/postgresql/data/postgresql.conf << EOF
listen_addresses = '*'
synchronous_standby_names = ''
synchronous_commit = on
archive_mode = on
archive_command = 'test ! -f /mnt/%f && cp %p /mnt/%f'
wal_level = replica
max_wal_senders = 5
wal_keep_segments = 128
wal_sender_timeout = 60s
max_connections = 200
hot_standby = on
max_standby_streaming_delay = 30s
wal_receiver_status_interval = 10s
hot_standby_feedback = on
wal_log_hints = on
logging_collector = on
log_directory = '/var/log/postgresql'
log_filename = 'postgresql-%Y-%m-%d_%H%M%S.1og'
EOF

修改客户端连接文件

cat > /apps/postgresql/data/pg_hba.conf  << EOF
local   all             all                                     trust
host    all             all             127.0.0.1/32            trust
host all all all md5
host    replication      sync           10.84.3.124/32          md5
EOF

对数据目录进行授权

chown -R 999:999 /apps/postgresql

停止服务

docker-compose stop

从配置操作

创建docker-compose文件

cat > docker-compose.yaml << EOF
version: '3'
services:
  postgres-slave:
    container_name: postgres-slave
    image: postgres:9.6
    restart: always
    environment:
      POSTGRES_PASSWORD: Kevin_1128
      POSTGRES_USER: postgres
    ports:
      - "5433:5432"
    volumes:
      - /apps/postgresql/data:/var/lib/postgresql/data
      - /apps/postgresql/logs:/var/log/postgresql
  init-chown:
    image: alpine:latest
    volumes:
      - /apps/postgresql/logs:/var/log/postgresql
    entrypoint: ["/bin/sh", "-c"]
    command: ["chown -R postgres:postgres /var/log/postgresql"]
    restart: "no"
    depends_on:
      - postgres-slave
EOF

构建从容器

docker-compose up -d

停止从容器

docker-compose stop

新增备用服务器（standby server）的配置文件

cat > /apps/postgresql/data/recovery.conf << EOF
standby_mode = 'on'
primary_conninfo = 'host=10.84.3.123 port=5432 user=sync password=Kevin_1128'
recovery_target_timeline = 'latest'
EOF

对数据目录进行授权

chown -R 999:999 /apps/postgresql

主库数据同步到备库(主节点操作)

rsync -cva --inplace --exclude=*pg_xlog* /apps/postgresql/data/ 10.84.3.124:/apps/postgresql/data/

配置从PostgreSQL配置文件

cat > /apps/postgresql/data/postgresql.conf << EOF
listen_addresses = '*'
hot_standby = on 
max_connections = 200
max_standby_streaming_delay = 30s
wal_receiver_status_interval = 10
hot_standby_feedback = on
max_wal_senders = 15
logging_collector = on
log_directory = '/var/log/postgresql'
log_filename = 'postgresql-%Y-%m-%d_%H%M%S.1og'
wal_level = replica
EOF

启动主库服务

docker-compose start

启动从库服务

docker-compose start

在主库查看备用库连接

root@PostgreSQL-Master:~/postgresql# docker exec -it postgres-master /bin/bash -c "psql -U postgres -x -c 'select * from pg_stat_replication;'"
-[ RECORD 1 ]----+------------------------------
pid              | 137
usesysid         | 16384
usename          | sync
application_name | walreceiver
client_addr      | 10.84.3.124
client_hostname  | 
client_port      | 54480
backend_start    | 2024-05-22 13:40:20.544918+00
backend_xmin     | 553
state            | streaming
sent_location    | 0/5001308
write_location   | 0/5001308
flush_location   | 0/5001308
replay_location  | 0/5001308
sync_priority    | 0
sync_state       | async #异步复制

"state" 列通常具有以下几种状态：
startup: 备用服务器正在启动，尚未与主服务器建立连接。
catchup: 备用服务器正在从主服务器追赶并应用 WAL 日志，以达到与主服务器的同步状态。
streaming: 备用服务器正在接收并应用来自主服务器的 WAL 日志流，处于实时复制状态。
backup: 备用服务器正在进行基于文件的备份，并正在等待完成。
quorum: 用于多个备用服务器场景中，表示当前备用服务器集群中的大多数备用服务器已经处于流复制状态。
stopped: 备用服务器已停止复制流。

测试数据能否正常复制

1. 创建数据库

root@PostgreSQL-Master:~/postgresql# docker exec -it postgres-master /bin/bash -c "psql -U postgres -x -c 'CREATE DATABASE test;'"
CREATE DATABASE

2. 验证是否创建成功

root@PostgreSQL-Master:~/postgresql# docker exec -it postgres-master /bin/bash -c "psql -U postgres -x -c '\l'"
List of databases
-[ RECORD 1 ]-----+----------------------
Name              | postgres
Owner             | postgres
Encoding          | UTF8
Collate           | en_US.utf8
Ctype             | en_US.utf8
Access privileges | 
-[ RECORD 2 ]-----+----------------------
Name              | template0
Owner             | postgres
Encoding          | UTF8
Collate           | en_US.utf8
Ctype             | en_US.utf8
Access privileges | =c/postgres          +
                  | postgres=CTc/postgres
-[ RECORD 3 ]-----+----------------------
Name              | template1
Owner             | postgres
Encoding          | UTF8
Collate           | en_US.utf8
Ctype             | en_US.utf8
Access privileges | =c/postgres          +
                  | postgres=CTc/postgres
-[ RECORD 4 ]-----+----------------------
Name              | test
Owner             | postgres
Encoding          | UTF8
Collate           | en_US.utf8
Ctype             | en_US.utf8
Access privileges |

3. 从库查看是否复制成功

root@PostgreSQL-Slave:~/postgresql# docker exec -it postgres-slave /bin/bash -c "psql -U postgres -x -c '\l'"
List of databases
-[ RECORD 1 ]-----+----------------------
Name              | postgres
Owner             | postgres
Encoding          | UTF8
Collate           | en_US.utf8
Ctype             | en_US.utf8
Access privileges | 
-[ RECORD 2 ]-----+----------------------
Name              | template0
Owner             | postgres
Encoding          | UTF8
Collate           | en_US.utf8
Ctype             | en_US.utf8
Access privileges | =c/postgres          +
                  | postgres=CTc/postgres
-[ RECORD 3 ]-----+----------------------
Name              | template1
Owner             | postgres
Encoding          | UTF8
Collate           | en_US.utf8
Ctype             | en_US.utf8
Access privileges | =c/postgres          +
                  | postgres=CTc/postgres
-[ RECORD 4 ]-----+----------------------
Name              | test
Owner             | postgres
Encoding          | UTF8
Collate           | en_US.utf8
Ctype             | en_US.utf8
Access privileges |

搭建遇到的问题

1. 搭建完成后从库报错

日志报错：

WARNING:  WAL was generated with wal_level=minimal, data may be missing
HINT:  This happens if you temporarily set wal_level=minimal without taking a new base backup.
FATAL:  hot standby is not possible because wal_level was not set to "replica" or higher on the master server

大概意思就是 从的主配置文件读取的wal_level为minimal，但是搭建的过程指定了replica，这个问题是因为同步主库的数据文件有问题，需要重新同步一次

容器状态：

root@ceph02:~/docker-compose/postgresql# docker-compose ps
         Name                        Command                 State      Ports
-----------------------------------------------------------------------------
postgres-slave            docker-entrypoint.sh postgres    Restarting        
postgresql_init-chown_1   /bin/sh -c chown -R postgr ...   Exit 1

解决办法：

1.1 停止从库和主库服务

docker-compose stop

1.2 主库数据同步到备库

rsync -cva --inplace --exclude=*pg_xlog* /apps/postgresql/data/ 10.84.3.124:/apps/postgresql/data/

重新执行从配置里面的主库数据同步到备库操作

1.3 依次启动主 备服务

docker-compose start

1.4 验证从是否正常

容器状态

root@ceph02:~/docker-compose/postgresql# docker-compose ps
         Name                        Command               State                     Ports                  
------------------------------------------------------------------------------------------------------------
postgres-slave            docker-entrypoint.sh postgres    Up       0.0.0.0:5433->5432/tcp,:::5433->5432/tcp
postgresql_init-chown_1   /bin/sh -c chown -R postgr ...   Exit 1

日志信息

LOG:  entering standby mode
LOG:  started streaming WAL from primary at 0/2000000 on timeline 1
LOG:  consistent recovery state reached at 0/2000098
LOG:  database system is ready to accept read only connections

查看流复制的相关信息（主节点操作）

docker exec -it postgres-master /bin/bash -c "psql -U postgres -x -c 'select * from pg_stat_replication;'"

不出意外会有以下打印：

root@ceph01:~/docker-compose/postgresql# docker exec -it postgres-master /bin/bash -c "psql -U postgres -x -c 'select * from pg_stat_replication;'"
-[ RECORD 1 ]----+-------------------------------[ RECORD 1 ]----+------------------------------
pid              | 34
usesysid         | 16384
usename          | sync
application_name | walreceiver
client_addr      | 10.84.3.124
client_hostname  | 
client_port      | 34686
backend_start    | 2024-05-24 01:07:34.716199+00
backend_xmin     | 552
state            | streaming
sent_location    | 0/20000D0
write_location   | 0/20000D0
flush_location   | 0/20000D0
replay_location  | 0/20000D0
sync_priority    | 0
sync_state       | async  #异步复制

2. 搭建完成后从库报错同步账户密码错误

日志报错：

FATAL:  could not connect to the primary server: connection to server at "10.84.3.123", port 5432 failed: FATAL:  password authentication failed for user "sync"
    
FATAL:  could not connect to the primary server: connection to server at "10.84.3.123", port 5432 failed: FATAL:  password authentication failed for user "sync"
    
FATAL:  could not connect to the primary server: connection to server at "10.84.3.123", port 5432 failed: FATAL:  password authentication failed for user "sync"

解决办法

在主服务中创建下sync账户

docker exec -it postgres-master bash -c 'psql -U postgres -c "create role sync login replication encrypted password '\''Kevin_1128'\'';"'
最后修改：2024 年 05 月 25 日
© 禁止转载
如果觉得我的文章对你有用，请随意赞赏