PostgreSQL 运维手册

type
Post
status
Published
date
Jan 22, 2026
slug
summary
tags
运维
Windows server
category
技术分享
icon
password

一、备份与恢复

1.1 备份工具 pg_dump

  • 全库备份（Windows 命令行）：
    • 压缩备份（需安装 gzip for Windows 或使用 PowerShell）：
      • 仅结构备份：
        • 仅数据备份：

          1.2 恢复工具

          • 文本格式恢复：
            • 自定义格式恢复：

              1.3 备份策略建议（Windows 任务计划程序）

              场景
              备份类型
              频率
              存储位置
              生产环境
              全量 + 增量
              全量每周，增量每日
              D:\\PgBackup\\ + 网络存储
              开发测试
              全量备份
              按需
              C:\\Backups\\
              关键业务
              连续归档（pg_basebackup）
              实时
              独立备份服务器
              ✅ 建议使用 Windows 任务计划程序 自动执行备份脚本。

              二、性能监控与优化

              2.1 慢查询分析

              • 启用 pg_stat_statements（在 postgresql.conf 中添加）：
                • 查看最慢的查询：

                  2.2 执行次数与调用统计

                  • 查看查询执行频率：

                    2.3 读写次数监控

                    • 表级读写统计：

                      三、日常运维操作手册（Windows 环境）

                      3.1 数据库管理

                      • 启动/停止 PostgreSQL 服务：
                          • 服务名可能因版本而异，可在“服务”管理中查看。
                      • 登录数据库（使用 psql）：

                        3.2 模式与权限管理

                        • 创建模式：
                          • 设置搜索路径：

                            3.3 远程连接配置（Windows）

                            • 编辑 pg_hba.conf（位于 C:\\Program Files\\PostgreSQL\\15\\data\\）：
                              • 重启服务生效：

                                3.4 防火墙配置（Windows 防火墙）

                                • 开放端口 5432：

                                  3.5 密码重置流程（Windows）

                                  1. 备份 pg_hba.conf
                                  1. 将本地认证改为 trust
                                  1. 重启 PostgreSQL 服务
                                  1. 使用 psql 无密码登录
                                  1. 执行：
                                    1. 恢复 pg_hba.conf 并重启服务

                                    四、Windows 特有运维注意事项

                                    4.1 路径与权限

                                    • PostgreSQL 默认安装路径：C:\\Program Files\\PostgreSQL\\15\\
                                    • 数据目录：C:\\Program Files\\PostgreSQL\\15\\data\\
                                    • 日志目录：C:\\Program Files\\PostgreSQL\\15\\data\\log\\
                                    • 确保运行 PostgreSQL 服务的 Windows 账户对该目录有完全控制权限。

                                    4.2 环境变量设置

                                    将 PostgreSQL 的 bin 目录添加到系统环境变量 PATH：

                                    4.3 备份脚本示例（Windows 批处理）

                                    五、运维检查清单（Windows 版）

                                    • ✅ 每周全量备份（使用任务计划程序定时执行）
                                    • ✅ 每日检查备份日志与磁盘空间
                                    • ✅ 每月审计用户权限（\\du、\\z）
                                    • ✅ 定期清理无用数据，执行 VACUUM ANALYZE
                                    • ✅ 监控 Windows 事件日志中的 PostgreSQL 错误
                                    • ✅ 更新 Windows 防火墙规则与 pg_hba.conf
                                    • ✅ 定期重启服务以释放内存（可选，建议在维护窗口）

                                    六、推荐工具（Windows 环境）

                                    • 图形化管理工具：pgAdmin 4（支持 Windows 安装）
                                    • 监控工具：pg_stat_statements、Windows 性能监视器（PerfMon）
                                    • 日志查看：Windows 事件查看器 + PostgreSQL 日志文件
                                    • 自动化备份：Windows 任务计划程序 + PowerShell 脚本

                                    PostgreSQL 配置文件关键修改

                                    📁 配置文件位置

                                    PostgreSQL 在 Windows 上的主要配置文件位于：
                                    • postgresql.conf：主要配置文件（参数设置）
                                    • pg_hba.conf：客户端认证配置文件
                                    • postgresql.auto.conf：自动生成的配置文件（优先级最高）
                                    默认路径（以 PostgreSQL 15 为例）：

                                    🔧 postgresql.conf 关键参数修改

                                    1. 连接与网络配置

                                    2. 内存与缓存配置

                                    3. 日志与监控配置

                                    4. WAL（Write-Ahead Logging）配置

                                    5. 性能优化参数

                                    6. 自动维护配置

                                    🔐 pg_hba.conf 关键配置

                                    1. 配置格式

                                    2. 常用认证方法

                                    3. 认证方法说明

                                    • trust：无需密码（仅用于受信本地连接）
                                    • md5：密码MD5加密（兼容性好）
                                    • scram-sha-256：强密码加密（推荐）
                                    • cert：SSL证书认证（最安全）

                                    ⚡ 性能调优关键参数示例

                                    以下是一个针对 8GB 内存、4核CPU的 Windows Server 的配置示例：

                                    🛠️ Windows 特定配置

                                    1. 优化Windows内核参数（通过注册表）

                                    2. 启用大页面支持（在postgresql.conf中）

                                    3. 调整I/O调度（如果使用SSD）

                                    📋 配置修改与生效步骤

                                    步骤1：备份原配置文件

                                    步骤2：编辑配置文件

                                    步骤3：检查配置语法

                                    步骤4：应用配置

                                    步骤5：验证配置

                                    🚨 重要注意事项

                                    1. 修改前务必备份原始配置文件
                                    1. 一次只修改少量参数，逐步测试
                                    1. 修改 shared_buffers 后需要重启服务
                                    1. pg_hba.conf 修改后只需要重新加载（reload）
                                    1. Windows 上 PostgreSQL 不能使用信号（SIGHUP），必须通过服务管理器或命令行操作
                                    1. 生产环境修改配置建议在维护窗口进行
                                    1. 使用 pg_settings 系统视图验证配置生效

                                    📊 监控配置生效

                                     
                                    Loading...