在 Ubuntu VPS 上安装 PostgreSQL — MySQL 的替代方案

如果您正在寻找一款稳定、支持 JSON、具备高级功能，且事务处理比 MySQL 更严谨的数据库，大多数开发者的答案都是 PostgreSQL——这款开源数据库驱动着 Discord、Instagram 和 Apple iCloud 等知名平台。

本教程将带您从零开始，在 Ubuntu 22.04 VPS 上安装 PostgreSQL，包括创建数据库、创建用户、设置密码，以及安全地允许外部应用程序连接。

什么是 PostgreSQL？为什么使用它？

PostgreSQL（读作 "post-gress-Q-L"）是自 1986 年起持续开发的对象关系型数据库管理系统（ORDBMS）。其核心优势在于严格遵循 ACID、原生支持 JSONB（可在关系型行旁边存储文档型数据），以及比 MySQL 更细粒度的类型系统。

PostgreSQL 与 MySQL 的核心差异

第一步 — 更新系统并安装 PostgreSQL

首先更新软件包列表，然后安装 PostgreSQL 及 contrib 扩展包：

sudo apt update && sudo apt upgrade -y
sudo apt install postgresql postgresql-contrib -y

检查版本和运行状态：

sudo systemctl status postgresql
psql --version

若看到 Active: active (exited) 且版本为 psql (PostgreSQL) 14.x 或更新，说明安装成功。设置开机自启：

sudo systemctl enable postgresql

第二步 — 以 postgres 用户身份进入 psql

安装程序会自动创建名为 postgres 的 Linux 用户，使用它进入控制台：

sudo -i -u postgres
psql

看到 postgres=# 提示符即表示已进入 PostgreSQL 控制台。列出现有数据库：

\l

输入 \q 退出 psql，输入 exit 返回普通 Shell。

第三步 — 创建数据库和用户

生产环境中不要直接使用 postgres 用户，应为每个应用创建专属用户。再次打开 psql：

sudo -u postgres psql

创建用户、数据库并授权：

-- Create user with password
CREATE USER myapp WITH PASSWORD 'StrongPassword123!';

-- Create database owned by the user above
CREATE DATABASE myapp_db OWNER myapp;

-- Grant full privileges on the database
GRANT ALL PRIVILEGES ON DATABASE myapp_db TO myapp;

\q

使用新用户测试登录：

psql -U myapp -d myapp_db -h 127.0.0.1 -W

输入刚才设置的密码，若成功登录，您的应用即可使用这组凭据连接数据库。

第四步 — 允许外部连接

PostgreSQL 默认只接受来自本地的连接。若需让其他服务器上的应用连接，需要编辑两个配置文件。

4.1 编辑 postgresql.conf

sudo nano /etc/postgresql/14/main/postgresql.conf

找到 listen_addresses 并改为：

listen_addresses = '*'

4.2 编辑 pg_hba.conf 以允许指定 IP

sudo nano /etc/postgresql/14/main/pg_hba.conf

在文件末尾添加一行（将 192.168.1.100/32 替换为您的应用真实 IP）：

# TYPE  DATABASE    USER    ADDRESS            METHOD
host    myapp_db    myapp   192.168.1.100/32   scram-sha-256

重启 PostgreSQL 使配置生效：

sudo systemctl restart postgresql

4.3 开放防火墙端口

PostgreSQL 监听 5432 端口。使用 UFW 时，只对所需 IP 开放即可：

sudo ufw allow from 192.168.1.100 to any port 5432

第五步 — 使用 pg_dump 备份

PostgreSQL 内置备份工具 pg_dump，可将数据库导出为 SQL 文件：

sudo -u postgres pg_dump myapp_db > /backup/myapp_db_$(date +%F).sql

从备份文件恢复：

sudo -u postgres psql myapp_db < /backup/myapp_db_2026-05-13.sql

第六步 — 常用 psql 命令速查

