首页 > 数据库 >postgresql主从部署、pgpool代理中间件部署

postgresql主从部署、pgpool代理中间件部署

时间：2024-04-02 14:36:01浏览次数：34  
标签：postgresql postgres 部署 中间件 pgsql usr pgpool local log

目录

源码编译安装postgresql、pgpool
环境：
centos 7.9
postgresql：15.6
pgpool：pgpool-II-3.6.0
节点：
master：10.0.0.192
slave：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

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中间件

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
sleep 3
tar xvf pgpool-II-3.6.0.tar.gz cd pgpool-II-3.6.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
 
# 日志文件路径
log_directory = 'pgpool_log'
 
# 日志文件的最大大小
log_filename = 'pgpool.log'
log_line_prefix = '[%m] '
log_statement = 'ddl'
enable_pool_hba= on 
pool_passwd = 'pool_passwd'
# Pgpool监听服务器的Unix域套接字文件
pid_file_name = 'pgpool.pid'
 
# 监听客户端连接的TCP端口
listen_port = 9999
listen_addresses = '*'
 
# 监听replication连接的TCP端口
replication_listen = '10.0.0.192'
replication_port = 5432
 
# 主服务器配置
backend_hostname0 = '10.0.0.192'
backend_port0 = 5432
backend_weight0 = 1
backend_data_directory0 = '/usr/local/pgdata'
 
# 从服务器配置
backend_hostname1 = '10.0.0.194'
backend_port1 = 5432
backend_weight1 = 1
backend_data_directory1 = '/usr/local/pgdata'
 
# 健康检查的时间间隔
health_check_period = 10
 
# 连接池配置
num_init_children = 32
max_pool = 2000
 
# 负载均衡方法
load_balance_mode = 'on'
 
# 主服务器故障转移设置
failover_command = 'pg_autoctl failover'
failover_on_panic = 'immediate'
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

3.启动服务

#启动pgpool服务
pgpool -n -d > /usr/local/pgpool/pgpool.log 2>&1 &
#关闭服务
#pgpool -m fast stop
#重新加载配置
#pgpool reload

六、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

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

标签：postgresql,postgres,部署,中间件,pgsql,usr,pgpool,local,log
From： https://www.cnblogs.com/wangchengshi/p/18110288

相关文章

  • centos7.9部署nginx服务
    首先，确保你已经安装了CentOS7.9，并且已经连接到互联网。下载Nginx的压缩包，可以在官方网站（https://nginx.org/en/download.html）上找到最新的稳定版本。使用以下命令下载压缩包：wgethttps://nginx.org/download/nginx-1.19.2.tar.gz也可用Xftp上传nginx-1.19.2.tar.gz包......
  • 数据库之迁移常规操作（Postgresql篇）
    一、docker安装postgresql1.拉取postgresdockerpullpostgres2.创建容器注：默认登录账户postgres,密码123456,对外暴露端口5432,卷映射：可在物理机修改数据库配置文件引用文章查看......
  • 中间件之Mycat
    一、概念介绍Mycat是开源的、活跃的、基于Java语言编写的MySQL数据库中间件。可以像使用mysql一样来使用mycat，对于开发人员来说根本感觉不到mycat的存在Mycat不负责存储数据，只是逻辑上多数据进行分处理，实际存储数据的为后端数据库。Mycat拦截了用户发送过来的SQL语句，对SQL......
  • Kylin Desktop V10部署达梦数据库（DM8）
    1.环境处理器：飞腾D2000/8E8C内存：16GB系统：银河麒麟桌面版操作系统（国防版）V10达梦数据库：dm8_20231226_FTarm2000_kylin10_sp1_642.软件下载链接：https://www.dameng.com/list_103.html3.环境部署我这里已经下载好了（软件、环境部署脚本），需拷贝至桌面运行no1.sh，安装完成后运行n......
  • 手动搭建 物流单据识别api服务 部署教程 Java
    最近遇到一个项目需求，需要自动化识别快递面单上的人名、电话、地址、邮编等信息。查询了很多方案，都不太满意。调研了使用付费api，如快递100和百度的api等。最大的问题是速度不行，本项目每天要识别上百万的面单信息，api接口速度跟不上。其次是各家解析的正确率也达不到预期。于是乎，......
  • docker-compose 部署OWASP Juice Shop + CTFd
    项目介绍1.OWASPJuiceShop原文OWASPJuiceShopisprobablythemostmodernandsophisticatedinsecurewebapplication!Itcanbeusedinsecuritytrainings,awarenessdemos,CTFsandasaguineapigforsecuritytools!JuiceShopencompassesvulnerabili......
  • 模型转化和移动端部署
    importtorchimporttorchvisionfromtorch.utils.mobile_optimizerimportoptimize_for_mobile#1、导入模型#1.1导入预训练好的模型#model=torchvision.models.mobilenet_v3_small(pretrained=True)#1.1注意如果你的模型是自己定义的，应当像这样进行模型加载mo......
  • Windows 部署 Stable Diffusion web UI
    系统要求环境要求1.安装Python3.10.6.2.安装git.#自行百度安装版本信息StableDiffusion:1.8.0-RC python:3.10.6 torch:2.1.2+cu121配置python环境打开AnacondaPowershellPrompt创建python环境(base)PSC:\Users\wgs>condacreate-pG:\st......
  • Kubernetes kafka系列 | Strimzi 部署kafka-bridge
    Strimzi+kafka集群部署直通车一、kafkabridge介绍KafkaBridge是ApacheKafka生态系统中的一个工具或组件，用于实现Kafka与其他系统或协议之间的通信或集成。Kafka本身是一个分布式事件流平台，广泛用于构建实时数据流水线和流式应用程序。然而，并非所有系统或应用程......
  • 基于Linux系统的Llama2模型部署与运行
    一、安装Linux系统（Ubuntu20.04）略。二、安装Nvidia-docker参考：nvidia-docker安装三、环境配置3.1拉取镜像在终端通过docker拉取一个Python版本为3.10的镜像。sudodockerpullpython:3.103.2在容器中的操作1、运行容器sudodockerrunit--name容器名python:3.......

赞助商

阅读排行

网站主页关于我们联系我们网站地图

本网站内容转载自其他媒体，侵权联系[admin##ips99.com]。

Copyright © 2020-2023 IPS99 版权所有 IPS99