PostgreSQL教程FG301-PostgreSQL高级服务器配置
本文档风哥主要介绍PostgreSQL高级服务器配置，包括内存参数、连接参数、写入性能参数的优化和调优。风哥教程参考PostgreSQL官方文档Server Administration部分，适合DBA人员在生产环境中使用。
Part01-基础概念与理论知识
1.1 PostgreSQL服务器配置概述
PostgreSQL服务器配置是影响数据库性能和稳定性的关键因素。通过合理配置服务器参数，可以充分利用系统资源，提高数据库性能，确保系统稳定运行。
- 性能优化：合理配置参数可以显著提高数据库性能
- 资源利用：充分利用系统资源，避免资源浪费
- 稳定性：确保数据库稳定运行，避免崩溃和数据丢失
- 安全性：通过配置参数提高数据库安全性
- 可扩展性：为系统扩展做好准备
1.2 PostgreSQL配置参数分类
PostgreSQL配置参数主要分为以下几类：
- 内存参数：控制PostgreSQL使用的内存量
- 连接参数：控制数据库连接的数量和行为
- 写入性能参数：控制写入操作的性能
- 查询优化参数：控制查询执行的行为
- 日志参数：控制日志的输出和格式
- 安全参数：控制数据库的安全性
1.3 PostgreSQL配置文件结构
PostgreSQL的主要配置文件是postgresql.conf，位于数据目录中。该文件包含了所有的配置参数，按照功能分类组织。
# 连接和认证
listen_addresses = ‘fgedu.localhost’ # 监听地址
port = 5432 # 端口号
max_connections = 100 # 最大连接数
# 资源使用
shared_buffers = 128MB # 共享缓冲区大小
work_mem = 4MB # 工作内存大小
maintenance_work_mem = 64MB # 维护工作内存大小
# 写入性能
temp_buffers = 8MB # 临时缓冲区大小
wal_buffers = 16MB # WAL缓冲区大小
checkpoint_completion_target = 0.9 # 检查点完成目标
# 查询优化
effective_cache_size = 4GB # 有效缓存大小
random_page_cost = 4.0 # 随机页面成本
seq_page_cost = 1.0 # 顺序页面成本
# 日志记录
log_destination = ‘stderr’ # 日志输出目标
logging_collector = on # 启用日志收集器
log_directory = ‘log’ # 日志目录
log_filename = ‘postgresql-%Y-%m-%d_%H%M%S.log’ # 日志文件名格式
Part02-生产环境规划与建议
2.1 内存参数配置
内存参数是影响PostgreSQL性能的关键因素，合理配置内存参数可以显著提高数据库性能。
# shared_buffers：共享缓冲区大小
# 建议值：物理内存的25%
shared_buffers = 4GB
# work_mem：每个查询操作使用的内存
# 建议值：(物理内存 × 0.25) / max_connections
work_mem = 16MB
# maintenance_work_mem：维护操作使用的内存
# 建议值：物理内存的10%，最大不超过1GB
maintenance_work_mem = 1GB
# effective_cache_size：查询优化器假设的可用缓存大小
# 建议值：物理内存的50-75%
effective_cache_size = 16GB
# temp_buffers：临时表使用的内存
# 建议值：8-32MB
temp_buffers = 32MB
2.2 连接参数配置
连接参数控制数据库连接的数量和行为，合理配置可以提高系统的稳定性和性能。
- max_connections：最大连接数，建议根据系统资源和应用需求设置
- superuser_reserved_connections：为超级用户预留的连接数
- idle_in_transaction_session_timeout：空闲事务会话超时时间
- statement_timeout：语句执行超时时间
2.3 写入性能参数配置
写入性能参数控制写入操作的行为，合理配置可以提高写入性能和数据安全性。
# wal_buffers：WAL缓冲区大小
# 建议值：32MB或更大
wal_buffers = 32MB
# checkpoint_completion_target：检查点完成目标
# 建议值：0.9
checkpoint_completion_target = 0.9
# max_wal_size：最大WAL大小
# 建议值：1GB或更大
max_wal_size = 2GB
# min_wal_size：最小WAL大小
# 建议值：80MB
min_wal_size = 1GB
# synchronous_commit：同步提交
# 建议值：on（生产环境）或off（性能优先）
synchronous_commit = on
# wal_writer_delay：WAL写入器延迟
# 建议值：200ms
wal_writer_delay = 200ms
Part03-生产环境项目实施方案
3.1 配置步骤
3.1.1 编辑配置文件
$ sudo vi /postgresql/fgdata/postgresql.conf
# 修改内存参数
shared_buffers = 4GB
work_mem = 16MB
maintenance_work_mem = 1GB
effective_cache_size = 16GB
# 修改连接参数
max_connections = 200
superuser_reserved_connections = 10
idle_in_transaction_session_timeout = 300000
# 修改写入性能参数
wal_buffers = 32MB
checkpoint_completion_target = 0.9
max_wal_size = 2GB
min_wal_size = 1GB
# 保存并退出
3.1.2 重启PostgreSQL
$ sudo systemctl restart postgresql
# 验证服务状态
$ sudo systemctl status postgresql
● postgresql.service – PostgreSQL database server
Loaded: loaded (/usr/lib/systemd/system/postgresql.service; enabled; vendor preset: disabled)
Active: active (running) since Fri 2026-04-06 10:00:00 UTC; 1min ago
Main PID: 12345 (postgres)
Tasks: 8
Memory: 4.2G
CPU: 100ms
CGroup: /system.slice/postgresql.service
├─12345 /usr/bin/postgres -D /postgresql/fgdata
├─12346 postgres: logger process
├─12348 postgres: checkpointer process
├─12349 postgres: writer process
├─12350 postgres: wal writer process
├─12351 postgres: autovacuum launcher process
├─12352 postgres: stats collector process
└─12353 postgres: bgworker: logical replication launcher
3.2 参数调优
3.2.1 性能监控
$ psql -U fgedu -d fgedudb -c “SELECT * FROM pg_stat_database WHERE datname = ‘fgedudb’;”
datid | datname | numbackends | xact_commit | xact_rollback | blks_read | blks_hit | tup_returned | tup_fetched | tup_inserted | tup_updated | tup_deleted | conflicts | temp_files | temp_bytes | deadlocks | checksum_failures | checksum_last_failure | stats_reset
——-+———-+————-+————-+—————+———–+———-+————–+————-+————–+————-+————-+———–+————+————+———–+——————+————————+——————————-
16384 | fgedudb | 5 | 12345 | 678 | 123456 | 987654 | 1234567 | 987654 | 123456 | 67890 | 12345 | 0 | 123 | 123456789 | 0 | 0 | | 2026-04-06 09:00:00.000000+00
# 监控缓冲区使用情况
$ psql -U fgedu -d fgedudb -c “SELECT * FROM pg_stat_bgwriter;”
checkpoints_timed | checkpoints_req | checkpoint_write_time | checkpoint_sync_time | buffers_checkpoint | buffers_clean | maxwritten_clean | buffers_backend | buffers_backend_fsync | buffers_alloc | stats_reset
——————-+—————–+———————–+———————-+———————+—————+——————-+——————+————————+—————+——————————-
123 | 45 | 1234567 | 89012 | 123456 | 78901 | 1234 | 56789 | 0 | 1234567 | 2026-04-06 09:00:00.000000+00
3.3 配置验证
3.3.1 验证配置参数
$ psql -U fgedu -d fgedudb -c “SHOW shared_buffers;”
shared_buffers
—————-
4GB
$ psql -U fgedu -d fgedudb -c “SHOW work_mem;”
work_mem
———-
16MB
$ psql -U fgedu -d fgedudb -c “SHOW max_connections;”
max_connections
—————–
200
$ psql -U fgedu -d fgedudb -c “SHOW wal_buffers;”
wal_buffers
————-
32MB
Part04-生产案例与实战讲解
4.1 内存参数调优案例
4.1.1 大内存服务器配置
# 编辑postgresql.conf文件
$ sudo vi /postgresql/fgdata/postgresql.conf
# 内存参数配置
shared_buffers = 16GB
work_mem = 32MB
maintenance_work_mem = 2GB
effective_cache_size = 48GB
temp_buffers = 64MB
# 重启PostgreSQL
$ sudo systemctl restart postgresql
# 验证配置
$ psql -U fgedu -d fgedudb -c “SHOW shared_buffers;”
shared_buffers
—————-
16GB
$ psql -U fgedu -d fgedudb -c “SHOW effective_cache_size;”
effective_cache_size
———————-
48GB
# 测试性能
$ pgbench -h fgedu.localhost -U fgedu -d fgedudb -c 50 -j 8 -T 60
# 输出示例
starting vacuum…end.
transaction type:
scaling factor: 100
query mode: simple
number of clients: 50
number of threads: 8
duration: 60 s
number of transactions actually processed: 123456
latency average = 24.321 ms
tps = 2055.678901 (including connections establishing)
tps = 2056.789012 (excluding connections establishing)
4.2 连接参数调优案例
4.2.1 高并发场景配置
# 编辑postgresql.conf文件
$ sudo vi /postgresql/fgdata/postgresql.conf
# 连接参数配置
max_connections = 500
superuser_reserved_connections = 10
idle_in_transaction_session_timeout = 300000
statement_timeout = 30000
# 内存参数调整（适应高并发）
work_mem = 8MB # 减少每个连接的工作内存
# 重启PostgreSQL
$ sudo systemctl restart postgresql
# 验证配置
$ psql -U fgedu -d fgedudb -c “SHOW max_connections;”
max_connections
—————–
500
$ psql -U fgedu -d fgedudb -c “SHOW idle_in_transaction_session_timeout;”
idle_in_transaction_session_timeout
————————————–
300000ms
# 测试并发连接
$ for i in {1..100}; do psql -h fgedu.localhost -U fgedu -d fgedudb -c “SELECT 1;” & done
# 查看连接数
$ psql -U fgedu -d fgedudb -c “SELECT count(*) FROM pg_stat_activity;”
count
——-
101
4.3 写入性能调优案例
4.3.1 高写入场景配置
# 编辑postgresql.conf文件
$ sudo vi /postgresql/fgdata/postgresql.conf
# 写入性能参数配置
wal_buffers = 64MB
checkpoint_completion_target = 0.9
max_wal_size = 4GB
min_wal_size = 2GB
synchronous_commit = off
wal_writer_delay = 100ms
# 重启PostgreSQL
$ sudo systemctl restart postgresql
# 验证配置
$ psql -U fgedu -d fgedudb -c “SHOW wal_buffers;”
wal_buffers
————-
64MB
$ psql -U fgedu -d fgedudb -c “SHOW synchronous_commit;”
synchronous_commit
——————–
off
# 测试写入性能
$ psql -U fgedu -d fgedudb -c “CREATE TABLE fgedu_test (id SERIAL PRIMARY KEY, data TEXT);”
$ time psql -U fgedu -d fgedudb -c “INSERT INTO fgedu_test (data) SELECT ‘test’ || generate_series(1, 100000);”
# 输出示例
INSERT 0 100000
real 0m3.456s
user 0m0.012s
sys 0m0.004s
Part05-风哥经验总结与分享
5.1 服务器配置最佳实践
PostgreSQL服务器配置的最佳实践：
- 根据硬件配置调整：根据服务器的CPU、内存、存储等硬件配置调整参数
- 根据应用需求调整：根据应用的读写比例、并发量等需求调整参数
- 监控与调整：定期监控系统性能，根据实际情况调整参数
- 逐步调整：参数调整应逐步进行，每次调整后观察系统性能变化
- 备份配置：在调整配置前，备份原配置文件，以便在出现问题时恢复
- 文档化：记录配置调整的原因和效果，形成配置管理文档
5.2 风哥经验分享
1. 内存配置是基础：合理配置shared_buffers和work_mem，充分利用系统内存
2. 连接数要适度：根据服务器能力设置合理的max_connections，避免连接过多导致系统崩溃
3. 写入性能要平衡：在数据安全性和写入性能之间找到平衡点
4. 监控是关键：建立完善的监控机制，及时发现性能问题
5. 调优要持续：数据库性能是一个动态过程，需要持续监控和调优
6. 测试是保障：在调整配置前，进行充分的测试，确保不会影响生产环境
通过合理的服务器配置，可以显著提高PostgreSQL的性能和稳定性，为业务提供更好的支持。from PostgreSQL视频:www.itpux.com
5.3 常见问题与解决方案
PostgreSQL服务器配置常见问题与解决方案：
症状：PostgreSQL进程因内存不足而崩溃
解决方案：
– 增加服务器内存
– 减少shared_buffers和work_mem的配置
– 减少max_connections的配置
# 常见问题2：连接数过多
症状：无法建立新连接，出现”connection limit exceeded”错误
解决方案：
– 增加max_connections配置
– 使用连接池管理连接
– 优化应用程序，减少连接数
# 常见问题3：写入性能下降
症状：写入操作变慢，系统响应时间延长
解决方案：
– 增加wal_buffers配置
– 调整checkpoint参数
– 考虑使用SSD存储
# 常见问题4：查询性能下降
症状：查询执行时间变长，系统负载增加
解决方案：
– 优化查询语句
– 增加effective_cache_size配置
– 调整work_mem配置
# 常见问题5：系统崩溃
症状：PostgreSQL进程意外终止
解决方案：
– 检查系统日志，找出崩溃原因
– 调整相关配置参数
– 考虑硬件问题，如内存故障
本文由风哥教程整理发布,仅用于学习测试使用,转载注明出处:http://www.fgedu.net.cn/10327.html
联系我们
在线咨询：
微信号：itpux-com
工作日：9:30-18:30，节假日休息
