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

[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  (Red Hat 8.5.0-22), -bit compiled on Dec   :: Bhuvnesh C.

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

-- 编译安装 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



createuser 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=
KillMode=process
KillSignal=SIGINT
Restart=on-abnormal
RestartSec=s
TimeoutSec=

[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  -d postgres -c "show pool_cache;"
psql -U gpadmin -h 127.0.0.1 -p  -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  -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  -d postgres -c 'SELECT 1'; done | egrep '152.'

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   /etc/pgpool/pgpool.conf
chmod   /etc/pgpool/pcp.conf



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

chown apache:apache /usr/share/httpd/.pcppass
chmod  /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

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 = 
EOF



cat > /usr/lib/systemd/system/php7-fpm.service <<"EOF"
[Unit]
Description=The PHP  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 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:/admin/login.php


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


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





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

mkdir -p /home/gpadmin/gpfailover/log/


## 本shell做gp切换
cat > /home/gpadmin/gpfailover/gpfailover.sh <<"EOF"
#!/bin/bash
source /home/gpadmin/.bashrc

base_dir=/home/gpadmin/gpfailover
MIP=$(cat $base_dir/master.host)
MIP=$(echo "$MIP" | xargs)
SMIP=$(cat $base_dir/smaster.host)
SMIP=$(echo "$SMIP" | xargs)

LOGFILE="$base_dir/log/gp_checkup_$(date +\%Y\%m\%d).log"
gpfailover_LOGFILE="$base_dir/log/gp_gpfailover_$(date +\%Y\%m\%d).log"
gp_restart="$base_dir/log/gp_restart.log"
# 如果日志文件不存在，则创建并初始化为0
if [ ! -f "$gp_restart" ]; then
    echo 0 > "$gp_restart"
fi


# 主备数据库同步时延，单位为秒，这里设置延迟5分钟
allowed_delay_time=300

# 切换命令只能在备库运行，且只能当主库宕掉时才可以
SWITCH_COMMAND="echo y | gpactivatestandby -d $MASTER_DATA_DIRECTORY"



# 获取文件中存储的时间（假设文件中只有一行时间）
standby_reply_time=$(cat $base_dir/log/standby_reply_time.log)
standby_reply_time="${standby_reply_time:-0}"


##### 先尝试做重启恢复操作
#ps -ef | grep -v grep | grep -q -e 'gpstop' -e 'gpstart' || gpstop -M fast -ar
# ps -ef | grep -v grep | grep -q -e 'gpstop' -e 'gpstart' || gpstart -a

# 读取日志文件中的当前值
RESTART_COUNT=$(ssh "$MIP" "cat '$gp_restart'")

# 如果重启次数已大于2，则退出
if [ "$RESTART_COUNT" -gt 2 ] || [  -z "$RESTART_COUNT" ]; then
    echo "重启次数超过限制 (2次)，不再执行 重启GP动作 "  >> $LOGFILE
    ##### 如果时间差小于 5 分钟（300 秒），且主库已经重启过2次了，则 运行切换
    if [ $standby_reply_time -lt $allowed_delay_time ] ; then
       if [ $SMIP == $HOSTNAME ]; then
           echo -e `date +"%F %T"` "===============>主库已宕机，开始做主备切换，详细切换日志请参考：     /home/gpadmin/gpAdminLogs/gpactivatestandby_$(date +\%Y\%m\%d).log 或 $gpfailover_LOGFILE !!!" >> $LOGFILE
           echo -e `date +"%F %T"` "$SWITCH_COMMAND"
           ssh $MIP rm -rf /tmp/.s.PGSQL.5432*
           bash -c  "$SWITCH_COMMAND" >> $gpfailover_LOGFILE
           if [ $? -eq 0 ] ;   then
               ssh $MIP rm -rf /tmp/.s.PGSQL.5432*
               ssh $MIP sudo ip addr del 192.72.6.60/32 dev eth0
               sudo ip addr add 192.72.6.60/32 dev eth0 label eth0:1
           fi
       else
         echo "当前主机为主库，不做切换，请前往备库做切换！！！" >> $LOGFILE
       fi
    else
       echo "备库应用延迟超过5分钟，请手工切换！！！" >> $LOGFILE
    fi

#elif [[ $MIP == $HOSTNAME ]]; then
else 
# 检查是否存在 gpstop 或 gpstart 进程，如果不存在则执行 gpstart -a
  ps -ef | grep -v grep | grep -q -e 'gpstop' -e 'gpstart' || {
      echo "未检测到 gpstop 或 gpstart，执行 重启GP动作 "  >> $LOGFILE
      ssh $MIP gpstop -M fast -ar
      ssh $MIP gpstart -a
      # 重启次数加1
      RESTART_COUNT=$((RESTART_COUNT + 1))
      echo "$RESTART_COUNT" > "$gp_restart"
  }
fi














EOF


chmod +x /home/gpadmin/gpfailover/gpfailover.sh



## 本脚本做gp状态检查
cat > /home/gpadmin/gpfailover/check_gp_mdw.sh <<"EOF"
#!/bin/bash

source /home/gpadmin/.bashrc


export PGPORT=5432
export PGVIP=192.72.6.60
export PGPASSWORD='lhr'

base_dir=/home/gpadmin/gpfailover
LOGFILE="$base_dir/log/gp_checkup_$(date +\%Y\%m\%d).log"
DOWNFILE="$base_dir/log/gp_downtime.log"
CKPTLOGFILE=$base_dir/log/standby_reply_time.log
MDW_HOST=$base_dir/master.host
SMDW_HOST=$base_dir/smaster.host
MIP=$(cat $MDW_HOST)
MIP=$(echo "$MIP" | xargs)
SMIP=$(cat $SMDW_HOST)
SMIP=$(echo "$SMIP" | xargs)
touch $MDW_HOST
touch $SMDW_HOST

gp_restart="$base_dir/log/gp_restart.log"


ping -c 1 $MIP &> /dev/null
if [ $? -ne 0 ]; then
  echo -e `date +"%F %T"` "Error: $MIP不通！！！" >> $LOGFILE
fi
ping -c 1 $SMIP &> /dev/null
if [ $? -ne 0 ]; then
  echo -e `date +"%F %T"` "Error: $SMIP不通！！！" >> $LOGFILE
fi

######### 1、判断端口是否存在
pg_port_status=$(ssh $PGVIP "netstat -tuln | grep :$PGPORT  | wc -l")
pg_port_status="${pg_port_status:-0}"
#echo $pg_port_status

if [ $pg_port_status -lt 1 ];then
   # 尝试ping PGVIP
   ping -c 1 $PGVIP &> /dev/null
   # 如果ping不通，执行a.sh
   if [ $? -ne 0 ]; then
     echo -e `date +"%F %T"` "Error: VIP $PGVIP不通！！！" >> $LOGFILE
     ssh $MIP sudo ip addr add 192.72.6.60/32 dev eth0 label eth0:1
   else
     echo -e `date +"%F %T"` "Error: $PGVIP的端口$PGPORT未启用！！！" >> $LOGFILE

   fi

   echo -e `date +"%F %T"`  >> $DOWNFILE

fi


######### 2、更新 standby_reply_time 文件
smdw_output=$(ssh $PGVIP "gpstate -f" 2>&1)    
if echo "$smdw_output" | grep -q "No entries found."; then
  ping -c 1 $SMIP &> /dev/null
  if [ $? -eq 0 ]; then
     gpinitstandby -n
  fi
 echo -e `date +"%F %T"` "注意：本套GP无备库，请添加备库：gpinitstandby -a -s $SMIP -S $MASTER_DATA_DIRECTORY  ！！！" >> $LOGFILE
fi

psql_version=$(psql --version | awk '{print $3}')
target_version="12.12"
psql -h $PGVIP -p $PGPORT -t -c "select;"
if [ $? -eq 0 ] ;   then
  if  [[ "$(printf '%s\n' "$target_version" "$psql_version" | sort -V | head -n 1)" == "$target_version" ]];   then
      psql -h $PGVIP -p $PGPORT -t -c "SELECTEXTRACT(EPOCH FROM (now() - reply_time)) AS seconds FROM pg_stat_replication;" > $CKPTLOGFILE
      if [ $? -eq 0 ] && [$(grep -v '^$' $CKPTLOGFILE | wc -l) -ne 0 ] ; then  
         echo -e `date +"%F %T"` '备库正常，更新standby_reply_time.log成功!' >> $LOGFILE
      fi
#  exit 0
  fi
  echo '' >  $DOWNFILE

else
   echo -e `date +"%F %T"` 'Error: 不能登录GP，请检查GP是否正常运行！！！' >> $LOGFILE
   echo -e `date +"%F %T"`  >> $DOWNFILE
#  exit 1
fi


######### 3、更新主节点和备节点主机名
result=$(psql -h $PGVIP -p $PGPORT -t -c "select hostname from gp_segment_configuration wherecontent=-1androle='p';")
# 判断查询结果是否为空
if [ -n "$result" ]; then
    # 如果查询结果不为空，执行 a.sh
    psql -h $PGVIP -p $PGPORT -t -c "select hostname from gp_segment_configuration wherecontent=-1androle='p';" > $MDW_HOST
fi

result=$(psql -h $PGVIP -p $PGPORT -t -c "select hostname from gp_segment_configuration wherecontent=-1androle='m';")
# 判断查询结果是否为空
if [ -n "$result" ]; then
    # 如果查询结果不为空，执行 a.sh
    psql -h $PGVIP -p $PGPORT -t -c "select hostname from gp_segment_configuration wherecontent=-1androle='m';" > $SMDW_HOST
fi


# 去掉空行并计算 $SMDW_HOST 文件的行数
SMDW_LINES=$(grep -v '^$' "$SMDW_HOST" | wc -l)

# 如果 $SMDW_HOST 文件为空（行数为 0）
if [ "$SMDW_LINES" -eq 0 ] || [ "$SMIP" = "$MIP" ] ; then

    # 根据规则更新 $SMDW_HOST 文件的内容
    if [ "$MIP" == "mdw" ]; then
        echo "smdw" > "$SMDW_HOST"
    elif [ "$MIP" == "smdw" ]; then
        echo "mdw" > "$SMDW_HOST"
    fi
fi



######### 4、判断是否需要做主备切换
MIP=$(cat $MDW_HOST)
MIP=$(echo "$MIP" | xargs)
SMIP=$(cat $SMDW_HOST)
SMIP=$(echo "$SMIP" | xargs)

first_non_empty_line=$(grep -m 1 -v '^$' "$DOWNFILE")
log_time=$(echo "$first_non_empty_line" | awk '{print $1, $2}')
log_timestamp=$(date -d "$log_time" +%s)
current_timestamp=$(date +%s)
time_diff=$((current_timestamp - log_timestamp))

# 判断数据库宕机大于30秒
if [ $time_diff -gt 30  ] && [ -n "$first_non_empty_line" ] ; then
   echo -e `date +"%F %T"` "本次检查结果：主库宕机已经$time_diff秒了，开始调用gpfailover.sh做切换！！！" >> $LOGFILE
   ssh $SMIP sh $base_dir/gpfailover.sh >> $LOGFILE
elif [ -n "$first_non_empty_line" ]  ; then
   echo -e `date +"%F %T"` "本次检查结果：主库异常已经$time_diff秒了！！！" >> $LOGFILE
   echo 0 > "$gp_restart"
else
   echo -e `date +"%F %T"` "本次检查结果：主库正常，无需切换！" >> $LOGFILE
   echo 0 > "$gp_restart"
fi



######### 5、判断主备是否2个VIP，做脑裂判断


# 使用gpssh命令在所有主机上执行ifconfig并过滤出指定IP
output=$(gpssh -f /home/gpadmin/conf/all_hosts "ifconfig | grep $PGVIP" | grep $PGVIP)

# 计算输出的行数
line_count=$(echo "$output" | wc -l)

# 如果输出的行数大于1，则执行ssh命令删除IP
if [ "$line_count" -gt 1 ]; then
    ssh $SMIP "sudo ip addr del $PGVIP/32 dev eth0"
fi


EOF

chmod +x /home/gpadmin/gpfailover/check_gp_mdw.sh




## 该脚本可以不需要
cat > /home/gpadmin/gpfailover/run_every_10_seconds.sh <<"EOF"
#!/bin/bash
while true; do
    # 在此处执行你想要的命令
    sh /home/gpadmin/gpfailover/check_gp_mdw.sh 

    # 每10秒执行一次
    sleep

done


EOF

# nohup  sh /home/gpadmin/gpfailover/run_every_10_seconds.sh & 

# echo "nohup  sh /home/gpadmin/gpfailover/run_every_10_seconds.sh & " >> /etc/rc.local


# 每10秒运行1次
cat > /tmp/gp_cron.txt <<"EOF"
* * * * * /home/gpadmin/gpfailover/check_gp_mdw.sh
* * * * * sleep; /home/gpadmin/gpfailover/check_gp_mdw.sh
* * * * * sleep 20; /home/gpadmin/gpfailover/check_gp_mdw.sh
* * * * * sleep 30; /home/gpadmin/gpfailover/check_gp_mdw.sh
* * * * * sleep 40; /home/gpadmin/gpfailover/check_gp_mdw.sh
* * * * * sleep 50; /home/gpadmin/gpfailover/check_gp_mdw.sh
EOF

(crontab -l; cat /tmp/gp_cron.txt) | crontab -


# 主库添加VIP
 sudo ip addr add 192.72.6.60/32 dev eth0 label eth0:1

# 删除VIP
 sudo ip addr del 192.72.6.60/32 dev eth0


 sudo systemctl restart crond 
 sudo systemctl status crond

-- 高可用测试
 pkill -9 postgres  #会自动启动GP恢复
 docker stop mdw
 重启mdw和smdw
 脑裂

-- 添加standby
rm -rf /opt/greenplum/data/master/gpseg-1
gpinitstandby -a -s smdw -S /opt/greenplum/data/master/gpseg-1

https://github.com/pgpool/pgpool2_exporter

https://github.com/pgpool/pgpool2_exporter/releases/download/v1.2.2/pgpool2_exporter-1.2.2.linux-386.tar.gz



export DATA_SOURCE_NAME="postgres://prometheus:lhr@192.16.7.163:9999/postgres?sslmode=disable"
nohup /usr/local/pgpool/pgpool2_exporter --web.listen-address=":9719" >/dev/null 2>&1 &

export DATA_SOURCE_NAME="postgres://prometheus:lhr@192.16.7.163:9979/postgres?sslmode=disable"
nohup /usr/local/pgpool/pgpool2_exporter --web.listen-address=":9720" >/dev/null 2>&1 &



  - job_name: 'pgpool_gpdb6'
    static_configs:
    - targets: ['localhost:9719']
      labels:
        instance: 192.16.7.163:5432
        node_ip: 192.16.7.163:5432
        hostname: pgpool_gpdb6


  - job_name: 'pgpool_gpdb7'
    static_configs:
    - targets: ['localhost:9720']
      labels:
        instance: 192.16.7.163:5437
        node_ip: 192.16.7.163:5437
        hostname: pgpool_gpdb7