Postgresql--HA高可用安装配置

1 PostgreSQLPostgreSQL HAHA 方案 目录 PostgreSQL HA 方案 1 1 方案架构图 3 2 环境信息 4 3 资源调整 master standby 5 3 1 主备节点时钟同步 5 3 2 配置 sysctl 5 3 3 limit 资源分配 5 3 4 关闭 SElinux 5 3 5 创建 postgres 用户并设置密码 6 3 6 配置 SSH 免登陆 6 4 postgreSQL 流复制环境安装配置 6 4 1 修改权限 6 4 2 安装依赖包 6 4 3 解压安装 7 4 4 设置环境变量 7 4 5 初始化和启动服务 master 7 4 6 配置流复制用户 7 4 7 PostgreSQL 主配置文件调整 master 8 4 8 监控 SQL 功能 8 2 4 9 访问控制文件 pg hba conf 的配置 8 4 10 标志文件的配置 9 4 11 生成备库实例 standby 9 4 12 修改备库标识文件 9 4 13 修改备库主配置文件 9 4 14 启动备库 9 4 15 检查运行情况 10 4 16 测试 10 5 pgpool II 安装配置 11 5 1 安装 pgpool master standby 11 5 2 安装 pgpool 相关函数 master 11 5 3 配置环境变量 master standby 12 5 4 配置 pcp conf master standby 12 5 5 配置 ifconfig arping 执行权限 master standby 12 5 6 配置 pgpool conf master 12 5 7 配置 pgpool conf standby 16 5 8 failover stream sh 文件内容 master standby 19 5 9 创建运行时目录 master standby 19 5 10 配置 pool hba master 19 5 11 配置 pool hba standby 20 5 12 配置密码文件 master standby 20 5 13 启动 pgpool 20 3 5 14 连接测试 21 1 架构图架构图 1 1 1 1 方案架构图方案架构图 4 1 2 1 2 postgrespostgres 故障切换图故障切换图 5 1 3 1 3 pgpoolpgpool 故障切换图故障切换图 6 1 4 1 4 pgpool postgrespgpool postgres 故障切换图故障切换图 2 安装资源信息安装资源信息 操作系统的安装就不在这里介绍 主机名和 IP 地址如下所示 操作系统 RHEL 6 5 数据库 PostgreSQL 9 4 4 虚拟机两台 主节点 node1 IP 192 168 1 231 备节点 node2 IP 192 168 1 232 7 pgpool pgpool II 3 4 3 VIP 192 168 1 233 虚拟 IP 3 资源调整资源调整 master standby 3 1 3 1 前期准备前期准备 创建安装文件夹创建安装文件夹 mkdir opt soft 用于放置源码安装文件 mkdir opt pgdata postgres 数据库目录 mkdir opt pgsql postgres 安装目录 mkdir opt pgpool pgpool 安装目录 修改文件夹权限修改文件夹权限 chown postgres postgres opt soft chown postgres postgres opt pgsql chown postgres postgres opt pgdata chown postgres postgres opt pgpool 复制以下安装文件到复制以下安装文件到 opt soft 并且配置权限 并且配置权限 pgpool II 3 4 3 tar gz postgresql 9 4 4 tar gz 配置安装文件所属人和所属组为配置安装文件所属人和所属组为 postgres chown R postgres potgres opt soft 把此目录及子目录的所属人和所属组全换成 postgres 3 2 3 2 关闭防火墙关闭防火墙 chkconfig iptables off service iptables stop 8 3 1 3 1 关闭关闭 SElinuxSElinux 修改 SELinux 配置文件 vim etc sysconfig selinux 把 SELINUX enforcing 修改为 SELINUX disabled 3 2 3 2 配置配置 hostshosts 文件文件 在 hosts 文件中增加以下内容 vim etc hosts 192 168 1 231 pgpool node01 192 168 1 232 pgpool node02 3 1 3 1 创建创建 postgrespostgres 用户并设置密码用户并设置密码 安装之前要先检查系统中是否有 postgres 系统账号 cat etc passwd grep postgres 如果没有 postgres 账号 需要新增 postgres 账号 groupadd g 26 postgres useradd d var lib pgsql g postgres u 26 postgres 给 postgres 配置密码 passwd postgres 9 3 2 3 2 主备节点时钟同步主备节点时钟同步 usr sbin ntpdate asia pool ntp org 4 9 4 9 备库实例生成备库实例生成 standby standby 从主库复制实例到备库从主库复制实例到备库 su postgres pg basebackup D PGDATA Fp Xs v P h node1 U replica 修改备库标识文件修改备库标识文件 mv opt pgdata recovery done opt pgdata recovery conf vim opt pgdata recovery conf standby mode on recovery target timeline latest 14 primary conninfo host 192 168 1 231 port 5432 user replica trigger file opt pgdata trigger file 4 10 4 10 启动备库启动备库服务服务 standby standby chown R postgres postgres opt pgdata chmod 0700 opt pgdata pg ctl D PGDATA l PGDATA logfile start 4 11 4 11 检查运行情况检查运行情况 查看备库进程 ps ef grep postgres 查看主库进程 ps ef grep postgres 4 12 4 12 测试测试 On Master psql U postgres postgres create table test id int name varchar 10 postgres select table name from information schema tables where table schema public table name test 15 1 row On Standby postgres select table name from information schema tables where table schema public table name test 1 row 新增的数据已经传输过去 并且 standby 端的会话是只读的 5 pgpool II 安装配置安装配置 5 1 5 1 安装安装 解压和安装解压和安装 master standby su postgres mkdir opt pgpool tar zxvf pgpool II 3 4 3 tar gz cd pgpool II 3 4 3 configure prefix opt pgpool with pgsql opt pgsql with openssl make make install 安装相关函数安装相关函数 master 安装 pgpool regclass pgpool recovery 函数 postgres node1 cd opt soft pgpool II 3 4 3 src sql pgpool regclass postgres node1 pgpool regclass make postgres node1 pgpool regclass make install postgres node1 pgpool regclass psql f pgpool regclass sql template1 postgres node1 pgpool regclass cd pgpool recovery postgres node1 pgpool recovery make postgres node1 pgpool recovery make install postgres node1 pgpool recovery psql f pgpool recovery sql template1 16 安装完以后 opt pgsql share extension 目录下应该有如下文件 pgpool recovery 1 1 sql pgpool recovery control pgpool recovery sql pgpool regclass 1 0 sql pgpool regclass control pgpool regclass sql 备注 备节点不需要创建 pgpool regclass pgpool recovery 查看新增加的函数 postgres node1 psql U postgres template1 template1 df 5 2 5 2 配置环境变量配置环境变量 master standby master standby vim etc profile export PGPOOL opt pgpool export PATH PGPOOL bin PATH 使环境变量立即生效 source etc profile 5 3 5 3 配置配置 ifconfig ifconfig arpingarping 执行权限执行权限 master standby master standby 配置 sudo 权限 vim etc sudoers postgres ALL ALL NOPASSWD sbin ifconfig postgres ALL ALL NOPASSWD sbin ifup postgres ALL ALL NOPASSWD sbin ifdown postgres ALL ALL NOPASSWD sbin arping 17 配置 s 权限 chmod u s sbin ifconfig chmod u s usr sbin arping 5 4 5 4 开启日志开启日志 master standby master standby 在日志 etc rsyslog conf 加入以下行 vim etc rsyslog conf local0 var log pgpool pgpool log 重启 rsyslog 服务 etc init d rsyslog restart pgpool conf 配置以下行 vim opt pgpool etc pgpool conf log destination syslog 5 1 5 1 主库修改配置文件主库修改配置文件 master master 主配置文件主配置文件 pgpool conf 参数调整参数调整 postgres node1 cd opt pgpool etc postgres node1 etc cp pgpool conf sample pgpool conf postgres node1 etc vim pgpool conf 主节点的 pgpool conf 配置文件中的用户名和配置能用 postgres 用户就用 postgres 用户 尽量减少用户 CONNECTIONS pgpool Connection Settings 18 listen addresses port 9999 socket dir opt pgpool pgpool Communication Manager Connection Settings pcp listen addresses pcp port 9898 pcp socket dir opt pgpool Backend Connection Settings backend hostname0 node1 配置数据节点 node1 backend data directory0 opt pgdata backend hostname1 node2 配置数据节点 node2 backend port1 5432 backend weight1 1 backend data directory1 opt pgdata backend flag1 ALLOW TO FAILOVER Authentication enable pool hba on pool passwd pool passwd authentication timeout 60 Where to log log destination syslog What to log log connections on log hostname on log statement on log per node statement off log standby delay none FILE LOCATIONS pid file name opt pgpool pgpool pid logdir var log pgpool CONNECTION POOLING connection cache on reset query list ABORT DISCARD ALL LOAD BALANCING MODE load balance mode on ignore leading white space on white function list black function list nextval setval nextval setval MASTER SLAVE MODE master slave mode on 设置流复制模式 19 master slave sub mode stream 设置流复制模式 Streaming sr check period 5 sr check user replica sr check password replica delay threshold 16000 Special commands follow master command parallel mode off pgpool2 hostname system db hostname localhost system db port 5432 system db dbname pgpool system db schema pgpool catalog system db user pgpool system db password HEALTH CHECK health check period 5 health check timeout 20 health check user replica health check password replica health check max retries 3 health check retry delay 1 FAILOVER AND FAILBACK failover command opt pgpool failover stream sh d H opt pgdata trigger file WATCHDOG use watchdog on Watchdog communication Settings wd hostname node1 Virtual IP control Setting delegate IP 192 168 1 233 代理的 IP 地址 Lifecheck Setting wd interval 6 heartbeat mode heartbeat destination0 node2 配置对端的 hostname heartbeat device0 eth0 Other pgpool Connection Settings other pgpool hostname0 node2 配置对端的地址 other pgpool port0 9999 20 other wd port0 9000 配置配置 pcp conf 生成 md5 pg md5 u postgres p password postgres e8a48653851e28c69d0506508fb27fc5 配置 pcp conf cd opt pgpool etc cp pcp conf sample pcp conf vim pcp conf 编写 pcp conf 文件 写入以下内容 USERID MD5PASSWD postgres e8a48653851e28c69d0506508fb27fc5 配置配置 pool hba postgres node1 cd opt pgpool etc postgres node1 etc cp pool hba conf sample pool hba conf postgres node1 etc vim pool hba conf host all all 192 168 1 232 24 trust host all all 192 168 1 231 24 trust 配置配置 failover 文件内容文件内容 touch opt pgpool failover stream sh chmod 775 opt pgpool failover stream sh vim opt pgpool failover stream sh bin sh Failover command for streaming replication This script assumes that DB node 0 is primary and 1 is standby If standby goes down do nothing If primary goes down create a trigger file so that standby takes over primary node Arguments 1 failed node id 2 new master hostname 3 path to trigger file failed node 1 new master 2 trigger file 3 21 Do nothing if standby goes down if failed node 1 then exit 0 fi usr bin ssh T new master bin touch trigger file exit 0 创建运行时目录创建运行时目录 跟据主配置文件配置内容创建跟据主配置文件配置内容创建 mkdir var log pgpool oiddir chown R postgres postgres var log pgpool su postgres 5 2 5 2 备库修改配置文件备库修改配置文件 standby standby 主配置文件主配置文件 pgpool conf 参数调整参数调整 postgres node2 cd opt pgpool etc postgres node2 etc cp pgpool conf sample pgpool conf postgres node2 etc vim pgpool conf CONNECTIONS listen addresses socket dir opt pgpool pgpool Communication Manager Connection Settings pcp socket dir opt pgpool Backend Connection Settings backend hostname0 node1 backend hostname1 node2 backend port1 5432 backend weight1 1 backend flag1 ALLOW TO FAILOVER Authentication enable pool hba on LOGS What to log log destination syslog print timestamp on log connections on log hostname on log statement on log per node statement off log standby delay none 22 FILE LOCATIONS pid file name opt pgpool pgpool pid logdir var log pgpool LOAD BALANCING MODE load balance mode on MASTER SLAVE MODE master slave mode on master slave sub mode stream Streaming sr check period 0 sr check user replica sr check password replica delay threshold 16000 Special commands follow master command FAILOVER AND FAILBACK failover command opt pgpool failover stream sh d H opt pgdata trigger file Enabling use watchdog on Watchdog communication Settings wd hostname node2 wd port 9000 wd authkey Virtual IP control Setting delegate IP 192 168 1 233 虚拟 IP 必须同网段的空闲 IP 用来故障切 换 heartbeat mode heartbeat destination0 node1 heartbeat device0 eth0 Other pgpool Connection Settings other pgpool hostname0 node1 other pgpool port0 9999 other wd port0 9000 配置配置 pcp conf 生成 md5 pg md5 u postgres p password postgres e8a48653851e28c69d0506508fb27fc5 23 配置 pcp conf cd opt pgpool etc cp pcp conf sample pcp conf vim pcp conf 编写 pcp conf 文件 写入以下内容 USERID MD5PASSWD postgres e8a48653851e28c69d0506508fb27fc5 配置配置 pool hba standby postgres node1 cd opt pgpool etc postgres node1 etc cp pool hba conf sample pool hba conf postgres node1 etc vim pool hba conf host all all 192 168 1 232 32 trust host all all 192 168 1 231 32 trust 配置配置 failover 文件内容 文件内容 standby touch opt pgpool failover stream sh chmod 775 opt pgpool failover stream sh bin sh Failover command for streaming replication This script assumes that DB node 0 is primary and 1 is standby If standby goes down do nothing If primary goes down create a trigger file so that standby takes over primary node Arguments 1 failed node id 2 new master hostname 3 path to trigger file failed node 1 new master 2 trigger file 3 Do nothing if standby goes down if failed node 1 then exit 0 fi usr bin ssh T new master bin touch trigger file exit 0 创建运行时目录创建运行时目录 根据主配置文件配置内容创建根据主配置文件配置内容创建 mkdir var log pgpool oiddir chown R postgres postgres var log pgpool 24 5 3 5 3 启动启动 pgpoolpgpool master standby master standby 建议使用 root 账号启动 pgpool 用普通用户启用虚拟 IP 无法漂移 root node1 pgpool n 关于 status 状态 如下 0 该状态仅仅用于初始化 PCP 从不显示它 1 节点已启动 还没有连接 2 节点已启动 连接被缓冲 3 节点已关闭 查看虚拟查看虚拟 IP On Master 26 6 Failover 测试测试 6 1 6 1 关闭主库关闭主库 postgrespostgres 进程测试进程测试 使用使用 pg ctl D PGDATA m fast stop 停止主库服务停止主库服务 6 2 6 2 关闭主库关闭主库 pgpoolpgpool 进程进程 Node1 关闭数据库 pg93 db2 etc pg ctl stop m fast waiting for server to shut down done server stopped node2 上测试 pg93 db1 etc pg controldata grep cluster Database cluster state in production 27