• \l — 列出所有数据库
• \c dbname — 切换到指定数据库
• \dt — 列出当前数据库的所有表
• \d tablename — 查看表结构
• \du — 列出用户与角色
• \q — 退出 psql

在 Ubuntu VPS 上安装 PostgreSQL — 深入讲解

如果您刚从 AsiaGB 开通了一台新 VPS（Ubuntu 22.04，月租低至 500 泰铢），SSH 登录后第一件事是查看 Ubuntu 仓库中的 PostgreSQL 版本。默认仓库有时会落后最新版本好几个大版本。若想安装最新版，请先添加 PostgreSQL 官方 APT 仓库：

# Add the PostgreSQL official repository (for the latest version)
sudo apt install -y postgresql-common
sudo /usr/share/postgresql-common/pgdg/apt.postgresql.org.sh

# Then install as usual
sudo apt update
sudo apt install -y postgresql postgresql-contrib

安装完成后服务会自动启动。确认 PostgreSQL 正在监听 5432 端口：

sudo ss -tlnp | grep 5432

若看到含有 127.0.0.1:5432 的行，说明 PostgreSQL 已准备好接受本地连接。如果端口未出现，通常原因是服务启动失败或端口被其他进程占用，请查看 /var/log/postgresql/ 下的日志排查原因。

深入解析：创建数据库、用户与权限管理

前面我们在 psql 中用 SQL 快速创建了用户和数据库。其实 PostgreSQL 还提供了可直接在 Shell 中调用的命令行工具 createuser 和 createdb，无需进入 psql，在自动化脚本中非常实用：

# Create a user interactively (it asks about privileges)
sudo -u postgres createuser --interactive --pwprompt

# Create a database with an explicit owner
sudo -u postgres createdb -O myapp myapp_db

PostgreSQL 的权限体系比很多人想象的更细致。GRANT ALL PRIVILEGES ON DATABASE 只授予连接和创建 Schema 的权限，并不会自动覆盖后续新建的表。自 PostgreSQL 15 起，public Schema 的权限收紧了，必须显式授予 Schema 级别的权限：

sudo -u postgres psql -d myapp_db
-- Make the user own the public schema
GRANT ALL ON SCHEMA public TO myapp;

-- Auto-grant privileges on future tables/sequences
ALTER DEFAULT PRIVILEGES IN SCHEMA public
  GRANT ALL ON TABLES TO myapp;
ALTER DEFAULT PRIVILEGES IN SCHEMA public
  GRANT ALL ON SEQUENCES TO myapp;
\q

在 psql 中用 \du 列出角色及其属性，用 \dp tablename 查看表级别权限。这样能更快地排查常见的"permission denied for table"错误——通常是因为应用以非表所有者身份连接。

加固安全性与远程访问

向外部开放 PostgreSQL 连接是整个配置中风险最高的环节。配置不当，扫描 5432 端口的机器人会立刻尝试暴力破解您的密码。黄金法则是只开放必要的访问权限，并做好纵深防御。

在 postgresql.conf 中，如果应用和数据库位于同一私有网络的不同机器上，建议只监听内网 IP，而非使用 '*' 监听所有接口：

# Listen only on the VPS private IP instead of '*' (safer)
listen_addresses = 'localhost,10.0.0.5'

在 pg_hba.conf 中，行的顺序至关重要——PostgreSQL 从上到下读取规则，以第一条匹配的规则为准。应将精确规则（单个 IP）置于宽泛规则之前，并始终使用 scram-sha-256 认证方式：

# TYPE  DATABASE   USER    ADDRESS           METHOD
local   all        all                       peer
host    myapp_db   myapp   10.0.0.10/32      scram-sha-256
host    all        all     127.0.0.1/32      scram-sha-256

为进一步提升安全性，可在应用与数据库之间的连接上启用 SSL，加密网络传输的数据。在 postgresql.conf 中配置：

ssl = on
ssl_cert_file = '/etc/ssl/certs/server.crt'
ssl_key_file = '/etc/ssl/private/server.key'

