PostgreSQL日常运维速查

本文主要汇总 PostgreSQL 日常运维中常用的几组操作：创建/删除业务用户与数据库、psql 查看表结构与垂直显示、pager、.pgpass 免交互密码、忘记密码时的重置思路，以及 Docker 跑实例的示例参数。

内容由 queue 中多篇短碎片合并改写（create/drop user、表头、expanded、pager、pgpass、reset-password、docker-deploy 等），去掉对话腔后按可执行步骤组织。密码与主机一律占位；trust / 0.0.0.0/0 仅作说明风险，生产勿照抄。流复制见热备文。

环境说明

项目	说明
客户端	`psql`、`pg_dumpall` 等
权限	管理操作通常需要超级用户（如 `postgres`）
业务账号示例	`<app_user>` / `<app_db>`（原文曾用 docmost、wikijs 等名）
Docker 镜像示例	`pgvector/pgvector:pg16`

创建用户与数据库

超级用户登录后：

psql -U postgres

CREATE USER <app_user> WITH PASSWORD '<password>';
CREATE DATABASE <app_db> OWNER <app_user>;
-- 可选：数据库级权限（OWNER 已基本足够时多用于显式授权）
GRANT ALL PRIVILEGES ON DATABASE <app_db> TO <app_user>;

说明：

CREATE USER 等价于可登录的 CREATE ROLE ... LOGIN。
指定 OWNER 后，该角色对库内对象的控制最直接；仅 GRANT ... ON DATABASE 不会自动等于「库内所有表任意权限」。
业务连接示例：

psql -U <app_user> -d <app_db> -h <db-host>

删除用户与数据库

不可逆，删除前备份。须先断开目标库连接，再 DROP DATABASE，最后 DROP USER。

psql -U postgres

-- 断开连到目标库的会话
SELECT pg_terminate_backend(pid)
FROM pg_stat_activity
WHERE datname = '<app_db>' AND pid <> pg_backend_pid();

DROP DATABASE <app_db>;

-- 若 DROP USER 提示角色仍在用
SELECT pg_terminate_backend(pid)
FROM pg_stat_activity
WHERE usename = '<app_user>';

DROP USER <app_user>;

顺序：先库后用户；库仍被占用时 DROP DATABASE 会失败。

只看表结构 / 列名

在 psql 中：

\d <table>
\d public."MixedCaseTable"

输出含列名、类型、可空、索引等，日常最常用。

纯 SQL 不返回行、只要表头：

SELECT * FROM <table> LIMIT 0;
-- 或
SELECT * FROM <table> WHERE false;

元数据：

SELECT column_name
FROM information_schema.columns
WHERE table_schema = 'public'
  AND table_name = '<table>';

information_schema 中名称大小写需与创建时一致（带引号的混合大小写表尤其注意）。

垂直显示（类似 MySQL \G）

会话级开关 `\x`

\x on          -- 或 \x 切换
SELECT * FROM <table> LIMIT 1;
\x off

\x auto 会按结果宽度自动选择（是否默认视版本/配置）。

单次查询 `\gx`（PostgreSQL 10+ 的 psql）

不以 ; 结束语句，改用 \gx：

SELECT * FROM <table> LIMIT 1 \gx

仅影响这一次输出，更接近 MySQL \G。

pager（分屏输出）

psql 对长结果可调用外部 pager（常见 less/more），便于翻页与搜索。

方式	示例
当前会话	`\pset pager off` / `\pset pager on`
启动参数	`psql -P pager=off <db>`；PG11+ 可用 `psql -n` / `--no-pager`
环境变量	`PSQL_PAGER=""` 禁用；`PAGER`/`LESS` 影响通用工具
持久配置	`~/.psqlrc` 写入 `\pset pager off`

按需选择：偶发关 pager 用 \pset；默认关闭写 .psqlrc。

.pgpass 免交互密码

家目录 ~/.pgpass，每行：

hostname:port:database:username:password

示例（主机与密码占位）：

<db-host>:5432:*:postgres:<password>

chmod 600 ~/.pgpass
pg_dumpall -U postgres -h <db-host> -p 5432 | gzip > full_backup_$(date +%Y%m%d%H%M%S).sql.gz

database 字段可用 * 匹配任意库。

对比：

方式	安全性	适用
`.pgpass` + `chmod 600`	相对好	自动化备份（推荐）
`PGPASSWORD=...` 前缀	易进 history/进程列表	临时
`pg_hba` 改 `trust`	风险高	仅可信内网/测试

忘记密码时的重置思路

需 OS 层对数据目录或服务的管理权限。托管 RDS 等走云控制台，不按下列本机流程。

临时 trust 本地（常见）

停库（unit 名按发行版）。
在 pg_hba.conf 临时允许本机 trust（路径因发行版而异，如 /var/lib/pgsql/data/ 或 /etc/postgresql/<ver>/main/）。
启动后：

sudo -u postgres psql

ALTER USER postgres WITH PASSWORD '<new_password>';

立即恢复 pg_hba 为 scram-sha-256/md5 等，再 reload/restart。
用 psql -U postgres -h localhost -W 验证新密码。

其它

若另有超级用户：直接 ALTER USER ... PASSWORD。
单用户模式：postgres --single -D <data_dir> 后执行 ALTER USER（数据目录路径待确认本机布局；操作前停写并备份）。
改完务必撤销临时 trust，否则等同空门。

Docker 跑实例（示例）

原 Unraid/手工命令可收敛为：

docker run -d \
  --name pgvector \
  --restart unless-stopped \
  --pids-limit 2048 \
  -e TZ=Asia/Shanghai \
  -e POSTGRES_PASSWORD='<password>' \
  -v /data/appdata/pgvector:/var/lib/postgresql/data:rw \
  -p 5433:5432 \
  pgvector/pgvector:pg16

说明：

数据目录务必持久化挂载；首次启动用空目录初始化。
host 网络与 bridge+端口映射二选一；上例用端口映射便于本机多实例。
镜像含 pgvector 扩展，仅需纯 PG 时可换官方 postgres:16。
密码用环境变量注入，勿写进 git；生产再叠加 secrets / 非 root 用户等加固。

验证：

docker ps --filter name=pgvector
psql -h 127.0.0.1 -p 5433 -U postgres -c 'SELECT version();'

注意事项

终止会话、删库删用户前确认对象名，避免打到生产错库。
.pgpass 与 Docker 卷权限按最小权限收紧。
pg_hba / listen_addresses='*' 与防火墙必须一起设计，禁止长期 trust + 0.0.0.0/0。
碎片合并后，同主题旧短文可逐步归档，以免 queue 重复。

参考资料

psql
CREATE ROLE / DATABASE
The Password File
pg_hba.conf
Docker Hub：postgres / pgvector/pgvector

环境说明#

创建用户与数据库#

删除用户与数据库#

只看表结构 / 列名#

垂直显示（类似 MySQL \G）#

会话级开关 \x#

单次查询 \gx（PostgreSQL 10+ 的 psql）#

pager（分屏输出）#

.pgpass 免交互密码#

忘记密码时的重置思路#

临时 trust 本地（常见）#

其它#

Docker 跑实例（示例）#

注意事项#

参考资料#