Featured image of post PostgreSQL 日常使用命令:Docker 环境下的连接、查询与备份

PostgreSQL 日常使用命令:Docker 环境下的连接、查询与备份

整理 Docker 中运行 PostgreSQL 时的常用命令,包括用 docker exec 进入 psql、执行本地 SQL 脚本、psql 元命令查看库表结构、创建数据库与用户授权、用 pg_dump 备份和恢复,以及查看连接数和数据库大小等日常维护操作。

-- 次浏览
-- 条评论

写在前面

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 自定义格式。