PostgreSQL在openEuler部署后的安全与性能调优实战指南当你完成PostgreSQL在openEuler系统上的基础安装后真正的挑战才刚刚开始。本文将带你深入探索那些容易被忽视却至关重要的配置细节从安全加固到性能优化让你的数据库在生产环境中既安全又高效。1. 生产环境防火墙精细配置许多教程会建议直接关闭防火墙但这在生产环境中无异于敞开大门迎接风险。正确的做法是精细控制访问权限只开放必要的端口和服务。首先确认firewalld服务状态systemctl status firewalld如果处于关闭状态需要先启用systemctl start firewalld systemctl enable firewalld为PostgreSQL添加专用防火墙规则假设使用默认端口5432firewall-cmd --permanent --add-port5432/tcp firewall-cmd --permanent --add-rich-rulerule familyipv4 source address192.168.1.0/24 port port5432 protocoltcp accept firewall-cmd --reload关键安全策略仅允许特定IP段访问数据库端口限制管理接口访问如pgAdmin定期审计防火墙规则提示生产环境中建议结合网络ACL和安全组实现多层防护2. PostgreSQL核心参数调优针对4核8G的服务器配置以下参数调整可以显著提升性能2.1 内存相关配置编辑postgresql.conf文件找到以下参数进行调整shared_buffers 2GB # 通常设置为总内存的25% work_mem 16MB # 每个查询操作的内存复杂查询可适当增加 maintenance_work_mem 512MB # 维护操作如VACUUM使用的内存 effective_cache_size 6GB # 操作系统和PostgreSQL可用的缓存估计2.2 并行查询配置max_worker_processes 4 # 等于CPU核心数 max_parallel_workers_per_gather 2 # 每个查询的并行工作进程 max_parallel_workers 4 # 系统总并行工作进程2.3 其他关键参数random_page_cost 1.1 # SSD存储建议1.0-1.1 effective_io_concurrency 200 # SSD建议100-200 wal_level replica # 复制环境需要replica或更高 synchronous_commit remote_write # 平衡性能与数据安全参数调整后需要重启服务生效pg_ctl restart -D $PGDATA3. 数据库安全加固措施3.1 创建专用监控账户避免使用超级用户进行日常监控创建专用只读账户CREATE ROLE monitor WITH LOGIN PASSWORD StrongPassword123!; GRANT pg_monitor TO monitor;3.2 密码策略强化修改pg_hba.conf启用SCRAM-SHA-256加密host all all 0.0.0.0/0 scram-sha-256设置密码有效期在postgresql.conf中password_encryption scram-sha-2563.3 日志审计配置log_destination csvlog logging_collector on log_directory pg_log log_filename postgresql-%Y-%m-%d_%H%M%S.log log_rotation_age 1d log_rotation_size 100MB log_statement mod log_connections on log_disconnections on4. 日常维护与监控方案4.1 自动化维护任务设置定期VACUUM和ANALYZE在postgresql.conf中autovacuum on autovacuum_max_workers 2 autovacuum_vacuum_cost_limit 1000创建维护脚本/usr/local/bin/pg_maintenance.sh#!/bin/bash su - postgres -c vacuumdb --all --analyze su - postgres -c reindexdb --all设置cron任务每周执行0 3 * * 0 /usr/local/bin/pg_maintenance.sh /var/log/pg_maintenance.log 214.2 性能监控关键指标建立基础监控查询-- 连接数监控 SELECT count(*) as total_connections, count(*) filter (where state active) as active_connections, count(*) filter (where state idle) as idle_connections FROM pg_stat_activity; -- 锁等待监控 SELECT blocked_locks.pid AS blocked_pid, blocking_locks.pid AS blocking_pid, blocked_activity.usename AS blocked_user, blocking_activity.usename AS blocking_user FROM pg_catalog.pg_locks blocked_locks JOIN pg_catalog.pg_stat_activity blocked_activity ON blocked_activity.pid blocked_locks.pid JOIN pg_catalog.pg_locks blocking_locks ON blocking_locks.locktype blocked_locks.locktype AND blocking_locks.DATABASE IS NOT DISTINCT FROM blocked_locks.DATABASE AND blocking_locks.relation IS NOT DISTINCT FROM blocked_locks.relation AND blocking_locks.page IS NOT DISTINCT FROM blocked_locks.page AND blocking_locks.tuple IS NOT DISTINCT FROM blocked_locks.tuple AND blocking_locks.virtualxid IS NOT DISTINCT FROM blocked_locks.virtualxid AND blocking_locks.transactionid IS NOT DISTINCT FROM blocked_locks.transactionid AND blocking_locks.classid IS NOT DISTINCT FROM blocked_locks.classid AND blocking_locks.objid IS NOT DISTINCT FROM blocked_locks.objid AND blocking_locks.objsubid IS NOT DISTINCT FROM blocked_locks.objsubid AND blocking_locks.pid ! blocked_locks.pid JOIN pg_catalog.pg_stat_activity blocking_activity ON blocking_activity.pid blocking_locks.pid WHERE NOT blocked_locks.GRANTED;4.3 备份策略实施配置基础备份脚本/usr/local/bin/pg_backup.sh#!/bin/bash DATE$(date %Y%m%d) BACKUP_DIR/backup/postgresql/$DATE mkdir -p $BACKUP_DIR su - postgres -c pg_dumpall | gzip $BACKUP_DIR/pg_dumpall_$DATE.sql.gz find /backup/postgresql -type d -mtime 30 -exec rm -rf {} \;设置每日备份任务0 2 * * * /usr/local/bin/pg_backup.sh /var/log/pg_backup.log 21对于关键业务数据库建议配置WAL归档和PITR时间点恢复wal_level replica archive_mode on archive_command test ! -f /backup/wal/%f cp %p /backup/wal/%f
PostgreSQL在openEuler部署后，别忘了做这几步安全与性能调优（附配置模板）
发布时间：2026/5/17 23:30:51
PostgreSQL在openEuler部署后的安全与性能调优实战指南当你完成PostgreSQL在openEuler系统上的基础安装后真正的挑战才刚刚开始。本文将带你深入探索那些容易被忽视却至关重要的配置细节从安全加固到性能优化让你的数据库在生产环境中既安全又高效。1. 生产环境防火墙精细配置许多教程会建议直接关闭防火墙但这在生产环境中无异于敞开大门迎接风险。正确的做法是精细控制访问权限只开放必要的端口和服务。首先确认firewalld服务状态systemctl status firewalld如果处于关闭状态需要先启用systemctl start firewalld systemctl enable firewalld为PostgreSQL添加专用防火墙规则假设使用默认端口5432firewall-cmd --permanent --add-port5432/tcp firewall-cmd --permanent --add-rich-rulerule familyipv4 source address192.168.1.0/24 port port5432 protocoltcp accept firewall-cmd --reload关键安全策略仅允许特定IP段访问数据库端口限制管理接口访问如pgAdmin定期审计防火墙规则提示生产环境中建议结合网络ACL和安全组实现多层防护2. PostgreSQL核心参数调优针对4核8G的服务器配置以下参数调整可以显著提升性能2.1 内存相关配置编辑postgresql.conf文件找到以下参数进行调整shared_buffers 2GB # 通常设置为总内存的25% work_mem 16MB # 每个查询操作的内存复杂查询可适当增加 maintenance_work_mem 512MB # 维护操作如VACUUM使用的内存 effective_cache_size 6GB # 操作系统和PostgreSQL可用的缓存估计2.2 并行查询配置max_worker_processes 4 # 等于CPU核心数 max_parallel_workers_per_gather 2 # 每个查询的并行工作进程 max_parallel_workers 4 # 系统总并行工作进程2.3 其他关键参数random_page_cost 1.1 # SSD存储建议1.0-1.1 effective_io_concurrency 200 # SSD建议100-200 wal_level replica # 复制环境需要replica或更高 synchronous_commit remote_write # 平衡性能与数据安全参数调整后需要重启服务生效pg_ctl restart -D $PGDATA3. 数据库安全加固措施3.1 创建专用监控账户避免使用超级用户进行日常监控创建专用只读账户CREATE ROLE monitor WITH LOGIN PASSWORD StrongPassword123!; GRANT pg_monitor TO monitor;3.2 密码策略强化修改pg_hba.conf启用SCRAM-SHA-256加密host all all 0.0.0.0/0 scram-sha-256设置密码有效期在postgresql.conf中password_encryption scram-sha-2563.3 日志审计配置log_destination csvlog logging_collector on log_directory pg_log log_filename postgresql-%Y-%m-%d_%H%M%S.log log_rotation_age 1d log_rotation_size 100MB log_statement mod log_connections on log_disconnections on4. 日常维护与监控方案4.1 自动化维护任务设置定期VACUUM和ANALYZE在postgresql.conf中autovacuum on autovacuum_max_workers 2 autovacuum_vacuum_cost_limit 1000创建维护脚本/usr/local/bin/pg_maintenance.sh#!/bin/bash su - postgres -c vacuumdb --all --analyze su - postgres -c reindexdb --all设置cron任务每周执行0 3 * * 0 /usr/local/bin/pg_maintenance.sh /var/log/pg_maintenance.log 214.2 性能监控关键指标建立基础监控查询-- 连接数监控 SELECT count(*) as total_connections, count(*) filter (where state active) as active_connections, count(*) filter (where state idle) as idle_connections FROM pg_stat_activity; -- 锁等待监控 SELECT blocked_locks.pid AS blocked_pid, blocking_locks.pid AS blocking_pid, blocked_activity.usename AS blocked_user, blocking_activity.usename AS blocking_user FROM pg_catalog.pg_locks blocked_locks JOIN pg_catalog.pg_stat_activity blocked_activity ON blocked_activity.pid blocked_locks.pid JOIN pg_catalog.pg_locks blocking_locks ON blocking_locks.locktype blocked_locks.locktype AND blocking_locks.DATABASE IS NOT DISTINCT FROM blocked_locks.DATABASE AND blocking_locks.relation IS NOT DISTINCT FROM blocked_locks.relation AND blocking_locks.page IS NOT DISTINCT FROM blocked_locks.page AND blocking_locks.tuple IS NOT DISTINCT FROM blocked_locks.tuple AND blocking_locks.virtualxid IS NOT DISTINCT FROM blocked_locks.virtualxid AND blocking_locks.transactionid IS NOT DISTINCT FROM blocked_locks.transactionid AND blocking_locks.classid IS NOT DISTINCT FROM blocked_locks.classid AND blocking_locks.objid IS NOT DISTINCT FROM blocked_locks.objid AND blocking_locks.objsubid IS NOT DISTINCT FROM blocked_locks.objsubid AND blocking_locks.pid ! blocked_locks.pid JOIN pg_catalog.pg_stat_activity blocking_activity ON blocking_activity.pid blocking_locks.pid WHERE NOT blocked_locks.GRANTED;4.3 备份策略实施配置基础备份脚本/usr/local/bin/pg_backup.sh#!/bin/bash DATE$(date %Y%m%d) BACKUP_DIR/backup/postgresql/$DATE mkdir -p $BACKUP_DIR su - postgres -c pg_dumpall | gzip $BACKUP_DIR/pg_dumpall_$DATE.sql.gz find /backup/postgresql -type d -mtime 30 -exec rm -rf {} \;设置每日备份任务0 2 * * * /usr/local/bin/pg_backup.sh /var/log/pg_backup.log 21对于关键业务数据库建议配置WAL归档和PITR时间点恢复wal_level replica archive_mode on archive_command test ! -f /backup/wal/%f cp %p /backup/wal/%f
)
