Every Day of a DBA，第132期: 新增 pg_get*ddl 系列函数并优化

作者：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 optionparsing and output formatting for the pg_get*ddl family of functions which will follow in later patches. These accept VARIADIC textarguments 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 optionalVARIADIC 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 rowswhere 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 anoptional 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 statementsto 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 VARIADICtext argument for options that are specified as alternating name/value pairs. The following options are supported: pretty (boolean) forformatted output and memberships (boolean) to include GRANT statements for role memberships and membership options. The return is one ormultiple 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 callingpg_get_publication_tables() and filtering the result by relid via a WHERE clause. Because the function itself was fully evaluated beforethe filter was applied, this forced the publisher to enumerate all tables in the publication. For publications covering a large number oftables, 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 tableOID. Instead of enumerating all published tables, it evaluates membership for the specified relation via syscache lookups, using thenew is_table_publishable_in_publication() helper. This helper correctly accounts for publish_via_partition_root, ALL TABLES withEXCEPT 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 newtwo-argument form when connected to a publisher running PostgreSQL 19 or later.