然后在 pg_hba.conf 中将 host 改为 hostssl，强制外部连接使用 SSL，并在应用端的连接字符串中添加 ?sslmode=require。

备份、恢复与基础性能调优

除了用 pg_dump 备份单个数据库，PostgreSQL 还提供 pg_dumpall 备份整个集群（含角色和全局设置），以及 pg_restore 从压缩的自定义格式备份中恢复（恢复速度比普通 SQL 文件更快）。推荐使用自定义格式备份：

# Backup in custom format (compressed + selective restore)
sudo -u postgres pg_dump -Fc myapp_db > /backup/myapp_db_$(date +%F).dump

# Restore from a custom-format file
sudo -u postgres pg_restore -d myapp_db --clean /backup/myapp_db_2026-06-07.dump

# Back up the whole cluster (roles + permissions)
sudo -u postgres pg_dumpall > /backup/full_cluster_$(date +%F).sql

在调优方面，PostgreSQL 的默认参数非常保守，适合在低配硬件上运行。在内存充足的 VPS 上，应在 postgresql.conf 中调整以下关键参数以充分发挥性能：

如果应用会建立大量连接，建议在前端使用 PgBouncer 等连接池，而非盲目提高 max_connections，因为每个 PostgreSQL 连接都会消耗相当多的内存。每次修改 postgresql.conf 后记得运行 sudo systemctl restart postgresql，并定期执行 VACUUM ANALYZE 以保持统计信息的新鲜度并回收已删除数据占用的空间。

为 postgres 用户设置密码

安装完成后，postgres 用户在数据库层面默认没有密码，请及时设置：

sudo -u postgres psql
ALTER USER postgres WITH PASSWORD 'AnotherStrongPassword!';
\q

安装后核查清单

1. 安装 postgresql + postgresql-contrib 并启用开机自启
2. 立即为 postgres 用户设置数据库密码
3. 为每个应用创建独立的用户和数据库——生产环境严禁直接使用 postgres
4. 仅在需要远程访问时才修改 postgresql.conf 和 pg_hba.conf
5. 防火墙只对有需要的 IP 开放——绝不使用 0.0.0.0/0
6. 设置每日 pg_dump 定时备份，并将备份文件传至服务器之外

常见问题解答（FAQ）

普通网站应该选 PostgreSQL 还是 MySQL？

如果您的网站是 WordPress 或以 MySQL 为核心的现成 CMS，MySQL/MariaDB 安装更简便、兼容性更好。但如果您在开发自己的应用，需要严格的事务处理、JSONB 文档存储或复杂的数据分析，PostgreSQL 更加合适，且长期扩展性更强。两者都可以在月租低至 500 泰铢的 AsiaGB VPS 上流畅运行。

安装 PostgreSQL 需要怎样的 VPS 配置？

小型或测试项目用 1GB 内存的 VPS 即可运行 PostgreSQL，但面对真实流量或数万行数据的工作负载，建议至少配备 2GB 内存，以便合理调整 shared_buffers 和 work_mem。AsiaGB 的 Linux SSD VPS 提供完整 root 权限，您可以自由调整这些参数。

忘记了 postgres 用户的密码怎么办？

由于 Linux 的 postgres 用户可通过 peer 认证无密码进入 psql，您随时可以运行 sudo -u postgres psql，然后执行 ALTER USER postgres WITH PASSWORD 'newpass'; 来重置密码——无需知道旧密码，只需拥有 VPS 的 sudo 权限即可。

如何设置每天自动运行 pg_dump 备份？

可以使用系统定时任务：运行 sudo crontab -u postgres -e，添加类似 0 3 * * * pg_dump -Fc myapp_db > /backup/myapp_db_$(date +\%F).dump 的行，即可在每天凌晨 3 点自动备份。将备份文件上传至云存储，并设置自动删除 14 天前的旧备份以节省空间。