中文

Linux 与网络

安装并加固 PostgreSQL

在 Debian 或 Ubuntu 上从官方软件源安装 PostgreSQL 18:角色与权限、基于 TLS 的远程访问、自动备份以及恢复测试。

阅读约 8 分钟, 更新于 2026年11月18日

本页内容

您的参数

只需填写一次,指南中的命令会自动更新。

本指南将说明如何从项目官方软件源在您的 VPS 或独立服务器上安装 PostgreSQL,创建权限受限的角色,在需要时开启加密的远程访问,并配置您确实能够恢复的备份。

完成后,PostgreSQL 将只在本地监听,每个应用程序都有自己独立的角色,并且每天生成一份备份,再通过恢复测试加以验证。

订购服务器

要托管您的数据库,HostMyServers 提供多种方案:

先决条件

  • 运行 Debian 12 或 13,或 Ubuntu 22.04 或 24.04 的服务器
  • 拥有 sudo 权限用户的 SSH 访问
  • 系统已更新(sudo apt update && sudo apt upgrade)
  • 仅限直接远程访问:需要一个指向该服务器的域名

需要开放的端口

用途 端口 是否需要开放?
同一服务器上的应用程序 5432/tcp,仅本地 否
通过 SSH 隧道远程访问(推荐) 22/tcp,或您的 SSH 端口 管理时已开放
基于 TLS 的直接远程访问 5432/tcp 是,仅对您的客户端 IP 地址开放

从官方软件源安装 PostgreSQL

Debian 和 Ubuntu 自带 PostgreSQL,但其版本在发行版的整个生命周期内保持不变。PGDG 软件源(apt.postgresql.org)提供所有仍在维护的版本。

截至撰写本文时,最新的主版本是 PostgreSQL 18(18.7)。请查阅版本策略了解当前版本。

  1. 安装所需工具并获取软件源密钥:

    终端
    sudo apt install -y curl ca-certificates gpg
    sudo install -d /usr/share/postgresql-common/pgdg
    sudo curl -fsSL -o /usr/share/postgresql-common/pgdg/apt.postgresql.org.asc https://www.postgresql.org/media/keys/ACCC4CF8.asc
  2. 检查密钥指纹:

    终端
    gpg --show-key /usr/share/postgresql-common/pgdg/apt.postgresql.org.asc

    它必须与 B97B0AFCAA1A47F044F244A07FCC7D46ACCC4CF8 一致。如果不一致,请就此停止。

  3. 声明软件源:

    终端
    . /etc/os-release
    sudo tee /etc/apt/sources.list.d/pgdg.sources > /dev/null <<EOF
    Types: deb
    URIs: https://apt.postgresql.org/pub/repos/apt
    Suites: ${VERSION_CODENAME}-pgdg
    Architectures: $(dpkg --print-architecture)
    Components: main
    Signed-By: /usr/share/postgresql-common/pgdg/apt.postgresql.org.asc
    EOF
  4. 安装服务器:

    终端
    sudo apt update
    sudo apt install -y postgresql-18

    该软件包会自动创建并启动一个名为 18/main 的实例。

验证安装

终端
pg_lsclusters
sudo systemctl status postgresql@18-main --no-pager
sudo -u postgres psql -c "SELECT version();"
sudo ss -tlnp | grep 5432

pg_lsclusters 必须显示实例 18 main,状态为 online。ss 必须显示在 127.0.0.1:5432 和 [::1]:5432 上监听,而不是 0.0.0.0。

项目 位置
配置 /etc/postgresql/18/main/
附加设置 /etc/postgresql/18/main/conf.d/
访问规则 /etc/postgresql/18/main/pg_hba.conf
数据 /var/lib/postgresql/18/main/
日志 /var/log/postgresql/postgresql-18-main.log

了解身份验证

pg_hba.conf 文件决定谁可以连接、可以连接到哪个数据库,以及使用何种方法。PostgreSQL 从上到下读取其中的各行,并应用第一条匹配的规则。如果该方法失败,则不会再尝试后面的行。

显示当前生效的规则:

