postgres 流复制环境大家

最新推荐文章于 2025-01-21 09:36:24 发布

xionglang7

最新推荐文章于 2025-01-21 09:36:24 发布

阅读量1.2k

点赞数

分类专栏： postgresql原理

本文链接：https://blog.youkuaiyun.com/xionglang7/article/details/48606783

版权

postgresql原理专栏收录该内容

1 篇文章

订阅专栏

本文介绍了PostgreSQL 9.0开始引入的流复制技术，类似于Oracle的Data Guard。详细阐述了流复制的原理及搭建过程，包括主库和从库的配置步骤，如修改配置文件、创建用户、启动和停止备份、归档设置等，最终实现从库成功进入热备状态并接受只读连接。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

postgresql在9.0开始有流复制的技术了。个人觉得这个和oracle的DG相识。

流复制的原理：

搭建过程：

主库上创建用户，并修改postgres.conf和pg_ha.conf的参数
CREATE USER replica REPLICATION LOGIN ENCRYPTED PASSWORD 'replica';

postgres.conf修改如下：(归档参数并不是必须的)
listen_address = '*'
wal_level = hot_standby
max_wal_senders = 3
checkpoint_segments = 8
wal_keep_segments = 8

pg_ha.conf修改如下：
host replication replica 192.168.159.132/32 md5

[postgres@localhost tmp]$ psql
psql (9.4.1)
Type "help" for help.

postgres=# select pg_start_backup('/pingan/pg5432/data');
pg_start_backup
-----------------
0/26000028
(1 row)
以上都是主库上操作

拷贝主库的/pingan/pg5432/data（$PGDATA）到从库上的相应目录，（如果归档开启了还需要copy 归档的那个目录）
在从库添加recover.conf文件
[postgres@localhost data]$ cat recovery.conf
standby_mode = 'on'
primary_conninfo = 'host=192.168.159.131 port=5432 user=replica password=replica'--链接的主库信息
trigger_file = '/pingan/pg5501/hot_file/postgresql.trigger'--注意这个必须是文件名，不能是目录

修改从库的postgresql.conf
hot_standby = on

停掉主库的备份（停掉备份这个过程中产生的归档可以不用copy到standby库）
postgres=# select pg_stop_backup();
NOTICE: pg_stop_backup complete, all required WAL segments have been archived
pg_stop_backup
----------------
0/26000128
(1 row)

删除从库的postmaster.pid
配置.bash_profile文件，最后起库
[postgres@localhost ~]$ cat .bash_profile
# .bash_profile

# Get the aliases and functions
if [ -f ~/.bashrc ]; then
. ~/.bashrc
fi

# User specific environment and startup programs

PATH=$PATH:$HOME/.local/bin:$HOME/bin

export PATH

export PGHOST=/usr/local/pgsql/var
export PGDATA=/pingan/pg5501/data
export PGHOME=/usr/local/pgsql
alias pgstart='pg_ctl -D $PGDATA start'
alias pgstop='pg_ctl kill INT `head -1 $PGDATA/postmaster.pid`'
export LANG=en_US.utf8
PATH=$PGHOME/bin:$PATH
export PATH
export LD_LIBRARY_PATH=/usr/local/pgsql/lib:$LD_LIBRARY_PATH

standby起库
[postgres@localhost tmp]$ pg_ctl start
server starting
[postgres@localhost tmp]$ LOG: database system was interrupted; last known up at 2015-07-21 18:17:27 EDT
LOG: entering standby mode
LOG: redo starts at 0/26000028
LOG: record with zero length at 0/26000100
LOG: started streaming WAL from primary at 0/26000000 on timeline 1
LOG: consistent recovery state reached at 0/26000128
LOG: database system is ready to accept read only connections