PostgreSQL 数据库维护应用指南

本文是一份面向实际工作的 PostgreSQL 命令手册，重点放在常用 SQL、psql 交互命令、权限管理、备份恢复、排障和性能观察上。目标是让你拿到它就能直接在 Debian 环境里用。

说明：

这里的命令适用于 PostgreSQL 常规版本，具体细节会随版本略有差异
文档默认你已经装好了 PostgreSQL 服务和客户端
如果你在 Debian 上使用 postgresql 软件包，常见命令会是 psql、pg_dump、pg_restore、createdb、createuser

适用场景

连接数据库执行 SQL
创建数据库、角色和权限
查询、修改和删除数据
导入导出数据库
查看表结构、索引和执行计划
排查连接、权限和性能问题

常用客户端命令

登录数据库

切换到系统用户 postgres：

sudo -u postgres psql

连接指定数据库：

psql -U postgres -d postgres

连接远程主机：

psql -h 127.0.0.1 -p 5432 -U appuser -d appdb

查看版本

psql --version

在交互环境中查看服务器版本：

SELECT version();

psql 常用元命令

psql 里很多以反斜杠开头的命令不是 SQL，而是客户端元命令。它们是日常排障和浏览对象最常用的工具。

常见元命令

\l
\du
\dt
\dn
\dv
\df
\c appdb
\conninfo
\x
\timing
\q

元命令说明

\l：列出数据库
\du：列出角色
\dt：列出表
\dn：列出 schema
\dv：列出视图
\df：列出函数
\c：切换连接到另一个数据库
\conninfo：显示当前连接信息
\x：切换扩展输出模式
\timing：显示每条 SQL 的耗时
\q：退出 psql

实用技巧

以分号结尾的是 SQL
以反斜杠开头的是 psql 命令
\g 可以立即执行当前缓冲区中的 SQL
\watch 2 可以每 2 秒重复执行一次查询，适合观察状态变化

数据库与角色

PostgreSQL 把“用户”和“组”统一抽象成 role。CREATE USER 本质上是 CREATE ROLE ... LOGIN 的简写。

创建数据库

CREATE DATABASE appdb;

更常见的是指定所有者：

CREATE DATABASE appdb OWNER appuser;

创建角色

CREATE ROLE appuser LOGIN PASSWORD 'StrongPasswordHere';

如果需要创建可建库的角色：

CREATE ROLE appuser LOGIN PASSWORD 'StrongPasswordHere' CREATEDB;

创建用户

CREATE USER appuser WITH PASSWORD 'StrongPasswordHere';

查看角色

\du

修改角色

ALTER ROLE appuser WITH PASSWORD 'NewStrongPasswordHere';

删除角色

DROP ROLE appuser;

删除数据库

DROP DATABASE appdb;

注意：

DROP DATABASE 会删除整个数据库
先确认没有连接该库的会话
生产环境执行前务必先备份

权限管理

PostgreSQL 的权限控制比很多人想象得更细。最常见的做法是先给数据库访问权，再给 schema 和表权限。

授予数据库连接权限

GRANT CONNECT ON DATABASE appdb TO appuser;

授予 schema 使用权

GRANT USAGE ON SCHEMA public TO appuser;

授予表权限

GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA public TO appuser;

授予序列权限

GRANT USAGE, SELECT, UPDATE ON ALL SEQUENCES IN SCHEMA public TO appuser;

设置默认权限

如果你希望未来新建表也自动继承权限：

ALTER DEFAULT PRIVILEGES IN SCHEMA public
GRANT SELECT, INSERT, UPDATE, DELETE ON TABLES TO appuser;

查看对象权限

\dp

常见原则

先授权，再连接
权限尽量给到 schema 或对象级别，而不是随手给超级用户
生产环境不要长期使用 postgres 跑业务

表结构操作

创建表

