On 5th of April 2026, Andrew Dunstan committed patch:
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. Author: Akshay Joshi <akshay.joshi@enterprisedb.com> Co-authored-by: Andrew Dunstan <andrew@dunslane.net> Co-authored-by: Euler Taveira <euler@eulerto.com> Reviewed-by: Japin Li <japinli@hotmail.com> Reviewed-by: Chao Li <li.evan.chao@gmail.com> Reviewed-by: Álvaro Herrera <alvherre@kurilemu.de> Reviewed-by: Quan Zongliang <quanzongliang@yeah.net> Discussion: https://postgr.es/m/CANxoLDc6FHBYJvcgOnZyS+jF0NUo3Lq_83-rttBuJgs9id_UDg@mail.gmail.com Discussion: https://postgr.es/m/e247c261-e3fb-4810-81e0-a65893170e94@dunslane.net
and 2nd patch:
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. Author: Mario Gonzalez <gonzalemario@gmail.com> Author: Bryan Green <dbryan.green@gmail.com> Co-authored-by: Andrew Dunstan <andrew@dunslane.net> Co-authored-by: Euler Taveira <euler@eulerto.com> Reviewed-by: Japin Li <japinli@hotmail.com> Reviewed-by: Quan Zongliang <quanzongliang@yeah.net> Reviewed-by: jian he <jian.universality@gmail.com> Discussion: https://postgr.es/m/4c5f895e-3281-48f8-b943-9228b7da6471@gmail.com Discussion: https://postgr.es/m/e247c261-e3fb-4810-81e0-a65893170e94@dunslane.net
and then 3rd patch:
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. Author: Nishant Sharma <nishant.sharma@enterprisedb.com> Author: Manni Wood <manni.wood@enterprisedb.com> Co-authored-by: Andrew Dunstan <andrew@dunslane.net> Co-authored-by: Euler Taveira <euler@eulerto.com> Reviewed-by: Jim Jones <jim.jones@uni-muenster.de> Reviewed-by: Álvaro Herrera <alvherre@kurilemu.de> Reviewed-by: Chao Li <li.evan.chao@gmail.com> Discussion: https://postgr.es/m/CAKWEB6rmnmGKUA87Zmq-s=b3Scsnj02C0kObQjnbL2ajfPWGEw@mail.gmail.com Discussion: https://postgr.es/m/e247c261-e3fb-4810-81e0-a65893170e94@dunslane.net
Let's see what's what. Quick check shows that there are actually four functions, but two of them are just different types of pg_get_tablespace_ddl():
=$ \df pg_get_*_ddl List of functions Schema │ Name │ Result data type │ Argument data types │ Type ────────────┼───────────────────────┼──────────────────┼───────────────────────────────────────────────┼────── pg_catalog │ pg_get_database_ddl │ SETOF text │ regdatabase, VARIADIC text DEFAULT NULL::text │ func pg_catalog │ pg_get_role_ddl │ SETOF text │ regrole, VARIADIC text DEFAULT NULL::text │ func pg_catalog │ pg_get_tablespace_ddl │ SETOF text │ name, VARIADIC text DEFAULT NULL::text │ func pg_catalog │ pg_get_tablespace_ddl │ SETOF text │ oid, VARIADIC text DEFAULT NULL::text │ func (4 rows)
Long story short – these functions will give you SQL-level access to what pg_dump would produce.
So, let's see that in action. Tablespaces are not all that common, but let's make test one, to see how that would work out:
=$ create tablespace test_tablespace location '/tmp/tmp.tablespace' with (seq_page_cost = 0.001); CREATE TABLESPACE =$ select * from pg_get_tablespace_ddl('test_tablespace'); pg_get_tablespace_ddl ──────────────────────────────────────────────────────────────────────────────── CREATE TABLESPACE test_tablespace OWNER depesz LOCATION '/tmp/tmp.tablespace'; ALTER TABLESPACE test_tablespace SET (seq_page_cost='0.001'); (2 rows)
Interestingly, I made it in one command, but the output contains two lines, as Pg generates creation separately from setting options.
If you'd be using many tablespaces, you can get output for all of them using:
=$ select x.* from pg_tablespace t, pg_get_tablespace_ddl(t.oid) x where t.spcname !~ '^pg_'; x ──────────────────────────────────────────────────────────────────────────────── CREATE TABLESPACE test_tablespace OWNER depesz LOCATION '/tmp/tmp.tablespace'; ALTER TABLESPACE test_tablespace SET (seq_page_cost='0.001'); (2 rows)
Now, roles, let's dump it all:
=$ select x.* from pg_roles r, pg_get_role_ddl(r.rolname::regrole) x where r.rolname !~ '^pg_'; x ───────────────────────────────────────────────────────────────────────────────────────────────────────── CREATE ROLE depesz SUPERUSER INHERIT NOCREATEROLE NOCREATEDB LOGIN NOREPLICATION NOBYPASSRLS; CREATE ROLE pgdba SUPERUSER INHERIT CREATEROLE CREATEDB LOGIN REPLICATION BYPASSRLS; CREATE ROLE pgdoc NOSUPERUSER INHERIT NOCREATEROLE NOCREATEDB LOGIN NOREPLICATION NOBYPASSRLS; CREATE ROLE postgres SUPERUSER INHERIT NOCREATEROLE NOCREATEDB LOGIN NOREPLICATION NOBYPASSRLS; CREATE ROLE depesz_explain NOSUPERUSER INHERIT NOCREATEROLE NOCREATEDB LOGIN NOREPLICATION NOBYPASSRLS; (5 rows)
Here is a small issue. Despite the fact that I do actually have password set for one of the roles, it wasn't exported…
=$ select rolname, rolpassword from pg_authid where rolpassword is not null \gx ─[ RECORD 1 ]────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────── rolname │ depesz_explain rolpassword │ SCRAM-SHA-256$4096:Fe/552TH9FAAKkyJprPwVQ==$Ah7TV+j3udVp7IF3StfgkySQi5KUjgVnVlPIaiailrw=:jaQjg95IKjxrfrGv+gIGFljCnAwFwjXHkwCIQSzF2zg=
Of course commit message, and docs, mention it, and I can always make a query that will output the passwords to set, but it is somewhat surprising on the first sight.
For whatever it's worth, such query would look like:
=$ select format('ALTER ROLE %I WITH PASSWORD %L;', rolname, rolpassword) from pg_authid where rolpassword is not null; format ────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────── ALTER ROLE depesz_explain WITH PASSWORD 'SCRAM-SHA-256$4096:Fe/552TH9FAAKkyJprPwVQ==$Ah7TV+j3udVp7IF3StfgkySQi5KUjgVnVlPIaiailrw=:jaQjg95IKjxrfrGv+gIGFljCnAwFwjXHkwCIQSzF2zg='; (1 row)
That leaves me with pg_get_database_ddl(). Which shows us information about database itself. Not objects in it, though:
=$ select * from pg_get_database_ddl('depesz'); pg_get_database_ddl ─────────────────────────────────────────────────────────────────────────────────────────────────────────────────── CREATE DATABASE depesz WITH TEMPLATE = template0 ENCODING = 'UTF8' LOCALE_PROVIDER = libc LOCALE = 'en_US.UTF-8'; ALTER DATABASE depesz OWNER TO depesz; (2 rows)
One last final note is that all of these functions take options. Options are provided as set of strings that mean option name, and value. Like this:
=$ select * from pg_get_database_ddl('depesz', 'pretty', 'true'); pg_get_database_ddl ──────────────────────────────────────── CREATE DATABASE depesz ↵ WITH TEMPLATE = template0 ↵ ENCODING = 'UTF8' ↵ LOCALE_PROVIDER = libc ↵ LOCALE = 'en_US.UTF-8'; ALTER DATABASE depesz OWNER TO depesz; (2 rows)
The ↵ character at the end means that this value contains literal new line character. So first row of result is create table, with all the base options, and the 2nd row is just alter table.
All of these functions take pretty option (which can be true, or false, but as string, not boolean value).
Other than that:
- pg_get_role_ddl – has memberships option (enabled by default) to output which roles this role belongs to
- pg_get_tablespace_ddl – has owner option (enabled by default) to output who is owner of given tablespace
- pg_get_database_ddl – has owner option (enabled by default) to output who is owner of given database, and tablespace option to include tablespace set for given database.
Generally, calling without options will given you concise, and full, quer(y|ies) that will rebuild the object.
We are still missing stuff for tables, views, functions, sequences, and so on, but I guess they will come in time.
All in all – great addition that many people over the years wanted to get. Thanks to everyone involved in this work.
About time!
We’re still waiting for pg_get_table_def. Let us hope we will get it before we pass away xd.