For thirty years, the answer to “how do I get the DDL for this object?” in PostgreSQL has been: shell out to pg_dump -s and grep. Every tool that has ever needed to reconstruct an object definition — migration tools, schema diff utilities, \d replacements, every ORM that has tried to introspect a live database — has either invoked pg_dump, written its own catalog-walking logic, or both. Usually both.
There are a handful of exceptions. pg_get_viewdef(), pg_get_triggerdef(), pg_get_functiondef(), and a few cousins have existed for a while. They work. They’re handy. But they stop at the object types where someone happened to agree the need was sharp.
A commit from Andrew Dunstan on April 5th, with the primary patch by Akshay Joshi and co-authorship from Euler Taveira, is the first installment of the broader “Retail DDL” program. pg_get_database_ddl(regdatabase, VARIADIC text[]) returns the statements required to recreate a database: the CREATE DATABASE on the first row, follow-on ALTER DATABASE statements on subsequent rows. The function accepts alternating name/value options: pretty (boolean), owner (boolean), tablespace (boolean). The caller needs CONNECT on the target database.
Why this is not a pg_dump replacement
It is not meant to be. pg_dump produces a runnable SQL file, handles cross-object dependencies, respects extension ownership, and knows how to deal with the weird corner cases that accumulate in a real schema over ten years. pg_get_database_ddl() serializes one object — a database — with stated semantics for owner and tablespace and nothing else.
The distinction matters because the replacement impulse will be strong. Do not substitute this function for pg_dump in a backup pipeline. Do use it in the places where calling pg_dump has always been the wrong tool: an admin query, a schema-comparison utility, a one-shot audit, a dashboard that answers “show me the DDL for the staging DB.” Anything that today shells out to pg_dump for a single object is a candidate.
What’s coming
The larger project is retail DDL for everything: tables, indexes, sequences, types, roles, policies, publications. Each object type lands as a separate function, with its own options for what to include or omit. Expect the table version to be the next flashpoint — table DDL has the most semantically interesting options (constraints, defaults, generated columns, storage parameters, tablespaces, partitioning), and the discussion on pgsql-hackers about which options belong on the function signature and which belong in a general options bag is already underway.
What to do
Start using pg_get_database_ddl() in PostgreSQL 19 where you would have shelled out before. File bugs on the edge cases — the point of shipping this piecemeal is to catch them now, not after six more object types have copied the same patterns.
And stop writing your own catalog walker. That problem is finally being solved.