终端
sudo -u postgres psql -c "SELECT line_number, type, database, user_name, address, auth_method, error FROM pg_hba_file_rules;"

在全新安装的系统上,您应当看到两种方法:

  • peer:用于通过套接字的本地连接,系统用户名必须与角色名一致。这就是 sudo -u postgres psql 无需密码即可运行的原因。
  • scram-sha-256:用于来自 127.0.0.1 和 ::1 的 TCP 连接,需要输入密码。

创建角色和数据库

不要在应用程序中使用超级用户 postgres。下面的示例创建一个数据库 appdb、一个属主角色 app_owner 和一个只读角色 app_reader。

  1. 创建角色。每条命令都会要求输入密码,这样密码就不会留在 shell 历史记录中:

    终端
    sudo -u postgres createuser --pwprompt app_owner
    sudo -u postgres createuser --pwprompt app_reader
  2. 创建数据库:

    终端
    sudo -u postgres createdb --owner=app_owner appdb
  3. 限制对该数据库的访问:

    终端
    sudo -u postgres psql -d appdb
    SQL
    REVOKE ALL ON DATABASE appdb FROM PUBLIC;
    GRANT CONNECT ON DATABASE appdb TO app_reader;
    GRANT USAGE ON SCHEMA public TO app_reader;
    GRANT SELECT ON ALL TABLES IN SCHEMA public TO app_reader;
    ALTER DEFAULT PRIVILEGES FOR ROLE app_owner IN SCHEMA public GRANT SELECT ON TABLES TO app_reader;
    \q

    最后一条语句还会让 app_reader 对 app_owner 今后创建的表拥有读取权限。

  4. 使用密码验证连接:

    终端
    psql -h 127.0.0.1 -U app_owner -d appdb -c "CREATE TABLE restore_check (id int PRIMARY KEY, note text);"
    psql -h 127.0.0.1 -U app_owner -d appdb -c "INSERT INTO restore_check VALUES (1, 'sauvegarde OK');"
    psql -h 127.0.0.1 -U app_reader -d appdb -c "SELECT * FROM restore_check;"

    使用 app_reader 执行写入必须失败,并提示 permission denied for table restore_check。表 restore_check 将在后面用于检查您的恢复结果。

远程访问数据库

推荐方式:SSH 隧道

隧道无需修改 PostgreSQL 或防火墙。在您的电脑上执行:

终端
ssh -N -L 5433:127.0.0.1:5432 您的用户名@您的服务器IP

请保持该命令运行,然后让您的客户端连接本地端口 5433:

终端
psql -h 127.0.0.1 -p 5433 -U app_owner -d appdb

进阶方式:基于 TLS 的直接连接

