Postgres

主从模式

Docker

master

• 创建目录赋权

  mkdir -p /mnt/da/postgres/data
  
  chown 1000:1000 /mnt/da/postgres/data
  

• docker-compose.yml

  services:
    postgres:
      image: timescale/timescaledb-ha:pg16.3-ts2.15.1-all
      container_name: postgres
      volumes:
        - /etc/localtime:/etc/localtime:ro
        - /mnt/da/postgres/data:/var/lib/postgresql/data
      environment:
        - TZ=Asia/Shanghai
        - PGDATA=/var/lib/postgresql/data/pgdata
        - POSTGRES_PASSWORD=postgres
      network_mode: host
      restart: always
  

• 启动

  docker compose -f docker-compose.yml up -d
  

• 修改 postgresql.conf

  listen_addresses = '*'
  max_connections = 1024
  password_encryption = scram-sha-256
  shared_buffers = 32GB
  work_mem = 8192kB
  maintenance_work_mem = 32GB
  effective_io_concurrency = 512
  max_worker_processes = 256
  max_parallel_workers_per_gather = 128
  max_parallel_workers = 64
  wal_level = replica
  synchronous_commit = off
  checkpoint_completion_target = 0.5
  max_wal_size = 4GB
  min_wal_size = 2GB
  #archive_command = 'cp %p /var/lib/postgresql/data/archive/%f'
  max_wal_senders = 8
  max_replication_slots = 8
  hot_standby = on
  

• 选择性修改 pg_hba.conf

  host  all         all     all               scram-sha-256
  host  replication replica 192.168.2.11/32    scram-sha-256
  host  replication replica 192.168.2.12/32    scram-sha-256
  

• 创建从用户

  CREATE USER replica WITH REPLICATION ENCRYPTED PASSWORD 'xxx';
  
  SELECT * FROM pg_create_physical_replication_slot('replica_12');
  
  SELECT * FROM pg_replication_slots;
  

replica

• 创建目录赋权

  mkdir -p /mnt/da/postgres/data
  
  chown 1000:1000 /mnt/da/postgres/data
  

• docker-compose.yml

  services:
    postgres:
      image: timescale/timescaledb-ha:pg16.3-ts2.15.1-all
      container_name: postgres
      volumes:
        - /etc/localtime:/etc/localtime:ro
        - /mnt/da/postgres/data:/var/lib/postgresql/data
      environment:
        - TZ=Asia/Shanghai
        - PGDATA=/var/lib/postgresql/data/pgdata
        - POSTGRES_PASSWORD=postgres
      network_mode: host
      restart: always
  

• 启动

  docker compose -f docker-compose.yml up -d
  

• 进入容器

  docker exec -it postgres bash
  

• 通过 pg_basebackup 创建主数据库的的备份

  pg_basebackup -h 192.168.2.11 -p 5432 -D /var/lib/postgresql/data/pgdata/bak -S replica_12 -X stream -P -U replica -Fp -R
  

• 删除容器

  docker rm -f postgres
  

• 替换目录

  /mnt/da/postgres/data/pgdata/bak
  /mnt/da/postgres/data/pgdata
  

• 再启动

  docker compose -f docker-compose.yml up -d
  

• 查看同步状态

  SELECT * FROM pg_replication_slots;
  SELECT * FROM pg_stat_replication;
  SELECT client_addr,sync_state FROM pg_stat_replication;
  

• 查看数据库是否只读

  SHOW transaction_read_only;
  

  # 主库:
  Database cluster state: in production
  # 从库:
  Database cluster state: in archive recovery
  

• 查看主从状态

  pg_controldata