docker 部署 postgresql的主从数据库

最新推荐文章于 2024-08-23 17:35:03 发布

liuyunshengsir

最新推荐文章于 2024-08-23 17:35:03 发布

阅读量623

点赞数

分类专栏：数据库文章标签： postgresql docker

本文链接：https://blog.csdn.net/liuyunshengsir/article/details/120065005

版权

数据库专栏收录该内容

29 篇文章 0 订阅

订阅专栏

该博客详细介绍了如何设置PostgreSQL的主从复制，包括启动容器、修改主节点配置、创建备份、从节点恢复及验证复制状态。涉及的配置文件有postgresql.conf和pg_hba.conf，并提到了归档模式的启用及其配置。

摘要由CSDN通过智能技术生成

启动

    docker run --name pgsmaster -p 5500:5432 -e POSTGRES_PASSWORD=pgsmaster -v /home/pg/pgsmaster:/var/lib/postgresql/data -d postgres


    docker run --name pgsslave -p 5501:5432 -e POSTGRES_PASSWORD=pgsslave -v /home/pg/pgsslave:/var/lib/postgresql/data -d postgres

环境变量

POSTGRES_USER
POSTGRES_PASSWORD
POSTGRES_DB

修改主节点配置

psql -h172.17.0.2 -Upostgres -p5432

vim postgresql.conf

sed -ir "s/#*max_replication_slots.*/max_replication_slots= 10/" postgresql.conf
sed -ir "s/#*max_wal_senders.*/max_wal_senders = 10/" postgresql.conf
sed -ir "s/#*wal_level.*/wal_level = replica/" postgresql.conf
sed -ir "s/#*archive_mode.*/archive_mode = on/" postgresql.conf
sed -ir "s/#*archive_command.*/archive_command = '\/bin\/date'/" postgresql.conf
sed -ir "s/#*synchronous_standby_names.*/synchronous_standby_names = '*'/" postgresql.conf


# 检查
cat postgresql.conf | grep "max_replication_slots\|max_wal_senders\|wal_level\|archive_mode\|archive_command\|synchronous_standby_names"

vim pg_hba.conf

增加复制节点授权

host    replication     postgres        172.17.0.3/32                 trust

重启主节点


docker exec -it -u postgres pgsmaster pg_ctl stop

docker restart pgsmaster

进入从节点

docker exec -it -u postgres pgsslave /bin/bash

postgres@2311e1d22803:~$ pg_basebackup -R -D /var/lib/postgresql/repl -Fp -Xs -v -P -h 172.17.0.2 -p 5432 -U postgres
pg_basebackup: initiating base backup, waiting for checkpoint to complete
pg_basebackup: checkpoint completed
pg_basebackup: write-ahead log start point: 0/3000028 on timeline 1
pg_basebackup: starting background WAL receiver
pg_basebackup: created temporary replication slot "pg_basebackup_37"
24291/24291 kB (100%), 1/1 tablespace
pg_basebackup: write-ahead log end point: 0/3000100
pg_basebackup: waiting for background process to finish streaming ...
pg_basebackup: syncing data to disk ...
pg_basebackup: renaming backup_manifest.tmp to backup_manifest
pg_basebackup: base backup completed

根据备份重建节点

docker cp pgsslave:/var/lib/postgresql/repl /home/pg



docker run --name pgsslave -p 5501:5432 -e POSTGRES_PASSWORD=pgsslave -v /home/pg/repl:/var/lib/postgresql/data -d postgres

主节点验证

select * from pg_stat_replication;

在这里插入图片描述

一主多备的情况下 could not receive data from WAL stream: ERROR: requested WAL segment 000000010000000000000004 has already been removed

开启归档

# - Archiving -

archive_mode = on
 # (change requires restart)
archive_command = 'mkdir -p /var/lib/postgresql/data/archivedir && test ! -f /var/lib/postgresql/data/archivedir/%f && cp %p /var/lib/postgresql/data/archivedir/%f'

 # placeholders: %p = path of file to archive
 #               %f = file name only
 # e.g. 'test ! -f /mnt/server/archivedir/%f && cp %p /mnt/server/archivedir/%f'

然后重复上述步骤即可，也可一次性到位

在这里插入图片描述