私有云搭建 Postgres 单节点

部署规划

节点IP	服务端口	数据目录	备份目录	归档目录	日志目录
192.168.1.99	5432	/data/postgresql/data	/data/postgresql/pg_bak	/data/postgresql/pg_arch	/data/postgresql/logs

备份策略规划

节点IP	备份周期	备份保留时长	归档日志保留时长
192.168.1.99	每天凌晨1点	7天	7天

用户权限规划

节点IP	用户名	密码	角色权限	用户描述
192.168.1.99	postgres	dasdsdsaw@321/@DWEQ	super	超级权限用户
192.168.1.99	repl	dasdspPw3fz	replication	备份复制用户
192.168.1.99	admin			管理员用户
192.168.1.99	readonly			只读用户
192.168.1.99	datasec			安全产品扫描用户

安装数据库

1. yum 下载安装 postgre15

# Install the repository RPM:
sudo yum install -y https://download.postgresql.org/pub/repos/yum/reporpms/EL-7-x86_64/pgdg-redhat-repo-latest.noarch.rpm

# Install PostgreSQL:
sudo yum install -y postgresql15-server

服务端与客户端以及插件下载参考 postgre 官方文档： https://www.postgresql.org/download/linux/redhat/

2. 初始化 postgre15

#创建 PG15 数据目录并修改属主属组
mkdir -p /data/postgresql/
chown -R postgres:postgres /data
#修改初始化操作生成 PGDATA 参数为数据目录/data/postgresql/data
cd /usr/lib/systemd/system
cat postgresql-15.service | grep PGDATA
vi postgresql-15.service
#初始化PG15
sudo /usr/pgsql-15/bin/postgresql-15-setup initdb
#修改postgres用户 PGDATA 环境变量为数据目录/data/postgresql/data
su - postgres
vi .bash_profile
#将PG15服务设置为开机自启动
systemctl enable postgresql-15 --now
#检查PG15服务状态
systemctl status postgresql-15

3. 自定义参数配置 postgre15

• 修改 pg_hba.conf 配置允许所有主机访问 pg15，允许所有主机通过复制账号备份数据库

cd /data/postgresql/data
vi pg_hba.conf
#增加如下配置
host  all  all  0.0.0.0/0  scram-sha-256
host replication repl 0.0.0.0/0 scram-sha-256

• 自定义 postgresql.conf 参数配置

#connect contorl& authentication
listen_addresses='*'
port=5432
max_connections='1200'
superuser_reserved_connections='10'


#memory management
shared_buffers='2048MB'    #推荐设置25%物理内存，一般不超过16GB
effective_cache_size='6144MB' #推荐设置50%-75%物理内存，用于评估postgre可用内存，非实际分配内存
wal_buffers='16MB'
work_mem='16MB'             #内存表排序内存，每个连接都会实际分配的内存


#log management
logging_collector='on'
log_destination='csvlog'
log_directory = '/data/postgresql/logs'
log_filename = 'postgresql-%d-%H.log'         #日志文件名称
log_line_prefix='%m - %a - %u - %d - %p: ' #日志中包含哪些内容，默认'’%m [%p] ',记录时间戳和进程ID
log_min_duration_statement='1000'   #超过1秒的sql记录到日志
log_checkpoints=on                                   
log_connections=on                  
log_disconnections=on               
log_error_verbosity=verbose
log_hostname=on
log_lock_waits=on 
log_statement='ddl'
log_timezone='PRC'
log_file_mode = 0640    #日志文件对应权限为0640，默认0600    
log_truncate_on_rotation = on  #截断日志
log_rotation_age = 1d          #单个日志被截断最大时间量，默认24h       
log_rotation_size = 1GB        #单个日志被截断最大尺寸，默认10M


#pg_stat_statements module
shared_preload_libraries='pg_stat_statements'
pg_stat_statements.max = 10000 #在pg_stat_statements中最多保留多少条统计信息，通过LRU算法，覆盖老的记录。
pg_stat_statements.track = 'all' #all - (所有SQL包括函数内嵌套的SQL), top - 直接执行的SQL(函数内的sql不被跟踪), none - (不跟踪)
pg_stat_statements.track_utility = 'off' #是否跟踪非DML语句 (例如DDL，DCL)，on表示跟踪, off表示不跟踪  
pg_stat_statements.save = 'on' # 重启后是否保留统计信息 
track_io_timing = 'on'  #慢查询中跟踪IO消耗的时间
track_activity_query_size = 2048  #慢查询sql长度，最大2048字节，超过截断


