公司PostgreSQL数据库安装部署规范v1.0

1参数与配置

1. sysctl.conf配置规

以32G内存为例，kernel.shmmax、kernel.shmall根据实际内存进行修改

# vi /etc/sysctl.conf



kernel.shmall = 8388608

kernel.shmmax = 34359738368

vm.swappiness=0

vm.overcommit_memory=2

vm.overcommit_ratio=80

vm.dirty_background_ratio=2

vm.dirty_ratio=2

kernel.sem=20 13000 20 650

kernel.sysrq = 1

kernel.core_uses_pid = 1

kernel.msgmnb = 65536

kernel.msgmax = 65536

kernel.msgmni = 2048

net.ipv4.tcp_syncookies = 1

net.ipv4.ip_forward = 0

net.ipv4.conf.default.accept_source_route = 0

net.ipv4.tcp_tw_recycle = 1

net.ipv4.tcp_max_syn_backlog = 4096

net.ipv4.conf.all.arp_filter = 1

net.ipv4.ip_local_port_range = 1025 65535

net.core.netdev_max_backlog = 10000

net.core.rmem_max = 2097152

net.core.wmem_max = 2097152

2. limit.conf配置

* soft nofile 65536* hard nofile 65536* soft nproc 131072

3. 20-nproc.conf配置

vi /etc/security/limits.d/20-nproc.conf* soft nproc 131072* hard nproc 131072

4. 关闭selinux

sed -i 's/SELINUX=enforcing/SELINUX=disabled/' /etc/selinux/config

5. 操作系统用户

groupadd -g 701 postgresuseradd -u 701 -g postgres -G postgres postgrespasswd postgres密码设为@baishengDB1122

环境变量

export PGHOME=/usr/pgsql-11export PGPORT=1921export PATH=$PGHOME/bin:$PATH:$HOME/.local/bin:$HOME/binexport LD_LIBRARY_PATH=$PGHOME/lib:$LD_LIBRARY_PATHexport PGHOST=/data/postgres/pgdataexport PGDATA=/data/postgres/pgdata

6. 数据库基本规范

数据目录：/data/postgres/pgdata

端口：1921

7. 归档配置

设置归档目录为：/data/arch

归档保留一周，配置参数

archive_mode = on

archive_command='/bin/bash /home/postgres/bin/pg_archive.sh %p %f'

调用脚本进行归档和归档清理

| [postgres@QCSHV105856 ~]$**mkdir** -p bin logs /data/arch[postgres@QCSHV105856 ~]$ vi bin/pg_archive.sh#!/bin/bashsource ~/.bash_profileDATE=date +%FDIR="/data/arch/$DATE"BACK="/data/arch/"`date -d '-7 day' +$BACK ]; thenrm -rf $BACKecho$(date "+%Y-%m-%d %H:%M:%S")":success rm $BACK" > ~/logs/pg_archive.logelseecho$(date "+%Y-%m-%d %H:%M:%S")":the old backup file $BACK not exists!" > ~/logs/pg_archive.logfi(test -d$DIR || mkdir -p DIR) && cp 1 $DIR/$2if [ $? -eq 0 ]; thenecho$(date "+%Y-%m-%d %H:%M:%S")":archive file $1 succeed" >> ~/logs/pg_archive.logelseecho$(date "+%Y-%m-%d %H:%M:%S")":archive file $1 failed" >> ~/logs/pg_archive.logfi | | ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- |

8. postgresql.conf配置

以32G内存为例

