PostgreSQL 教程

本文以 Debian 系统默认包环境为例，说明 PostgreSQL 的安装、psql 常用命令、数据库与角色创建、表设计、常用 SQL、事务、权限、远程连接以及备份恢复。

本文依次说明：

安装 PostgreSQL
psql 常用命令
创建数据库、角色和数据表
插入、查询、更新、删除
排序、分页、聚合、分组、连接查询
约束、索引、视图、事务和权限
远程连接
备份与恢复

除特别说明外，文中的 SQL 均在 psql 终端中执行；文中的系统命令均在 Linux shell 中执行。示例数据库为 appdb，示例用户为 appuser。

文中的配置路径以系统默认版本为例。如果你的机器使用的是其他大版本，请将路径中的版本号替换为实际版本。

适用范围

PostgreSQL 初次安装与初始化
个人学习 PostgreSQL 常用 SQL 和基础运维
部署本机开发环境
为个人项目或小型自用服务准备数据库环境

连接池、复制和高可用部署不在本文范围内。

一、安装后的默认状态

安装完成后，先确认以下默认状态：

PostgreSQL 服务已经创建好并由 systemd 管理
默认端口通常是 5432
数据目录通常类似 /var/lib/postgresql/<版本>/main
配置目录通常类似 /etc/postgresql/<版本>/main

本文只区分下面几个概念：

数据库：比如 appdb
schema：默认常见的是 public
表：真正存数据的对象
角色：登录用户、权限主体都算角色

二、安装 PostgreSQL

先更新软件源并安装服务端、客户端和常见扩展包：

sudo apt update
sudo apt install -y postgresql postgresql-client postgresql-contrib

然后检查状态：

systemctl status postgresql
ss -ltn | grep 5432

systemctl status postgresql 用于检查服务状态，ss -ltn | grep 5432 用于确认本机已经监听 PostgreSQL 默认端口。

三、进入 `psql`，先学会最常用的命令

先进入 psql：

sudo -u postgres psql

常用命令如下：

-- 列出数据库
\l

-- 列出角色
\du

-- 查看当前连接信息
\conninfo

-- 列出当前数据库中的表
\dt

-- 查看 notes 表结构
\d notes

-- 退出 psql
\q

这些以反斜杠开头的是 psql 元命令，不是 SQL 语句，因此不需要在末尾加分号。

需要查看帮助时，可以执行：

-- 查看 psql 元命令帮助
\?

-- 查看 CREATE TABLE 语法帮助
\h CREATE TABLE

前者看 psql 自己的命令，后者看 SQL 语句帮助。

四、创建角色和数据库

先进入 psql：

sudo -u postgres psql

设置 postgres 密码：

ALTER USER postgres WITH PASSWORD '请改成你的强密码';  -- 设置 postgres 密码

也可以使用交互式方式：

-- 交互式修改 postgres 密码
\password postgres

创建业务角色和数据库：

CREATE ROLE appuser WITH LOGIN PASSWORD '请改成你的业务密码';  -- 创建可登录角色
CREATE DATABASE appdb OWNER appuser;                            -- 创建数据库并指定所有者

检查：

-- 检查角色是否创建成功
\du

-- 检查数据库是否创建成功
\l

退出：

-- 退出 psql
\q

使用业务账号登录：

psql -h 127.0.0.1 -U appuser -d appdb -W

这里显式写 -h 127.0.0.1，是为了让连接走 TCP，从而直接测试密码认证是否正常。

五、创建一套示例数据结构

示例使用两张表：

users
notes

其中 notes.user_id 关联 users.id。

在 psql 里执行：

CREATE TABLE users (
  id BIGSERIAL PRIMARY KEY,                    -- 用户主键
  name TEXT NOT NULL,                         -- 用户名
  email TEXT NOT NULL UNIQUE,                 -- 邮箱，要求唯一
  created_at TIMESTAMPTZ NOT NULL DEFAULT now() -- 创建时间
);