如果外部服务无法通过 SSH 连接,请使用此方式。它需要一个域名(例如 db.您的域名.com)和一个有效的证书:请参阅我们的指南使用 Let's Encrypt 安装 SSL 证书,通过 Certbot 获取证书。

  1. 创建一个脚本,将证书复制给 PostgreSQL,并在每次续期时执行:

    终端
    sudo nano /etc/letsencrypt/renewal-hooks/deploy/postgresql.sh
    终端
    #!/bin/sh
    set -e
    DOMAIN="db.您的域名.com"
    [ "$RENEWED_LINEAGE" = "/etc/letsencrypt/live/$DOMAIN" ] || exit 0
    install -o postgres -g postgres -m 644 "$RENEWED_LINEAGE/fullchain.pem" /etc/postgresql/18/main/server.crt
    install -o postgres -g postgres -m 600 "$RENEWED_LINEAGE/privkey.pem" /etc/postgresql/18/main/server.key
    systemctl reload postgresql@18-main
  2. 赋予其可执行权限,并先运行一次:

    终端
    sudo chmod 755 /etc/letsencrypt/renewal-hooks/deploy/postgresql.sh
    sudo RENEWED_LINEAGE=/etc/letsencrypt/live/db.您的域名.com /etc/letsencrypt/renewal-hooks/deploy/postgresql.sh
  3. 创建配置文件:

    终端
    sudo nano /etc/postgresql/18/main/conf.d/90-remote-access.conf
    配置
    listen_addresses = '*'
    ssl = on
    ssl_cert_file = '/etc/postgresql/18/main/server.crt'
    ssl_key_file = '/etc/postgresql/18/main/server.key'
    ssl_min_protocol_version = 'TLSv1.2'
  4. 在 /etc/postgresql/18/main/pg_hba.conf 末尾添加一条规则,限定为某个数据库、某个角色和某个地址:

    文本
    hostssl appdb app_owner 您的IP/32 scram-sha-256

    hostssl 会拒绝任何未加密的连接。对于 IPv6 地址,请使用后缀 /128。

  5. 在防火墙中只允许您的地址。使用 UFW 时:

    终端
    sudo ufw allow from 您的IP to any port 5432 proto tcp
  6. 重启 PostgreSQL,因为仅重新加载配置不会使 listen_addresses 生效:

    终端
    sudo systemctl restart postgresql@18-main
    sudo -u postgres psql -c "SELECT line_number, error FROM pg_hba_file_rules WHERE error IS NOT NULL;"

    第二条命令不应返回任何行。

  7. 在您的电脑上使用 16 或更高版本的 psql 客户端进行连接:

    终端
    psql "host=db.您的域名.com port=5432 dbname=appdb user=app_owner sslmode=verify-full sslrootcert=system"

    然后检查会话是否已加密:

    SQL
    SELECT ssl, version FROM pg_stat_ssl WHERE pid = pg_backend_pid();

自动执行备份

pg_dump 可以在不停止数据库的情况下导出数据库,但它既不会备份角色,也不会备份其密码。因此,下面的脚本还会通过 pg_dumpall --globals-only 导出这些全局对象。

  1. 创建目标目录:

    终端
    sudo install -d -o postgres -g postgres -m 700 /var/backups/postgresql
  2. 创建脚本:

    终端
    sudo nano /usr/local/sbin/postgresql-backup.sh
    终端
    #!/bin/bash
    set -euo pipefail
    umask 077
    
    DEST="/var/backups/postgresql"
    RETENTION_DAYS=14
    STAMP="$(date +%F_%H%M%S)"
    TARGET="$DEST/$STAMP"
    
    mkdir -p "$TARGET"
    pg_dumpall --globals-only -f "$TARGET/globals.sql"
    
    psql -AtX -d postgres -c "SELECT datname FROM pg_database WHERE datallowconn AND NOT datistemplate" |
    while IFS= read -r db; do
      pg_dump -Fc -d "$db" -f "$TARGET/$db.dump"
    done
    
    find "$DEST" -mindepth 1 -maxdepth 1 -type d -mtime +"$RETENTION_DAYS" -exec rm -rf {} +
  3. 创建服务:

    终端
    sudo chmod 755 /usr/local/sbin/postgresql-backup.sh
    sudo nano /etc/systemd/system/postgresql-backup.service
    配置
    [Unit]
    Description=PostgreSQL backup
    After=postgresql.service
    
    [Service]
    Type=oneshot
    User=postgres
    Group=postgres
    ExecStart=/usr/local/sbin/postgresql-backup.sh
  4. 创建定时器:

    终端
    sudo nano /etc/systemd/system/postgresql-backup.timer
    配置
    [Unit]
    Description=Daily PostgreSQL backup
    
    [Timer]
    OnCalendar=*-*-* 02:30:00
    RandomizedDelaySec=10min
    Persistent=true
    
    [Install]
    WantedBy=timers.target
  5. 启用定时器并运行第一次备份:

    终端
    sudo systemctl daemon-reload
    sudo systemctl enable --now postgresql-backup.timer
    sudo systemctl start postgresql-backup.service
    sudo systemctl status postgresql-backup.service --no-pager
    sudo -u postgres ls -lR /var/backups/postgresql

测试恢复

只有已经成功恢复过的备份才有价值。此测试使用一个临时数据库,不会影响生产数据库。

