[ postgre主从复制 ]

发表评论
194 views

postgre主从复制[https://gitee.com/chen-haifeng1/pg_master_slave]

1.参考的文档

2. bilibili参考视频

一. 部署使用docker compose 启动两个postgre数据库

docker-compose.yml

version: "3.1"

networks:
  mynet:
    driver: bridge

services:
  postgres-master:
    image: postgres:13.5
    environment:
      - POSTGRES_USER=master
      - POSTGRES_PASSWORD=123456
      - POSTGRES_DB=master
    volumes:
      # - ./master/pg_hba.conf:/var/lib/postgresql/data/pg_hba.conf
      # - ./master/postgresql.conf:/var/lib/postgresql/data/postgresql.conf
      - ./master/data:/var/lib/postgresql/data
    ports:
      - "5437:5432"
    networks:
      - mynet

  postgres-slave:
    image: postgres:13.5
    environment:
      - POSTGRES_USER=slave
      - POSTGRES_PASSWORD=123456
      - POSTGRES_DB=slave
    volumes:
      # - ./slave/postgresql.conf:/var/lib/postgresql/data/postgresql.conf
      - ./slave/data:/var/lib/postgresql/data
    ports:
      - "5438:5432"
    networks:
      - mynet

二. 主数据库

1.修改文件1postgresql.conf

listen_addresses = '*'
wal_level = replica
archive_command = 'cp %p /home/storage/pgsql/archive/%f'
max_wal_senders = 5

2.修改文件2pg_hba.conf - 主要是最后一行添加权限

host    replication     test1           0.0.0.0/0               md5

3.主数据库创建用户

进入容器
docker exec -it xxxx /bin/bash

进入数据库
psql -U master

创建用户给从数据库用
CREATE ROLE slave login replication encrypted password '123456';

4.重启一下主数据库(让挂载的文件修改进去)

docker restart xxxx

三.从数据库

1.进入从数据库基础备份

docker exec -it xxxx /bin/bash

# 删除
rm -rf $PGDATA

# 备份?
pg_basebackup -h 192.168.1.157 -p 5437 -U slave --password -X stream -Fp --progress -D $PGDATA -R
Password: 
40128/40128 kB (100%), 2/2 tablespaces

# 由于原来的数据已经不存在, 会自动退出

# 重启一下从数据库docker环境
docker restat xxxx

2.从数据库启动

pg_ctl start

3.从数据库检查data文件夹

  • 文件1
standby.signal
  • 文件2
[$] cat postgresql.auto.conf
# Do not edit this file manually!
# It will be overwritten by the ALTER SYSTEM command.
primary_conninfo = 'user=slave password=123456 channel_binding=prefer host=192.168.1.157 port=5437 sslmode=prefer sslcompression=0 sslsni=1 ssl_min_protocol_version=TLSv1.2 gssencmode=prefer krbsrvname=postgres target_session_attrs=any'

2025-05-15版本

master的conf文件

(base) chf@chf:~/Docker-Compose/master_replica/postgres/master$ cat conf/postgresql.conf 
listen_addresses = '*'
wal_level = replica
archive_command = 'cp %p /home/storage/pgsql/archive/%f'
max_wal_senders = 5

master启动的时候执行的脚本（创建给从数据库连接的用户， 和配置权限)

(base) chf@chf:~/Docker-Compose/master_replica/postgres/master$ cat initdb.d/01-create-replica-user.sh 
#!/bin/bash
set -e

psql -v ON_ERROR_STOP=1 --username "$POSTGRES_USER" --dbname "$POSTGRES_DB" <<-EOSQL
    CREATE ROLE slave login replication encrypted password '123456';
EOSQL

echo "host replication slave 0.0.0.0/0 md5" >> "$PGDATA/pg_hba.conf"