作者：bytehouse Oracle ACE、PostgreSQL ACE

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

公众号：bytehouse

墨天轮专栏：bytehouse

CSDN：Young DBA

摘要：针对 Every Day of a DBA，第132期: 新增 pg_get_*_ddl 系列函数并优化。今天有时间编译了测试环境，针对下面几个新特性进行了测试。后续将延续这个话题，测试反馈更多的特性给你大家。

一、数据库DDL导出函数 pg_get_database_ddl

测试SQL

select pg_get_database_ddl(oid) as database from pg_database where datname=current_database();

执行结果

database                                                        
--------------------------------------------------------------------------------------------------------------------
CREATE DATABASE bigdata WITH TEMPLATE = template0 ENCODING = ‘UTF8’ LOCALE_PROVIDER = libc LOCALE = ‘en_US.UTF-8’;
ALTER DATABASE bigdata OWNER TO postgres;
(2 rows)

说明

内置函数可一键输出数据库完整创建+赋权DDL语句，便于数据库元数据备份、迁移。

二、表空间（PG19 新增AIO异步IO参数）

前置要求

表空间对应本地目录需提前创建，目录属主/权限为 postgres 用户读写权限。

1. 基础表空间创建（无AIO参数）

CREATE TABLESPACE ts_hot LOCATION ‘/opt/postgresql-19beta1/data/ts_hot’;

执行反馈

WARNING: tablespace location should not be inside the data directory
CREATE TABLESPACE

警告说明：表空间路径不建议放置在PG主数据目录内，规范部署应独立磁盘目录。

查看表空间基础元数据

select * from pg_tablespace ;

结果：

oid | spcname   | spcowner | spcacl | spcoptions
-------±-----------±---------±-------±-----------
1663 | pg_default |       10 |       |
1664 | pg_global |       10 |       |
16457 | ts_hot     |       10 |       |
(3 rows)

2. PG19新特性：创建表空间指定AIO异步IO参数

CREATE TABLESPACE ts_cold
LOCATION ‘/opt/postgresql-19beta1/data/ts_cold’  – 原测试笔误ts_hot，已修正
WITH (
  io_min_workers = 2,
  io_max_workers = 8
);

参数释义：

• io_min_workers：AIO最小工作线程

• io_max_workers：AIO最大工作线程

3. 在线修改表空间AIO参数（无需重启实例）

– 修改最大AIO线程
ALTER TABLESPACE ts_cold SET (io_max_workers = 12);
– 重载配置生效
SELECT pg_reload_conf();

4. PG19新增函数：pg_get_tablespace_ddl 导出表空间完整DDL

测试语句

select pg_get_tablespace_ddl(16457);
– 或按表空间名称过滤
SELECT pg_get_tablespace_ddl(oid) FROM pg_tablespace WHERE spcname = ‘ts_cold’;

ts_hot 输出结果

pg_get_tablespace_ddl                                  
-----------------------------------------------------------------------------------------
CREATE TABLESPACE ts_hot OWNER postgres LOCATION ‘/opt/postgresql-19beta1/data/ts_hot’;
(1 row)

ts_cold 输出会自动携带 WITH AIO 参数配置，完整还原建表空间语句。

5. 表空间业务操作

（1）原生REPACK迁移整表至目标表空间

ALTER TABLE big_table REPACK TABLESPACE ts_hot;

无需第三方pg_repack插件，内核原生支持在线表空间迁移。

（2）分区拆分并为新分区指定不同冷热表空间

ALTER TABLE range_t SPLIT PARTITION p1 AT (1000)
INTO (PARTITION p1_1 TABLESPACE ts_hot, PARTITION p1_2 TABLESPACE ts_cold);

支持拆分分区时分别指定冷热存储介质表空间，分层存储。

三、角色DDL导出函数 pg_get_role_ddl

测试SQL

select pg_get_role_ddl(16458);

执行结果

pg_get_role_ddl                                          
--------------------------------------------------------------------------------------------------
CREATE ROLE admin NOSUPERUSER INHERIT NOCREATEROLE NOCREATEDB NOLOGIN NOREPLICATION NOBYPASSRLS;
(1 row)

说明

一键导出角色完整创建语句，包含所有权限开关，用于账号元数据备份、环境同步。

四、逻辑复制发布集查询 pg_get_publication_tables

测试SQL（关联查询获取发布集全量表信息）

SELECT
  p.pubid,
  p.relid,
  nsp.nspname AS schemaname,
  cls.relname AS tablename
FROM pg_get_publication_tables(‘pub_all’) p
JOIN pg_class cls ON p.relid = cls.oid
JOIN pg_namespace nsp ON cls.relnamespace = nsp.oid;

执行结果

pubid | relid | schemaname | tablename
-------±------±-----------±----------
16471 | 16414 | public     | users
16471 | 16422 | public     | follow
16471 | 16459 | public     | test_log
16471 | 16462 | public     | tmp_table
16471 | 16465 | public     | biz_data
(5 rows)

说明

通过内置函数快速获取指定publication下所有同步表，关联系统表带出库名、表名，用于CDC链路元数据巡检。

测试功能汇总

1. 元数据DDL导出系列（PG19新增）

   • pg_get_database_ddl：导出数据库DDL

   • pg_get_tablespace_ddl：导出表空间DDL（支持AIO参数完整还原）

   • pg_get_role_ddl：导出角色创建语句

2. 表空间增强（PG19核心新特性）

   • 创建/修改表空间支持AIO异步IO线程参数 io_min_workers / io_max_workers

   • ALTER TABLE … REPACK TABLESPACE 原生在线迁移表空间

   • 分区拆分时独立指定分区表空间，冷热分层存储

3. 逻辑复制元数据查询

   • pg_get_publication_tables 获取发布集同步表清单，可关联系统表完整巡检

4. 元数据备份配套能力：数据库、表空间、账号均支持一键导出建库/建表空间/建角色语句