postgresql主从部署、pgsql中间件

目录

• 一、安装postgresql准备工作
  • 1.配置节点间postgres普通用户免密登录
  • 2.执行初始化脚本
• 二、主从部署
  • 1.master执行
  • 2.slave执行
• 三、查看主从数据库状态
  • 1.master执行
  • 2.slave执行
• 四、配置pgpool中间件
  • 1.编译安装pgpool
  • 2.配置准备
  • 3.启动服务
  • 4.查看状态
• 五、pgsql定时备份和监控shell脚本
  • 1.pgsql定时全备脚本
  • 2.服务监控脚本
• 六、pgbouncer代理中间件
  • 1.pgbouncer介绍
  • 2.编译安装pgbouncer
  • 3.准备配置文件
  • 4.启动服务
  • 5.查询服务状态

源码编译安装postgresql、pgpool、pgbouncer
环境：
centos 7.9
postgresql：15.6
pgpool：pgpool-II-4.5.0
pgbouncer：1.20.1
节点：
master：10.0.0.192
slave：10.0.0.193
pgpool：10.0.0.194
注意：pg10版本后，认证方式默认从md5升级到SCRAM-SHA-256,下面方式为安装pgpool统一认证方式为md5（scram-sha-256认证有些问题没有调试成功）

一、安装postgresql准备工作

1.配置节点间postgres普通用户免密登录

master执行：

ssh-keygen -t rsa
ssh-copy-id postgres@pgsql-02
ssh-copy-id postgres@pgsql-01

2.执行初始化脚本

master执行：
slave执行：

#!/bin/bash
cd /usr/local
wget http://ftp.postgresql.org/pub/source/v15.6/postgresql-15.6.tar.gz
sleep 3 
yum -y install perl-ExtUtils-Embed readline-devel zlib-devel pam-devel libxml2-devel libxslt-devel openldap-devel python-devel  gcc-c++ openssl-devel cmake
sleep 3
tar xvf postgresql-15.6.tar.gz
mv postgresql-15.6 /usr/local/postgresql
sleep 3
cd /usr/local/postgresql/
./configure --prefix=/usr/local/pgsql
sleep 2
make && make install
sleep 3
groupadd postgres
useradd -g postgres postgres
id postgres
cd /usr/local
mkdir pgdata
chown -R postgres.postgres pgsql
chown -R postgres.postgres pgdata
chmod 700 pgdata

cat >> /etc/profile << EOF
export PGHOME=/usr/local/pgsql
export PGDATA=/usr/local/pgdata
PATH=$HOME/bin:$PGHOME/bin:$PATH
export PATH
EOF

cat >> /home/postgres/.bash_profile << EOF
export PGHOME=/usr/local/pgsql
export PGDATA=/usr/local/pgdata
PATH=$HOME/bin:$PGHOME/bin:$PATH
export PATH
EOF

source /home/postgres/.bash_profile
source /etc/profile

二、主从部署

1.master执行

#1.初始化数据
/usr/local/pgsql/bin/initdb -D  --encoding=UTF8 --lc-collate=en_US.UTF-8 --lc-ctype=en_US.UTF-8

#2.创建归档日志目录
mkdir  /usr/local/pgdata/archive

#3.设置数据库访问权限及白名单,密码认证为md5或trust无需密码认证
echo "host    replication     all             10.0.0.194/22           trust" >>  /usr/local/pgdata/pg_hba.conf
echo "host    all     all             10.0.0.192/22           trust" >>  /usr/local/pgdata/pg_hba.conf
echo "host    all             all             0.0.0.0/0               md5" >>  /usr/local/pgdata/pg_hba.conf

