postgresql主从部署、pgsql中间件
目录
源码编译安装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;
