原 GreenPlum + pgpool 架构缓存查询结果，加速web界面展示，并配置脚本开发自动故障转移高可用功能

2025年3月6日

Tags：原创 GreenPlum Pgpool-II pgpoolAdmin pgpool总结

👉 本文共约9192个字，系统预计阅读时间或需35分钟。

简介

在GreenPlum后端中，若前端有相关的web应用，则可以配置pgpool来缓存相关的结果，可以加速web应用展示。

需要注意几点：

1、在GreenPlum中，standby master是属于不可连接的，所以不能直接使用pgpool来进行高可用的自动切换，可以使用keepalived+脚本的形式来自动切换：

[gpadmin@smdw ~]$ psql
psql: error: FATAL:  the database system is in recovery mode
DETAIL:  last replayed record at 0/C70BE50
- VERSION: PostgreSQL 12.12 (Greenplum Database 7.3.3 build commit:ce20fc237ed7520a2476c96ed7d9edddea136932) on x86_64-pc-linux-gnu, compiled by gcc (GCC) 8.5.0 20210514 (Red Hat 8.5.0-22), 64-bit compiled on Dec 18 2024 05:34:04 Bhuvnesh C.

2、在centos、欧拉、麒麟上都测试，可行！！！

在GreenPlum的mdw节点编译安装pgpool+pgpooladmin

下载：https://www.pgpool.net/mediawiki/index.php/Downloads

https://www.pgpool.net/yum/rpms/4.5/redhat/rhel-8-x86_64/

wget https://www.pgpool.net/mediawiki/download.php?f=pgpool-II-4.5.5.tar.gz
wget https://www.pgpool.net/mediawiki/download.php?f=pgpoolAdmin-4.2.0.tar.gz

安装pgpool

-- 编译安装 root用户
mkdir -p /usr/local/pgpool
source /home/gpadmin/.bashrc
tar -zxvf pgpool-II-*.tar.gz
cd pgpool-II-*/
autoreconf -fi
./configure --prefix=/usr/local/pgpool
make -j8
make install

mkdir -p /etc/pgpool/
chown apache.apache /etc/pgpool/

mkdir -p /var/run/pgpool/
chown apache.apache /var/run/pgpool/

cp /usr/local/pgpool/etc/pgpool.conf.sample  /etc/pgpool/pgpool.conf 

ln -s /etc/pgpool/pgpool.conf /usr/local/etc/pgpool.conf

find /usr/local/pgpool/bin/ -type f -exec bash -c 'ln -s "$1" /usr/local/bin/$(basename "$1")' _ {} \;

cat >> /etc/pgpool/pgpool.conf <<"EOF"

# - pgpool Connection Settings -
listen_addresses = '*'
pcp_listen_addresses = '*'
port=9999

# - Streaming Replication Check
sr_check_user = 'pgpool'
sr_check_password = 'lhr'

# - Health Check
health_check_period = 5
health_check_timeout = 30
health_check_user = 'pgpool'
health_check_password = 'lhr'
health_check_max_retries = 3

# - Backend Connection Settings -
backend_hostname0 = '152.52.52.10'
backend_port0 = 5432
backend_weight0 = 1
backend_data_directory0 = '/winning/opt/greenplum/data/master/gpseg-1'
backend_flag0 = 'DISALLOW_TO_FAILOVER'
backend_application_name0 = 'gp_mdw'

# pool
connection_cache = on
max_pool = 10
num_init_children = 1000

# IN MEMORY QUERY MEMORY CACHE
memory_cache_enabled = on
memqcache_oiddir = '/var/log/pgpool/oiddir'
memqcache_total_size = 2048MB
memqcache_max_num_cache = 9000000
memqcache_maxcache = 4096000
memqcache_cache_block_size = 10485760
relcache_size = 25600

# - Where to log -
log_destination = 'stderr'
logging_collector = on
log_directory = '/var/log/pgpool'
log_filename = 'pgpool-%Y-%m-%d_%H%M%S.log'
log_truncate_on_rotation = on
log_rotation_age = 1d
log_rotation_size = 1024MB

EOF

mkdir -p /var/log/pgpool/
chmod 777 /var/log/pgpool/ -R

create user pgpool password 'lhr' superuser;