#4.修改配置文件
cat > /usr/local/pgdata/postgresql.conf << EOF
# basic	
listen_addresses= '*'	
port= 5432	# 端口
max_connections = 9999	# 最大连接数
superuser_reserved_connections = 10	#给超级用户预留的连接数
shared_buffers = 16GB	# 共享内存，一般设置为内存的1/4
effective_cache_size = 2GB	# 查询优化器估计的可用于缓存数据文件系统的总内存量，
max_worker_processes = 48	# 最大工作线程，和cpu核数一致
max_parallel_workers_per_gather = 4	# 单个查询在执行过程中可以使用的最大并行工作进程数，(
max_parallel_workers =24	# 整个数据库实例允许的最大并行进程数，CPU核心数的1/2
max_parallel_maintenance_workers = 6	# 维护操作期间允许的最大并行进程数，CPU核心数的1/8
work_mem = 16MB	# 设置在写入临时磁盘文件之前查询操作(例如排序或哈希表
maintenance_work_mem = 256MB	#在维护性操作(例如VACUUM、CREATE INDEX和ALTERT
timezone = 'Asia/Shanghai'	# 系统时区
hot_standby = on	# 打开热备
#_optimizer	
default_statistics_target = 500	# 默认100，ANALYZE在pg statistic中存储的信息量，
#_wal	
max_wal_size = 16GB	# 建议与shared buffers保持一致
min_wal_size = 2GB	# 建议max wal size/12.5
wal_log_hints = on	# 控制WAL日志记录的方式，建议打开
wal_level = replica	# wal日志写入级别，要使用流复制，必须使用replica或
wal_sender_timeout = 60s	# 设置WAL发送者在发送WAL数据时等待主服务器响应的超时
#_archive	# 开启归档日志
archive_command = 'gzip < %p > /usr/local/pgdata/archive/%f.gz'	
archive_mode = on	
#_log_近7天轮询	
log_destination = 'csvlog'	# 日志格式
logging_collector = on	# 日志收集器
log_directory = 'pg_log'	# 日志目录$PGDATA/pg_log
log_filename = 'postgresql-%Y-%m-%d.log'	# 日志名称格式
Log_rotation_age = 43200	# 日志保留时间单位是分钟
log_file_mode = 0600	# 日志文件的权限
log_rotation_size = 0	# 日志的最大尺寸，设置为零时将禁用基于大小创建新的日
log_truncate_on_rotation = on	# 这个参数将导致PostgreSQL截断(覆盖而不是追加)任们
Log_min_duration_statement = 0	# 如果语句运行至少指定的时间量，将导致记录每一个这种
log_duration = on	# 每一个完成的语句的持续时间被记录
log_lock_waits = on	# 控制当一个会话为获得一个锁等到超过deadlock timeo
log_statement = 'mod'	# 控制哪些 SOL 语句被记录。有效值是 none(off)、dc
log_timezone = 'Asia/Shanghai'	# 设置在服务器日志中写入的时间的时区
#_sql	
statement_timeout = 300000	# 语句执行超时时间 5分钟
idle_in_transaction_session_timeout = 300000	#事务空闲超时时间 5分钟
idle_session_timeout = 1800000	# 会话空闲超时时间 30分钟
lock_timeout = 60000	#等锁超时时间 1分钟
EOF

##切换至postergres用户
su - postgres
#5.启动数据库
/usr/local/pgsql/bin/pg_ctl start

#6.修改管理员postgres密码，创建指定用户
psql -c "ALTER ROLE postgres  WITH PASSWORD 'Aa532733691';"

#7.修改pgsql密码认证方式(pgpool前置工作)
查看数据库中用户密码协议
psql -c "show password_encryption;"
查看数据库已有用户及密码协议
psql -c "select * from pg_shadow;"
临时修改数据库中用户密码协议
psql -c "set password_encryption = 'md5';"
更改用户密码使用新协议
psql -c "ALTER ROLE postgres  WITH PASSWORD 'Aa532733691' LOGIN;"
验证数据库中用户密码协议是否更改
psql -c "select * from pg_shadow;"

#8.创建主从复制账户
psql -c "create role repl replication encrypted password 'Aa532733691';"

#9.pgpool中间件账户(pgpool前置工作)
psql -c "create role nobody login encrypted password 'Aa532733691';"
psql -c "create role pgsql login encrypted password 'Aa532733691';"
psql -c "create role pgpool login encrypted password 'Aa532733691';"
psql -c "grant postgres to nobody,pgsql,pgpool;"