CREATE TABLE notes (
  id BIGSERIAL PRIMARY KEY,                       -- 笔记主键
  user_id BIGINT NOT NULL REFERENCES users(id) ON DELETE CASCADE, -- 外键，关联 users.id
  title TEXT NOT NULL,                           -- 标题
  body TEXT,                                     -- 正文
  status TEXT NOT NULL DEFAULT 'draft',          -- 状态，默认 draft
  views INTEGER NOT NULL DEFAULT 0 CHECK (views >= 0), -- 浏览量，不能小于 0
  created_at TIMESTAMPTZ NOT NULL DEFAULT now()  -- 创建时间
);  -- notes.user_id 外键关联 users.id

执行后检查：

-- 查看当前库里的表
\dt

-- 查看 users 表结构
\d users

-- 查看 notes 表结构
\d notes

执行 \d users 和 \d notes 后，可以直接看到这些约束已经挂到表结构上。

六、插入测试数据

先插入两条用户数据：

INSERT INTO users (name, email)
VALUES
  ('Alice', 'alice@example.com'),  -- 第 1 个用户
  ('Bob', 'bob@example.com');      -- 第 2 个用户

再插入三条笔记数据：

INSERT INTO notes (user_id, title, body, status, views)
VALUES
  (1, 'Debian setup', 'install and first boot notes', 'published', 18), -- Alice 的第 1 条笔记
  (1, 'PostgreSQL basics', 'psql and SQL practice', 'published', 35),   -- Alice 的第 2 条笔记
  (2, 'Draft idea', 'this is still a draft', 'draft', 2);               -- Bob 的草稿笔记

先插入 users，再插入 notes。因为 notes.user_id 依赖 users.id，如果先插 notes，外键检查会失败。

插入后先查询整表：

SELECT * FROM users;  -- 查看 users 表数据
SELECT * FROM notes;  -- 查看 notes 表数据

七、最常用的查询语法

1. `SELECT`

SELECT * FROM notes;                 -- 查询 notes 表全部列
SELECT id, title, status FROM notes; -- 只查询指定列

2. `WHERE`

SELECT id, title, views
FROM notes
WHERE status = 'published';  -- 只保留已发布记录

3. `AND` / `OR`

SELECT id, title, views
FROM notes
WHERE status = 'published' AND views >= 10;  -- 两个条件同时满足

SELECT id, title, status
FROM notes
WHERE status = 'draft' OR views >= 30;       -- 满足任意一个条件

第一条要求两个条件同时满足；第二条只要满足其中一个条件即可。

4. `LIKE`

SELECT id, title
FROM notes
WHERE title LIKE '%PostgreSQL%';  -- 匹配标题中包含 PostgreSQL 的记录

5. `ORDER BY`

SELECT id, title, views
FROM notes
ORDER BY views DESC;  -- 按浏览量降序排序

6. `LIMIT`

SELECT id, title, views
FROM notes
ORDER BY views DESC
LIMIT 2;  -- 只取前 2 条

LIMIT 用来限制返回行数。这里表示只取排序后的前 2 条记录。

7. `DISTINCT`

SELECT DISTINCT status
FROM notes;  -- 去重后返回 status

8. 别名

SELECT
  n.id,
  n.title,
  n.views AS page_views  -- 列别名
FROM notes AS n;         -- 表别名

这里 AS 用来给列或表起别名。

八、更新和删除

更新一条记录：

UPDATE notes
SET title = 'PostgreSQL quick notes',
    views = views + 5
WHERE id = 2;  -- 更新 id=2 这条记录

删除一条记录：

DELETE FROM notes
WHERE id = 3;  -- 删除 id=3 这条记录

再查一次：

SELECT * FROM notes ORDER BY id;  -- 再查一次确认结果

九、聚合、分组和筛选

1. 聚合函数

SELECT COUNT(*) FROM notes;  -- 统计总行数
SELECT SUM(views) FROM notes; -- 统计 views 总和
SELECT AVG(views) FROM notes; -- 计算 views 平均值
SELECT MAX(views) FROM notes; -- 查询最大 views

