Subject: Re: [PATCH] Add pg_get_database_ddl() function to reconstruct CREATE DATABASE statement

From: Andrew Dunstan <[email-masked]>
Date: Wed, 7 Jan 2026 18:27:54 -0500

On 2025-12-12 Fr 10:19 AM, Euler Taveira wrote:
> On Fri, Dec 12, 2025, at 7:52 AM, Akshay Joshi wrote:
>> On Thu, Dec 11, 2025 at 7:29 PM Euler Taveira<[email-masked]> wrote:
>> Is there any way to obtain the default values directly from the source
>> code itself, or do I need to refer to the documentation? If we rely on
>> the documentation and compare against that, then in the future, if the
>> default values change, we would also need to update our logic
>> accordingly.
>>
> No, you need to check the documentation. If you are changing the default value,
> you are breaking compatibility; that rarely happens. If we are really concern
> about this fact, you can add a test case that creates the object without
> properties (all default values) and another with all default properties and
> then compare the output.

Maybe the function should have a VERBOSE option that emits all the defaults.

>
>> Constantly having to check the documentation for default values may
>> feel annoying to some users. Some users run queries with parameters
>> such as encoding, connection limit, and locale using their default
>> values. When they call the pg_get_database_ddl function, it
>> reconstructs the short command based on those defaults.
>>
> Encoding and locale, ok but I doubt about connection limit.
>
> postgres=# SELECT current_user;
>   current_user
> --------------
>   euler
> (1 row)
>
> postgres=# CREATE DATABASE foo;
> CREATE DATABASE
> postgres=# CREATE DATABASE bar OWNER euler;
> CREATE DATABASE
>
> When you are learning a new command, you generally don't set the default value
> for a property just to be correct. I'm not saying this function shouldn't
> include OWNER. I'm just suggesting it to be optional.  See some arguments
> below.
>
>>> * OWNER. There is no guarantee that the owner exists in the cluster you will
>>>    use this output. That's something that pg_dumpall treats separately (see
>>>    above). Does it mean we should include the owner? No. We can make it an
>>>    option.
>>>
>> If I understand correctly, the owner should be an option provided by
>> the caller of the function, and we reconstruct the Database DDL using
>> that specified owner. Is that right?
>> If so, then in my humble opinion, this is not truly a reconstruction
>> of the existing database object.
>>
> No. My idea is to have something like the pg_dump --no-owner option. This is
> important if you are transporting the objects from one cluster to another one.
> Owner might be different. That's why I'm suggesting it should be optional. It
> means flexibility. See pg_dump output format that always apply the OWNER as a
> separate ALTER command.

+1