vi /data/postgres/pgdata/postgresql.conflisten_addresses = '*'port = 1921max_connections = 2000max_prepared_transactions = 2000work_mem = 8MBmaintenance_work_mem = 512MBautovacuum_work_mem = 512MBmax_parallel_maintenance_workers = 6max_parallel_workers_per_gather = 0max_parallel_workers = 32superuser_reserved_connections = 10unix_socket_directories = '.'tcp_keepalives_idle = 5tcp_keepalives_interval = 5tcp_keepalives_count = 3shared_buffers = 8GBshared_preload_libraries = 'pg_stat_statements'wal_level = replicamax_wal_size = 50GBmin_wal_size = 4GBwal_keep_segments=3200wal_compression = oneffective_cache_size = 40GBautovacuum_max_workers = 10temp_file_limit = 20GBcheckpoint_timeout = 15mincheckpoint_completion_target=0.5checkpoint_flush_after = 1MBcommit_delay = 10archive_mode = onarchive_command = '/bin/bash /home/postgres/bin/pg_archive.sh %p %f'max_wal_senders = 16hot_standby = onlog_destination = 'csvlog'log_filename = 'postgresql-%a.log'log_truncate_on_rotation = onlog_rotation_age = 7dlog_rotation_size = 100MBlog_statement = 'ddl'track_functions = alltrack_activity_query_size = 4096wal_log_hints=onfull_page_writes=onstatement_timeout = 3600000archive_timeout = '10min'autovacuum_vacuum_cost_delay = 2msautovacuum_vacuum_cost_limit = -1idle_in_transaction_session_timeout = '6h'random_page_cost = 1.1vacuum_cost_delay = 2msvacuum_cost_limit = 2000deadlock_timeout = 600stemp_file_limit = 20GBeffective_cache_size = 40GB

9. pg_hba.conf

vi /data/postgres/pgdata/pg_hba.confhost all all ::1/128 trusthost all all 127.0.0.1/32 trustlocal all all trustlocal replication all trusthost all all 0/0 md5host replication all 0/0 md5

10. recovery.conf配置

vi /data/postgres/pgdata/recovery.confstandby_mode = 'on'recovery_target_timeline = 'latest'primary_conninfo = 'application_name=本机ip地址 user=replica password=@baishengDB1122 host=主库ip地址 port=1921 sslmode=prefer sslcompression=1 krbsrvname=postgres'

11. 数据库用户

创建流复制用户CREATE ROLE replica superuser PASSWORD '@baishengDB1122' login;新交付的实例默认建一个用户YumDBuser，密码是10位大小写+数字混合，权限给：建库、建对象、CRUD每个集群使用单独的随机密码CREATE ROLE "YumDBuser" CREATEDB PASSWORD ' ************** ' login;

12. 插件

create extension pg_stat_statements;

13. 备份

备份目录：/data/pgbak

[postgres@QCSHV105856 ~]$mkdir /data/pgbak[postgres@QCSHV105856 ~]$ vi bin/pgbackup.sh#!/bin/bashsource /home/postgres/.bash_profileprefixBackDir=/data/pgbakback=$prefixBackDir"/"`date -d '-7 day' +$prefixBackDir"/"date +%F"/base"current_time=$(date "+$current_time starting backup....."pg_basebackup -D $prefixBackDir/`date +$backf ]; thenecho "successd backup to $backf"if [ -d$back ]; thenrm -rf $backecho "success rm$back"elseecho "the old backup file $back not exists!"fielseecho "backup failed, because of the file$backf not found"fiecho "$current_time end backup....."

配置crontab

[postgres@QCSHV105856 ~]$ crontab -l0 0 * * * /home/postgres/bin/pgbackup.sh >/home/postgres/logs/pgbackup.log 2>&1 &

2标准操作规范

2.1 clup接管安装agent

修改postgresql.conf

vi $PGDATA/postgresql.confmax_wal_size=50GBmin_wal_size=4GBwal_keep_segments=3200wal_log_hints=onfull_page_writes=on

注意：修改前备份此文件，wal_keep_segments、max_wal_size、min_wal_size根据服务器可用空间来进行修改

统一密码

DBA统一使用postgres账号，流复制统一使用replica账号

生产环境密码统一，具体密码值请联系DBA获取

修改语句：

ALTER USER postgres WITH PASSWORD '@baishengDB1122';ALTER USER replica WITH PASSWORD '@baishengDB1122';

修改pg_hba.conf

vi $PGDATA/pg_hba.confhost all all ::1/128 trusthost all all 127.0.0.1/32 trustlocal all all trustlocal replication all trusthost all all 0/0 md5host replication all 0/0 md5