2. `GROUP BY`

SELECT status, COUNT(*) AS total
FROM notes
GROUP BY status;  -- 按 status 分组统计

3. `HAVING`

SELECT user_id, COUNT(*) AS total_notes
FROM notes
GROUP BY user_id
HAVING COUNT(*) >= 1;  -- 只保留统计结果大于等于 1 的分组

WHERE 先筛行，HAVING 在分组后筛结果。

十、连接查询、子查询和 `WITH`

1. `JOIN`

SELECT
  u.name,
  n.title,
  n.status,
  n.views
FROM users AS u
JOIN notes AS n
  ON u.id = n.user_id  -- 用外键关系把两张表连起来
ORDER BY n.views DESC; -- 按浏览量降序显示

这条语句通过 u.id = n.user_id 把用户和笔记连在一起。

2. 子查询

查出浏览量高于平均值的笔记：

SELECT id, title, views
FROM notes
WHERE views > (
  SELECT AVG(views) FROM notes  -- 子查询先算平均值
);                              -- 外层再筛选高于平均值的记录

3. `WITH`

先把已发布的笔记抽出来，再做统计：

WITH published_notes AS (
  SELECT * FROM notes WHERE status = 'published'  -- 先定义已发布笔记集合
)
SELECT COUNT(*) AS total_published
FROM published_notes;  -- 再统计已发布笔记数量

WITH 可以先定义一个临时结果集，再在后面的主查询里继续使用。

十一、表结构变更、索引和视图

1. `ALTER TABLE`

给 notes 加一个摘要字段：

ALTER TABLE notes
ADD COLUMN summary TEXT;  -- 给 notes 表增加 summary 字段

2. 索引

按 status 或 user_id 查询较多时，可以建立索引：

CREATE INDEX idx_notes_status ON notes(status);   -- 为 status 建索引
CREATE INDEX idx_notes_user_id ON notes(user_id); -- 为 user_id 建索引

索引用于提升查询速度。主键和 UNIQUE 约束通常也会自动带索引。

3. 视图

创建一个已发布笔记视图：

CREATE VIEW published_notes AS
SELECT id, user_id, title, views, created_at
FROM notes
WHERE status = 'published';  -- 只保留已发布笔记

以后查已发布笔记时，可以直接查 published_notes。

查询时就可以直接：

SELECT * FROM published_notes;  -- 像查表一样查询视图

十二、事务

事务用于将多条 SQL 作为一个整体执行：

BEGIN;  -- 开始事务

UPDATE notes
SET views = views + 1
WHERE id = 1;

UPDATE notes
SET views = views + 1
WHERE id = 2;

COMMIT;  -- 提交事务

需要撤销时执行：

ROLLBACK;  -- 回滚事务

如果在事务中发现条件写错、更新范围不对，执行 ROLLBACK 就能把本次事务中的改动全部撤销。

十三、权限

业务程序不要直接使用超级用户 postgres。常见做法是：

让 appuser 只负责业务数据库
按需要授予 schema 和 table 权限

示例：

GRANT CONNECT ON DATABASE appdb TO appuser; -- 允许连接 appdb
GRANT USAGE ON SCHEMA public TO appuser; -- 允许使用 public schema
GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA public TO appuser; -- 授予表级读写权限
GRANT USAGE, SELECT ON ALL SEQUENCES IN SCHEMA public TO appuser; -- 授予序列访问权限

如果之后还会继续建新表，再补默认权限：

ALTER DEFAULT PRIVILEGES IN SCHEMA public
GRANT SELECT, INSERT, UPDATE, DELETE ON TABLES TO appuser;  -- 新建表默认也授予 appuser 权限

十四、远程连接

需要远程连接时，修改以下两处配置：

postgresql.conf
pg_hba.conf

先编辑：

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

把：

#listen_addresses = 'localhost'

改成：