>
>>> * options. Since I mentioned options for some properties (owner, strategy,
>>>    template), these properties can be accommodated as a VARIADIC argument. The
>>>    function signature can be something like
>>>
>>> pg_get_database_ddl(oid, VARIADIC options text[])
>>>
>>> I would include the pretty print into options too.
>>>
>> Same comment as the one I gave for the Owner, if you are referring to
>> these as options to the function.
>>
> Let me elaborate a bit. As I suggested you can control the output with options.
> Why? Flexibility.
>
> Why am I suggesting such a general purpose implementation? See some of the use
> cases.
>
> 1. object DDL. Check DDL to recreate the object. It is not the exact DDL that
> the user informed but it produces the same result.
> 2. clone tool. Clone the objects to recreate the environment for another
> customer. These objects can be created in the same cluster or in another one.
> (Of course, global objects don't apply for the same cluster.)
> 3. dump tool. Dump the commands to recreate the existing objects.
> 4. diff tool. There are tools like pgquarrel [1] that queries the catalog and
> compare the results to create commands to turn the target database into the
> source database. The general purpose functions can be used if the object
> doesn't exist in the target database. (Of course, it doesn't apply for global
> objects but again it is a good UI to have all of these pg_get_OBJECT_ddl
> functions using the same approach.)
> 5. logical replication. These pg_get_OBJECT_ddl functions can be good
> candidates to be used in the initial schema replication and even in the DDL
> replication (if the object doesn't exist in the target database).
>
> The "options" parameter is to get the DDL command to serve any of these use
> cases. There are some properties in a certain object that you *don't* want for
> whatever reason. See some --no-OBJECT options in pg_dump. Let's say you don't
> want the TABLESPACE or the table access method while getting the CREATE TABLE
> DDL because it is different in the other database.

+1

>
>> I received a review comment suggesting the use of tabs. I also looked
>> up PostgreSQL best practices on google, which recommend using tabs for
>> indentation and spaces for alignment. I’m open to updating my code
>> accordingly.
>>
> I didn't check all of the possible output but the majority uses space instead
> of tabs. Check psql. If you check the git history (git log --grep=tabs), you
> will notice that tabs are removed from source code.

We should follow the pretty printing style in ruleutils.c, which uses 
spaces.

>
>>> * permission. I don't think you need to check for permissions inside the
>>>    function. I wouldn't want a different behavior than pg_dump(all). You can
>>>    always adjust it in system_functions.sql.
>>>
>> We’ve already had extensive discussions on this topic in the same
>> email thread, and ultimately we decided to add the permission check.
>>
> That's fair. Again, I expect that all of these pg_get_OBJECT_ddl functions use
> the same approach. We can always relax this restriction in the future.

+1

cheers

andrew

--
Andrew Dunstan
EDB:https://www.enterprisedb.com

---

From: Akshay Joshi <[email-masked]>
Date: Wed, 14 Jan 2026 21:18:12 +0530

I have incorporated Euler’s changes, which modify the declaration and
definition of the *pg_get_database_ddl* function. Please find the attached
v6 patch, which is now ready for review. The following updates have been
made:

   1.

   Function signature updated to: pg_get_database_ddl(database_id
   regdatabase, VARIADIC ddl_options text[])
   2.

   Added options *--no-owner* and *--no-tablespace* to omit the OWNER and
   TABLESPACE clauses from the reconstructed DDL.
   3.

   Moved the "*pretty*" parameter into ddl_options for formatted output.
   4.

   Properties such as ENCODING, LOCALE_PROVIDER, TABLESPACE, CONNECTION
   LIMIT, and ALLOW_CONNECTIONS are omitted if they are set to default values.
   5.

   Introduced the *--with-defaults* option to include clauses for
   parameters even when they are at their default values.
   6.

   Standardized formatting to use spaces instead of tabs.

*Usage examples:*

   1. SELECT pg_get_database_ddl('postgres'); // Non pretty-formatted DDL
   2. SELECT pg_get_database_ddl(16835); // Non pretty-formatted DDL
   3. SELECT pg_get_database_ddl('postgres', 'pretty'); // pretty-formatted
   DDL
   4. SELECT pg_get_database_ddl('postgres', '--no-owner',
   '--no-tablespace'); // Omits the Owner and Tablespace clause from the DDL.
   5. SELECT pg_get_database_ddl('postgres', 'pretty', '--with-defaults');
   // Includes clauses for parameters that are currently at their default
   values.

On Thu, Jan 8, 2026 at 4:57 AM Andrew Dunstan <[email-masked]> wrote:

> On Thu, Dec 11, 2025 at 7:29 PM Euler Taveira <[email-masked]> <[email-masked]> wrote:
>
> EDB: https://www.enterprisedb.com
>
>

---

From: Akshay Joshi <[email-masked]>
Date: Fri, 16 Jan 2026 12:53:48 +0530

-

1) Implement *uint64* for the flags to provide capacity for up to 64
distinct options.
2) Refactor parse_ddl_options to return the flag set directly rather than
using an *out parameter*.

Please find the attached *v7* patch, which is now ready for review.

On Wed, Jan 14, 2026 at 9:18 PM Akshay Joshi <[email-masked]>
wrote:

> definition of the *pg_get_database_ddl* function. Please find the
> attached v6 patch, which is now ready for review. The following updates
> have been made:
>
>    1.
>
>    3. SELECT pg_get_database_ddl('postgres', 'pretty'); //
>    pretty-formatted DDL
>    5. SELECT pg_get_database_ddl('postgres', 'pretty',
>    '--with-defaults'); // Includes clauses for parameters that are currently
>    at their default values.
>
>

---

From: Akshay Joshi <[email-masked]>
Date: Tue, 20 Jan 2026 17:06:53 +0530

All,

Following Alvaro's suggestion to use DefElem syntax, I have modified the
code to support key-value pairs.

I have attached two different patches :
v7-0001: Uses the double-dash syntax.
v7-0002: Uses the DefElem syntax.

*Usage Example for double-dash approach: *
SELECT pg_get_database_ddl('postgres', '--no-owner', '--no-tablespace'); --
Omit Owner and Tablespace clauses.
SELECT pg_get_database_ddl('postgres', 'pretty', '--with-defaults'); --
Includes clauses for parameters at their default values.

*Usage Example for DefElem approach*: The DefElem implementation supports
various boolean values (no, false, 0) and is case-insensitive.
SELECT pg_get_database_ddl('postgres', 'owner=no', 'tablespace=false'); --
Omits Owner and Tablespace clauses.
SELECT pg_get_database_ddl('postgres', 'pretty', 'defaults=yes'); --
Includes clauses for parameters at their default values.
SELECT pg_get_database_ddl('postgres', 'pretty', 'defaults'); -- Includes
clauses for parameters at their default values.

*Please suggest which approach is preferred. The patches are ready for
review.*

On Fri, Jan 16, 2026 at 12:53 PM Akshay Joshi <[email-masked]>
wrote:

>    2) Refactor parse_ddl_options to return the flag set directly rather
>    than using an *out parameter*.
>
> On Wed, Jan 14, 2026 at 9:18 PM Akshay Joshi <
> [email-masked]> wrote:
>
>>    Added options *--no-owner* and *--no-tablespace* to omit the OWNER
>>    and TABLESPACE clauses from the reconstructed DDL.
>>    3.
>>

---

From: Akshay Joshi <[email-masked]>
Date: Wed, 21 Jan 2026 15:01:44 +0530