2.slave执行

#1.同步主库的数据库文件，pg_basebackup是pgsql数据库物理全备服务
##切换至postergres用户
su - postgres
/usr/local/pgsql/bin/pg_basebackup -Fp --progress -D  /usr/local/pgdata -R -h 10.0.0.192 -p 5432 -U repl 

#2.创建从库专属文件standby.singal
cat > /usr/local/pgdata/standby.signal << EOF
standby_mode = on #on为从库
primary_conninfo = 'host=10.0.0.192 port=5432 user=repl password=Aa532733691' #主库信息
recovery_target_timeline = 'latest'  #流复制同步最新数据
EOF

#3.启动从数据库
/usr/local/pgsql/bin/pg_ctl start

三、查看主从数据库状态

1.master执行

su - postgres
psql -c "\x on;"
psql -c "select * from pg_stat_replication;"
#查询显示f为主库，显示t为从库
psql -c "SELECT pg_is_in_recovery();"

2.slave执行

su - postgres
psql -c "SELECT pg_is_in_recovery();"

四、配置pgpool中间件

pgpool-II 是一个数据库连接池和代理程序,可以实现读写分离。它通过代理和路由请求来实现读写分离、查询负载，还可以做到主故障转移

1.编译安装pgpool

pgpool部署在master上

cd /usr/local/
yum install postgresql-devel
#wget https://www.pgpool.net/mediawiki/images/pgpool-II-3.6.0.tar.gz
wget https://www.pgpool.net/mediawiki/images/pgpool-II-4.5.0.tar.gz
sleep 3
tar xvf pgpool-II-4.5.0.tar.gz cd pgpool-II-4.5.0/
./configure --prefix=/usr/local/pgpool -with-pgsql=/usr/local/pgsql  -with-pgsql=/usr/local/pgdata
sleep 2
make && make install
chown -R postgres.postgres /usr/local/pgpool

2.配置准备

su - postgres
#1.配置权限文件pool_hba.conf
echo "host    all         all         0.0.0.0/0             md5" >  /usr/local/pgpool/etc/pool_hba.conf

#2.配置总配置文件pgpool.conf
cat > /usr/local/pgpool/etc/pgpool.conf << EOF
# pgpool.conf
#basic
backend_clustering_mode = 'logical_replication'
listen_addresses = '*'
port = 9999
pcp_listen_addresses = '*'
pcp_port = 9898
listen_backlog_multiplier = 2

#Backend
backend_hostname0 = 'pg1'
backend_port0 = 5432
backend_weight0 = 1
backend_data_directory0 = '/usr/local/pgdata'
backend_flag0 = 'ALLOW_TO_FAILOVER'
backend_application_name0 = 'server0'

backend_hostname1 = 'pg2'
backend_port1 = 5432
backend_weight1 = 1
backend_data_directory1 = '/usr/local/pgdata'
backend_flag1 = 'ALLOW_TO_FAILOVER'
backend_application_name1 = 'server1'


#auth
enable_pool_hba = on
pool_passwd = 'pool_passwd'
authentication_timeout = 1min

#ssl
ssl = off

#pool
process_management_mode = static
num_init_children = 32
max_pool = 2000

# 日志文件路径
log_directory = '/usr/local/pgpool/etc'

# # 日志文件的最大大小
log_filename = 'pg.log'
log_line_prefix = '[%m] '
log_statement = 'ddl'

#识别负载和SQL
load_balance_mode = on
ignore_leading_white_space = on
allow_sql_comments = on

# - Health Check -
health_check_period = 5
health_check_timeout = 30
health_check_user = 'postgres'
health_check_password = 'Aa532733691'
health_check_max_retries = 3
health_check_retry_delay = 1

# REPLICATION
#------------------------------------------------------------------------------

# - Streaming Replication Check -
sr_check_period = 0
sr_check_user = 'sr_check_user'
sr_check_password = 'Aa532733691'
sr_check_database = 'postgres'