CREATE TABLE users (
    id BIGSERIAL PRIMARY KEY,
    username VARCHAR(64) NOT NULL UNIQUE,
    email VARCHAR(128) NOT NULL UNIQUE,
    status SMALLINT NOT NULL DEFAULT 1,
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

查看表结构

\d users
\d+ users

修改表

添加列：

ALTER TABLE users ADD COLUMN nickname VARCHAR(64);

修改列类型：

ALTER TABLE users ALTER COLUMN nickname TYPE VARCHAR(128);

删除列：

ALTER TABLE users DROP COLUMN nickname;

设置默认值：

ALTER TABLE users ALTER COLUMN status SET DEFAULT 1;

删除表

DROP TABLE users;

基础增删改查

插入数据

INSERT INTO users (username, email) VALUES ('alice', 'alice@example.com');

查询数据

SELECT * FROM users;
SELECT id, username FROM users WHERE id = 1;
SELECT * FROM users ORDER BY id DESC LIMIT 10;

更新数据

UPDATE users SET email = 'new@example.com' WHERE id = 1;

删除数据

DELETE FROM users WHERE id = 1;

事务

BEGIN;
UPDATE users SET email = 'a@example.com' WHERE id = 1;
INSERT INTO audit_log(action) VALUES ('update user');
COMMIT;

回滚：

ROLLBACK;

说明：

PostgreSQL 默认强事务支持
多表一致性更新时优先使用事务
出现异常后不要忘记回滚

查询分析

EXPLAIN

EXPLAIN SELECT * FROM users WHERE email = 'alice@example.com';

EXPLAIN ANALYZE

EXPLAIN ANALYZE SELECT * FROM users WHERE email = 'alice@example.com';

EXPLAIN ANALYZE 会真正执行查询并返回实际执行情况，适合分析慢查询。

查看索引

\di
\di+ users*

创建索引

CREATE INDEX idx_users_email ON users(email);

删除索引

DROP INDEX idx_users_email;

常见优化原则

经常出现在 WHERE、JOIN、ORDER BY 中的列值得优先考虑索引
不要盲目增加索引，写入性能会受影响
先用 EXPLAIN ANALYZE 看计划，再决定是否改索引

导入导出

导出单个数据库

pg_dump -U postgres -d appdb > appdb.sql

导出自定义格式

pg_dump -U postgres -F c -d appdb -f appdb.dump

导入 SQL 文件

psql -U postgres -d appdb -f appdb.sql

导入自定义格式

pg_restore -U postgres -d appdb appdb.dump

只导出结构

pg_dump -U postgres -s -d appdb > schema.sql

只导出数据

pg_dump -U postgres -a -d appdb > data.sql

导出整个集群

如果你要备份角色和所有数据库，使用：

pg_dumpall -U postgres > cluster.sql

说明：

pg_dump 只备份单个数据库
pg_restore 用于恢复非纯文本归档
pg_dumpall 适合整实例备份，包括角色等全局对象

字符集与编码

PostgreSQL 常见建议：

客户端连接使用 UTF8
数据库编码在创建时确定
业务侧统一处理字符串编码

查看编码：

SHOW server_encoding;
SHOW client_encoding;

设置客户端编码：

SET client_encoding = 'UTF8';

连接与会话排查

查看当前连接

SELECT current_user;
SELECT session_user;
SELECT current_database();

查看活动会话

SELECT * FROM pg_stat_activity;

终止会话

SELECT pg_terminate_backend(pid)
FROM pg_stat_activity
WHERE datname = 'appdb' AND pid <> pg_backend_pid();

查看锁

SELECT * FROM pg_locks;

性能观察

当前数据库概况

\l+

表统计信息

SELECT * FROM pg_stat_user_tables;

索引统计信息

SELECT * FROM pg_stat_user_indexes;

查找慢查询方向

优先看：

EXPLAIN ANALYZE
pg_stat_activity
pg_stat_user_tables
索引是否命中
锁等待是否存在

常用系统命令

在服务器层面，常会配合这些命令：

sudo systemctl status postgresql
sudo systemctl restart postgresql
sudo systemctl reload postgresql
sudo journalctl -u postgresql -xe

如果有多个版本：

sudo pg_lsclusters

常见问题

1. `psql` 连不上

先检查：

服务是否启动
主机、端口和数据库名是否正确
pg_hba.conf 是否允许
postgresql.conf 是否监听正确地址

2. `permission denied for schema public`

通常是 schema 权限没给。给应用用户补：

GRANT USAGE ON SCHEMA public TO appuser;

3. 无法创建表

检查：

是否有 CREATE 权限
当前用户是否是数据库 owner
是否在正确的 schema 下

4. 导入失败

常见原因：

编码不一致
对象已存在
扩展或函数缺失
角色或权限不完整

5. 慢查询

先执行：

EXPLAIN ANALYZE ...

再判断：

是否需要索引
是否存在锁等待
是否统计信息过旧

最小可用流程

如果你想快速完成一次基础操作，可以按下面顺序：

sudo -u postgres psql

然后执行：

CREATE ROLE appuser LOGIN PASSWORD 'StrongPasswordHere';
CREATE DATABASE appdb OWNER appuser;
\c appdb
CREATE TABLE users (
    id BIGSERIAL PRIMARY KEY,
    username VARCHAR(64) NOT NULL UNIQUE,
    email VARCHAR(128) NOT NULL UNIQUE
);
INSERT INTO users (username, email) VALUES ('alice', 'alice@example.com');
SELECT * FROM users;

结论

PostgreSQL 真正好用的命令，不只是 psql 能登录，而是要能覆盖：

角色和数据库管理
psql 元命令
权限与 schema 控制
事务、索引和执行计划
pg_dump、pg_restore、pg_dumpall
连接、锁和会话排查