在 Debian 上配置 PostgreSQL 的完整步骤

一 安装与启动

• 更新索引并安装服务器与扩展包：
  • sudo apt update
  • sudo apt install -y postgresql postgresql-contrib
• 启动服务并设为开机自启：
  • sudo systemctl start postgresql
  • sudo systemctl enable postgresql
• 安装完成后会自动初始化并运行，Debian 的 APT 包通常会创建默认实例（如 /etc/postgresql//main/）。

二 基础安全与账户设置

• 切换到 postgres 系统用户并进入 psql：
  • sudo -i -u postgres
  • psql
• 修改 postgres 超级用户密码：
  • ALTER USER postgres WITH PASSWORD ‘YourStrongP@ssw0rd’;
• 创建业务用户与数据库并授权：
  • CREATE USER appuser WITH ENCRYPTED PASSWORD ‘AppP@ssw0rd’;
  • CREATE DATABASE appdb OWNER appuser;
  • GRANT ALL PRIVILEGES ON DATABASE appdb TO appuser;
• 退出 psql：
  • \q
• 以上操作完成后，本地可通过 psql 使用新用户连接测试。

三 远程访问与防火墙

• 编辑配置文件（将 替换为实际版本，如 15 或 16）：
  • 打开：sudo nano /etc/postgresql//main/postgresql.conf
  • 关键参数：
    • listen_addresses = ‘*’ # 允许来自任意地址的连接
    • port = 5432
• 配置客户端认证规则：
  • 打开：sudo nano /etc/postgresql//main/pg_hba.conf
  • 建议仅对需要的网段开放，示例：

    • 本地 IPv4

    • host all all 127.0.0.1/32 md5

    • 本地 IPv6

    • host all all ::1/128 md5

    • 内网网段示例（请按实际替换）

    • host all all 192.168.1.0/24 scram-sha-256
• 应用配置并重启：
  • 仅调整认证规则时可热加载：sudo systemctl reload postgresql
  • 修改了监听地址或端口需重启：sudo systemctl restart postgresql
• 防火墙放行（如使用 UFW）：
  • sudo ufw allow 5432/tcp
  • 如部署在云服务器，还需在云安全组放行 TCP 5432。
• 远程连接测试（从客户端）：
  • psql -h <服务器IP> -U appuser -d appdb
• 安全提示：生产环境避免使用 0.0.0.0/0，优先采用最小权限与更安全的认证方式（如 scram-sha-256）。

四 性能与日常维护

• 常用性能参数（postgresql.conf，按内存与负载调优，示例为保守值）：
  • shared_buffers = 128MB # 通常设为内存的 1/4 左右
  • effective_cache_size = 1GB # 通常设为内存的 1/2 左右
  • work_mem = 64MB # 每个排序/哈希操作可用内存
  • maintenance_work_mem = 128MB # VACUUM/创建索引等维护操作内存
  • max_connections = 100 # 根据业务并发调整
• 维护与统计：
  • 手动执行：VACUUM ANALYZE;
  • 启用扩展以分析慢 SQL：
    • CREATE EXTENSION IF NOT EXISTS pg_stat_statements;
    • 查询：SELECT * FROM pg_stat_statements ORDER BY total_time DESC LIMIT 10;
• 修改参数后建议先 reload，涉及连接/监听变更再 restart。

五 常见问题排查

• 服务无法启动或端口被占用：
  • 检查端口：sudo ss -tulnp | grep 5432
  • 若异常残留 pid 文件导致失败，排查并清理后再启动（注意数据安全）。
• 忘记 postgres 密码：
  • 以 postgres 用户进入 psql 后执行：ALTER USER postgres WITH PASSWORD ‘NewP@ssw0rd’;
• 远程连接被拒绝：
  • 核对 postgresql.conf 的 listen_addresses 与 pg_hba.conf 的网段/认证方式是否匹配，并确认防火墙与安全组已放行 5432/TCP。