PostgreSQL 9 的 standby 集群实践
-
Upload
march-liu -
Category
Technology
-
view
2.837 -
download
2
description
Transcript of PostgreSQL 9 的 standby 集群实践
![Page 2: PostgreSQL 9 的 standby 集群实践](https://reader033.fdocument.pub/reader033/viewer/2022050702/54986f47b47959794d8b54b5/html5/thumbnails/2.jpg)
. . . . . .
Standby 集群知识
![Page 3: PostgreSQL 9 的 standby 集群实践](https://reader033.fdocument.pub/reader033/viewer/2022050702/54986f47b47959794d8b54b5/html5/thumbnails/3.jpg)
. . . . . .
PostgreSQL 内置的集群方案
![Page 4: PostgreSQL 9 的 standby 集群实践](https://reader033.fdocument.pub/reader033/viewer/2022050702/54986f47b47959794d8b54b5/html5/thumbnails/4.jpg)
. . . . . .
基于预写入机制
![Page 5: PostgreSQL 9 的 standby 集群实践](https://reader033.fdocument.pub/reader033/viewer/2022050702/54986f47b47959794d8b54b5/html5/thumbnails/5.jpg)
. . . . . .
client DML or DDL−−−−−−−→ [switch file −→ archive file]wal level −→ data storage
![Page 6: PostgreSQL 9 的 standby 集群实践](https://reader033.fdocument.pub/reader033/viewer/2022050702/54986f47b47959794d8b54b5/html5/thumbnails/6.jpg)
. . . . . .
高性能
![Page 7: PostgreSQL 9 的 standby 集群实践](https://reader033.fdocument.pub/reader033/viewer/2022050702/54986f47b47959794d8b54b5/html5/thumbnails/7.jpg)
. . . . . .
同步差可控
![Page 8: PostgreSQL 9 的 standby 集群实践](https://reader033.fdocument.pub/reader033/viewer/2022050702/54986f47b47959794d8b54b5/html5/thumbnails/8.jpg)
. . . . . .
流式同步
![Page 9: PostgreSQL 9 的 standby 集群实践](https://reader033.fdocument.pub/reader033/viewer/2022050702/54986f47b47959794d8b54b5/html5/thumbnails/9.jpg)
. . . . . .
基于交换文件的快同步:backup in progress
![Page 10: PostgreSQL 9 的 standby 集群实践](https://reader033.fdocument.pub/reader033/viewer/2022050702/54986f47b47959794d8b54b5/html5/thumbnails/10.jpg)
. . . . . .
较低负载压力的“温备”模式
![Page 11: PostgreSQL 9 的 standby 集群实践](https://reader033.fdocument.pub/reader033/viewer/2022050702/54986f47b47959794d8b54b5/html5/thumbnails/11.jpg)
. . . . . .
基于归档文件的增量恢复:archive/restore
![Page 12: PostgreSQL 9 的 standby 集群实践](https://reader033.fdocument.pub/reader033/viewer/2022050702/54986f47b47959794d8b54b5/html5/thumbnails/12.jpg)
. . . . . .
实施
![Page 13: PostgreSQL 9 的 standby 集群实践](https://reader033.fdocument.pub/reader033/viewer/2022050702/54986f47b47959794d8b54b5/html5/thumbnails/13.jpg)
. . . . . .
基本步骤
![Page 14: PostgreSQL 9 的 standby 集群实践](https://reader033.fdocument.pub/reader033/viewer/2022050702/54986f47b47959794d8b54b5/html5/thumbnails/14.jpg)
. . . . . .
预备 standby 配置文件
![Page 15: PostgreSQL 9 的 standby 集群实践](https://reader033.fdocument.pub/reader033/viewer/2022050702/54986f47b47959794d8b54b5/html5/thumbnails/15.jpg)
. . . . . .
预设 primary 节点。
![Page 16: PostgreSQL 9 的 standby 集群实践](https://reader033.fdocument.pub/reader033/viewer/2022050702/54986f47b47959794d8b54b5/html5/thumbnails/16.jpg)
. . . . . .
primary 进入备份模式
![Page 17: PostgreSQL 9 的 standby 集群实践](https://reader033.fdocument.pub/reader033/viewer/2022050702/54986f47b47959794d8b54b5/html5/thumbnails/17.jpg)
. . . . . .
建立 standby 环境
![Page 18: PostgreSQL 9 的 standby 集群实践](https://reader033.fdocument.pub/reader033/viewer/2022050702/54986f47b47959794d8b54b5/html5/thumbnails/18.jpg)
. . . . . .
配置 standby
![Page 19: PostgreSQL 9 的 standby 集群实践](https://reader033.fdocument.pub/reader033/viewer/2022050702/54986f47b47959794d8b54b5/html5/thumbnails/19.jpg)
. . . . . .
启动 standby
![Page 20: PostgreSQL 9 的 standby 集群实践](https://reader033.fdocument.pub/reader033/viewer/2022050702/54986f47b47959794d8b54b5/html5/thumbnails/20.jpg)
. . . . . .
将 primary 转入正常模式。
![Page 21: PostgreSQL 9 的 standby 集群实践](https://reader033.fdocument.pub/reader033/viewer/2022050702/54986f47b47959794d8b54b5/html5/thumbnails/21.jpg)
. . . . . .
standby 实施
![Page 22: PostgreSQL 9 的 standby 集群实践](https://reader033.fdocument.pub/reader033/viewer/2022050702/54986f47b47959794d8b54b5/html5/thumbnails/22.jpg)
. . . . . .
计划与设计
![Page 23: PostgreSQL 9 的 standby 集群实践](https://reader033.fdocument.pub/reader033/viewer/2022050702/54986f47b47959794d8b54b5/html5/thumbnails/23.jpg)
. . . . . .
采用何种架构?
![Page 24: PostgreSQL 9 的 standby 集群实践](https://reader033.fdocument.pub/reader033/viewer/2022050702/54986f47b47959794d8b54b5/html5/thumbnails/24.jpg)
. . . . . .
读写分离 or Standlone?
![Page 25: PostgreSQL 9 的 standby 集群实践](https://reader033.fdocument.pub/reader033/viewer/2022050702/54986f47b47959794d8b54b5/html5/thumbnails/25.jpg)
. . . . . .
根据同步率和性能要求采用不同的方案。
![Page 26: PostgreSQL 9 的 standby 集群实践](https://reader033.fdocument.pub/reader033/viewer/2022050702/54986f47b47959794d8b54b5/html5/thumbnails/26.jpg)
. . . . . .
文件 or 流?
![Page 27: PostgreSQL 9 的 standby 集群实践](https://reader033.fdocument.pub/reader033/viewer/2022050702/54986f47b47959794d8b54b5/html5/thumbnails/27.jpg)
. . . . . .
希望回溯?
![Page 28: PostgreSQL 9 的 standby 集群实践](https://reader033.fdocument.pub/reader033/viewer/2022050702/54986f47b47959794d8b54b5/html5/thumbnails/28.jpg)
. . . . . .
预备 standby 配置
![Page 29: PostgreSQL 9 的 standby 集群实践](https://reader033.fdocument.pub/reader033/viewer/2022050702/54986f47b47959794d8b54b5/html5/thumbnails/29.jpg)
. . . . . .
编辑 postgresql.conf
![Page 30: PostgreSQL 9 的 standby 集群实践](https://reader033.fdocument.pub/reader033/viewer/2022050702/54986f47b47959794d8b54b5/html5/thumbnails/30.jpg)
. . . . . .
hot standby?
hot standby = on/off
![Page 31: PostgreSQL 9 的 standby 集群实践](https://reader033.fdocument.pub/reader033/viewer/2022050702/54986f47b47959794d8b54b5/html5/thumbnails/31.jpg)
. . . . . .
建立 recovery.conf
![Page 32: PostgreSQL 9 的 standby 集群实践](https://reader033.fdocument.pub/reader033/viewer/2022050702/54986f47b47959794d8b54b5/html5/thumbnails/32.jpg)
. . . . . .
文件同步
standby mode = ’on’ restore command = ’cp $ARCHIVEDIR/%f%p’
![Page 33: PostgreSQL 9 的 standby 集群实践](https://reader033.fdocument.pub/reader033/viewer/2022050702/54986f47b47959794d8b54b5/html5/thumbnails/33.jpg)
. . . . . .
流式同步
standby mode = ’on’ primary conninfo = ’host=primaryport=5432 user=repl’
![Page 34: PostgreSQL 9 的 standby 集群实践](https://reader033.fdocument.pub/reader033/viewer/2022050702/54986f47b47959794d8b54b5/html5/thumbnails/34.jpg)
. . . . . .
预备 primary
![Page 35: PostgreSQL 9 的 standby 集群实践](https://reader033.fdocument.pub/reader033/viewer/2022050702/54986f47b47959794d8b54b5/html5/thumbnails/35.jpg)
. . . . . .
编辑 postgresql.conf
![Page 36: PostgreSQL 9 的 standby 集群实践](https://reader033.fdocument.pub/reader033/viewer/2022050702/54986f47b47959794d8b54b5/html5/thumbnails/36.jpg)
. . . . . .
wal level = archive/hot standby
![Page 37: PostgreSQL 9 的 standby 集群实践](https://reader033.fdocument.pub/reader033/viewer/2022050702/54986f47b47959794d8b54b5/html5/thumbnails/37.jpg)
. . . . . .
基于 archive 模式
archive mode = on
![Page 38: PostgreSQL 9 的 standby 集群实践](https://reader033.fdocument.pub/reader033/viewer/2022050702/54986f47b47959794d8b54b5/html5/thumbnails/38.jpg)
. . . . . .
基于归档文件
archive command = ’test ! -f $ARCHIVEDIR/%f && cp %p$ARCHIVEDIR/%f’
![Page 39: PostgreSQL 9 的 standby 集群实践](https://reader033.fdocument.pub/reader033/viewer/2022050702/54986f47b47959794d8b54b5/html5/thumbnails/39.jpg)
. . . . . .
基于 switch file
archive command = ’test ! -f $PGDATA/backup in progress &&cp %p $ARCHIVEDIR/%f’
![Page 40: PostgreSQL 9 的 standby 集群实践](https://reader033.fdocument.pub/reader033/viewer/2022050702/54986f47b47959794d8b54b5/html5/thumbnails/40.jpg)
. . . . . .
基于流
max wal sender = n (n>0)
![Page 41: PostgreSQL 9 的 standby 集群实践](https://reader033.fdocument.pub/reader033/viewer/2022050702/54986f47b47959794d8b54b5/html5/thumbnails/41.jpg)
. . . . . .
基于流
编辑 pg hba.conf
![Page 42: PostgreSQL 9 的 standby 集群实践](https://reader033.fdocument.pub/reader033/viewer/2022050702/54986f47b47959794d8b54b5/html5/thumbnails/42.jpg)
. . . . . .
基于流
# TYPE DATABASE USER CIDR−ADDRESS METHODhos t r e p l i c a t i o n r e p l 192 . 168 . 1 . 100/32 md5
![Page 43: PostgreSQL 9 的 standby 集群实践](https://reader033.fdocument.pub/reader033/viewer/2022050702/54986f47b47959794d8b54b5/html5/thumbnails/43.jpg)
. . . . . .
进入备份模式
![Page 44: PostgreSQL 9 的 standby 集群实践](https://reader033.fdocument.pub/reader033/viewer/2022050702/54986f47b47959794d8b54b5/html5/thumbnails/44.jpg)
. . . . . .
superuser 登录 primary
![Page 45: PostgreSQL 9 的 standby 集群实践](https://reader033.fdocument.pub/reader033/viewer/2022050702/54986f47b47959794d8b54b5/html5/thumbnails/45.jpg)
. . . . . .
第二参数可以提示尽快完成事务
select pg start backup(”);
![Page 46: PostgreSQL 9 的 standby 集群实践](https://reader033.fdocument.pub/reader033/viewer/2022050702/54986f47b47959794d8b54b5/html5/thumbnails/46.jpg)
. . . . . .
建立 standby 环境
![Page 47: PostgreSQL 9 的 standby 集群实践](https://reader033.fdocument.pub/reader033/viewer/2022050702/54986f47b47959794d8b54b5/html5/thumbnails/47.jpg)
. . . . . .
以 postgres 用户身份
mkdir /standby/path
![Page 48: PostgreSQL 9 的 standby 集群实践](https://reader033.fdocument.pub/reader033/viewer/2022050702/54986f47b47959794d8b54b5/html5/thumbnails/48.jpg)
. . . . . .
chmod -R 700 /standby/path
![Page 49: PostgreSQL 9 的 standby 集群实践](https://reader033.fdocument.pub/reader033/viewer/2022050702/54986f47b47959794d8b54b5/html5/thumbnails/49.jpg)
. . . . . .
tar 或 rsync
将 primary 数据区复制过来
![Page 50: PostgreSQL 9 的 standby 集群实践](https://reader033.fdocument.pub/reader033/viewer/2022050702/54986f47b47959794d8b54b5/html5/thumbnails/50.jpg)
. . . . . .
删除 postmaster.pid
![Page 51: PostgreSQL 9 的 standby 集群实践](https://reader033.fdocument.pub/reader033/viewer/2022050702/54986f47b47959794d8b54b5/html5/thumbnails/51.jpg)
. . . . . .
将预设的 standby 文件,postgresql.conf 和 recovery.conf 放到数据区。
![Page 52: PostgreSQL 9 的 standby 集群实践](https://reader033.fdocument.pub/reader033/viewer/2022050702/54986f47b47959794d8b54b5/html5/thumbnails/52.jpg)
. . . . . .
根据实际情况,调整 pg hba.conf
![Page 53: PostgreSQL 9 的 standby 集群实践](https://reader033.fdocument.pub/reader033/viewer/2022050702/54986f47b47959794d8b54b5/html5/thumbnails/53.jpg)
. . . . . .
启动 standby
![Page 54: PostgreSQL 9 的 standby 集群实践](https://reader033.fdocument.pub/reader033/viewer/2022050702/54986f47b47959794d8b54b5/html5/thumbnails/54.jpg)
. . . . . .
pg ctl -D $PGSTANDPATH start
![Page 55: PostgreSQL 9 的 standby 集群实践](https://reader033.fdocument.pub/reader033/viewer/2022050702/54986f47b47959794d8b54b5/html5/thumbnails/55.jpg)
. . . . . .
primary 转为正常工作模式
![Page 56: PostgreSQL 9 的 standby 集群实践](https://reader033.fdocument.pub/reader033/viewer/2022050702/54986f47b47959794d8b54b5/html5/thumbnails/56.jpg)
. . . . . .
select pg stop backup();
![Page 57: PostgreSQL 9 的 standby 集群实践](https://reader033.fdocument.pub/reader033/viewer/2022050702/54986f47b47959794d8b54b5/html5/thumbnails/57.jpg)
. . . . . .
发散讨论
![Page 58: PostgreSQL 9 的 standby 集群实践](https://reader033.fdocument.pub/reader033/viewer/2022050702/54986f47b47959794d8b54b5/html5/thumbnails/58.jpg)
. . . . . .
可以使用 clean command 清理过期的归档文件。
![Page 59: PostgreSQL 9 的 standby 集群实践](https://reader033.fdocument.pub/reader033/viewer/2022050702/54986f47b47959794d8b54b5/html5/thumbnails/59.jpg)
. . . . . .
利用归档文件,可以建立可回溯的高可靠性备份机制。
![Page 60: PostgreSQL 9 的 standby 集群实践](https://reader033.fdocument.pub/reader033/viewer/2022050702/54986f47b47959794d8b54b5/html5/thumbnails/60.jpg)
. . . . . .
利用 hot standby 机制,可以方便的实现单写多读的负载均衡机制。
![Page 61: PostgreSQL 9 的 standby 集群实践](https://reader033.fdocument.pub/reader033/viewer/2022050702/54986f47b47959794d8b54b5/html5/thumbnails/61.jpg)
. . . . . .
压缩传输可以利用宽裕的 CPU 资源换取有限的带宽。
![Page 62: PostgreSQL 9 的 standby 集群实践](https://reader033.fdocument.pub/reader033/viewer/2022050702/54986f47b47959794d8b54b5/html5/thumbnails/62.jpg)
. . . . . .
刘鑫 @ 金山快盘
再见!