Postgres 数据库在docker环境下分布式部署（arm64框架）

AMD参考：https://www.cnblogs.com/1285026182YUAN/p/20018931

1. 制作镜像

docker pull  --platform linux/arm64 bitnamilegacy/postgresql-repmgr:17.6.0-debian-12-r2

docker inspect bitnamilegacy/postgresql-repmgr:17.6.0-debian-12-r2 | findstr "Architecture"

创建 D:\武清服务器-路网\images-arm64-common\postgis-repmgr\Dockerfile

FROM docker.io/bitnamilegacy/postgresql-repmgr:17.6.0-debian-12-r2

USER root

# 安装基础工具
RUN apt-get update && \
    apt-get install -y --no-install-recommends \
        curl \
        ca-certificates \
        lsb-release \
        gnupg2 \
    && rm -rf /var/lib/apt/lists/*

# =========================
# 1. 使用官方 PostgreSQL APT 源（HTTPS）
# =========================
RUN curl -fsSL https://www.postgresql.org/media/keys/ACCC4CF8.asc | gpg --dearmor -o /etc/apt/trusted.gpg.d/postgresql.gpg && \
    echo "deb https://apt.postgresql.org/pub/repos/apt bookworm-pgdg main" > /etc/apt/sources.list.d/pgdg.list

# =========================
# 2. 换 Debian 源为国内镜像（解决 buildx 卡死）
# =========================
RUN sed -i 's|http://deb.debian.org/debian|https://mirrors.ustc.edu.cn/debian|g' /etc/apt/sources.list && \
    sed -i 's|http://deb.debian.org/debian-security|https://mirrors.ustc.edu.cn/debian-security|g' /etc/apt/sources.list

# =========================
# 3. 更新并安装 PostGIS
# =========================
RUN apt-get update && \
    apt-get install -y --no-install-recommends \
        postgresql-17-postgis-3 \
        postgresql-17-postgis-3-scripts && \
    rm -rf /var/lib/apt/lists/*

USER 1001

生成镜像

核验

docker run --rm postgis-repmgr:arm64.17 postgres --version

docker run --rm postgis-repmgr:arm64.17 repmgr --version

保存镜像

docker save -o D:\武清服务器-路网\images-arm64-common\postgis-repmgr-arm64-17.tar postgis-repmgr:arm64.17

加载镜像

docker load -i postgis-repmgr:arm64.17

2. 生成容器

  确认两台机器的 IP 地址

  机器A (10.5.84.36)

mkdir -p /data/dockerMappingFiles/data_postgis_repmgr/data-0
mkdir -p /data/dockerMappingFiles/data_postgis_repmgr/conf.d
mkdir -p /data/dockerMappingFiles/data_postgis_repmgr/db-back

wal_level = replica
max_wal_senders = 16
max_replication_slots = 16

hot_standby = on
wal_log_hints = on

listen_addresses = '*'

archive_mode = on
archive_timeout = 300
archive_command = 'test ! -f /bitnami/postgresql/archive/%f && cp %p /bitnami/postgresql/archive/%f'

shared_preload_libraries = 'repmgr,pg_stat_statements'

pg_stat_statements.track = all
pg_stat_statements.max = 10000
pg_stat_statements.save = on

docker-compose.yml

  # 节点1：主库候选
  repmgr-node-0:
    image: repmgr:arm64.17
    container_name: repmgr-node-0
    hostname: pg-node-0
    restart: always
    ports:
      - "5432:5432"
    environment:
      - POSTGRESQL_POSTGRES_PASSWORD=Gres*1299
      - POSTGRESQL_USERNAME=postgres
      - POSTGRESQL_PASSWORD=Gres*1299
      - POSTGRESQL_DATABASE=gis_db

      - REPMGR_USERNAME=repmgr
      - REPMGR_PASSWORD=Gres*1299
      - REPMGR_DATABASE=repmgr_db

      - REPMGR_NODE_ID=1
      - REPMGR_NODE_NAME=pg-node-0
      - REPMGR_NODE_NETWORK_NAME=10.5.84.36
        #      - REPMGR_NODE_PORT=5432

      - REPMGR_PRIMARY_HOST=10.5.84.36
        #      - REPMGR_PRIMARY_PORT=5432
      - REPMGR_PARTNER_NODES=10.5.84.36:5432,10.5.84.37:5432,10.5.84.36:5433

        #      - REPMGR_NODE_ROLE=primary 

      - REPMGR_FAILOVER=automatic
      - REPMGR_CONNECT_TIMEOUT=5
      - REPMGR_RECONNECT_ATTEMPTS=3
      - REPMGR_CHILD_NODES_CHECK_INTERVAL=5

      - POSTGRESQL_NUM_SYNCHRONOUS_REPLICAS=0
      - REPMGRD_ENABLED=yes

    volumes:
      - /data/dockerMappingFiles/data_postgis_repmgr/conf.d:/opt/bitnami/postgresql/conf/conf.d
      - /data/dockerMappingFiles/data_postgis_repmgr/data-0:/bitnami/postgresql
      - /data/dockerMappingFiles/data_postgis_repmgr/data-0/archive:/bitnami/postgresql/archive
      - /data/dockerMappingFiles/data_postgis_repmgr/db-back:/db-back
    networks:
            - pgnet




networks:
  pgnet:
    driver: bridge

启动

docker-compose up -d repmgr-node-0

  机器B (10.5.84.37)

mkdir -p /data/dockerMappingFiles/data_postgis_repmgr/data-1
mkdir -p /data/dockerMappingFiles/data_postgis_repmgr/conf.d
mkdir -p /data/dockerMappingFiles/data_postgis_repmgr/db-back

wal_level = replica
max_wal_senders = 16
max_replication_slots = 16

hot_standby = on
wal_log_hints = on

listen_addresses = '*'

archive_mode = on
archive_timeout = 300
archive_command = 'test ! -f /bitnami/postgresql/archive/%f && cp %p /bitnami/postgresql/archive/%f'

shared_preload_libraries = 'repmgr,pg_stat_statements'

pg_stat_statements.track = all
pg_stat_statements.max = 10000
pg_stat_statements.save = on

docker-compose.yml

  # 节点2：
  repmgr-node-1:
    image: repmgr:arm64.17
    container_name: repmgr-node-1
    hostname: pg-node-1
    restart: always
    ports:
      - "5432:5432"
    environment:
      - POSTGRESQL_POSTGRES_PASSWORD=Gres*1299
      - POSTGRESQL_USERNAME=postgres
      - POSTGRESQL_PASSWORD=Gres*1299
      - POSTGRESQL_DATABASE=gis_db

      - REPMGR_USERNAME=repmgr
      - REPMGR_PASSWORD=Gres*1299
      - REPMGR_DATABASE=repmgr_db

      - REPMGR_NODE_ID=2
      - REPMGR_NODE_NAME=pg-node-1
      - REPMGR_NODE_NETWORK_NAME=10.5.84.37

      - REPMGR_PRIMARY_HOST=10.5.84.36
      - REPMGR_PARTNER_NODES=10.5.84.36:5432,10.5.84.37:5432,10.5.84.18:5432

      - REPMGR_FAILOVER=automatic
      - REPMGR_CONNECT_TIMEOUT=5
      - REPMGR_RECONNECT_ATTEMPTS=3
      - REPMGR_CHILD_NODES_CHECK_INTERVAL=5

      - POSTGRESQL_NUM_SYNCHRONOUS_REPLICAS=0
      - REPMGRD_ENABLED=yes

    volumes:
      - /data/dockerMappingFiles/data_postgis_repmgr/conf.d:/opt/bitnami/postgresql/conf/conf.d      
      - /data/dockerMappingFiles/data_postgis_repmgr/data-1:/bitnami/postgresql
      - /data/dockerMappingFiles/data_postgis_repmgr/data-1/archive:/bitnami/postgresql/archive
      - /data/dockerMappingFiles/data_postgis_repmgr/db-back:/db-back

    networks:
      - pgnet





networks:
  pgnet:
    driver: bridge

启动

docker-compose up -d repmgr-node-1

  机器B (10.5.84.18)

mkdir -p /data/dockerMappingFiles/data_postgis_repmgr/data-witness

docker-compose.yml

  repmgr-witness:
    image: repmgr:arm64.17
    container_name: repmgr-witness
    hostname: pg-witness-0
    restart: always
    ports:
      - "5432:5432"
    environment:
      - POSTGRESQL_POSTGRES_PASSWORD=Gres*1299
      - POSTGRESQL_USERNAME=repmgr
      - POSTGRESQL_PASSWORD=Gres*1299
      - POSTGRESQL_DATABASE=repmgr_db

      - REPMGR_USERNAME=repmgr
      - REPMGR_PASSWORD=Gres*1299
      - REPMGR_DATABASE=repmgr_db

      - REPMGR_NODE_ID=1002
      - REPMGR_NODE_NAME=pg-witness-0
      - REPMGR_NODE_NETWORK_NAME=10.5.84.18

      - REPMGR_NODE_TYPE=witness
      - REPMGR_PRIMARY_HOST=10.5.84.36
      - REPMGR_PARTNER_NODES=10.5.84.36:5432,10.5.84.37:5432,10.5.84.18:5432

      - REPMGRD_ENABLED=yes
    volumes:
      - /data/dockerMappingFiles/data_postgis_repmgr/data-witness:/bitnami/postgresql
    networks:
      - pgnet


networks:
  pgnet:
    driver: bridge

启动

docker-compose up -d repmgr-witness

3. 校验

docker exec -it -u postgres repmgr-node-0 /opt/bitnami/repmgr/bin/repmgr -f /opt/bitnami/repmgr/conf/repmgr.conf cluster show

4. 调试

如需要删除 映射文件（无权限的情况）

docker run --rm -it -v /data/dockerMappingFiles/data_postgis_repmgr/data-0:/target alpine
find /target -mindepth 1 -delete
exit

5. 进入容器

docker exec -it repmgr-node-0 bash

psql -U postgres

输入密码：Gres*1299

6. 数据命令

1） \l 列出所有数据库

     

2) \du 查看所有用户（角色）

  

3) 创建 postuser 用户

CREATE USER postuser WITH PASSWORD 'DB*pg.2022p' CREATEDB SUPERUSER CREATEROLE;

4)  创建 postdb 数据库

CREATE DATABASE postdb;

5) 登录测试

psql -U postuser -d postdb

DB*pg.2022p

6） \c postdb 切换到 postdb 数据库

7） \dt  列出当前数据库的表

8） 创建扩展

CREATE EXTENSION postgis;

CREATE EXTENSION postgis_topology;

select PostGIS_version(); 验证

9）\d+ 表名 查看表里面的字段信息

7 数据还原

docker exec -it repmgr-node-0 bash -c "PGPASSWORD='Gres*1299' pg_restore -U postgres -d postdb -F c -v /db-back/db_railnet-20260420.dump"

end.