一键安装: PostgreSQL主从读写分离定时备份

[zhongfangxiong]

第二台机器（默认端口）（读写分离 + 每日备份）

主库 5432，从库 5433，镜像走 docker-hk.loda.net.cn
每日 03:00 自动 dump 主库 → /mnt/disk5t/backup/（保留 7 天）

---

1. 目录准备

# SSD
sudo mkdir -p /srv/pg-ha/data
sudo chown -R $USER:$USER /srv/pg-ha

# HDD
sudo mkdir -p /mnt/disk5t/backup
sudo chown -R $USER:$USER /mnt/disk5t/backup

---

2. 文件清单（放进 /srv/pg-ha）

2.1 .env

POSTGRES_DB=appdb
POSTGRES_USER=appuser
POSTGRES_PASSWORD=AppUser123
REPL_USER=repl_user
REPL_PASS=ReplPass123

2.2 docker-compose.yml

version: "3.9"

services:
  pg-primary:
    image: docker-hk.loda.net.cn/bitnami/postgresql:latest
    container_name: pg-primary
    restart: unless-stopped
    environment:
      POSTGRESQL_DATABASE: ${POSTGRES_DB}
      POSTGRESQL_USERNAME: ${POSTGRES_USER}
      POSTGRESQL_PASSWORD: ${POSTGRES_PASSWORD}
      POSTGRESQL_REPLICATION_MODE: master
      POSTGRESQL_REPLICATION_USER: ${REPL_USER}
      POSTGRESQL_REPLICATION_PASSWORD: ${REPL_PASS}
    volumes:
      - /srv/pg-ha/data:/bitnami/postgresql
    ports:
      - "5432:5432"
    networks:
      - pg-net

  pg-replica:
    image: docker-hk.loda.net.cn/bitnami/postgresql:latest
    container_name: pg-replica
    restart: unless-stopped
    environment:
      POSTGRESQL_MASTER_HOST: pg-primary
      POSTGRESQL_MASTER_PORT_NUMBER: 5432
      POSTGRESQL_REPLICATION_MODE: slave
      POSTGRESQL_REPLICATION_USER: ${REPL_USER}
      POSTGRESQL_REPLICATION_PASSWORD: ${REPL_PASS}
      POSTGRESQL_DATABASE: ${POSTGRES_DB}
      POSTGRESQL_USERNAME: ${POSTGRES_USER}
      POSTGRESQL_PASSWORD: ${POSTGRES_PASSWORD}
    volumes:
      - /mnt/disk5t/postgre-r:/bitnami/postgresql
    ports:
      - "5433:5432"
    depends_on:
      - pg-primary
    networks:
      - pg-net

  # 每日备份服务
  pg-backup:
    image: docker-hk.loda.net.cn/bitnami/postgresql:latest
    container_name: pg-backup
    restart: unless-stopped
    environment:
      PGPASSWORD: ${POSTGRES_PASSWORD}
    volumes:
      - /mnt/disk5t/backup:/backup
    command: |
      bash -c '
        echo "0 3 * * * pg_dump -h pg-primary -U ${POSTGRES_USER} ${POSTGRES_DB} | gzip > /backup/backup_$$(date +\%Y\%m\%d_\%H\%M\%S).sql.gz && find /backup -name "*.sql.gz" -mtime +7 -delete" | crontab -
        crond -f'
    depends_on:
      - pg-primary
    networks:
      - pg-net

networks:
  pg-net:
    driver: bridge

---

3. 一键启动

cd /srv/pg-ha
docker compose up -d

---

4. 验证备份

# 手动触发一次
docker exec pg-backup bash -c 'pg_dump -h pg-primary -U appuser appdb | gzip > /backup/test.sql.gz'

# 查看文件
ls -lh /mnt/disk5t/backup/

---

5. 常用命令

# 看日志
docker compose logs -f pg-primary
docker compose logs -f pg-replica
docker compose logs -f pg-backup

# 停集群
docker compose down

---

完事！ 默认端口 + 每日备份 + 自建镜像源，全齐了。