又一个槽点被干掉了：PG 19 将内置DDL 抽取

PG 19 终于要内置数据库对象 DDL 抽取接口了

本文档解读 Andrew Dunstan 提交的 pg_get_*_ddl 函数系列，这些函数可以抽取数据库、表空间、角色等数据库对象的 DDL 语句，便于元数据备份、文档生成和审计。

相关 Commit

核心价值

• 元数据备份：抽取数据库对象的 DDL 定义
• 审计追踪：记录 schema 变更历史
• 文档生成：自动生成数据库文档

原理详解

1. 基础设施架构

1.1 函数注册 (src/backend/utils/adt/ddlutils.c)

所有 pg_get_*_ddl 函数使用统一的接口：

/*
 * 基础设施函数
 * 支持任意数量和类型的选项，以 name/value 对形式传入
 */
Datum
pg_get_database_ddl(PG_FUNCTION_ARGS)
{
/* 获取参数 */
    Oid database = PG_GETARG_OID(0);
/* 解析可选参数 (VARIADIC text) */
bool pretty = false;
bool owner = true;
bool tablespace = true;
if (PG_NARGS() > 1)
    {
/* 解析 name/value 对 */
// ...
    }
/* 生成 DDL */
    StringInfoData buf;
    initStringInfo(&buf);
/* 添加 CREATE DATABASE */
    appendStringInfo(&buf, "CREATE DATABASE %s",
                     quote_identifier(get_database_name(database)));
/* 添加 OWNER */
if (owner)
    {
        Oid ownerOid = get_database_owner(database);
        appendStringInfo(&buf, "\nALTER DATABASE %s OWNER TO %s;",
            quote_identifier(get_database_name(database)),
            quote_identifier(get_user_name_from_id(ownerOid)));
    }
/* 添加 TABLESPACE */
if (tablespace)
    {
// ...
    }
    PG_RETURN_TEXT_P(cstring_to_text(buf.data));
}

2. pg_get_database_ddl()

2.1 函数签名

pg_get_database_ddl(database regdatabase, VARIADIC options text[])
RETURNS text

2.2 支持的选项

2.3 使用示例

-- 基本用法
SELECT pg_get_database_ddl('mydb'::regdatabase);
-- 格式化输出
SELECT pg_get_database_ddl('mydb'::regdatabase, 'pretty' := true);
-- 不包含 OWNER 和 TABLESPACE
SELECT pg_get_database_ddl('mydb'::regdatabase, 'owner' := false, 'tablespace' := false);

3. pg_get_tablespace_ddl()

3.1 函数签名

pg_get_tablespace_ddl(tablespace regnamespace, VARIADIC options text[])
RETURNS text

3.2 支持的选项

3.3 使用示例

-- 获取表空间 DDL
SELECT pg_get_tablespace_ddl('pg_default'::regnamespace);
-- 格式化输出
SELECT pg_get_tablespace_ddl('mytbs'::regnamespace, 'pretty' := true);

4. pg_get_role_ddl()

4.1 函数签名

pg_get_role_ddl(role regrole, VARIADIC options text[])
RETURNS text

4.2 支持的选项

4.3 使用示例

-- 获取角色 DDL（包含成员资格）
SELECT pg_get_role_ddl('myrole'::regrole);
-- 格式化输出
SELECT pg_get_role_ddl('myrole'::regrole, 'pretty' := true);

5. 权限检查

这些函数有权限要求：

-- 必须有 CONNECT 权限才能获取数据库 DDL
GRANTCONNECTONDATABASE mydb TO calling_role;
-- 角色 DDL 通常只能 superuser 或角色自身调用

使用实践

1. 备份数据库 Schema

-- 创建 schema 备份函数
CREATEORREPLACEFUNCTION backup_schema_ddl()
RETURNSTABLE(ddltext) AS $$
DECLARE
    rec record;
BEGIN
-- 获取数据库 DDL
ddl := pg_get_database_ddl(current_database()::regdatabase);
    RETURN NEXT;
-- 获取所有表空间
    FOR rec IN SELECT spcname FROM pg_tablespace LOOP
ddl := pg_get_tablespace_ddl(rec.spcname::regnamespace);
        RETURN NEXT;
ENDLOOP;
-- 获取所有角色
    FOR rec IN SELECT rolname FROM pg_roles LOOP
ddl := pg_get_role_ddl(rec.rolname::regrole);
        RETURN NEXT;
ENDLOOP;
END;
$$ LANGUAGE plpgsql;
-- 执行备份
COPY (SELECT backup_schema_ddl()) TO'/tmp/schema_backup.sql';

2. 审计 Schema 变更

