PostgreSQL（常简称 PG）是功能最完整的开源关系型数据库之一，以严格的 SQL 标准遵从性、强大的 JSON 支持和高扩展性著称。如果你的应用是 Django、Rails、Redmine，或需要地理空间数据（PostGIS），PostgreSQL 是首选。本文演示在搬瓦工 VPS（Debian/Ubuntu）上完整安装 PostgreSQL、初始化角色与数据库，并掌握 psql 基础操作。

本文要点

  • 从官方 PostgreSQL APT 源安装最新稳定版
  • 理解 postgres 超级用户与 peer 认证机制
  • 创建角色（Role）与数据库，并授权
  • psql 常用命令速查
  • 修改 pg_hba.conf 配置认证方式

安装 PostgreSQL

方式一：从系统源安装（快速）

apt update
apt install -y postgresql postgresql-contrib

# 查看安装版本
psql --version

方式二：从官方 PostgreSQL APT 源安装（推荐，获取最新版）

# 添加官方仓库密钥
apt install -y curl ca-certificates
install -d /usr/share/postgresql-common/pgdg
curl -o /usr/share/postgresql-common/pgdg/apt.postgresql.org.asc \
  --fail https://www.postgresql.org/media/keys/ACCC4CF8.asc

# 添加仓库
. /etc/os-release
echo "deb [signed-by=/usr/share/postgresql-common/pgdg/apt.postgresql.org.asc] \
  https://apt.postgresql.org/pub/repos/apt ${VERSION_CODENAME}-pgdg main" \
  > /etc/apt/sources.list.d/pgdg.list

# 安装最新稳定版（如 17）
apt update
apt install -y postgresql-17

# 启动并设置开机自启
systemctl enable --now postgresql

CentOS/AlmaLinux 安装

执行 dnf install -y postgresql-server postgresql-contrib，然后初始化数据目录：postgresql-setup --initdb，再 systemctl enable --now postgresql。

理解 postgres 超级用户

PostgreSQL 安装后会自动创建一个名为 postgres 的 Linux 系统用户和同名数据库角色。默认使用 peer 认证，即 Linux 用户名与数据库用户名必须一致。进入 psql 的最简方式：

# 切换到 postgres 系统用户后进入 psql
sudo -u postgres psql

创建角色与数据库

在 psql 内执行以下 SQL（以英文分号结尾）：

-- 创建角色（用户），并设置密码
CREATE ROLE myapp_user WITH LOGIN PASSWORD '强密码';

-- 创建数据库，指定所有者
CREATE DATABASE myapp OWNER myapp_user ENCODING 'UTF8' LC_COLLATE 'C.UTF-8' LC_CTYPE 'C.UTF-8' TEMPLATE template0;

-- 授予角色连接权限（PostgreSQL 15+ 默认限制了 public schema 权限）
GRANT ALL PRIVILEGES ON DATABASE myapp TO myapp_user;

-- 查看所有角色
\du

-- 查看所有数据库
\l

修改认证方式（pg_hba.conf）

如果应用程序需要用密码连接 PostgreSQL，需要将认证方式从 peer 改为 md5 或 scram-sha-256：

# 找到配置文件位置
psql -U postgres -c "SHOW hba_file;"

# 编辑 pg_hba.conf，将 local 行的 peer 改为 scram-sha-256
nano /etc/postgresql/17/main/pg_hba.conf

# 修改示例（local 连接改为密码认证）：
# local   all   all   scram-sha-256

# 重载配置
systemctl reload postgresql

修改 pg_hba.conf 需谨慎

改错认证方式可能导致连不上数据库。建议修改前备份该文件：cp pg_hba.conf pg_hba.conf.bak，修改后立即测试新连接，确认无误再关闭旧连接。

psql 常用命令速查

命令说明
\l列出所有数据库
\c dbname切换到指定数据库
\dt列出当前库所有表
\d tablename查看表结构
\du列出所有角色/用户
\q退出 psql
\?显示 psql 帮助

用密码连接指定数据库

# 用 myapp_user 登录 myapp 数据库
psql -h 127.0.0.1 -U myapp_user -d myapp

# 或者用连接字符串
psql 'postgresql://myapp_user:强密码@127.0.0.1/myapp'

数据库编码建议

建库时指定 ENCODING 'UTF8'，并使用 LC_COLLATE 'C.UTF-8'，在保证 UTF-8 支持的同时避免本地化排序带来的性能损耗，是大多数 Web 应用的最佳选择。

小结

  • 从官方 APT 源安装可获取最新稳定版，功能与安全补丁更全
  • postgres 是内置超级用户，通过 sudo -u postgres psql 进入管理
  • 为每个应用单独建角色与数据库，不共用 postgres 超级账户
  • 调整 pg_hba.conf 控制认证方式，记得备份再改
  • psql 的 \l、\dt、\du 是日常管理必备三件套

常见问题

PostgreSQL 和 MySQL 如何选择？

MySQL/MariaDB 生态更广泛，WordPress 等主流 CMS 首选。PostgreSQL 功能更完整，JSON 和地理空间支持更强，适合 Django、Rails 及复杂查询场景。

sudo -u postgres psql 后如何退出？

在 psql 内输入 \q 退出 psql，然后 exit 回到原用户。

应用程序连接报 peer authentication failed 怎么办？

修改 pg_hba.conf，将对应行的认证方式从 peer 改为 scram-sha-256 或 md5，然后 systemctl reload postgresql。

如何备份 PostgreSQL 数据库？

用 pg_dump 命令：sudo -u postgres pg_dump dbname > backup.sql。恢复用 psql dbname < backup.sql。