PostgreSQL教程FG271-PG单机部署实战：企业级生产环境配置
本文档风哥主要介绍PostgreSQL单机部署的企业级生产环境配置，包括PostgreSQL单机部署的概念、架构、硬件与软件要求、部署规划、安装步骤、性能优化、备份策略等内容，风哥教程参考PostgreSQL官方文档Installation和Server Administration内容编写，适合DBA人员在学习和测试中使用，如果要应用于生产环境则需要自行确认。更多视频教程www.fgedu.net.cn
Part01-基础概念与理论知识
1.1 PostgreSQL单机部署的概念
PostgreSQL单机部署是指在单个服务器上安装和运行PostgreSQL数据库的部署方式。这种部署方式结构简单，易于管理，适合中小规模的应用场景。虽然单机部署在高可用性方面不如集群部署，但通过合理的配置和维护，可以满足大多数企业的业务需求。学习交流加群风哥微信: itpux-com
- 结构简单，易于管理和维护
- 硬件成本低，适合中小规模应用
- 部署和配置相对简单
- 单点故障风险较高
- 性能受限于单台服务器的资源
1.2 PostgreSQL单机部署架构
PostgreSQL单机部署的基本架构包括：
- PostgreSQL服务器：运行PostgreSQL数据库实例
- 存储系统：存储数据文件、WAL日志、配置文件等
- 客户端应用：连接到数据库的应用程序
- 备份系统：定期备份数据库数据
- 监控系统：监控数据库运行状态
1.3 PostgreSQL单机部署的硬件与软件要求
## 处理器（CPU）
– 推荐：4核及以上
– 生产环境：8核及以上
– 高并发场景：16核及以上
## 内存（RAM）
– 推荐：8GB及以上
– 生产环境：16GB及以上
– 大数据场景：32GB及以上
## 存储
– 推荐：SSD存储
– 容量：根据数据量规划，预留50%以上空间
– I/O性能：随机读写性能要求高
## 网络
– 推荐：千兆网卡
– 生产环境：万兆网卡
– 网络延迟：越低越好
# PostgreSQL单机部署软件要求
## 操作系统
– 推荐：Oracle Linux 9.3 / RHEL 9.3
– 可选：CentOS 8 / Ubuntu 22.04 LTS
– 内核版本：3.10及以上
## PostgreSQL版本
– 推荐：PostgreSQL 18
– 最低：PostgreSQL 14
## 依赖软件
– glibc 2.17及以上
– openssl 1.0.2及以上
– libreadline 6.0及以上
– zlib 1.2.3及以上
Part02-生产环境规划与建议
2.1 PostgreSQL单机部署规划
PostgreSQL单机部署规划包括以下内容：
## 1. 服务器规划
– 服务器选型：根据业务需求选择合适的服务器
– 硬件配置：CPU、内存、存储、网络等
– 操作系统：选择稳定的Linux发行版
## 2. 存储规划
– 数据目录：/postgresql/fgdata
– WAL目录：建议与数据目录分离，使用SSD
– 备份目录：/backup，建议使用独立存储
– 日志目录：/postgresql/log
## 3. 网络规划
– IP地址：固定IP地址
– 端口：默认5432，可根据需要修改
– 防火墙：开放数据库端口
– 网络安全：配置网络访问控制
## 4. 数据库规划
– 数据库名：fgedudb
– 表空间：默认表空间 + 业务表空间
– 角色管理：超级用户、应用用户、只读用户
– 权限管理：最小权限原则
## 5. 备份规划
– 全量备份：每天执行
– 增量备份：每小时执行
– WAL归档：实时归档
– 备份验证：定期验证备份有效性
## 6. 监控规划
– 系统监控：CPU、内存、磁盘、网络
– 数据库监控：连接数、查询性能、WAL生成
– 告警机制：设置合理的告警阈值
– 监控工具：Prometheus + Grafana
2.2 PostgreSQL单机部署配置建议
## postgresql.conf核心参数
### 内存参数
– shared_buffers = 25% of RAM
– work_mem = 32MB
– maintenance_work_mem = 1GB
– effective_cache_size = 50% of RAM
### WAL参数
– wal_level = replica
– synchronous_commit = on
– wal_buffers = 16MB
– checkpoint_timeout = 15min
– max_wal_size = 4GB
– min_wal_size = 80MB
### 连接参数
– max_connections = 100
– superfgedu_reserved_connections = 3
– idle_in_transaction_session_timeout = 30min
### 查询优化
– random_page_cost = 1.1 # SSD存储
– effective_io_concurrency = 200 # SSD存储
– work_mem = 32MB
– maintenance_work_mem = 1GB
### 统计信息
– autovacuum = on
– autovacuum_max_workers = 3
– autovacuum_naptime = 10min
– autovacuum_vacuum_scale_factor = 0.1
– autovacuum_analyze_scale_factor = 0.05
## pg_hba.conf配置
– local all pgsql peer
– local all all md5
– fgedu.net.cn all all 127.0.0.1/32 md5
– fgedu.net.cn all all ::1/128 md5
– fgedu.net.cn all all 192.168.1.0/24 md5 # 业务网络
2.3 PostgreSQL单机部署安全策略
PostgreSQL单机部署安全策略包括：
- 访问控制：通过pg_hba.conf限制访问来源
- 密码策略：使用强密码，定期更换
- 权限管理：遵循最小权限原则
- 加密：启用SSL连接
- 审计：启用审计日志
- 防火墙：配置防火墙规则
- 定期更新：及时应用安全补丁
Part03-生产环境项目实施方案
3.1 PostgreSQL单机部署安装步骤
3.1.1 PostgreSQL单机部署安装步骤
## 1. 系统准备
– 检查系统版本：
$ cat /etc/redhat-release
Oracle Linux Server release 9.3
– 关闭SELinux：
$ sudo setenforce 0
$ sudo sed -i ‘s/SELINUX=enforcing/SELINUX=permissive/g’ /etc/selinux/config
– 关闭防火墙（或开放端口）：
$ sudo systemctl stop firewalld
$ sudo systemctl disable firewalld
## 2. 安装PostgreSQL 18
– 添加PostgreSQL YUM仓库：
$ sudo tee /etc/yum.repos.d/pgdg.repo << 'EOF'
[pgdg18]
name=PostgreSQL 18 for RHEL/CentOS 9 - x86_64
baseurl=https://download.postgresql.org/pub/repos/yum/18/redhat/rhel-9-x86_64/
enabled=1
gpgcheck=1
gpgkey=https://download.postgresql.org/pub/repos/yum/RPM-GPG-KEY-PGDG
EOF
- 安装PostgreSQL：
$ sudo dnf install -y postgresql18-server postgresql18-contrib
## 3. 初始化数据库
- 初始化数据库集群：
$ sudo /postgresql/fgapp/bin/postgresql-18-setup --initdb
- 配置数据目录权限：
$ sudo chown -R pgsql: pgsql /postgresql/data18/
## 4. 配置PostgreSQL
- 编辑postgresql.conf：
$ sudo vi /postgresql/data18/postgresql.conf
# 修改以下参数
listen_addresses = '*'
port = 5432
shared_buffers = 4GB
work_mem = 32MB
maintenance_work_mem = 1GB
effective_cache_size = 8GB
wal_level = replica
checkpoint_timeout = 15min
max_wal_size = 4GB
min_wal_size = 80MB
max_connections = 100
- 编辑pg_hba.conf：
$ sudo vi /postgresql/data18/pg_hba.conf
# 添加以下内容
fgedu.net.cn all all 192.168.1.0/24 md5
## 5. 启动服务
- 启动PostgreSQL服务：
$ sudo systemctl start postgresql-18
- 设置开机自启：
$ sudo systemctl enable postgresql-18
- 检查服务状态：
$ sudo systemctl status postgresql-18
## 6. 创建用户和数据库
- 切换到postgres用户：
$ sudo -u pgsql psql
- 创建应用用户：
postgres=# CREATE USER fgedu WITH PASSWORD 'fgedu123';
- 创建数据库：
postgres=# CREATE DATABASE fgedudb OWNER fgedu;
- 授予权限：
postgres=# GRANT ALL PRIVILEGES ON DATABASE fgedudb TO fgedu;
- 退出psql：
postgres=# \q
## 7. 验证安装
- 连接数据库：
$ psql -U fgedu -d fgedudb -h localfgedu.net.cn
- 测试查询：
fgedudb=> SELECT version();
version
—————————————————————————————————————————–
PostgreSQL 18.0 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 11.4.1 20230605 (Red Hat 11.4.1-2), 64-bit
(1 row)
fgedudb=> CREATE TABLE fgedu_fgedus (id SERIAL PRIMARY KEY, name VARCHAR(50), email VARCHAR(100));
CREATE TABLE fgedu_ fgedudb=> INSERT INTO fgedu_fgedus (name, email) VALUES (‘风哥’, ‘fengge@fgedu.net.cn’);
INSERT 0 1
fgedudb=> SELECT * FROM fgedu_fgedus;
id | name | email
—-+——+———————
1 | 风哥 | fengge@fgedu.net.cn
(1 row)
3.2 PostgreSQL单机部署性能优化
3.2.1 PostgreSQL单机部署性能优化
## 1. 系统参数优化
– 编辑/etc/sysctl.conf：
$ sudo vi /etc/sysctl.conf
# 添加以下参数
# 共享内存
kernel.shmmax = 8589934592 # 8GB
kernel.shmall = 2097152
# 文件描述符
fs.file-max = 65536
# 网络
net.core.somaxconn = 1024
net.ipv4.tcp_max_syn_backlog = 4096
net.ipv4.tcp_fin_timeout = 30
# 应用参数
$ sudo sysctl -p
## 2. 存储优化
– 使用SSD存储
– 配置RAID 10
– 优化文件系统：
$ sudo mkfs.xfs -f -m crc=1 -m finobt=1 /dev/sdb1
– 挂载选项：
$ sudo vi /etc/fstab
/dev/sdb1 /postgresql xfs defaults,noatime,nodiratime 0 0
## 3. PostgreSQL参数优化
– 编辑postgresql.conf：
$ sudo vi /postgresql/data18/postgresql.conf
# 内存参数
shared_buffers = 4GB # 25% of RAM
work_mem = 32MB # 用于排序等操作
maintenance_work_mem = 1GB # 用于VACUUM等维护操作
effective_cache_size = 8GB # 50% of RAM
# WAL参数
wal_level = replica
synchronous_commit = on
wal_buffers = 16MB
checkpoint_timeout = 15min
max_wal_size = 4GB
min_wal_size = 80MB
# 查询优化
random_page_cost = 1.1 # SSD存储
effective_io_concurrency = 200 # SSD存储
work_mem = 32MB
# 连接参数
max_connections = 100
# 统计信息
autovacuum = on
autovacuum_max_workers = 3
autovacuum_naptime = 10min
autovacuum_vacuum_scale_factor = 0.1
autovacuum_analyze_scale_factor = 0.05
## 4. 索引优化
– 为频繁查询的列创建索引
– 使用合适的索引类型（B-tree、GiST、GIN等）
– 定期重建索引：
$ psql -U fgedu -d fgedudb -c “REINDEX TABLE fgedu_fgedus;”
## 5. 查询优化
– 使用EXPLAIN ANALYZE分析查询计划
– 优化SQL语句，避免全表扫描
– 使用参数化查询，避免SQL注入
– 合理使用分区表
## 6. 维护优化
– 定期执行VACUUM和ANALYZE：
$ psql -U fgedu -d fgedudb -c “VACUUM FULL ANALYZE;”
– 监控表膨胀：
$ psql -U fgedu -d fgedudb -c “SELECT relname, n_dead_tup FROM pg_stat_fgedu_tables WHERE n_dead_tup > 0;”
– 定期备份数据库：
$ pg_dump -U fgedu -d fgedudb -F c -f /backup/fgedudb.dump
3.3 PostgreSQL单机部署备份策略
3.3.1 PostgreSQL单机部署备份策略
## 1. 备份类型
– 逻辑备份：使用pg_dump/pg_dumpall
– 物理备份：使用pg_basebackup
– WAL日志备份：用于PITR恢复
## 2. 备份策略
– 全量备份：每天凌晨2点执行
– 增量备份：每小时执行
– WAL归档：实时归档
– 备份保留：保留7天的备份
## 3. 备份脚本
– 创建全量备份脚本：
$ sudo vi /usr/local/bin/pg_full_backup.sh
#!/bin/bash
# pg_full_backup.sh
# from:www.itpux.com.qq113257174.wx:itpux-com
# web: http://www.fgedu.net.cn
BACKUP_DIR=”/backup/full”
DATE=$(date +”%Y%m%d%H%M”)
mkdir -p $BACKUP_DIR
pg_dump -U fgedu -d fgedudb -F c -f “$BACKUP_DIR/fgedudb_$DATE.dump”
# 清理7天前的备份
find $BACKUP_DIR -name “*.dump” -mtime +7 -delete
– 创建WAL归档配置：
$ sudo vi /postgresql/data18/postgresql.conf
wal_level = replica
archive_mode = on
archive_command = ‘cp %p /backup/wal/%f’
– 创建归档目录：
$ sudo mkdir -p /backup/wal
$ sudo chown pgsql: pgsql /backup/wal
## 4. 备份验证
– 定期验证备份文件：
$ pg_restore -l /backup/full/fgedudb_202604020200.dump
– 测试恢复：
$ createdb -U fgedu fgedudb_test
$ pg_restore -U fgedu -d fgedudb_test /backup/full/fgedudb_202604020200.dump
$ psql -U fgedu -d fgedudb_test -c “SELECT count(*) FROM fgedu_fgedus;”
$ dropdb -U fgedu fgedudb_test
## 5. 恢复策略
– 全量恢复：使用pg_restore
– 时间点恢复（PITR）：使用基础备份+WAL日志
– 表级恢复：使用pg_restore恢复单个表
Part04-生产案例与实战讲解
4.1 PostgreSQL单机部署实战案例
案例：企业级PostgreSQL单机部署实战
## 项目背景
– 企业：某中型电商企业
– 业务：电商交易系统
– 数据量：约50GB
– 并发：高峰期50-100并发
## 部署规划
– 服务器：2核8GB内存，500GB SSD
– 操作系统：Oracle Linux 9.3
– PostgreSQL版本：18.0
– 数据目录：/postgresql/fgdata
– 备份目录：/backup
## 部署步骤
1. **系统准备**
– 安装Oracle Linux 9.3
– 配置网络和防火墙
– 安装必要的依赖包
2. **PostgreSQL安装**
– 添加PostgreSQL YUM仓库
– 安装PostgreSQL 18
– 初始化数据库
3. **配置优化**
– 系统参数优化
– PostgreSQL参数优化
– 存储配置
4. **安全配置**
– 配置pg_hba.conf
– 设置强密码
– 启用SSL
5. **备份配置**
– 配置WAL归档
– 创建备份脚本
– 设置定时任务
6. **监控配置**
– 部署Prometheus + Grafana
– 配置监控指标
– 设置告警
## 验证测试
– 连接测试：
$ psql -U fgedu -d fgedudb -h localfgedu.net.cn
– 性能测试：
$ pgbench -i -U fgedu -d fgedudb
$ pgbench -c 50 -j 2 -T 300 -U fgedu -d fgedudb
– 备份测试：
$ pg_dump -U fgedu -d fgedudb -F c -f /backup/test.dump
$ pg_restore -U fgedu -d fgedudb_test /backup/test.dump
## 部署结果
– 数据库服务正常运行
– 性能满足业务需求
– 备份策略有效
– 监控系统正常
4.2 PostgreSQL单机部署性能调优案例
案例：PostgreSQL单机部署性能调优
## 问题描述
– 系统：PostgreSQL 18.0
– 服务器：4核16GB内存，1TB SSD
– 问题：查询响应慢，CPU使用率高
## 分析步骤
1. **监控系统状态**
– 查看CPU使用率：
$ top
– 查看I/O性能：
$ iostat -x 1
– 查看内存使用：
$ free -h
2. **分析数据库状态**
– 查看连接数：
$ psql -U fgedu -d fgedudb -c “SELECT count(*) FROM pg_stat_activity;”
– 查看慢查询：
$ psql -U fgedu -d fgedudb -c “SELECT * FROM pg_stat_statements ORDER BY total_exec_time DESC LIMIT 10;”
– 查看表膨胀：
$ psql -U fgedu -d fgedudb -c “SELECT relname, n_dead_tup FROM pg_stat_fgedu_tables WHERE n_dead_tup > 0;”
3. **定位问题**
– 发现问题：
– 缺少必要的索引
– 共享内存设置过小
– 自动清理不及时
– 慢查询未优化
## 优化措施
1. **参数优化**
– 调整shared_buffers：
shared_buffers = 4GB # 25% of RAM
– 调整work_mem：
work_mem = 64MB
– 调整维护参数：
maintenance_work_mem = 2GB
– 调整WAL参数：
checkpoint_timeout = 30min
max_wal_size = 8GB
2. **索引优化**
– 为频繁查询的列创建索引：
$ psql -U fgedu -d fgedudb -c “CREATE INDEX idx_fgedu_fgedus_email ON fgedu_fgedus(email);”
– 重建碎片索引：
$ psql -U fgedu -d fgedudb -c “REINDEX TABLE fgedu_fgedus;”
3. **查询优化**
– 优化慢查询：
# 原查询
SELECT * FROM fgedu_fgedus WHERE created_at > ‘2026-01-01’;
# 优化后
SELECT id, name, email FROM fgedu_fgedus WHERE created_at > ‘2026-01-01’;
# 添加索引
CREATE INDEX idx_fgedu_fgedus_created_at ON fgedu_fgedus(created_at);
4. **维护优化**
– 执行VACUUM和ANALYZE：
$ psql -U fgedu -d fgedudb -c “VACUUM FULL ANALYZE;”
– 调整自动清理参数：
autovacuum_max_workers = 4
autovacuum_naptime = 5min
autovacuum_vacuum_scale_factor = 0.05
## 优化结果
– 查询响应时间：从500ms降至50ms
– CPU使用率：从80%降至30%
– 系统稳定性：显著提高
– 业务满意度：明显提升
4.3 PostgreSQL单机部署备份恢复案例
案例：PostgreSQL单机部署备份恢复
## 故障描述
– 系统：PostgreSQL 18.0
– 故障：数据文件损坏
– 影响：数据库无法启动
## 恢复步骤
1. **故障确认**
– 检查数据库状态：
$ pg_ctl status -D /postgresql/fgdata
– 查看日志：
$ tail -f /postgresql/fgdata/log/postgresql-*.log
2. **准备恢复**
– 停止数据库服务：
$ pg_ctl stop -D /postgresql/fgdata
– 备份损坏的数据目录：
$ cp -r /postgresql/fgdata /backup/corrupted_data/
3. **执行恢复**
– 使用最近的全量备份恢复：
$ pg_restore -U fgedu -d fgedudb /backup/full/fgedudb_202604020200.dump
– 或者使用基础备份+WAL恢复（PITR）：
# 恢复基础备份
$ pg_basebackup -D /postgresql/fgdata -F p -X stream -c fast -h 192.168.1.100 -U replication
# 创建recovery.conf
$ vi /postgresql/fgdata/recovery.conf
restore_command = ‘cp /backup/wal/%f %p’
recovery_target_time = ‘2026-04-02 10:00:00’
# 启动数据库
$ pg_ctl start -D /postgresql/fgdata
4. **验证恢复**
– 检查数据库状态：
$ pg_isready -h localfgedu.net.cn -U fgedu
– 验证数据完整性：
$ psql -U fgedu -d fgedudb -c “SELECT count(*) FROM fgedu_fgedus;”
– 验证业务功能：
$ psql -U fgedu -d fgedudb -c “SELECT * FROM fgedu_fgedus WHERE id = 1;”
5. **恢复后处理**
– 执行VACUUM和ANALYZE：
$ psql -U fgedu -d fgedudb -c “VACUUM FULL ANALYZE;”
– 重新创建索引：
$ psql -U fgedu -d fgedudb -c “REINDEX DATABASE fgedudb;”
– 验证备份策略：
$ pg_dump -U fgedu -d fgedudb -F c -f /backup/test.dump
## 恢复结果
– 数据库成功恢复
– 数据完整性保持
– 服务正常运行
– 业务功能恢复
Part05-风哥经验总结与分享
5.1 PostgreSQL单机部署最佳实践
PostgreSQL单机部署最佳实践：
- 硬件选择：选择高性能的CPU、足够的内存和SSD存储
- 系统配置：优化系统参数，关闭不必要的服务
- 存储规划：数据目录和WAL目录分离，使用RAID 10
- 参数调优：根据硬件配置和业务需求调整PostgreSQL参数
- 安全配置：限制访问来源，使用强密码，启用SSL
- 备份策略：实施多重备份策略，定期验证备份有效性
- 监控系统：部署完善的监控系统，及时发现和解决问题
- 维护计划：制定定期维护计划，包括VACUUM、索引重建等
- 文档管理：保持系统文档的及时更新，包括架构、配置、操作步骤等
- 应急演练：定期进行故障演练，提高应急处理能力
5.2 PostgreSQL单机部署常见问题与解决方案
## 1. 数据库无法启动
– **问题**：启动时出现错误
– **原因**：配置错误、数据文件损坏、端口被占用
– **解决方案**：
– 检查日志文件
– 验证配置文件
– 检查端口占用
– 修复数据文件
## 2. 性能下降
– **问题**：查询响应慢、CPU使用率高
– **原因**：缺少索引、参数配置不合理、表膨胀
– **解决方案**：
– 添加必要的索引
– 优化PostgreSQL参数
– 执行VACUUM和ANALYZE
– 优化慢查询
## 3. 连接数超限
– **问题**：无法建立新连接
– **原因**：max_connections设置过小、连接泄漏
– **解决方案**：
– 增加max_connections
– 检查应用连接池配置
– 查找并关闭空闲连接
## 4. 磁盘空间不足
– **问题**：磁盘空间耗尽
– **原因**：数据增长、WAL日志积累、备份文件未清理
– **解决方案**：
– 清理不必要的文件
– 调整WAL保留策略
– 扩大磁盘空间
– 实施数据归档策略
## 5. 备份失败
– **问题**：备份过程中出现错误
– **原因**：权限问题、磁盘空间不足、网络故障
– **解决方案**：
– 检查权限设置
– 确保足够的磁盘空间
– 验证网络连接
– 检查备份脚本
## 6. 安全问题
– **问题**：未授权访问、密码泄露
– **原因**：配置不当、密码策略薄弱
– **解决方案**：
– 配置pg_hba.conf限制访问
– 使用强密码
– 启用SSL
– 定期进行安全审计
5.3 PostgreSQL单机部署维护计划
## 日常维护（每天）
– 检查数据库状态：
$ pg_isready -h localfgedu.net.cn
– 检查备份状态：
$ ls -la /backup/full/
– 检查磁盘空间：
$ df -h
– 检查日志文件：
$ tail -f /postgresql/fgdata/log/postgresql-*.log
## 周维护（每周）
– 执行VACUUM和ANALYZE：
$ psql -U fgedu -d fgedudb -c “VACUUM FULL ANALYZE;”
– 重建碎片索引：
$ psql -U fgedu -d fgedudb -c “REINDEX DATABASE fgedudb;”
– 验证备份有效性：
$ pg_restore -l /backup/full/fgedudb_$(date +”%Y%m%d”)0200.dump
– 检查表膨胀：
$ psql -U fgedu -d fgedudb -c “SELECT relname, n_dead_tup FROM pg_stat_fgedu_tables WHERE n_dead_tup > 0;”
## 月维护（每月）
– 系统更新：
$ sudo dnf update -y
– PostgreSQL补丁更新：
$ sudo dnf update postgresql18-server
– 性能评估：
$ pgbench -c 50 -j 2 -T 300 -U fgedu -d fgedudb
– 安全审计：
$ psql -U fgedu -d fgedudb -c “SELECT * FROM pg_stat_fgedu_functions;”
– 备份策略评估：
$ du -sh /backup/
## 季度维护（每季度）
– 全面备份测试：
$ pg_restore -U fgedu -d fgedudb_test /backup/full/fgedudb_$(date +”%Y%m%d”)0200.dump
– 系统参数优化：
$ sudo vi /etc/sysctl.conf
– PostgreSQL参数调整：
$ sudo vi /postgresql/data18/postgresql.conf
– 应急演练：
模拟故障并执行恢复流程
– 性能基准测试：
$ pgbench -i -s 100 -U fgedu -d fgedudb
$ pgbench -c 100 -j 4 -T 600 -U fgedu -d fgedudb
本文由风哥教程整理发布,仅用于学习测试使用,转载注明出处:http://www.fgedu.net.cn/10327.html
联系我们
在线咨询：
微信号：itpux-com
工作日：9:30-18:30，节假日休息
