作者：bytehouse

Oracle ACE、PostgreSQL ACE

10+年数据库架构与运维实战经验

公众号：bytehouse

墨天轮专栏：bytehouse

CSDN：Young DBA

近期，PostgreSQL 社区邮件列表提出了 pg_get*ddl 系列函数的设计方案。

相信很多同学都曾为如何便捷获取数据库、角色、表空间等对象的 DDL 定义而烦恼。pg_get*ddl 相关的提议，它可以让我们更快速、更规范、更可靠地导出数据库对象定义，极大提升工作效率，无论是日常运维、结构比对，还是数据库迁移、备份重建，都会变得更加轻松便捷。

Add infrastructure for pg_get*ddl functions

Add parse_ddl_options(), append_ddl_option(), and append_guc_value() helper functions in a new ddlutils.c file that provide common option parsing and output formatting for the pg_get*ddl family of functions which will follow in later patches. These accept VARIADIC text arguments as alternating name/value pairs.

Callers declare an array of DdlOption descriptors specifying the accepted option names and their types (boolean, text, or integer). parse_ddl_options() matches each supplied pair against the array, validates the value, and fills in the result fields. This descriptor-based scheme is based on an idea from Euler Taveira.

This is placed in a new ddlutils.c file which will contain the pg_get*ddl functions.

Add pg_get_database_ddl() function

Add a new SQL-callable function that returns the DDL statements needed to recreate a database. It takes a regdatabase argument and an optional VARIADIC text argument for options that are specified as alternating name/value pairs. The following options are supported: pretty (boolean) for formatted output, owner (boolean) to include OWNER and tablespace (boolean) to include TABLESPACE. The return is one or multiple rows where the first row is a CREATE DATABASE statement and subsequent rows are ALTER DATABASE statements to set some database properties.

The caller must have CONNECT privilege on the target database.

Add pg_get_tablespace_ddl() function

Add a new SQL-callable function that returns the DDL statements needed to recreate a tablespace. It takes a tablespace name or OID and an optional VARIADIC text argument for options that are specified as alternating name/value pairs. The following options are supported: pretty (boolean) for formatted output and owner (boolean) to include OWNER. (It includes two variants because there is no regtablespace pseudotype.) The return is one or multiple rows where the first row is a CREATE TABLESPACE statement and subsequent rows are ALTER TABLESPACE statements to set some tablespace properties.

The caller must have SELECT privilege on pg_tablespace.

get_reloptions() in ruleutils.c is made non-static so it can be called from the new ddlutils.c file.

Add pg_get_role_ddl() function

Add a new SQL-callable function that returns the DDL statements needed to recreate a role. It takes a regrole argument and an optional VARIADIC text argument for options that are specified as alternating name/value pairs. The following options are supported: pretty (boolean) for formatted output and memberships (boolean) to include GRANT statements for role memberships and membership options. The return is one or multiple rows where the first row is a CREATE ROLE statement and subsequent rows are ALTER ROLE statements to set some role properties. Password information is never included in the output.

The caller must have SELECT privilege on pg_authid.

Add target_relid parameter to pg_get_publication_tables().

When a tablesync worker checks whether a specific table is published, it previously issued a query to the publisher calling pg_get_publication_tables() and filtering the result by relid via a WHERE clause. Because the function itself was fully evaluated before the filter was applied, this forced the publisher to enumerate all tables in the publication. For publications covering a large number of tables, this resulted in expensive catalog scans and unnecessary CPU overhead on the publisher.

This commit adds a new overloaded form of pg_get_publication_tables() that accepts an array of publication names and a target table OID. Instead of enumerating all published tables, it evaluates membership for the specified relation via syscache lookups, using the new is_table_publishable_in_publication() helper. This helper correctly accounts for publish_via_partition_root, ALL TABLES with EXCEPT clauses, schema publications, and partition inheritance, while avoiding the overhead of building the complete published table list.

The existing VARIADIC array form of pg_get_publication_tables() is preserved for backward compatibility. Tablesync workers use the new two-argument form when connected to a publisher running PostgreSQL 19 or later.