终端
STAMP="$(sudo -u postgres ls -1 /var/backups/postgresql | tail -n 1)"
sudo -u postgres createdb restore_test
sudo -u postgres pg_restore --no-owner --no-privileges --exit-on-error -d restore_test "/var/backups/postgresql/$STAMP/appdb.dump"
sudo -u postgres psql -d restore_test -c "SELECT * FROM restore_check;"
sudo -u postgres dropdb restore_test

您必须找回 sauvegarde OK 这一行。请定期重复这项检查,并且也要对比您应用程序中的几张重要的表。

故障后的恢复

在已安装 PostgreSQL 的新服务器上,或在停止应用程序并删除受损数据库之后:

终端
STAMP="2026-11-18_023000"
sudo -u postgres psql -X -f "/var/backups/postgresql/$STAMP/globals.sql" postgres
sudo -u postgres dropdb --if-exists appdb
sudo -u postgres pg_restore --create --exit-on-error -d postgres "/var/backups/postgresql/$STAMP/appdb.dump"
sudo -u postgres psql -d appdb -c "ANALYZE;"

请将 STAMP 的值替换为所需的目录。恢复角色时出现错误 role "postgres" already exists 属于正常现象。

更新 PostgreSQL

次要版本(例如从 18.6 到 18.7)用于修复漏洞和安全缺陷。它们随系统一同安装,并会重启服务:

终端
sudo apt update
sudo apt upgrade

新的主版本会与旧版本并行安装,并需要迁移。请先运行一次备份,然后调整版本号:

终端
OLD=18
NEW=19
sudo systemctl start postgresql-backup.service
sudo apt install -y "postgresql-$NEW"
pg_lsclusters
sudo pg_dropcluster --stop "$NEW" main
sudo pg_upgradecluster -v "$NEW" "$OLD" main
pg_lsclusters

此处的 pg_dropcluster 会删除新软件包创建的空实例:请使用 pg_lsclusters 确认其中没有任何有用的内容。随后 pg_upgradecluster 会把新版本放在原来的端口上,并保留已停止的旧版本。检查完您的应用程序后,再将其移除:

终端
sudo pg_dropcluster "$OLD" main
sudo apt purge "postgresql-$OLD" "postgresql-client-$OLD"

如果您使用了证书续期脚本,请记得同时调整其中的版本号。

故障排除

FATAL: no pg_hba.conf entry for host

没有任何规则与该地址、角色、数据库或加密方式相匹配。该消息会显示服务器收到的值。请核对它们是否与您的 hostssl 行一致,包括客户端实际的公网 IP 地址,然后重新加载配置:

终端
sudo systemctl reload postgresql@18-main

FATAL: password authentication failed for user

密码不正确,或该角色没有设置密码。请在管理会话中重新设置:

终端
sudo -u postgres psql -c "\password app_owner"

FATAL: Peer authentication failed for user

您通过本地套接字连接,使用的角色与您的系统用户不同。请添加 -h 127.0.0.1 以使用密码,或通过 sudo -u 以正确的用户运行该命令。

Connection refused 或超时

终端
pg_lsclusters
sudo ss -tlnp | grep 5432
sudo ufw status
sudo tail -n 50 /var/log/postgresql/postgresql-18-main.log

Connection refused 通常表示 PostgreSQL 已停止,或未在该地址上监听。超时则更倾向于是防火墙的问题。

permission denied for table 或 for schema public

该角色没有所需的权限。请连接到正确的数据库后重新执行 GRANT。对于由其他角色创建的现有表,ALTER DEFAULT PRIVILEGES 并不够:它只对今后创建的表生效。

修改后服务无法再启动

日志会指出出问题的文件和行:

终端
sudo journalctl -u postgresql@18-main -n 50 --no-pager
sudo tail -n 50 /var/log/postgresql/postgresql-18-main.log

如果使用了自定义证书,还请检查 server.key 是否属于 postgres,且权限为 600。

could not change directory to "/root"

当您在 postgres 无法读取的目录中运行 sudo -u postgres 时,会出现此消息。它不会造成影响:切换到 /tmp 即可使其消失。

参考资料