PostgreSQL+pgpool-II复制方案
内容导读
互联网集市收集整理的这篇技术教程文章主要介绍了PostgreSQL+pgpool-II复制方案，小编现在分享给大家，供广大互联网技能从业者学习和参考。文章包含14258字，纯文字阅读大概需要21分钟。
内容图文
-3.6-1.noarch.rpmyum -y install pgpool-II-pg95
yum -y install pgpool-II-pg95-debuginfo
yum -y install pgpool-II-pg95-devel
yum -y install pgpool-II-pg95-extensions
chkconfig pgpool on # 添加开机启动
service start/stop pgpool # 服务启/停
2.2 添加Pgpool-II运行用户
添加Pgpool-II运行用户，并给相关目录的读写权限
[root@im110 pgpool-II]# useradd pgpool
[root@im110 pgpool-II]# passwd pgpool
Changing password for user pgpool.
New password:
Retype new password:
passwd: all authentication tokens updated successfully.
[root@im110 pgpool-II]# chown -R pgpool.pgpool /etc/pgpool-II
[root@im110 pgpool-II]# mkdir -p /var/run/pgpool/
[root@im110 pgpool-II]# chown pgpool.pgpool /var/run/pgpool/
修改启动脚本的pgpool运行用户：
2.3 设置pcp.conf
cp /etc/pcp.conf.sample /etc/pcp.conf
内容格式为如下，一行一个，#号为注释标识
username:[md5 encrypted password]
[md5 encrypted password] 可以使用如下命令生成
$ pg_md5 pgpool
ba777e4c2f15c11ea8ac3be7e0440aa0
使用pg_md5 -p会隐藏输入的密码
$ pg_md5 -p
password: your_password
配置文件pcp.conf必须允许pgpool执行用户可读。
2.4 设置Pgpool-II配置文件
pgpool.conf是Pgpool-II的主配置文件。启动Pgpool-II时可以使用 -f 参数指定 pgpool.conf路径， 默认是使用/etc/pgpool.conf.
Pgpool-II每个模式对应的配置文件模板
复制一份作为你的配置文件：
# cd /etc
# cp pgpool.conf.sample-replication pgpool.conf
2.5 配置backend信息
在pgpool.conf中加入如下格式的配置，其中0为backend主机号，不能重复。
backend_socket_dir = ‘/tmp‘
backend_hostname0 = ‘10.1.0.110‘
backend_port0 = 5432
backend_weight0 = 1
backend_data_directory0 = ‘/var/lib/pgsql/9.5/data/‘
backend_flag0 = ‘ALLOW_TO_FAILOVER‘
2.6 修改认证方式
为了更安全，修改认证方式为md5
2.6.1 修改pgpool-II的认证方式为md5
vim /etc/pgpool-II/pool_hba.conf
# TYPE DATABASE USER CIDR-ADDRESS METHOD
host all all 10.1.0.0/24 md5
# "local" is for Unix domain socket connections only
local all all md5
# IPv4 local connections:
host all all 127.0.0.1/32 md5
host all all ::1/128 md5
2.6.2 修改PostgreSQL的认证方式为md5
vim pg_hba.conf
# TYPE DATABASE USER ADDRESS METHOD
# "local" is for Unix domain socket connections only
# IPv4 local connections:
host all all 127.0.0.1/32 trust
host all all 10.1.0.0/24 md5
# IPv6 local connections:
host all all ::1/128 trust
# Allow replication connections from localhost, by a user with the
# replication privilege.
local all postgres md5
#local replication postgres peer
#host replication postgres 127.0.0.1/32 ident
#host replication postgres ::1/128 ident
[root@im110 pgpool-II]#
2.7 测试pgpool-II同步
这里作简单测试，在pgpool-II入口创建数据库，看各节点是否自动创建，删除后，看各节点是否自动删除。
[root@im109 ~]# psql -U postgres -h 10.1.0.115 -p 9999
Password for user postgres:
psql (9.5.6)
Type "help" for help.
postgres=# show pool_nodes;
node_id | hostname | port | status | lb_weight | role | select_cnt | load_balance_node | replication_delay
---------+------------+-------+--------+-----------+--------+------------+-------------------+-------------------
0 | 10.1.0.110 | 54321 | up | 0.500000 | master | 0 | true | 0
1 | 10.1.0.109 | 54321 | up | 0.500000 | slave | 0 | false | 0
(2 rows)
postgres=#
postgres=# \l
List of databases
Name | Owner | Encoding | Collate | Ctype | Access privileges
-----------+----------+----------+-------------+-------------+-----------------------
postgres | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 |
template0 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/postgres +
| | | | | postgres=CTc/postgres
template1 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/postgres +
| | | | | postgres=CTc/postgres
wiseucmsg | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 |
(4 rows)
postgres=# create database test01;
CREATE DATABASE
postgres=# \q
[root@im109 ~]# psql -U postgres -h 10.1.0.110 -p 54321
Password for user postgres:
psql (9.5.6)
Type "help" for help.
postgres=# \l
List of databases
Name | Owner | Encoding | Collate | Ctype | Access privileges
-----------+----------+----------+-------------+-------------+-----------------------
postgres | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 |
template0 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/postgres +
| | | | | postgres=CTc/postgres
template1 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/postgres +
| | | | | postgres=CTc/postgres
test01 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 |
wiseucmsg | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 |
(5 rows)
postgres=# \q
[root@im109 ~]# psql -U postgres -h 10.1.0.109 -p 54321
Password for user postgres:
psql (9.5.6)
Type "help" for help.
postgres=# \l
List of databases
Name | Owner | Encoding | Collate | Ctype | Access privileges
-----------+----------+----------+-------------+-------------+-----------------------
postgres | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 |
template0 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/postgres +
| | | | | postgres=CTc/postgres
template1 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/postgres +
| | | | | postgres=CTc/postgres
test01 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 |
wiseucmsg | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 |
(5 rows)
postgres=# \q
[root@im109 ~]# psql -U postgres -h 10.1.0.115 -p 9999
Password for user postgres:
psql (9.5.6)
Type "help" for help.
postgres=# \l
List of databases
Name | Owner | Encoding | Collate | Ctype | Access privileges
-----------+----------+----------+-------------+-------------+-----------------------
postgres | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 |
template0 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/postgres +
| | | | | postgres=CTc/postgres
template1 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/postgres +
| | | | | postgres=CTc/postgres
test01 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 |
wiseucmsg | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 |
(5 rows)
postgres=# drop database test01;
DROP DATABASE
postgres=# \l
List of databases
Name | Owner | Encoding | Collate | Ctype | Access privileges
-----------+----------+----------+-------------+-------------+-----------------------
postgres | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 |
template0 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/postgres +
| | | | | postgres=CTc/postgres
template1 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/postgres +
| | | | | postgres=CTc/postgres
wiseucmsg | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 |
(4 rows)
postgres=# \q
[root@im109 ~]# psql -U postgres -h 10.1.0.110 -p 54321
Password for user postgres:
psql (9.5.6)
Type "help" for help.
postgres=# \l
List of databases
Name | Owner | Encoding | Collate | Ctype | Access privileges
-----------+----------+----------+-------------+-------------+-----------------------
postgres | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 |
template0 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/postgres +
| | | | | postgres=CTc/postgres
template1 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/postgres +
| | | | | postgres=CTc/postgres
wiseucmsg | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 |
(4 rows)
postgres=# \q
[root@im109 ~]# psql -U postgres -h 10.1.0.109 -p 54321
Password for user postgres:
psql (9.5.6)
Type "help" for help.
postgres=# \l
List of databases
Name | Owner | Encoding | Collate | Ctype | Access privileges
-----------+----------+----------+-------------+-------------+-----------------------
postgres | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 |
template0 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/postgres +
| | | | | postgres=CTc/postgres
template1 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/postgres +
| | | | | postgres=CTc/postgres
wiseucmsg | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 |
(4 rows)
postgres=# \q
[root@im109 ~]#
测试成功，说明从pgpool-II入口操作，各节点会同步数据。
3. 安装pgpoolAdmin（不推荐）
虽然官网提供此工具，用于页面管理pgpool，但是我觉得不安全，且可能bug较多。以下文中会有一些bug列出来。
安装方法：
3.1 解压pgpoolAdmin至web目录，使用户能访问其php
wget http://www.pgpool.net/download.php?f=pgpoolAdmin-3.5.3.tar.gz -O pgpoolAdmin-3.5.3.tar.gz
cd /var/www
tar -zxvf pgpoolAdmin-3.5.3.tar.gz
ln -s pgpoolAdmin-3.5.3 pgpooladmin
mkdir templates_c
chmod 777 templates_c
chown www conf/pgmgt.conf.php
chmod 644 conf/pgmgt.conf.php
3.2 php-fpm运行用户和pgpool用户统一
pgpoolAdmin使用pcp命令控制pgpool启停，因此需要统一php和pgpool运行用户，以便pgpoolAdmin从页面控制pgpool启停。可参考上文中2.2步骤操作。
3.3 根据向导完成安装
访问安装页面，完成检测，完成安装。
http://yourweb/pgpooladmin/install/
从上图看到，3.5下面的勾处未有文字提示，我此处为通过。因为我装了php-psql扩展。从源码中也可以看出，是检测pgsql扩展。
.pcppass文件格式如下（pgpool3.5以下用到）：
它的作用是用于指定连接postgresql的连接串，包括主机、端口、用户、密码。
host:port:user:password
3.4 一些细节说明
完全设置好的完整功能如下：
显示pgpoolAdmin运行机器的hostname
若php报warning，可在hosts中添加IP对应主机名
[root@im110 pgpoolAdmin-3.5.3]# vim /etc/hosts
10.1.0.110 im110
此处为pgpoolAdmin的登录用户，superuser: yes表示在pgpool数据库中，用户为管理员用户。此数据库可以pgpool下，也可分离到其它postgresql中。若此处非yes，则节点相关操作为不可操作的灰色。
页面中显示节点是否连接，是在pgpool中show pool_nodes命令下的节点status.
[root@im109 ~]# psql -U postgres -h 10.1.0.115 -p 9999
Password for user postgres:
psql (9.5.6)
Type "help" for help.
postgres=# show pool_nodes;
node_id | hostname | port | status | lb_weight | role | select_cnt | load_balance_node | replication_delay
---------+------------+-------+--------+-----------+--------+------------+-------------------+-------------------
0 | 10.1.0.110 | 54321 | up | 0.500000 | master | 0 | true | 0
1 | 10.1.0.109 | 54321 | up | 0.500000 | slave | 0 | false | 0
(2 rows)
其它命令参考官方文档：
http://www.pgpool.net/docs/latest/en/html/reference.html
pgpool.conf 设置中的健康检查，检查postgressql是否启动
此处是通过连接各节点是否成功来判断的，因此需要在各节点创建用于连接的角色。
[root@im110 pgpool-II]# psql -U pgpool -p 54321 -h 10.1.0.110 template1
Password for user pgpool:
psql (9.5.6)
Type "help" for help.
template1=>
3.5 php.ini的disable_functions设置
源代码中php调用的exec执行pcp相关命令，
因此需要将php.ini配置文件中的disable_functions中的exec去掉，以允许php使用该函数。
3.6 pgpoolAdmin中pcp_stop_pgpool参数
在pgpool-II3.6中，pcp_stop_pgpool用法如下：
root@im110 pgpooladmin]# pcp_stop_pgpool --help
pcp_stop_pgpool - terminate pgpool-II
Usage:
pcp_stop_pgpool [OPTION...]
Options:
-U, --username=NAME username for PCP authentication
-h, --host=HOSTNAME pgpool-II host
-
本文系统来源：http://ygqygq2.blog.51cto.com/1009869/1903290
数据库问题热搜
- PHP7.X连接SQLSERVER数据库（CENTOS7）
- mysql"闪回"技术恢复误删除误更改的数据
- 数据库事务
- mongodb07---用户权限
- MySQL ACID及四种隔离级别的解释
- Oracle审计－－AUD$占用空间较大处理方案
- Oracle Linux 6.9下安装并配置多路径multipath
- Activiti数据库
- 单机启动TiDB
- Flask学习笔记（3）-数据库迁移
- oracle时间函数
- CentOS7离线安装TIDB
- 【转】单KEY业务，数据库水平切分架构实践
- 常用SQL语句集合
- sqli-labs-master第二关：Error Based- Intiger
- oracle-PL/SQL2
- 审批流程数据库设计
- EAS开发报错 ：数据库表 或 视图 不存在
- 使用JDBC连接mysql遇到的若干问题及解决办法
- MySQL自动化运维平台建设
- SQLServer基本操作
- MySQL导入数据库--开启/关闭外键约束
- python连接SQL Server数据库
- adb logcat 通过包名过滤日志并输出到txt文件
- VB.net数据库编程pdf
