写在前面
PostgreSQL 跑在 Docker 里之后,大部分操作都要通过 docker exec 进入容器执行。命令本身不难,但时间久了容易忘,这里把日常最常用的整理在一起。
下文假设:
- 容器名为
postgres - 数据库用户为
nextnewep,数据库为 nextnewep
使用时替换成自己的实际名称。
连接数据库
进入容器里的 psql 交互终端:
1
| docker exec -it postgres psql -U nextnewep -d nextnewep
|
-it 用于交互式会话。退出 psql 输入 \q。
执行本地 SQL 脚本
把宿主机上的 SQL 文件直接交给容器中的 psql 执行:
1
| docker exec -i postgres psql -U nextnewep -d nextnewep < ./000_nextnewep.sql
|
这里用 Shell 的 < 把本地文件作为标准输入传进容器,不需要先把文件拷贝进去。注意要加 -i 保持标准输入打开,但不要加 -t,否则会报 the input device is not a TTY。
希望脚本出错时立即停止,而不是继续执行后面的语句,可以加上 -v ON_ERROR_STOP=1:
1
| docker exec -i postgres psql -U nextnewep -d nextnewep -v ON_ERROR_STOP=1 < ./000_nextnewep.sql
|
只执行一条语句时用 -c:
1
| docker exec postgres psql -U nextnewep -d nextnewep -c "SELECT version();"
|
psql 常用元命令
进入 psql 后,以反斜杠开头的是元命令:
| 命令 | 作用 |
|---|
\l | 列出所有数据库 |
\c 数据库名 | 切换到另一个数据库 |
\dt | 列出当前数据库的表 |
\d 表名 | 查看表结构、索引和约束 |
\du | 列出用户和角色 |
\dn | 列出 schema |
\x | 切换扩展显示,字段多时按行竖排,便于阅读 |
\timing | 显示每条语句的执行耗时 |
\q | 退出 |
创建数据库和用户
1
2
3
| CREATE USER app_user WITH PASSWORD 'change-me';
CREATE DATABASE app_db OWNER app_user;
GRANT ALL PRIVILEGES ON DATABASE app_db TO app_user;
|
PostgreSQL 15 起,普通用户默认不能在 public schema 中建表。如果用户不是数据库的所有者,还需要切换到该数据库后授权:
1
2
| \c app_db
GRANT ALL ON SCHEMA public TO app_user;
|
备份与恢复
用 pg_dump 备份
导出为纯 SQL 文件,结果直接写到宿主机:
1
| docker exec postgres pg_dump -U nextnewep -d nextnewep > nextnewep_$(date +%F).sql
|
数据量较大时,用自定义格式(-Fc)体积更小,恢复时也可以只挑部分表:
1
| docker exec postgres pg_dump -U nextnewep -d nextnewep -Fc > nextnewep_$(date +%F).dump
|
恢复
纯 SQL 备份用 psql 恢复:
1
| docker exec -i postgres psql -U nextnewep -d nextnewep < nextnewep_2025-01-10.sql
|
自定义格式用 pg_restore 恢复:
1
| docker exec -i postgres pg_restore -U nextnewep -d nextnewep --clean --if-exists < nextnewep_2025-01-10.dump
|
--clean --if-exists 会先删除再重建已存在的对象,恢复到已有数据的库前务必确认。
日常维护查询
查看各数据库大小:
1
2
3
| SELECT datname, pg_size_pretty(pg_database_size(datname)) AS size
FROM pg_database
ORDER BY pg_database_size(datname) DESC;
|
查看当前数据库中最大的表:
1
2
3
4
| SELECT relname, pg_size_pretty(pg_total_relation_size(relid)) AS total_size
FROM pg_catalog.pg_statio_user_tables
ORDER BY pg_total_relation_size(relid) DESC
LIMIT 10;
|
查看当前连接:
1
2
3
| SELECT pid, usename, datname, client_addr, state, query_start
FROM pg_stat_activity
WHERE datname IS NOT NULL;
|
结束某个卡住的连接:
1
| SELECT pg_terminate_backend(<pid>);
|
总结
- 交互操作用
docker exec -it,执行本地脚本用 docker exec -i 加 < 重定向。 - 结构查看靠
psql 元命令,\d 表名 最常用。 - 定期用
pg_dump 备份,大库优先用 -Fc 自定义格式。