#backup & replication
wal_level='replica'
archive_mode='on'    #备库日志归档always
archive_command='/bin/bash archive.sh %f %p' #执行归档操作对应的shell脚本
archive_timeout=900 #强制wal日志切换时间
wal_keep_size=128   #wal日志数量，PG13版本开始使用此参数，PG13之前版本使用wal_keep_segments
max_wal_size=2046  #单位MB,默认1024
min_wal_size=256   #单位MB,默认80
hot_standby='on'   #恢复模式与standby模式时是否可以进行连接和查询，默认为on


#query optimize
max_parallel_workers_per_gather ='0' #最小worker数量，默认为2，0表示禁止并行[针对join，排序的并行查询优化] 
max_parallel_workers ='0'            #最大worker数量，默认为8


#autovacuum
autovacuum = on                         
vacuum_cost_delay = 0                   
old_snapshot_threshold = 6h            
log_autovacuum_min_duration = 0         
autovacuum_max_workers = 8              
autovacuum_vacuum_scale_factor = 0.02   
autovacuum_analyze_scale_factor = 0.01  
autovacuum_freeze_max_age = 1200000000  
autovacuum_multixact_freeze_max_age = 1250000000       
autovacuum_vacuum_cost_delay = 0ms  


#system environment
datestyle = 'iso, mdy'
timezone = 'Asia/Shanghai'
lc_messages = 'en_US.utf8'     
lc_monetary = 'en_US.utf8'     
lc_numeric = 'en_US.utf8'      
lc_time = 'en_US.utf8'         
default_text_search_config = 'pg_catalog.english'

4. 配置 PG15 归档脚本

cd /data/postgresql/data
vi archive.sh
#归档脚本如下
test ! -f /data/postgresql/pg_arch/$1 && cp --preserve=timestamp $2 /data/postgresql/pg_arch/$1 ; find /data/postgresql/pg_arch/ -type f -mtime +7 -exec rm -f {} \
chmod 744 archive.sh

5. 创建备份目录，归档目录，日志目录，并修改/data目录下所有文件属主属组

cd /data/postgresql
mkdir /data/postgresql/logs
mkdir /data/postgresql/pg_arch
mkdir /data/postgresql/pg_bak
#修改目录属主数组
chown -R postgres:postgres /data

6. 重启数据库服务

#关闭PG15服务
systemctl stop postgresql-15
#启动PG15服务
systemctl start postgresql-15
#检查PG15服务状态
systemctl status postgresql-15

配置数据库备份

1. 修改super用户密码

#使用超级权限用户本地登录postgres
su - postgres
psql
#修改postgres用户密码,设置为规划密码
alter user postgres with password 'dasdsdsaw@321/@DWEQ';

1. 创建备份用户

#使用超级权限用户本地登录postgres
su - postgres
psql
#执行如下sql创建备份用户，设置为规划权限，规划密码
create role repl nosuperuser replication login connection limit 32 encrypted password 'dasdspPw3fz';

1. 配置备份脚本

cd /data/postgresql
# --创建如下脚本--
vi pg_backup.sh
#!/bin/bash
DATE=$(date +%F)
mkdir /data/postgresql/pg_bak/$DATE
PGPASSWORD=dasdsdsaw@321/@DWEQ pg_basebackup -Ft -Xs -P -R -D /data/postgresql/pg_bak/$DATE -h 192.168.1.99 -p 5432 -U repl
find /data/postgresql/pg_bak -type f -mtime +7 -exec rm -f {} \;

1. 修改备份脚本属主属组与权限

cd /data/postgresql
chmod 755 pg_backup.sh
chown postgres:postgres pg_backup.sh

1. 配置定时备份任务

crontab -e
0 1 * * * sh /data/postgresql/pg_backup.sh