In my previous email, I included two different patches (for two separate
approaches) from different branches. As a result, CommitFest is indicating
that a rebase is required.

Apologies for the inconvenience, I’m still getting familiar with the
process.

Attached are the patches, layered one on top of the other, representing two
approaches:

   - *Double Dash*:
   v8-0001-Add-pg_get_database_ddl-function-to-reconstruct-double-dash.patch
   - *DefElem (Key-Value)*:
   v8-0002-Add-pg_get_database_ddl-function-to-reconstruct-DefElem.patch

I am now submitting the *v8 patches*, which are ready for review. Please
let me know which approach you find more suitable and preferable.

On Tue, Jan 20, 2026 at 5:06 PM Akshay Joshi <[email-masked]>
wrote:

> SELECT pg_get_database_ddl('postgres', '--no-owner', '--no-tablespace');
> -- Omit Owner and Tablespace clauses.
> On Fri, Jan 16, 2026 at 12:53 PM Akshay Joshi <

---

From: Mark Wong <[email-masked]>
Date: Fri, 30 Jan 2026 16:27:53 -0800

> let me know which approach you find more suitable and preferable.

I have some documentation style suggestions that I've also attached as a patch:

* Update the pg_get_database_ddl() definitions in the func-info table to list
  the multiple database way to reference a database, similar to
  pg_database_size()
* Shorten the argument description for OID and name, again similar to
  pg_database_size()'s style
* Shorten ddl_options argument description to simply options, to match the
  style of how other functions in the docs describe optional parameters
* Remove the redundant explanation of the optional parameters in the individual
  function's description, and instead refer the reader to the explanation
  following the table
* Some word choice and phrasing changes in the pg_get_database_ddl function
  description to be grammatically closer to something like
  pg_restore_relation_stats()

I hope that is helpful...

Regards,
Mark
-- 
Mark Wong <[email-masked]>
EDB https://enterprisedb.com
diff --git a/doc/src/sgml/func/func-info.sgml b/doc/src/sgml/func/func-info.sgml
index 218578c318f..4fc74ca29a1 100644
--- a/doc/src/sgml/func/func-info.sgml
+++ b/doc/src/sgml/func/func-info.sgml
@@ -3874,20 +3874,21 @@ acl      | {postgres=arwdDxtm/postgres,foo=r/postgres}
         <indexterm>
          <primary>pg_get_database_ddl</primary>
         </indexterm>
-        <function>pg_get_database_ddl</function>
-        ( <parameter>database_id</parameter> <type>regdatabase</type>
-        <optional>, <literal>VARIADIC</literal> <parameter>ddl_options</parameter>
-        <type>text[]</type> </optional> )
+        <function>pg_get_database_ddl</function> ( <type>name</type> <optional>, <literal>VARIADIC</literal> <parameter>options</parameter> <type>text[]</type> </optional> )
+        <returnvalue>text</returnvalue>
+       </para>
+       <para role="func_signature">
+        <indexterm>
+         <primary>pg_get_database_ddl</primary>
+        </indexterm>
+        <function>pg_get_database_ddl</function> ( <type>oid</type> <optional>, <literal>VARIADIC</literal> <parameter>options</parameter> <type>text[]</type> </optional> )
         <returnvalue>text</returnvalue>
        </para>
        <para>
         Reconstructs the <command>CREATE DATABASE</command> statement from the
-        system catalogs for a specified database. The first argument is the OID or
-        name of the database. The optional variadic argument is an array of text
-        flags to control the output. Supported options include
-        <literal>pretty</literal>, <literal>owner=no/false/0</literal>,
-        <literal>tablespace=no/false/0</literal>, and <literal>defaults</literal>
-        Or <literal>defaults=yes</literal>.
+        system catalogs for a specified database by name or OID. The optional
+        variadic argument is an array of arguments to control the output
+        formatting and content. Supported arguments explained below.
         </para></entry>
       </row>
      </tbody>
@@ -3895,8 +3896,8 @@ acl      | {postgres=arwdDxtm/postgres,foo=r/postgres}
    </table>
 
   <para>
-    The <parameter>ddl_options</parameter> for <function>pg_get_database_ddl</function>
-    provide fine-grained control over the generated SQL:
+    The <parameter>options</parameter> for these object DDL functions provide
+    fine-grained control over the generated SQL:
     <itemizedlist>
     <listitem>
       <para>
diff --git a/src/backend/catalog/system_functions.sql b/src/backend/catalog/system_functions.sql
index 16c0d52479a..69a098561e7 100644
--- a/src/backend/catalog/system_functions.sql
+++ b/src/backend/catalog/system_functions.sql
@@ -658,7 +658,7 @@ STRICT VOLATILE PARALLEL UNSAFE
 AS 'pg_replication_origin_session_setup';
 
 CREATE OR REPLACE FUNCTION
-  pg_get_database_ddl(database_id regdatabase, VARIADIC ddl_options text[] DEFAULT '{}')
+  pg_get_database_ddl(database_id regdatabase, VARIADIC options text[] DEFAULT '{}')
 RETURNS text
 LANGUAGE internal
 AS 'pg_get_database_ddl';