# FAILOVER AND FAILBACK
failover_command = '/usr/local/pgpool/etc/failover.sh %H %R'
EOF

#3.配置密码文件pool_passwd，执行命令自动生产pool_passwd
pg_md5 --md5auth --username=nobody "Aa532733691"
pg_md5 --md5auth --username=pgsql "Aa532733691"
pg_md5 --md5auth --username=pgpool "Aa532733691"
pg_md5 --md5auth --username=postgres "Aa532733691"
cat /usr/local/pgpool/etc/pool_passwd 

#4.配置pgpool和pgsql的认证文件pcp.conf
echo "pgpool:`psql -c "select * from pg_shadow;"|grep pgpool |awk '{print $13}'`" > /usr/local/pgpool/etc/pcp.conf


#5.准备故障转移脚本
 failover.sh
#!/bin/bash


api='https://open.feishu.cn/open-apis/bot/v2/hook/xxxx'
contents=""  #要发送的信息
#换行用\n 空格用\t
function dd
{
curl -X POST \
  $api \
  -H 'Content-Type: application/json' \
  -d "{
    \"msg_type\": \"post\",
    \"content\": {
        \"post\": {
            \"zh_cn\": {
                \"title\": \"服务检测报警\",
                \"content\": [
                    [
                        {
                            \"tag\": \"text\",
                            \"un_escape\": true,
                            \"text\": \"$contents\"
                        }
                    ],
                    [

                    ]
                ]
            }
        }
    }
}"
}

newmaster=$1
newpgsql=$2
contents="pgsql主服务器异常"
dd
echo "$1 $2 " >> /usr/local/pgpool/etc/lsn.log
echo "`date`"  >> /usr/local/pgpool/etc/lsn.log
ssh postgres@$newmaster -c "pgsql -U postgres postgres -c 'SELECT * FROM pg_wal_recode_lsn('0/0'); '" >> /usr/local/pgpool/etc/lsn.log
ssh postgres@$newmaster -c "pg_ctl promote -D /app/pgdata" 

3.启动服务

#启动pgpool服务
pgpool -n -d  -a /usr/local/pgpool/etc/pool_hba.conf -f /usr/local/pgpool/etc/pgpool.conf  -F /usr/local/pgpool/etc/pcp.conf > /usr/local/pgpool/etc/pg.log 2>&1 &
##pgpool -n -d > /usr/local/pgpool/pgpool.log 2>&1 &
#关闭服务
#ps aux|grep 'pgpool -n -d' |grep -v grep |awk '{print $2}'|xargs kill -15
#重新加载配置
#pgpool reload

4.查看状态

psql  -U postgres -p 9999 -d postgres
show pool_nodes;

五、pgsql定时备份和监控shell脚本

1.pgsql定时全备脚本

cat /root/allbackup.sh 
#!/bin/bash

time1=`date +%F_%H-%M-%S`
find /app/backup/20* -type d -mtime +7  |xargs rm -rf
mkdir /app/backup
#逻辑备份
#/usr/local/pgsql/bin/pg_dump  -U postgres   -p 5432 -F c  |gzip  >  /app/backup/$time1.gz
#物理备份
/usr/local/pgsql/bin/pg_basebackup -Fp --progress -D  /app/backup/`date +%F`-pgdata -R -h 10.0.0.194 -p 5432 -U repl

2.服务监控脚本

cat /root/monitor.sh 
#!/bin/bash

#api=https://open.feishu.cn/open-apis/bot/v2/hook/e618cadd-c17e-440d-bb85-87xxx  #飞书机器人webhook 地址
api='https://open.feishu.cn/open-apis/bot/v2/hook/xxxx'
contents=""  #要发送的信息
#换行用\n 空格用\t
function dd
{
curl -X POST \
  $api \
  -H 'Content-Type: application/json' \
  -d "{
    \"msg_type\": \"post\",
    \"content\": {
        \"post\": {
            \"zh_cn\": {
                \"title\": \"服务检测报警\",
                \"content\": [
                    [
                        {
                            \"tag\": \"text\",
                            \"un_escape\": true,
                            \"text\": \"$contents\"
                        }
                    ],
                    [

                    ]
                ]
            }
        }
    }
}"
}

