Summary: PostgreSQL 19 adds pg_get_database_ddl(), pg_get_role_ddl(), and pg_get_tablespace_ddl() functions for programmatic DDL extraction, and extends pg_dumpall to support custom, directory, and tar output formats. Together, these features give you better tools for schema management, auditing, and backup workflows.

pgget*_ddl() Functions

PostgreSQL has long provided pg_get_tabledef() and pg_get_viewdef() for extracting DDL of individual objects. But getting the DDL for databases, roles, and tablespaces required parsing pg_dump or pg_dumpall output, which is fragile and inconvenient.

PostgreSQL 19 adds three new functions that return clean, executable DDL directly from SQL.

pg_get_database_ddl()

Returns the CREATE DATABASE and ALTER DATABASE statements for a given database:

SELECT * FROM pg_get_database_ddl('myapp');
pg_get_database_ddl
--------------------------------------------------------------------------------------------
 CREATE DATABASE myapp WITH TEMPLATE = template0 ENCODING = 'UTF8' LOCALE_PROVIDER = libc
   LC_COLLATE = 'en_US.UTF-8' LC_CTYPE = 'en_US.UTF-8';
 ALTER DATABASE myapp OWNER TO app_admin;
 ALTER DATABASE myapp SET timezone TO 'UTC';
(3 rows)

Each row is a complete, executable SQL statement. The first row is the CREATE statement, and subsequent rows are ALTER statements for properties like owner, connection limit, and configuration settings.

Options:

Options are passed as alternating name, value text pairs after the database argument. Supported options: pretty (boolean), owner (boolean), tablespace (boolean).

-- Disable owner output
SELECT * FROM pg_get_database_ddl('myapp', 'owner', 'false');

-- Pretty-printed output
SELECT * FROM pg_get_database_ddl('myapp', 'pretty', 'true');

-- Combine multiple options
SELECT * FROM pg_get_database_ddl('myapp', 'pretty', 'true', 'tablespace', 'false');

pg_get_role_ddl()

Returns the CREATE ROLE and related statements for a given role:

SELECT * FROM pg_get_role_ddl('app_user');
pg_get_role_ddl
---------------------------------------------------------------------------
 CREATE ROLE app_user LOGIN CONNECTION LIMIT 100;
 GRANT developer TO app_user;
 GRANT readonly TO app_user;
(3 rows)

The first row is the CREATE ROLE statement with all role attributes (LOGIN, SUPERUSER, CREATEDB, etc.). Subsequent rows include GRANT statements for role memberships.

Options:

Options are passed as alternating name, value text pairs after the role argument. Supported options: pretty (boolean) and memberships (boolean, defaults to true).

-- Omit GRANT statements for role memberships
SELECT * FROM pg_get_role_ddl('app_user', 'memberships', 'false');

-- Pretty-printed output
SELECT * FROM pg_get_role_ddl('app_user', 'pretty', 'true');

note

Passwords are never included in the output for security reasons. To migrate roles with passwords, use pg_dumpall --roles-only.

pg_get_tablespace_ddl()

Returns the CREATE TABLESPACE and ALTER TABLESPACE statements:

SELECT * FROM pg_get_tablespace_ddl('fast_storage');
pg_get_tablespace_ddl
---------------------------------------------------------------------------
 CREATE TABLESPACE fast_storage LOCATION '/mnt/nvme/pg_data';
 ALTER TABLESPACE fast_storage OWNER TO postgres;
 ALTER TABLESPACE fast_storage SET (seq_page_cost = 0.5, random_page_cost = 1.0);
(3 rows)

Practical Use Cases

The DDL extraction functions plug naturally into auditing, migration tooling, and drift detection. The patterns below show one example of each.

Schema auditing

Compare the current state of roles across environments:

-- Export all role DDL for comparison
SELECT r.rolname, d.ddl
FROM pg_roles r
CROSS JOIN LATERAL pg_get_role_ddl(r.rolname::regrole) AS d(ddl)
WHERE r.rolname NOT LIKE 'pg_%'
ORDER BY r.rolname;

Migration scripts

Generate DDL for specific databases without running pg_dump:

-- Generate a migration script for a database
\t on
\o /tmp/recreate_myapp.sql
SELECT * FROM pg_get_database_ddl('myapp', 'pretty', 'true');
\o
\t off

Configuration drift detection

Store DDL snapshots and compare them over time:

-- Store current state
CREATE TABLE ddl_snapshots (
    captured_at TIMESTAMP DEFAULT now(),
    object_type TEXT,
    object_name TEXT,
    ddl_line TEXT
);

INSERT INTO ddl_snapshots (object_type, object_name, ddl_line)
SELECT 'database', 'myapp', ddl
FROM pg_get_database_ddl('myapp') AS t(ddl);

pg_dumpall Non-Text Output Formats

pg_dumpall has historically only supported plain text output. If you wanted custom format (for selective restore with pg_restore) or directory format (for parallel dump/restore), you had to use pg_dump per-database and handle globals separately.

PostgreSQL 19 adds support for custom, directory, and tar output formats to pg_dumpall.

Available Formats

The new formats mirror the ones pg_dump already supports. Pick custom for a single compressed archive, directory for parallel dump and restore, and tar for portability with archive tooling.

# Custom format (single compressed file, supports pg_restore)
pg_dumpall -Fc -f cluster_backup

# Directory format (parallel dump/restore capable)
pg_dumpall -Fd -f cluster_backup_dir

# Tar format
pg_dumpall -Ft -f cluster_backup.tar

Output Structure

The non-text formats produce a structured output containing:

  • toc.glo - Global objects (roles, tablespaces) in custom format
  • map.dat - Mapping between database OIDs and database names
  • databases/ - A subdirectory with per-database archives, organized by OID

Selective Restore

The main advantage of non-text formats is selective restore with pg_restore:

# Restore only global objects (roles, tablespaces)
pg_restore --globals-only cluster_backup

# Restore a specific database from the cluster backup
pg_restore --dbname=myapp cluster_backup

# List contents without restoring
pg_restore --list cluster_backup

Parallel Operations

Directory format supports parallel dump and restore:

# Parallel dump
pg_dumpall -Fd -j 4 -f cluster_backup_dir

# Parallel restore
pg_restore -j 4 -d postgres cluster_backup_dir

Comparison to Previous Workflow

Before PostgreSQL 19, a full cluster backup with selective restore required a multi-step process:

# Old approach: dump globals separately, then each database
pg_dumpall --globals-only > globals.sql
pg_dump -Fc myapp > myapp.dump
pg_dump -Fc analytics > analytics.dump

# Restore selectively
psql -f globals.sql
pg_restore -d myapp myapp.dump

Now it is a single command:

# New approach: one command, selective restore built in
pg_dumpall -Fc -f cluster_backup
pg_restore --globals-only cluster_backup
pg_restore --dbname=myapp cluster_backup

Summary

The pg_get_*_ddl() functions and pg_dumpall improvements address a gap in PostgreSQL's schema management tooling. DDL extraction no longer requires parsing text output from pg_dump. Cluster-wide backups now support the same flexible formats that per-database backups have had for years.

References