注：修改前备份此文件

修改recovery.conf

vi $PGDATA/recovery.conf#restored_command='xxxxxx'primary_conninfo='application_name=本机ip password=@baishengDB1122'

注：修改前备份此文件，primary_conninfo中增加application_name=本机ip 和password=@baishengDB1122,并注释掉restored_command

安装依赖包

yum install -y tcl tcl-devel tk ipmitool psmisc openssl-devel

安装包

clupX.Y.Z.tar.xz: 其中X.Y.Z是版本号，如3.10.2clupmdbX.Y.Z.tar.xz：其中X.Y.Z是版本号，如3.10.2clup-agentX.Y.Z.tar.xz：其中X.Y.Z是版本号，如3.10.2python3.6.10_centos7-x86.tar.xz: python支持包etcd.3.4.14.tar.xz: etcd

注：如果是centos6操作系统agent包和python3.6都要用对应6的版本

Centos6 开机自启脚本

| #!/bin/bash## clup-agent This starts, stops## Copyright 2017~2020 csudata.com#### BEGIN INIT INFO# Default-Start: 2 3 4 5# Default-Stop: 0 1 6# Short-Description: Start and stop the MD software RAID monitor# Description: The mdmonitor service checks the status of all software# RAID arrays on the system. In the event that any of the arrays# transition into a degraded state, it notifies the system# administrator. Other options are available, see the mdadm.conf# and mdadm man pages for possible ways to configure this service.# Centos6 开机自启# 把这个脚本放到机器的/etc/init.d目录下，然后：# chmod +x clup-agent && chkconfig --add clup-agent && chkconfig clup-agent on# service clup-agent status# service clup-agent start# service clup-agent stop### END INIT INFORETVAL=0prog=clup-agent# Source function library.. /etc/rc.d/init.d/functionsusage (){echo "Usage: service prog {start|stop|status|restart}"RETVAL=1}start (){/opt/clup-agent/bin/clup-agent startret=?return ret}stop (){/opt/clup-agent/bin/clup-agent stop}restart (){stopstart}status (){/opt/clup-agent/bin/clup-agent status}case "1" instart|stop|restart)[ id -u != "0" ] && exit 4 ;;esaccase "$1" instart) start; RETVAL=$? ;;stop) stop; RETVAL=$? ;;status) status; RETVAL=$? ;;restart) restart; RETVAL=$? ;;reload) RETVAL=3 ;;*) usage ; RETVAL=2 ;;esacexit$RETVAL | | --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- |

2.2 clup-agent升级

防止systemd结束数据库

vi /etc/systemd/system/clup-agent.serviceKillMode=process

备份旧的配置文件，解压新的agent版本，重启agent服务

systemctl daemon-reloadcp /opt/clup-agent/conf/clup-agent.conf /opttar -xf clup-agent3.10.2.tar.xzmv /opt/clup-agent.conf /opt/clup-agent/conf/systemctl restart clup-agent如果服务启动失败可以通过手动启动查看错误信息/opt/clup-agent/bin/clup-agent start -f

2.3 集群下线

2.3.1使用clup管理

test集群下线

1、HA集群 -> HA管理 -> 离线

2、HA集群 -> 集群定义 -> 删除

此时该集群已经从clup中移除，clup不会对改集群数据库有任何操作

但是这个集群主机上的agent程序还在运行，在监控管理->Agent状态查看中还会有这个集群的主机，主机性能监控也会对这几个主机进行信息采集

需要停掉对应主机上面的Agent程序，然后在Agent状态查看页面中点对应的 '移除记录'，此时该集群所有信息已经从clup中移除，不会有任何操作

2.3.2无clup管理

关闭监控

systemctl stop node_exportersystemctl stop postgres_exporter

关闭crontab中备份任务

关闭crontab中归档清理任务

2.4 备库搭建

pg_basebackup -D /data/postgres/pgdata -Fp -R -P -v -h 主库ip -p 1921 -U replica