-- 创建变更记录表
CREATETABLE schema_change_log (
idserial PRIMARY KEY,
    recorded_at timestamptz DEFAULTnow(),
    object_type text,
    object_name text,
ddltext
);
-- 记录当前状态
INSERTINTO schema_change_log(object_type, object_name, ddl)
SELECT'database', current_database(), pg_get_database_ddl(current_database()::regdatabase);
-- 查看变更历史
SELECT recorded_at, object_type, object_name
FROM schema_change_log
ORDERBY recorded_at DESC;

3. 生成数据库文档

-- 创建文档生成视图
CREATEORREPLACEVIEW database_documentation AS
SELECT
'Database: ' || current_database() ASsection,
    pg_get_database_ddl(current_database()::regdatabase, 'pretty' := true) AScontent
UNIONALL
SELECT
'Tablespace: ' || spcname,
    pg_get_tablespace_ddl(spcname::regnamespace, 'pretty' := true)
FROM pg_tablespace
WHERE spcname NOTLIKE'pg_%'
UNIONALL
SELECT
'Role: ' || rolname,
    pg_get_role_ddl(rolname::regrole, 'pretty' := true)
FROM pg_roles
WHERENOT rolname LIKE'pg_%';
-- 生成 Markdown 文档
\pset format aligned
\o database_docs.md
SELECT * FROM database_documentation;
\o

4. CI/CD Schema 比对

-- 比较两个数据库的 schema
CREATEORREPLACEFUNCTION compare_schema_ddl(db1 regdatabase, db2 regdatabase)
RETURNSTABLE(only_in_db1 boolean, only_in_db2 boolean, ddltext) AS $$
BEGIN
-- 简化实现，实际需要更复杂的比较逻辑
-- ...
END;
$$ LANGUAGE plpgsql;

5. 权限管理审计

-- 导出所有角色的 DDL（包含成员资格）
SELECT pg_get_role_ddl(rolname::regrole, 'pretty' := true) AS role_ddl
FROM pg_roles
WHERENOT rolname LIKE'pg_%'
ORDERBY rolname;

6. 迁移准备

-- 在迁移前获取所有 DDL
-- 确保可以在新环境中重建相同的配置
-- 导出所有角色的成员资格
WITHrolesAS (
SELECT
        r.rolname,
        r.rolsuper,
        r.rolcreatedb,
        r.rolcreaterole,
        r.rolreplication,
        r.rolbypassrls,
ARRAY(
SELECT g.rolname
FROM pg_auth_members m
JOIN pg_roles g ON g.oid = m.roleid
WHERE m.member = r.oid
        ) AS member_of
FROM pg_roles r
WHERE r.rolname NOTLIKE'pg_%'
)
SELECT
'CREATE ROLE ' || rolname ||
CASEWHEN rolsuper THEN' SUPERUSER'ELSE''END ||
CASEWHEN rolcreatedb THEN' CREATEDB'ELSE''END ||
CASEWHEN rolcreaterole THEN' CREATEROLE'ELSE''END ||
CASEWHEN rolreplication THEN' REPLICATION'ELSE''END ||
CASEWHEN rolbypassrls THEN' BYPASSRLS'ELSE''END ||
';'AS create_role_ddl,
COALESCE(
'GRANT ' || array_to_string(member_of, ', ') || ' TO ' || rolname || ';',
''
    ) AS grant_ddl
FROMroles;

7. 自动化 Schema 快照

#!/bin/bash
# 每日 schema 快照脚本
PGDATABASE="mydb"
SNAPSHOT_DIR="/var/backups/schema"
DATE=$(date +%Y%m%d)
mkdir -p "$SNAPSHOT_DIR"
# 获取 schema DDL
psql -d "$PGDATABASE" -t -c "
    SELECT pg_get_database_ddl('$PGDATABASE'::regdatabase, 'pretty' := true);
" > "$SNAPSHOT_DIR/database_$DATE.sql"
# 记录快照信息
echo"$DATE: Schema snapshot created" >> "$SNAPSHOT_DIR/snapshots.log"

限制与注意事项

1. 当前限制

-- 目前只能获取单个对象
-- 不支持批量获取（如一次获取所有表）
-- 不包含数据
-- 只获取 schema 定义
-- 不包含依赖关系
-- 需要手动处理顺序

2. 权限要求

3. 不包含的内容

-- 需要额外处理的内容：
-- - 表数据 (使用 pg_dump)
-- - 索引定义 (未来可能支持)
-- - 触发器 (未来可能支持)
-- - 约束 (未来可能支持)

总结

pg_get_*_dl 函数系列为 DBA 提供了便捷的 DDL 抽取能力。

关键要点：

1. 透明接口：统一的 VARIADIC options 参数设计
2. 格式化支持：pretty print 选项使输出更易读
3. 权限控制：有适当的权限检查
4. 审计友好：便于 schema 变更追踪

使用场景：

1. Schema 备份和恢复
2. 数据库文档生成
3. 变更审计追踪
4. CI/CD schema 比对
5. 迁移准备