部署Postdock高可用PostgreSQL时Pgpool无法启动的问题

问题解决步骤 #

1. 修正pgpool后端节点配置格式 #

你的BACKEND变量中slave节点的字段缺失，导致pgpool无法正确解析后端节点。将pgpool的BACKEND参数修改为完整格式，确保每个后端包含6个冒号分隔的字段：

BACKEND: "0:pgmaster:5432:1:/var/lib/postgresql/data:ALLOW_TO_FAILOVER,1:pgslave:5432:1:/var/lib/postgresql/data:"

每个字段依次为：节点ID、主机名、端口、权重、数据目录、节点标志，slave节点标志可留空，但字段数必须与master一致。

2. 添加postgres服务健康检查与pgpool依赖 #

postgres服务显示1/1副本不代表数据库已完全就绪，需要添加健康检查让pgpool等待数据库初始化完成后再启动：

• 给pgmaster和pgslave添加健康检查配置：

pgmaster:
    # ... 原有配置 ...
    healthcheck:
        test: ["CMD-SHELL", "pg_isready -U monkey_user -d monkey_db"]
        interval: 10s
        timeout: 5s
        retries: 5
pgslave:
    # ... 原有配置 ...
    healthcheck:
        test: ["CMD-SHELL", "pg_isready -U monkey_user -d monkey_db"]
        interval: 10s
        timeout: 5s
        retries: 5

• 给pgpool添加依赖，确保仅在postgres节点健康后启动：

pgpool:
    # ... 原有配置 ...
    depends_on:
        pgmaster:
            condition: service_healthy
        pgslave:
            condition: service_healthy

3. 确保数据库允许pgpool访问 #

在postgres节点的CONFIGS中添加pg_hba.conf规则，允许来自集群网络的md5认证连接：

# pgmaster的CONFIGS修改为
CONFIGS: "listen_addresses:'*',pg_hba.conf:host all all 0.0.0.0/0 md5"
# pgslave的CONFIGS修改为
CONFIGS: "max_replication_slots:10,pg_hba.conf:host all all 0.0.0.0/0 md5"

4. 调整超时参数（可选） #

将WAIT_BACKEND_TIME_OUT从60延长到120，给数据库初始化和主从同步留足时间：

WAIT_BACKEND_TIME_OUT: 120

完整修改后的docker-compose.yml #

version: '3'
networks:
    cluster:

services:
    pgmaster:
        image: postdock/postgres
        environment:
            PARTNER_NODES: "pgmaster,pgslave"
            CLUSTER_NODE_NETWORK_NAME: pgmaster
            NODE_PRIORITY: 100
            NODE_ID: 1
            NODE_NAME: pgmaster
            POSTGRES_PASSWORD: monkey_pass
            POSTGRES_USER: monkey_user
            POSTGRES_DB: monkey_db
            CONFIGS: "listen_addresses:'*',pg_hba.conf:host all all 0.0.0.0/0 md5"
            CLUSTER_NAME: pg_cluster
            REPLICATION_DB: replication_db
            REPLICATION_USER: replication_user
            REPLICATION_PASSWORD: replication_pass
        ports:
            - 5440:5432
        networks:
            - cluster
        healthcheck:
            test: ["CMD-SHELL", "pg_isready -U monkey_user -d monkey_db"]
            interval: 10s
            timeout: 5s
            retries: 5
    pgslave:
        image: postdock/postgres
        environment:
            PARTNER_NODES: "pgmaster,pgslave"
            REPLICATION_PRIMARY_HOST: pgmaster
            NODE_ID: 2
            NODE_NAME: pgslave
            CLUSTER_NODE_NETWORK_NAME: pgslave
            REPLICATION_PRIMARY_PORT: 5432
            CONFIGS: "max_replication_slots:10,pg_hba.conf:host all all 0.0.0.0/0 md5"
        ports:
            - 5441:5432
        networks:
            - cluster
        healthcheck:
            test: ["CMD-SHELL", "pg_isready -U monkey_user -d monkey_db"]
            interval: 10s
            timeout: 5s
            retries: 5
    pgpool:
        image: postdock/pgpool
        environment:
            PCP_USER: pcp_user
            PCP_PASSWORD: pcp_pass
            WAIT_BACKEND_TIME_OUT: 120
            CHECK_USER: monkey_user
            CHECK_PASS: monkey_pass
            CHECK_PGCONNECT_TIMEOUT: 5
            DB_USERS: monkey_user:monkey_pass
            BACKEND: "0:pgmaster:5432:1:/var/lib/postgresql/data:ALLOW_TO_FAILOVER,1:pgslave:5432:1:/var/lib/postgresql/data:"
            REQUIRE_MIN_BACKENDS: 1
            CONFIGS: "num_init_children:250,max_pool:4"
        ports:
            - 5432:5432
            - 9898:9898
        networks:
            - cluster
        depends_on:
            pgmaster:
                condition: service_healthy
            pgslave:
                condition: service_healthy

重新部署 #

执行以下命令更新集群：

docker stack rm master_slave_postgres
docker stack deploy -c docker-compose.yml master_slave_postgres

内容的提问来源于stack exchange，提问作者pxq