PostgreSQL常用命令记录

PostgreSQL 清空、备份、恢复指定 Schema 数据

在 PostgreSQL 中，我们经常会遇到需要清空某个 schema 下所有表数据、备份某个 schema，或者恢复备份数据的情况。下面整理几个常用命令和脚本，方便日常运维使用。

一、清空 Schema 下所有表数据

如果只想清空某个 schema 下所有表的数据，但不删除表结构，可以使用下面的匿名代码块。

DO $$
DECLARE
    r RECORD;
BEGIN
    -- 查询 public schema 下的所有表
    FOR r IN
        SELECT tablename
        FROM pg_tables
        WHERE schemaname = 'public'
    LOOP
        -- 清空表数据，并重置自增序列
        EXECUTE 'TRUNCATE TABLE public.' || quote_ident(r.tablename) || ' RESTART IDENTITY CASCADE';
    END LOOP;
END $$;

脚本说明：

DO $$ ... END $$;：PostgreSQL 的匿名代码块，可以在里面写变量、循环、条件判断等逻辑。
DECLARE r RECORD;：声明一个记录类型变量，用来保存每次查询出来的表名。
FOR r IN ... LOOP：循环遍历当前 schema 下的所有表。
TRUNCATE TABLE：快速清空整张表的数据。
RESTART IDENTITY：重置表中的自增序列。
CASCADE：如果表之间存在外键关联，会同时处理相关依赖表。
quote_ident：用于安全地处理表名，避免表名中存在特殊字符导致 SQL 执行失败。

注意：上面的脚本操作的是 public schema。如果你要清空其他 schema，例如 mawl00，需要把脚本中的 public 改成对应的 schema 名称。

二、备份指定 Schema 的结构和数据

如果需要备份某个 schema 下所有表的结构和数据，可以使用 pg_dump 命令。

pg_dump -U postgres -d 数据库名 -n schema名 > backup.sql

参数说明：

-U postgres：指定连接数据库的用户为 postgres。
-d 数据库名：指定要备份的数据库名称。
-n schema名：指定只备份某个 schema。
> backup.sql：将备份内容输出到 backup.sql 文件中。

例如：

pg_dump -U postgres -d my_database -n public > backup.sql

如果执行命令时遇到：

Peer authentication failed for user "postgres"

说明当前 Linux 用户无法直接通过 postgres 数据库用户进行本地认证。可以切换到 Linux 系统中的 postgres 用户来执行命令。

解决方式如下：

sudo -u postgres pg_dump -d 数据库名 -n schema名 > backup.sql

例如：

sudo -u postgres pg_dump -d my_database -n public > backup.sql

三、恢复指定 Schema 的结构和数据

如果已经有备份文件 backup.sql，可以使用 psql 命令恢复到指定数据库中。

psql -U postgres -d 数据库名 < backup.sql

例如：

psql -U postgres -d my_database < backup.sql

如果同样遇到 Peer authentication failed for user "postgres"，可以使用下面的方式执行：

sudo -u postgres psql -d 数据库名 < backup.sql

例如：

sudo -u postgres psql -d my_database < backup.sql

四、常见注意事项

清空数据前一定要确认 schema 名称，避免误清空其他业务表。
TRUNCATE 执行速度很快，但也很危险，执行前建议先做好备份。
RESTART IDENTITY 会重置自增 ID，如果不想重置自增序列，可以去掉该参数。
CASCADE 会处理外键关联表，使用前需要确认是否会影响其他表数据。
如果只是备份某个 schema，建议使用 pg_dump -n schema名，不要直接备份整个数据库。
恢复数据前，最好确认目标数据库是否已经存在相同的 schema 和表结构，避免冲突。

五、总结

日常 PostgreSQL 运维中，清空、备份和恢复 schema 是比较常见的操作。简单总结如下：

# 备份指定 schema
pg_dump -U postgres -d 数据库名 -n schema名 > backup.sql

# 恢复备份文件
psql -U postgres -d 数据库名 < backup.sql

# 如果遇到 Peer authentication failed
sudo -u postgres pg_dump -d 数据库名 -n schema名 > backup.sql
sudo -u postgres psql -d 数据库名 < backup.sql

实际操作时，建议先在测试环境验证脚本，再到正式环境执行，避免误操作造成数据丢失。