listen_addresses = '*'

然后编辑：

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

host    all             all             203.0.113.10/32         scram-sha-256

需要一段 IPv4 网段时：

host    all             all             192.0.2.0/24            scram-sha-256

有 IPv6 客户端时：

host    all             all             2001:db8::/64           scram-sha-256

不建议长期直接写：

host    all             all             0.0.0.0/0               scram-sha-256

改完之后重启：

sudo systemctl restart postgresql

如果你启用了 nftables，再放行 5432：

sudo nft add rule inet filter input tcp dport 5432 accept
sudo nft list ruleset

服务端确认监听：

ss -ltn | grep 5432

客户端测试：

psql -h 你的服务器IP -U appuser -d appdb -W

连进去后可以跑：

SELECT version();  -- 查看 PostgreSQL 版本
\conninfo         -- 查看当前连接的主机、数据库和用户

十五、备份与恢复

常用备份方式如下：

1. 导出成普通 SQL 文件

sudo -u postgres pg_dump -d appdb -f /var/backups/appdb.sql

恢复时先建库：

sudo -u postgres createdb appdb_restore

再导回去：

sudo -u postgres psql -d appdb_restore -f /var/backups/appdb.sql

2. 导出成自定义格式

sudo -u postgres pg_dump -Fc -d appdb -f /var/backups/appdb.dump

恢复时：

sudo -u postgres createdb appdb_restore2
sudo -u postgres pg_restore -d appdb_restore2 /var/backups/appdb.dump

注意：

pg_dump 默认只导出单个数据库
角色和表空间这类全局对象不在单库导出里
真要做生产级备份，通常还要把保留周期、异地存储、恢复演练一起考虑进去

十六、常用速查

1. 系统侧

systemctl status postgresql
ss -ltn | grep 5432
sudo -u postgres psql

2. `psql` 侧

-- 列出数据库
\l

-- 列出角色
\du

-- 列出表
\dt

-- 查看 notes 表结构
\d notes

-- 查看当前连接信息
\conninfo

-- 退出 psql
\q

3. SQL 侧

CREATE DATABASE appdb;
CREATE ROLE appuser WITH LOGIN PASSWORD '***';
CREATE TABLE notes (...);
INSERT INTO notes ...;
SELECT ... FROM notes ...;
UPDATE notes SET ... WHERE ...;
DELETE FROM notes WHERE ...;

十七、常见问题

1. `psql -U postgres` 认证失败

先使用系统用户登录：

sudo -u postgres psql

2. 改了 `pg_hba.conf` 还是连不上

优先检查：

postgresql.conf 里的 listen_addresses 是否真的改了
PostgreSQL 是否重启了
服务端 5432 是否在监听
防火墙或云安全组是否还拦着

3. `psql -h 127.0.0.1 ...` 能连，直接 `psql -U appuser -d appdb` 却不行

这通常不是 bug，而是因为前者走 TCP，后者走本地 socket，命中的认证规则可能不一样。

4. 执行 SQL 后提示符一直变成 `appdb->`

这通常说明上一条 SQL 还没结束，最常见原因就是少写了分号 ;。

参考资料

菜鸟教程 PostgreSQL 总览：https://www.runoob.com/postgresql/postgresql-tutorial.html
Debian PostgreSQL 元包与版本信息：https://packages.debian.org/trixie/postgresql
PostgreSQL 官方 psql 文档：https://www.postgresql.org/docs/current/app-psql.html
PostgreSQL 官方教程总览：https://www.postgresql.org/docs/current/tutorial.html
PostgreSQL 连接参数文档：https://www.postgresql.org/docs/current/runtime-config-connection.html
PostgreSQL pg_hba.conf 文档：https://www.postgresql.org/docs/current/auth-pg-hba-conf.html
PostgreSQL pg_dump 文档：https://www.postgresql.org/docs/current/app-pgdump.html
PostgreSQL 约束文档：https://www.postgresql.org/docs/current/ddl-constraints.html