#pgpool启动命令
#pgpool -n -d > /usr/local/pgpool/pgpool.log 2>&1 &
#pgpool关闭命令
#pgpool -m fast stop

#pgsql启动命令
#su - postgres -c "/usr/local/pgsql/bin/pg_ctl start"
#pgsql关闭命令
#su - postgres -c "/usr/local/pgsql/bin/pg_ctl stop"

for i in `seq 3`;do 
  sleep 60
  ps aux |grep 'pgpool -n -d' |grep -v grep
  if [ $? = 0 ];then
    echo aa
    #contents="test"
    #dd
    #pgpool -n -d > /usr/local/pgpool/pgpool.log 2>&1 &
  else
    contents="`cat /etc/hostname`  pgpool数据库负载服务异常，重启中"
    dd
    su - postgres -c "/usr/local/pgpool/bin/pgpool -n -d > /usr/local/pgpool/pgpool.log 2>&1 &"
    echo "`date` pgpool异常" >> /root/momitor.log
  fi
  ps aux |grep '/usr/local/pgsql/bin/postgres' | grep -v grep
  if [ $? = 0 ];then
    echo aa
    #contents="test1"
    #dd
    #pgpool -n -d > /usr/local/pgpool/pgpool.log 2>&1 &
  else
    content="`cat /etc/hostname` pgsql数据库负载服务异常，重启中"
    dd
    su - postgres -c "/usr/local/pgsql/bin/pg_ctl start"
    echo "`date` pgsql异常" >> /root/momitor.log
  fi
done

六、pgbouncer代理中间件

1.pgbouncer介绍

2.编译安装pgbouncer

cd /usr/local
wget https://www.pgbouncer.org/downloads/files/1.20.1/pgbouncer-1.20.1.tar.gz
tar xvf pgbouncer-1.20.1.tar.gz 
cd pgbouncer-1.20.1/
#安装依赖
yum -y install make libevent libevent-devel pgkconfig openssl openssl-devel
#编译安装
./configure --prefix=/app/pgbouncer
make && make install

3.准备配置文件

cd /app/pgbouncer/share/doc/pgbouncer

#查询数据库账户密码并按格式写入账户文件
psql -c 'select usename,passwd from pg_shadow;' |sed '1,2d'|sed '$d' |sed '$d'|awk -F '|' '{print $1,$2}'|sed "s/\([^ ]\+\)/\"\1\"/g"|sed 's/^[[:space:]]*//' > userlist.txt

#总配置文件pgbouncer.ini。readyonly为数据库连接账户且只读权限，后面dbname是真实数据库名
pgbouncer.ini配置文件分两部分
第一部分是[databases]区域，是用来配置数据库连接相关信息的。
第二部分是[pgbouncer]，是pgbouncer自身的配置。
cat > pgbouncer.ini << EOF
[databases]
a1 = host=127.0.0.1 port=35432 dbname=postgres
readyonly = host=10.0.0.193 port=5432 dbname=postgres
readywrite = host=10.0.0.192 port=5432 dbname=postgres
[pgbouncer]
listen_addr=*
listen_port=6432
auth_type=md5
auth_file=/app/pgbouncer/share/doc/pgbouncer/userlist.txt
logfile=/app/pgbouncer/pgbouncer1.log
pidfile=/app/pgbouncer/pgbouncerl.pid
unix_socket_dir = /tmp
max_client_conn=1000
default_pool_size=50
EOF

4.启动服务

/app/pgbouncer/bin/pgbouncer -d /app/pgbouncer/share/doc/pgbouncer/pgbouncer.ini

5.查询服务状态

#pgbouncer为默认的虚拟数据库
psql -p 6432 -U pgbouncer pgbouncer
pgbouncer=#show help； #查看帮助
pgbouncer=#SHOW DATABASES;