# /usr/local/pgpool/bin/pgpool -f /etc/pgpool/pgpool.conf -n -D

cat > /usr/lib/systemd/system/pgpool.service <<"EOF"
[Unit]
Description=pgpool
After=syslog.target network.target

[Service]
User=root
Group=root
EnvironmentFile=-/etc/sysconfig/pgpool
ExecStart=/usr/local/pgpool/bin/pgpool -f /etc/pgpool/pgpool.conf  -n
ExecStop=/usr/local/pgpool/bin/pgpool -f /etc/pgpool/pgpool.conf  -m fast stop
ExecReload=/usr/local/pgpool/bin/pgpool -f /etc/pgpool/pgpool.conf  reload
LimitNOFILE=65536
KillMode=process
KillSignal=SIGINT
Restart=on-abnormal
RestartSec=30s
TimeoutSec=0

[Install]
WantedBy=multi-user.target
EOF

sudo systemctl daemon-reload
sudo systemctl enable pgpool.service
sudo systemctl restart pgpool.service
sudo systemctl status pgpool.service

psql -U gpadmin -h 127.0.0.1 -p 9999 -d postgres -c "show pool_cache;"
psql -U gpadmin -h 127.0.0.1 -p 9999 -d postgres -c "show pool_backend_stats;"

show pool_health_check_stats;
show pool_backend_stats;
show pool_cache;
show pool_processes;
show pool_pools;

for i in $(seq 1 20); do psql -U gpadmin -h 127.0.0.1 -p 9999 -d postgres -c 'SELECT inet_server_addr()'; done | egrep '152.' 

for i in $(seq 1 200); do psql -U gpadmin -h 127.0.0.1 -p 9999 -d postgres -c 'SELECT 1'; done | egrep '152.'

安装pgpooladmin

https://www.pgpool.net/docs/pgpoolAdmin/index_en.html

php推荐php7


mkdir -p /var/www/html/
tar -zxvf pgpoolAdmin-4.2.0.tar.gz -C /var/www/html/
mv /var/www/html/pgpoolAdmin-4.2.0 /var/www/html/admin
sudo chown apache.apache -R /var/www/html/admin/
chmod -R 777 /var/www/html/admin/

systemctl enable httpd.service
systemctl restart httpd.service
systemctl status httpd.service

php -v

ncat --sh-exec "ncat 152.52.52.10 80" -l 810 --keep-open
ncat --sh-exec "ncat 152.52.52.10 3389" -l 3380 --keep-open
http://192.16.7.162:810/admin/install/

echo  "pgpooladmin:3996643de967b80174e48fb45d7227b1" > /etc/pgpool/pcp.conf

ln -s   /etc/pgpool/pcp.conf  /usr/local/pgpool/etc/pcp.conf
ln -s   /etc/pgpool/pcp.conf  /usr/local/etc/pcp.conf

chmod 666  /etc/pgpool/pgpool.conf
chmod 666  /etc/pgpool/pcp.conf

echo  "*:*:*:lhr" >> /usr/share/httpd/.pcppass
echo  "*:*:*:lhr" >> /usr/share/httpd/.pcppass

chown apache:apache /usr/share/httpd/.pcppass
chmod 0600 /usr/share/httpd/.pcppass

-- 更新pool_passwd：cat /etc/pgpool/pool_passwd
-- pg_md5 --md5auth --username=pgpool "lhr"

-- 数据库创建用户
-- ate user pgpooladmin login encrypted password 'lhr' superuser;

-- sudo chown -R apache:apache /var/lib/php/session
-- sudo chmod 700 /var/lib/php/session

# /etc/pgpool/pcp.conf  pgpooladmin界面的登陆密码

 systemctl enable php-fpm.service 
 systemctl restart php-fpm.service 
 systemctl status php-fpm.service 

ln -s /var/www/html/admin/lang/zh_cn.lang.php /var/www/html/admin/lang/.lang.php

欧拉系统安装php7


wget https://www.php.net/distributions/php-7.4.33.tar.gz
tar -xvf php-7.4.33.tar.gz 
cd php-7.4.33

