docker/pgsql/Demo/StreamReplication

部署 PostgreSQL 流复制

• 按实际环境修改

  • docker-compose.yml
  • pgsql1/conf.d/*.cnf
  • pgsql2/conf.d/*.cnf

• 创建目录

  grep '\<source:' docker-compose.yml | cut -d: -f2 | xargs mkdir -p
  chmod 0750 pgsql{1..2}/db
  

• 启动

  docker-compose up -d
  

• 查看日志，直至出现 "PGSQL is ready for connections."

  docker-compose logs pgsql1
  docker-compose logs pgsql2
  

• 登录 pgsql1 数据库，创建测试表，写入测试数据

  docker exec -u postgres pgsql1 \
      psql -c "create table t1(id int, name varchar)"
  docker exec -u postgres pgsql1 \
      psql -c "insert into t1 values (1, 'aa'),(2, 'bb')"
  

• 登录 pgsql1 数据库，创建同步用户，配置权限

  docker exec -u postgres pgsql1 \
      psql -c "create user replicator login replication password 'Replicator_1234'"
  docker exec -u postgres pgsql1 \
      echo 'host replication replicator pgsql2 trust' >> pgsql1/db/pg_hba.conf
  #docker exec -u postgres pgsql1 pg_ctl reload
  

• 增加主库配置

  echo '
  wal_level = hot_standby
  max_wal_senders = 8
  wal_keep_size = 512
  wal_sender_timeout = 60s
  max_connections = 100
  ' > pgsql1/conf.d/replication.conf
  
  

• 重启 pgsql1 数据库

  docker-compose restart pgsql1
  

• 登录 pgsql2 数据库，同步 pgsql1 的数据目录

  docker exec -u postgres pgsql2 mkdir -p /var/log/pgsql/db_pg1
  docker exec -u postgres pgsql2 \
      pg_basebackup \
          -h pgsql1 \
          -D /var/log/pgsql/db_pg1 \
          -p 5432 \
          -U replicator \
          -Fp -Xs -P -c fast
  

• 停止并删除 pgsql2 容器，替换其数据目录，注意数据目录权限

  docker-compose stop pgsql2
  docker-compose rm -f pgsql2
  rm -rf pgsql2/db
  mv pgsql2/log/db_pg1 pgsql2/db
  chmod 0750 pgsql2/db
  

• 增加从库配置

  echo '
  primary_conninfo = 'host=pgsql1 port=5432 user=replicator'
  recovery_target_timeline = latest
  hot_standby = on
  max_standby_streaming_delay = 30s
  wal_receiver_status_interval = 10s
  hot_standby_feedback = on
  max_connections = 200
  max_worker_processes = 20
  ' > pgsql2/conf.d/replication.conf
  

• 启动 pgsql2 数据库

  docker-compose up -d pgsql2
  

• 登录 pgsql1 数据库，查看从库信息

  docker exec -u postgres pgsql1 \
      psql -c "select
          client_addr,
          usename,
          backend_start,
          application_name,
          sync_state,
          sync_priority
          FROM pg_stat_replication;"
  # 在 pgsql1 里创建数据库、表或更改数据，在 pgsql2 里可以看到
  

• 查看状态

  docker exec -u postgres pgsql1 pg_controldata -D /var/lib/pgsql | grep cluster
  # 主库(pgsql1)输出: in production
  docker exec -u postgres pgsql2 pg_controldata -D /var/lib/pgsql | grep cluster
  # 从库(pgsql2)输出: in archive recovery
  

• 从库提升为主库

  docker exec -u postgres pgsql2 pg_ctl promote