安装并加固 PostgreSQL
在 Debian 或 Ubuntu 上从官方软件源安装 PostgreSQL 18:角色与权限、基于 TLS 的远程访问、自动备份以及恢复测试。
本页内容
您的参数
只需填写一次,指南中的命令会自动更新。
本指南将说明如何从项目官方软件源在您的 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)。请查阅版本策略了解当前版本。
-
安装所需工具并获取软件源密钥:
终端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 -
检查密钥指纹:
终端gpg --show-key /usr/share/postgresql-common/pgdg/apt.postgresql.org.asc它必须与
B97B0AFCAA1A47F044F244A07FCC7D46ACCC4CF8一致。如果不一致,请就此停止。 -
声明软件源:
终端. /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 -
安装服务器:
终端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 5432pg_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。
-
创建角色。每条命令都会要求输入密码,这样密码就不会留在 shell 历史记录中:
终端sudo -u postgres createuser --pwprompt app_owner sudo -u postgres createuser --pwprompt app_reader -
创建数据库:
终端sudo -u postgres createdb --owner=app_owner appdb -
限制对该数据库的访问:
终端sudo -u postgres psql -d appdbSQLREVOKE 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今后创建的表拥有读取权限。 -
使用密码验证连接:
终端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 获取证书。
-
创建一个脚本,将证书复制给 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 -
赋予其可执行权限,并先运行一次:
终端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 -
创建配置文件:
终端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' -
在
/etc/postgresql/18/main/pg_hba.conf末尾添加一条规则,限定为某个数据库、某个角色和某个地址:文本hostssl appdb app_owner 您的IP/32 scram-sha-256hostssl会拒绝任何未加密的连接。对于 IPv6 地址,请使用后缀/128。 -
在防火墙中只允许您的地址。使用 UFW 时:
终端sudo ufw allow from 您的IP to any port 5432 proto tcp -
重启 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;"第二条命令不应返回任何行。
-
在您的电脑上使用 16 或更高版本的
psql客户端进行连接:终端psql "host=db.您的域名.com port=5432 dbname=appdb user=app_owner sslmode=verify-full sslrootcert=system"然后检查会话是否已加密:
SQLSELECT ssl, version FROM pg_stat_ssl WHERE pid = pg_backend_pid();
自动执行备份
pg_dump 可以在不停止数据库的情况下导出数据库,但它既不会备份角色,也不会备份其密码。因此,下面的脚本还会通过 pg_dumpall --globals-only 导出这些全局对象。
-
创建目标目录:
终端sudo install -d -o postgres -g postgres -m 700 /var/backups/postgresql -
创建脚本:
终端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 {} + -
创建服务:
终端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 -
创建定时器:
终端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 -
启用定时器并运行第一次备份:
终端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-mainFATAL: 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.logConnection 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 即可使其消失。