yum -y install cmake libxml2 libxml2-devel openssl openssl-devel curl-devel libjpeg-devel libpng-devel freetype-devel libzip libzip-devel libsodium sqlite sqlite-devel oniguruma oniguruma-devel libwebp-devel 

. /usr/local/greenplum-db/greenplum_path.sh
./configure --prefix=/usr/local/php7 --with-config-file-path=/usr/local/php7/etc --with-config-file-scan-dir=/usr/local/php7/etc/php.d --enable-mysqlnd --with-mysqli --with-pdo-mysql --enable-fpm --with-fpm-user=nginx --with-fpm-group=nginx --enable-gd  --with-iconv --with-zlib --enable-xml --enable-shmop --enable-sysvsem --enable-inline-optimization --enable-mbregex --enable-mbstring --enable-ftp --with-openssl --enable-pcntl --enable-sockets --with-xmlrpc --with-zip --with-jpeg --with-webp --enable-soap --without-pear --with-gettext --enable-session --with-curl --with-freetype --enable-opcache --disable-fileinfo --with-pgsql -with-pdo-pgsql
make -j16 && make install
cp /usr/bin/php /usr/bin/php_bk
cp /usr/sbin/php-fpm /usr/sbin/php-fpm_bk
ln -sf /usr/local/php7/bin/php /usr/bin/php
ln -sf /usr/local/php7/sbin/php-fpm  /usr/sbin/php-fpm
php -v

cp /usr/local/php7/etc/php-fpm.conf.default /usr/local/php7/etc/php-fpm.conf
cp /usr/local/php7/etc/php-fpm.d/www.conf.default /usr/local/php7/etc/php-fpm.d/www.conf

sudo mkdir -p /run/php-fpm
sudo chown apache:apache /run/php-fpm

cat >> /usr/local/php7/etc/php-fpm.d/www.conf <<"EOF"

user = apache
group = apache

listen = /run/php-fpm/www.sock
listen.owner = apache
listen.group = apache
listen.mode = 0660
EOF

cat > /usr/lib/systemd/system/php7-fpm.service <<"EOF"
[Unit]
Description=The PHP 7 FastCGI Process Manager
After=network.target

[Service]
Type=simple
PIDFile=/usr/local/php7/var/run/php-fpm.pid
ExecStart=/usr/local/php7/sbin/php-fpm --nodaemonize --fpm-config /usr/local/php7/etc/php-fpm.conf
ExecReload=/bin/kill -USR2 $MAINPID
ExecStop=/bin/kill -SIGQUIT $MAINPID
PrivateTmp=true

[Install]
WantedBy=multi-user.target

EOF

sudo systemctl daemon-reload
sudo systemctl enable php7-fpm
sudo systemctl restart php7-fpm
sudo systemctl status php7-fpm

php --ini

php -m | grep pgsql

-- 如果是openEuler 22.03 (LTS-SP3)，则需要安装如下匹配的包：
rpm -ivh /soft/pkg/libpng-1.6.37-2.oe2203.x86_64.rpm --force --nodeps
rpm -ivh /soft/pkg/libxml2-devel-2.9.14-13.oe2203sp3.x86_64.rpm --force --nodeps
rpm -ivh /soft/pkg/xz-devel-5.2.5-3.oe2203sp3.x86_64.rpm --force --nodeps
rpm -ivh /soft/pkg/zlib-devel-1.2.11-24.oe2203sp3.x86_64.rpm --force --nodeps

docker使用


docker rm -f lhrpgpool
docker run -d --name lhrpgpool -h lhrpgpool \
  -p 19999:9999 -p 19898:9898 -p 180:80 -p 31389:3389 \
  -v /sys/fs/cgroup:/sys/fs/cgroup \
  --privileged=true lhrbest/lhrpgpool:4.2.2_02 \
  /usr/sbin/init

docker network connect bridge lhrpgpool

docker restart lhrpgpool
docker exec -it lhrpgpool bash

systemctl status pgpool

vi  /postgresql/pgpool/etc/pgpool.conf

backend_hostname0 = '192.92.0.59'
backend_port0 = 5432
backend_weight0 = 1
backend_data_directory0 = '/opt/greenplum/data/master/gpseg-1/'
backend_application_name0 = 'gpdb6'

systemctl restart pgpool
systemctl status pgpool

