postgreSQL+pgpool+pg_rman高可用实施手册.docx_plsql格式化

postgreSQL

pgpool

需积分: 48 33 浏览量更新于2023-03-03 评论 2 收藏 742KB DOCX 举报

身份认证购VIP最低享 7 折!

领优惠券(最高得80元）

资源详情

资源评论

资源推荐



主办部门：

执笔人：

审核人：

 高可用实施手册

版本号 

 年  月  日



目  录
第一章 方案介绍...........................................................................................................1
方案综述....................................................................................................................1
postgreSQL 流复制方案............................................................................................1
1. 两种同步模式.........................................................................................................1
2. 同步和异步.............................................................................................................1
pgpool-II 高可用方案................................................................................................1
1. 连接池.....................................................................................................................1
2. 复制.........................................................................................................................2
3. 负载均衡/读写分离................................................................................................2
4. 限制超过限度的连接.............................................................................................2
备份............................................................................................................................2
第二章 实践环境...........................................................................................................3
软件环境....................................................................................................................3
环境示意图................................................................................................................3
示例环境....................................................................................................................4
第三章 客户环境准备...................................................................................................5
下载文件到特定目录................................................................................................5
1. postgresql.................................................................................................................5
2. pgpool......................................................................................................................5
3. pg_rman...................................................................................................................5
创建 postgres 用户并设置密码.................................................................................5
配置安装文件夹........................................................................................................6
修改文件夹权限........................................................................................................6
第四章 资源调整...........................................................................................................7
关闭防火墙................................................................................................................7
关闭 selinux................................................................................................................7
网络配置....................................................................................................................7
配置 hosts 文件..........................................................................................................8
主备节点时钟同步....................................................................................................8
配置 sysctl..................................................................................................................8
limit 资源分配............................................................................................................9
第五章 PostgreSQL 安装（master、standby）.........................................................10
安装依赖包..............................................................................................................10
解压和安装..............................................................................................................10
设置环境变量..........................................................................................................10
初始化数据库..........................................................................................................10
第六章 配置 PostgreSQL 流复制................................................................................11
配置 SSH 免登陆（master、standby）..................................................................11
启动主库实例(master).............................................................................................11
1. 主配置文件 postgresql.conf 参数调整(master)....................................................11
2. 访问控制文件 pg_hba.conf 配置(master)............................................................12
3. 修改 standby 标志(master)....................................................................................12

3.1. 标志文件 recovery.done(12.X 以前的版本).....................................................12
3.2. 主配置文件 postgresql.conf(12.X 以后的版本)...............................................12
4. 启动主库服务(master)..........................................................................................13
5. 配置流复制用户(master)......................................................................................13
配置备库实例(standby)...........................................................................................13
1. 从主库复制实例到备库(standby)........................................................................13
2. 修改备库标识文件(12.X 以前版本)....................................................................13
3. 修改备库标识及配置(12.X 以后版本)................................................................14
4. 启动备库服务(standby)........................................................................................14
关于主从库配置的说明..........................................................................................14
检查准备运行情况..................................................................................................15
1. 查看进程...............................................................................................................15
2. 测试.......................................................................................................................15
主从库切换..............................................................................................................16
第七章 pgpool 环境.....................................................................................................17
命令执行权限(master/standby)...............................................................................17
开启日志(master/standby).......................................................................................17
配置工作目录(master/standby)...............................................................................17
第八章 Pgpool-ll 安装.................................................................................................18
解压和安装(master/standby)...................................................................................18
安装相关函数(master).............................................................................................18
配置环境变量(master/standby)...............................................................................19
第九章 配置 Pgpool-ll.................................................................................................20
主库修改配置文件(master).....................................................................................20
1. 主配置文件 pgpool.conf 参数调整......................................................................20
1.1. 3.X 版本示例.....................................................................................................20
1.2. 4.X 版本示例.....................................................................................................33
2. 配置 pcp.conf........................................................................................................53
3. 创建 pool_passwd 文件.........................................................................................53
4. 配置 pool_hba.......................................................................................................54
5. 配置 failover 文件内容.........................................................................................54
6. 创建运行时目录...................................................................................................54
备库修改配置文件(standby)...................................................................................54
1. 主配置文件 pgpool.conf 参数调整......................................................................54
2. 其他配置文件.......................................................................................................55
启动 pgpool(master/standby)...................................................................................55
连接测试..................................................................................................................55
1. 检查 pgpool 启动进程..........................................................................................55
2. 通过 pgpool 访问数据库......................................................................................56
3. 查看 pgpool 集群中 nodes 的状态.......................................................................56
4. 查看虚拟 IP...........................................................................................................57
错误..........................................................................................................................57
1. 问题排查方法.......................................................................................................57
2. 问题分类...............................................................................................................57
2

5.2.1. no pg_hba.conf entry for host.............................................................................58

5.2.2. all backend nodes are down................................................................................58

第十章 Failover 测试...................................................................................................60

1. 关闭主库 postgres 进程测试...................................................................................60

2. 关闭主库 pgpool 进程.............................................................................................60

2.1. 停止 pgpool 节点 1 的进程...................................................................................61

2.2. 检查虚拟 IP 已经漂移到节点 2 服务器..............................................................61

第十一章使用 pg_rman 做数据备份.........................................................................63

1. 创建工作目录..........................................................................................................63

2. 数据库中配置归档模式..........................................................................................63

2.1. 配置及检测...........................................................................................................63

2.2. command 使用逻辑运算符..................................................................................64

2.3. 手动切换日志.......................................................................................................64

3. 安装 pg_rman..........................................................................................................64

3.1. 下载.......................................................................................................................64

3.1.1. 源码安装............................................................................................................64

3.1.2. 关于版本问题....................................................................................................64

3.2. 配置环境变量......................................................................................................66

3.3. 初始化备份目录...................................................................................................67

3.4. 备份测试...............................................................................................................67

4. 备份脚本..................................................................................................................68

4.1. pg_rman_full.sh.....................................................................................................68

4.2. pg_rman_archive.sh..............................................................................................69

第十二章参考.............................................................................................................71

附件：配置安装环境脚本..........................................................................................72

附件：安装 postgreSQL 和 pg_rman 的脚本.............................................................74

附件：RHEL 安装环境获取脚本...............................................................................80

剩余63页未读，继续阅读

qq_19833861

粉丝: 0
资源: 5

会员权益专享

postgreSQL+pgpool+pg_rman高可用实施手册.docx

评论0

会员权益专享

最新资源

postgreSQL+pgpool+pg_rman高可用实施手册.docx

评论0

PostgreSQL的高可用方案(文档)

postgresql高可用对比

postgresql高可用

如何在pgsql的docker容器内使用pg_rman恢复数据库

pg_basebackup与pg_rman使用对比

pg_basebackup与pg_rman恢复数据库时是否需要停止postgresql服务

修改pg_hba.conf与postgresql.conf哪些配置，连接数据库客户端，生产环境

修改pg_hba.conf与postgresql.conf

如何在正运行的postgres的dockers容器使用pg_rman恢复数据库

修改pg_hba.conf与postgresql.conf哪些配置，连接数据库客户端，测试环境

修改pg_hba.conf与postgresql.conf哪些配置，连接数据库客户端

pgsql 查看表创建时间

postgresql服务中有多个数据库 如何使用pg_rman恢复其中一个数据库到指定时间点 其他数据库不变

pg_dump: server version: 14.2; pg_dump version: 9.2.24 pg_dump: aborting because of server version mismatch

通过sql语句获取pg数据库创建的函数详情

postgresql12.7 如何安装pg_stat_statements 扩展

会员权益专享

最新资源

postgresql服务中有多个数据库如何使用pg_rman恢复其中一个数据库到指定时间点其他数据库不变