PostgreSQL在Ubuntu上的最佳实践
apt安装PostgreSQL（sudo apt install postgresql postgresql-contrib），创建专用系统用户（默认postgres）并设置强密码，避免使用超级用户运行数据库。postgresql.conf中的listen_addresses为localhost或特定IP，限制数据库监听范围；编辑pg_hba.conf，仅允许信任IP访问（如host all all 192.168.1.0/24 md5），禁止默认的trust认证方式。postgresql.conf中开启SSL（ssl = on），指定证书路径（ssl_cert_file/ssl_key_file），确保证书权限为600；要求用户使用复杂密码（如包含大小写字母、数字和符号），定期更换密码。pg_stat_statements扩展（CREATE EXTENSION pg_stat_statements;）监控慢查询，配置log_statement = 'all'记录所有SQL操作，结合auditd工具实现系统级审计，及时发现异常行为。shared_buffers设为物理内存的25%-50%（如8GB内存设为2GB），work_mem设为4MB-128MB（每个排序/哈希操作内存），effective_cache_size设为物理内存的50%-75%（查询规划器参考值）。tune2fs -o journal_dev=yes /dev/sdX1）优化日志性能；配置WAL（Write-Ahead Log）参数：wal_buffers = 64MB、max_wal_size = 2GB、min_wal_size = 1GB，平衡I/O负载。EXPLAIN ANALYZE分析查询计划，避免全表扫描；优化隐式类型转换（如WHERE id = 123而非WHERE id = '123'），减少索引失效；对大表使用COPY命令批量插入数据，降低事务开销。wal_level = replica、max_wal_senders = 10，从节点通过pg_basebackup初始化并设置为热备模式（hot_standby = on）；采用Patroni等Kubernetes原生工具实现自动故障转移，提升集群可靠性。pgBackRest工具进行物理备份（配置repo1-path存储备份路径，retention-full=2保留2次全量备份），定期执行全量备份（如每天凌晨）和增量备份；配置WAL归档（archive_mode = on、archive_command = 'pgbackrest --stanza=demo archive-push %p'），支持时间点恢复（PITR），每月执行恢复演练验证备份有效性。VACUUM（清理死元组）、ANALYZE（更新统计信息）命令，避免表膨胀；调整autovacuum参数（autovacuum_vacuum_scale_factor = 0.1、autovacuum_analyze_scale_factor = 0.05），使其更积极地运行，减少手动维护成本。pgBadger分析PostgreSQL日志，生成可视化性能报告；部署Prometheus+Grafana监控集群状态（如CPU使用率、内存占用、查询延迟、连接数），设置告警阈值（如连接数超过50时触发告警），及时响应性能问题。