systemctl enable httpd
systemctl start httpd
systemctl status httpd

http://192.16.7.162:180/admin/login.php

psql -U postgres -h 127.0.0.1 -p 19999 -d sbtest

for i in $(seq 1 20); do psql -U postgres -h 127.0.0.1 -p 19999 -d sbtest -c 'SELECT inet_server_addr()'; done | egrep '192.' 

mkdir -p /var/log/pgpool/oiddir
chown -R pgsql.pgsql /var/log/pgpool/oiddir

内存查询缓存说明

https://www.pgpool.net/docs/latest/en/html/in-memory-query-caching.html

https://www.pgpool.net/docs/latest/en/html/runtime-in-memory-query-cache.html

其他参考：https://my.dbaup.com/pgpoolshiyongzongjie.html

master和standby master自动切换高可用

配置：

1、主备的主机都配置对VIP的免密远程登陆

2、主备的pg_hba.conf文件对VIP需要免密且开放登陆

本人提供Oracle(OCP、OCM)、MySQL(OCP)、PostgreSQL(PGCA、PGCE、PGCM)等数据库的培训和考证业务，私聊QQ646634621或微信dbaup66，谢谢！

后续精彩内容已被站长无情隐藏，请输入验证码解锁本文！

获取验证码：请先关注本站微信公众号，然后回复“验证码”，获取验证码。在微信里搜索“AiDBA”或者“dbaup6”或者微信扫描右侧二维码都可以关注本站微信公众号。

打赏赞(2)

标签：原创 GreenPlum Pgpool-II pgpoolAdmin pgpool总结

小麦苗

学习或考证，均可联系麦老师，请加微信db_bao或QQ646634621

发表回复取消回复

要发表评论，您必须先登录。

1、声明：本网站有部分文章整合或转载自网络，文章著作权归属原作者或原团队；若有侵权，深表歉意，请联系本站站长进行处理，谢谢！！！
2、本站提供数据库的培训和考证业务，培训包含但不限于Oracle、MySQL、PostgreSQL、SQL Server、国产数据库等，考证包括但不限于Oracle OCP、Oracle OCM、MySQL OCP、PGCA、PGCE、PGCM等，私聊QQ646634621或微信dbaup66，非诚勿扰，谢谢！！！
3、麦老师新建了《西安DBA数据架构师》微信群，有志同道合的朋友可以私聊我入群，我们一起聊技术，聊生活，空了可以喝酒约饭。
4、为了方便大家更有针对性的讨论学习数据库，故新建数据库分类群，包括Oracle群、MySQL群、SQL Server群、PG&GP群、国产&信创群。志同道合的朋友可以入群聊技术聊人生，捣乱的勿进，喷子勿进，极端人士勿进，谢谢。
5、愿大家出走半生，归来仍是少年！！！

原 GreenPlum + pgpool 架构缓存查询结果，加速web界面展示，并配置脚本开发自动故障转移高可用功能

简介

在GreenPlum的mdw节点编译安装pgpool+pgpooladmin

安装pgpool

安装pgpooladmin

欧拉系统安装php7

docker使用

内存查询缓存说明

master和standby master自动切换高可用

相关文章

您可能还喜欢...

发表回复取消回复

网站公告

网站寄语

本站其它工具

搜索本网站

标签云☁

网站日历

网站归档

网站分类

2025 年 3 月
一	二	三	四	五	六	日
					1	2
3	4	5	6	7	8	9
10	11	12	13	14	15	16
17	18	19	20	21	22	23
24	25	26	27	28	29	30
31

原 GreenPlum + pgpool 架构缓存查询结果，加速web界面展示，并配置脚本开发自动故障转移高可用功能

简介

在GreenPlum的mdw节点编译安装pgpool+pgpooladmin

安装pgpool

安装pgpooladmin

欧拉系统安装php7

docker使用

内存查询缓存说明

master和standby master自动切换高可用

相关文章

您可能还喜欢...

MSSQL作业JOB介绍

MySQL考证

【视频】小麦苗高可用课程第5节-grid和db的静默安装

发表回复 取消回复

网站公告

网站寄语

本站其它工具

搜索本网站

标签云☁

网站日历

网站归档

网站分类

发表回复取消回复