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

From 25bd4ed143c5dae77c85ade087dfee10356bd562 Mon Sep 17 00:00:00 2001
From: Akshay Joshi <akshay.joshi@enterprisedb.com>
Date: Wed, 24 Sep 2025 17:47:59 +0530
Subject: [PATCH v2] Add pg_get_database_ddl() function to reconstruct CREATE
 DATABASE statements.
MIME-Version: 1.0
Content-Type: text/plain; charset=UTF-8
Content-Transfer-Encoding: 8bit

This adds a new system function, pg_get_database_ddl(database_name/database_oid, pretty),
which reconstructs the CREATE DATABASE statement for a given database name or database oid.

Usage:
  SELECT pg_get_database_ddl('postgres'); // Non pretty-formatted DDL
  SELECT pg_get_database_ddl(16835); // Non pretty-formatted DDL
  SELECT pg_get_database_ddl('postgres', true); // pretty-formatted DDL
  SELECT pg_get_database_ddl(16835, true); // pretty-formatted DDL

Reference: PG-150
Author: Akshay Joshi <akshay.joshi@enterprisedb.com>
Reviewed-by: Álvaro Herrera <alvherre@kurilemu.de>
---
 doc/src/sgml/func/func-info.sgml         |  70 ++++++++
 src/backend/catalog/system_functions.sql |  12 ++
 src/backend/utils/adt/ruleutils.c        | 206 +++++++++++++++++++++++
 src/include/catalog/pg_proc.dat          |   6 +
 src/test/regress/expected/database.out   |  76 +++++++++
 src/test/regress/sql/database.sql        |  62 +++++++
 6 files changed, 432 insertions(+)

diff --git a/doc/src/sgml/func/func-info.sgml b/doc/src/sgml/func/func-info.sgml
index d4508114a48..88fe04e649d 100644
--- a/doc/src/sgml/func/func-info.sgml
+++ b/doc/src/sgml/func/func-info.sgml
@@ -3797,4 +3797,74 @@ acl      | {postgres=arwdDxtm/postgres,foo=r/postgres}
 
   </sect2>
 
+  <sect2 id="functions-get-object-ddl">
+   <title>Get Object DDL Functions</title>
+
+   <para>
+    The functions described in <xref linkend="functions-get-object-ddl-table"/>
+    return the Data Definition Language (DDL) statement for any given database object.
+    This feature is implemented as a set of distinct functions for each object type.
+   </para>
+
+   <table id="functions-get-object-ddl-table">
+    <title>Get Object DDL Functions</title>
+    <tgroup cols="1">
+     <thead>
+      <row>
+       <entry role="func_table_entry"><para role="func_signature">
+        Function
+       </para>
+       <para>
+        Description
+       </para></entry>
+      </row>
+     </thead>
+
+     <tbody>
+      <row>
+       <entry role="func_table_entry"><para role="func_signature">
+        <indexterm>
+         <primary>pg_get_database_ddl</primary>
+        </indexterm>
+        <function>pg_get_database_ddl</function>
+        ( <parameter>database_name</parameter> <type>name</type>, <optional> <parameter>pretty</parameter> <type>boolean</type> </optional> )
+        <returnvalue>text</returnvalue>
+       </para>
+       <para>
+        Reconstructs the <command>CREATE DATABASE</command> statement from the
+        system catalogs for a specified database name. The result is a
+        comprehensive <command>CREATE DATABASE</command> statement.
+       </para></entry>
+      </row>
+      <row>
+       <entry role="func_table_entry"><para role="func_signature">
+        <indexterm>
+         <primary>pg_get_database_ddl</primary>
+        </indexterm>
+        <function>pg_get_database_ddl</function>
+        ( <parameter>database_oid</parameter> <type>oid</type>, <optional> <parameter>pretty</parameter> <type>boolean</type> </optional> )
+        <returnvalue>text</returnvalue>
+       </para>
+       <para>
+        Reconstructs the <command>CREATE DATABASE</command> statement from the
+        system catalogs for a specified database oid. The result is a
+        comprehensive <command>CREATE DATABASE</command> statement.
+       </para></entry>
+      </row>
+     </tbody>
+    </tgroup>
+   </table>
+
+  <para>
+   Most of the functions that reconstruct (decompile) database objects have an
+   optional <parameter>pretty</parameter> flag, which if
+   <literal>true</literal> causes the result to be
+   <quote>pretty-printed</quote>. Pretty-printing adds tab character and new
+   line character for legibility. Passing <literal>false</literal> for the
+   <parameter>pretty</parameter> parameter yields the same result as omitting
+   the parameter.
+  </para>
+
+  </sect2>
+
   </sect1>
diff --git a/src/backend/catalog/system_functions.sql b/src/backend/catalog/system_functions.sql
index 2d946d6d9e9..2db9d3bbcfc 100644
--- a/src/backend/catalog/system_functions.sql
+++ b/src/backend/catalog/system_functions.sql
@@ -657,6 +657,18 @@ LANGUAGE INTERNAL
 STRICT VOLATILE PARALLEL UNSAFE
 AS 'pg_replication_origin_session_setup';
 
+CREATE OR REPLACE FUNCTION
+  pg_get_database_ddl(database_name name, pretty bool DEFAULT false)
+RETURNS text
+LANGUAGE internal
+AS 'pg_get_database_ddl_name';
+
+CREATE OR REPLACE FUNCTION
+  pg_get_database_ddl(database_oid oid, pretty bool DEFAULT false)
+RETURNS text
+LANGUAGE internal
+AS 'pg_get_database_ddl_oid';
+
 --
 -- The default permissions for functions mean that anyone can execute them.
 -- A number of functions shouldn't be executable by just anyone, but rather
diff --git a/src/backend/utils/adt/ruleutils.c b/src/backend/utils/adt/ruleutils.c
index 556ab057e5a..2b622bcc66d 100644
--- a/src/backend/utils/adt/ruleutils.c
+++ b/src/backend/utils/adt/ruleutils.c
@@ -28,6 +28,7 @@
 #include "catalog/pg_authid.h"
 #include "catalog/pg_collation.h"
 #include "catalog/pg_constraint.h"
+#include "catalog/pg_database.h"
 #include "catalog/pg_depend.h"
 #include "catalog/pg_language.h"
 #include "catalog/pg_opclass.h"
@@ -94,6 +95,10 @@
 	((pretty) ? (PRETTYFLAG_PAREN | PRETTYFLAG_INDENT | PRETTYFLAG_SCHEMA) \
 	 : PRETTYFLAG_INDENT)
 
+#define GET_DDL_PRETTY_FLAGS(pretty) \
+	((pretty) ? (PRETTYFLAG_PAREN | PRETTYFLAG_INDENT | PRETTYFLAG_SCHEMA) \
+	 : 0)
+
 /* Default line length for pretty-print wrapping: 0 means wrap always */
 #define WRAP_COLUMN_DEFAULT		0
 
@@ -546,6 +551,11 @@ static void get_json_table_nested_columns(TableFunc *tf, JsonTablePlan *plan,
 										  deparse_context *context,
 										  bool showimplicit,
 										  bool needcomma);
+static void get_formatted_string(StringInfo buf,
+								 int prettyFlags,
+								 int noOfTabChars,
+								 const char *fmt,...) pg_attribute_printf(4, 5);
+static char *pg_get_database_ddl_worker(Oid dbOid, int prettyFlags);
 
 #define only_marker(rte)  ((rte)->inh ? "" : "ONLY ")
 
@@ -13743,3 +13753,199 @@ get_range_partbound_string(List *bound_datums)
 
 	return buf.data;
 }
+
+/*
+ * get_formatted_string
+ *
+ * Return a formatted version of the string.
+ *
+ * prettyFlags - Based on prettyFlags the output includes tabs (\t) and
+ *               newlines (\n).
+ * noOfTabChars - indent with specified no of tabs.
+ * fmt - printf-style format string used by appendStringInfoVA.
+ */
+static void
+get_formatted_string(StringInfo buf, int prettyFlags, int noOfTabChars, const char *fmt,...)
+{
+	va_list		args;
+
+	if (prettyFlags & PRETTYFLAG_INDENT)
+	{
+		appendStringInfoChar(buf, '\n');
+		/* Indent with tabs */
+		for (int i = 0; i < noOfTabChars; i++)
+		{
+			appendStringInfoChar(buf, '\t');
+		}
+	}
+	else
+		appendStringInfoChar(buf, ' ');
+
+	va_start(args, fmt);
+	appendStringInfoVA(buf, fmt, args);
+	va_end(args);
+}
+
+/*
+ * pg_get_database_ddl_name
+ *
+ * Generate a CREATE DATABASE statement for the specified database name.
+ *
+ * dbName - Name of the database for which to generate the DDL.
+ * pretty - If true, format the DDL with indentation and line breaks.
+ */
+Datum
+pg_get_database_ddl_name(PG_FUNCTION_ARGS)
+{
+	Name		dbName = PG_GETARG_NAME(0);
+	bool		pretty = PG_GETARG_BOOL(1);
+	int			prettyFlags;
+	char	   *res;
+
+	/* Get the database oid respective to the given database name */
+	Oid			dbOid = get_database_oid(NameStr(*dbName), false);
+
+	prettyFlags = GET_DDL_PRETTY_FLAGS(pretty);
+	res = pg_get_database_ddl_worker(dbOid, prettyFlags);
+
+	if (res == NULL)
+		PG_RETURN_NULL();
+
+	PG_RETURN_TEXT_P(string_to_text(res));
+}
+
+/*
+ * pg_get_database_ddl_oid
+ *
+ * Generate a CREATE DATABASE statement for the specified database oid.
+ *
+ * dbName - Name of the database for which to generate the DDL.
+ * pretty - If true, format the DDL with indentation and line breaks.
+ */
+Datum
+pg_get_database_ddl_oid(PG_FUNCTION_ARGS)
+{
+	Oid			dbOid = PG_GETARG_OID(0);
+	bool		pretty = PG_GETARG_BOOL(1);
+	int			prettyFlags;
+	char	   *res;
+
+	prettyFlags = GET_DDL_PRETTY_FLAGS(pretty);
+	res = pg_get_database_ddl_worker(dbOid, prettyFlags);
+
+	if (res == NULL)
+		PG_RETURN_NULL();
+
+	PG_RETURN_TEXT_P(string_to_text(res));
+}
+
+static char *
+pg_get_database_ddl_worker(Oid dbOid, int prettyFlags)
+{
+	char	   *dbOwner = NULL;
+	char	   *dbTablespace = NULL;
+	bool		attrIsNull;
+	Datum		dbValue;
+	HeapTuple	tupleDatabase;
+	Form_pg_database dbForm;
+	StringInfoData buf;
+
+	/* Look up the database in pg_database */
+	tupleDatabase = SearchSysCache1(DATABASEOID, ObjectIdGetDatum(dbOid));
+	if (!HeapTupleIsValid(tupleDatabase))
+		ereport(ERROR,
+				errcode(ERRCODE_UNDEFINED_OBJECT),
+				errmsg("database with oid %d does not exist", dbOid));
+
+	dbForm = (Form_pg_database) GETSTRUCT(tupleDatabase);
+
+	initStringInfo(&buf);
+
+	/* Look up the owner in the system catalog */
+	if (OidIsValid(dbForm->datdba))
+		dbOwner = GetUserNameFromId(dbForm->datdba, false);
+
+	/* Build the CREATE DATABASE statement */
+	appendStringInfo(&buf, "CREATE DATABASE %s",
+					 quote_identifier(dbForm->datname.data));
+	get_formatted_string(&buf, prettyFlags, 1, "WITH");
+	if (dbOwner)
+		get_formatted_string(&buf, prettyFlags, 1, "OWNER = %s",
+							 quote_identifier(dbOwner));
+
+	if (dbForm->encoding != 0)
+		get_formatted_string(&buf, prettyFlags, 1, "ENCODING = %s",
+							 quote_identifier(pg_encoding_to_char(dbForm->encoding)));
+
+	/* Fetch the value of LC_COLLATE */
+	dbValue = SysCacheGetAttr(DATABASEOID, tupleDatabase,
+							  Anum_pg_database_datcollate, &attrIsNull);
+	if (!attrIsNull)
+		get_formatted_string(&buf, prettyFlags, 1, "LC_COLLATE = %s",
+							 quote_identifier(TextDatumGetCString(dbValue)));
+
+	/* Fetch the value of LC_CTYPE */
+	dbValue = SysCacheGetAttr(DATABASEOID, tupleDatabase,
+							  Anum_pg_database_datctype, &attrIsNull);
+	if (!attrIsNull)
+		get_formatted_string(&buf, prettyFlags, 1, "LC_CTYPE = %s",
+							 quote_identifier(TextDatumGetCString(dbValue)));
+
+	/* Fetch the value of LOCALE */
+	dbValue = SysCacheGetAttr(DATABASEOID, tupleDatabase,
+							  Anum_pg_database_datlocale, &attrIsNull);
+	if (!attrIsNull && dbForm->datlocprovider == COLLPROVIDER_BUILTIN)
+		get_formatted_string(&buf, prettyFlags, 1, "BUILTIN_LOCALE = %s",
+							 quote_identifier(TextDatumGetCString(dbValue)));
+	else if (!attrIsNull && dbForm->datlocprovider == COLLPROVIDER_ICU)
+		get_formatted_string(&buf, prettyFlags, 1, "ICU_LOCALE = %s",
+							 quote_identifier(TextDatumGetCString(dbValue)));
+
+	/* Fetch the value of ICU_RULES */
+	dbValue = SysCacheGetAttr(DATABASEOID, tupleDatabase,
+							  Anum_pg_database_daticurules, &attrIsNull);
+	if (!attrIsNull)
+		get_formatted_string(&buf, prettyFlags, 1, "ICU_RULES = %s",
+							 quote_identifier(TextDatumGetCString(dbValue)));
+
+	/* Fetch the value of COLLATION_VERSION */
+	dbValue = SysCacheGetAttr(DATABASEOID, tupleDatabase,
+							  Anum_pg_database_datcollversion, &attrIsNull);
+	if (!attrIsNull)
+		get_formatted_string(&buf, prettyFlags, 1, "COLLATION_VERSION = %s",
+							 quote_identifier(TextDatumGetCString(dbValue)));
+
+	/* Set the appropriate LOCALE_PROVIDER */
+	if (dbForm->datlocprovider == COLLPROVIDER_BUILTIN)
+		get_formatted_string(&buf, prettyFlags, 1, "LOCALE_PROVIDER = 'builtin'");
+	else if (dbForm->datlocprovider == COLLPROVIDER_ICU)
+		get_formatted_string(&buf, prettyFlags, 1, "LOCALE_PROVIDER = 'icu'");
+	else
+		get_formatted_string(&buf, prettyFlags, 1, "LOCALE_PROVIDER = 'libc'");
+
+	/* Get the tablespace name respective to the given tablespace oid */
+	if (OidIsValid(dbForm->dattablespace))
+	{
+		dbTablespace = get_tablespace_name(dbForm->dattablespace);
+		if (dbTablespace)
+			get_formatted_string(&buf, prettyFlags, 1, "TABLESPACE = %s",
+								 quote_identifier(dbTablespace));
+	}
+
+	get_formatted_string(&buf, prettyFlags, 1, "ALLOW_CONNECTIONS = %s",
+						 dbForm->datallowconn ? "true" : "false");
+
+	if (dbForm->datconnlimit != 0)
+		get_formatted_string(&buf, prettyFlags, 1, "CONNECTION LIMIT = %d",
+							 dbForm->datconnlimit);
+
+	if (dbForm->datistemplate)
+		get_formatted_string(&buf, prettyFlags, 1, "IS_TEMPLATE = %s",
+							 dbForm->datistemplate ? "true" : "false");
+
+	appendStringInfoChar(&buf, ';');
+
+	ReleaseSysCache(tupleDatabase);
+
+	return buf.data;
+}
diff --git a/src/include/catalog/pg_proc.dat b/src/include/catalog/pg_proc.dat
index 5cf9e12fcb9..27fbb71297f 100644
--- a/src/include/catalog/pg_proc.dat
+++ b/src/include/catalog/pg_proc.dat
@@ -4021,6 +4021,12 @@
   proname => 'pg_get_function_sqlbody', provolatile => 's',
   prorettype => 'text', proargtypes => 'oid',
   prosrc => 'pg_get_function_sqlbody' },
+{ oid => '9492', descr => 'get CREATE statement for database name',
+  proname => 'pg_get_database_ddl', prorettype => 'text',
+  proargtypes => 'name bool', prosrc => 'pg_get_database_ddl_name' },
+{ oid => '9493', descr => 'get CREATE statement for database oid',
+  proname => 'pg_get_database_ddl', prorettype => 'text',
+  proargtypes => 'oid bool', prosrc => 'pg_get_database_ddl_oid' },
 
 { oid => '1686', descr => 'list of SQL keywords',
   proname => 'pg_get_keywords', procost => '10', prorows => '500',
diff --git a/src/test/regress/expected/database.out b/src/test/regress/expected/database.out
index 6b879b0f62a..df90fded42c 100644
--- a/src/test/regress/expected/database.out
+++ b/src/test/regress/expected/database.out
@@ -1,3 +1,49 @@
+--
+-- Reconsturct DDL
+--
+-- To produce stable regression test output, it's usually necessary to
+-- ignore collation and locale related details. This filter
+-- functions removes collation and locale related details.
+CREATE OR REPLACE FUNCTION ddl_filter(ddl_input TEXT)
+RETURNS TEXT AS $$
+DECLARE
+    cleaned_ddl TEXT;
+BEGIN
+	-- Remove LC_COLLATE assignments 
+    cleaned_ddl := regexp_replace(
+        ddl_input,
+        '\s*LC_COLLATE\s*=\s*([''"])[^''"]*\1',
+        '',
+        'gi'
+    );
+
+    -- Remove LC_CTYPE assignments
+    cleaned_ddl := regexp_replace(
+        cleaned_ddl,
+        '\s*LC_CTYPE\s*=\s*([''"])[^''"]*\1',
+        '',
+        'gi'
+    );
+
+    -- Remove %LOCALE% placeholders
+    cleaned_ddl := regexp_replace(
+        cleaned_ddl,
+        '\s*\S*LOCALE\S*\s*=?\s*([''"])[^''"]*\1',
+		'',
+		'gi'
+	);
+
+    -- Remove %COLLATION% placeholders
+    cleaned_ddl := regexp_replace(
+        cleaned_ddl,
+        '\s*\S*COLLATION\S*\s*=?\s*([''"])[^''"]*\1',
+        '',
+        'gi'
+    );
+
+    RETURN cleaned_ddl;
+END;
+$$ LANGUAGE plpgsql;
 CREATE DATABASE regression_tbd
 	ENCODING utf8 LC_COLLATE "C" LC_CTYPE "C" TEMPLATE template0;
 ALTER DATABASE regression_tbd RENAME TO regression_utf8;
@@ -16,6 +62,36 @@ CREATE ROLE regress_datdba_before;
 CREATE ROLE regress_datdba_after;
 ALTER DATABASE regression_utf8 OWNER TO regress_datdba_before;
 REASSIGN OWNED BY regress_datdba_before TO regress_datdba_after;
+-- Test pg_get_database_ddl
+-- Database doesn't exists
+SELECT pg_get_database_ddl('regression_database', false);
+ERROR:  database "regression_database" does not exist
+-- Test NULL value
+SELECT pg_get_database_ddl(NULL);
+ pg_get_database_ddl 
+---------------------
+ 
+(1 row)
+
+-- Without pretty
+SELECT ddl_filter(pg_get_database_ddl('regression_utf8'));
+                                                                          ddl_filter                                                                          
+--------------------------------------------------------------------------------------------------------------------------------------------------------------
+ CREATE DATABASE regression_utf8 WITH OWNER = regress_datdba_after ENCODING = "UTF8" TABLESPACE = pg_default ALLOW_CONNECTIONS = true CONNECTION LIMIT = 123;
+(1 row)
+
+-- With Pretty formatted
+\pset format unaligned
+SELECT ddl_filter(pg_get_database_ddl('regression_utf8', true));
+ddl_filter
+CREATE DATABASE regression_utf8
+	WITH
+	OWNER = regress_datdba_after
+	ENCODING = "UTF8"
+	TABLESPACE = pg_default
+	ALLOW_CONNECTIONS = true
+	CONNECTION LIMIT = 123;
+(1 row)
 DROP DATABASE regression_utf8;
 DROP ROLE regress_datdba_before;
 DROP ROLE regress_datdba_after;
diff --git a/src/test/regress/sql/database.sql b/src/test/regress/sql/database.sql
index 4ef36127291..392a4d96bb5 100644
--- a/src/test/regress/sql/database.sql
+++ b/src/test/regress/sql/database.sql
@@ -1,3 +1,51 @@
+--
+-- Reconsturct DDL
+--
+-- To produce stable regression test output, it's usually necessary to
+-- ignore collation and locale related details. This filter
+-- functions removes collation and locale related details.
+
+CREATE OR REPLACE FUNCTION ddl_filter(ddl_input TEXT)
+RETURNS TEXT AS $$
+DECLARE
+    cleaned_ddl TEXT;
+BEGIN
+	-- Remove LC_COLLATE assignments 
+    cleaned_ddl := regexp_replace(
+        ddl_input,
+        '\s*LC_COLLATE\s*=\s*([''"])[^''"]*\1',
+        '',
+        'gi'
+    );
+
+    -- Remove LC_CTYPE assignments
+    cleaned_ddl := regexp_replace(
+        cleaned_ddl,
+        '\s*LC_CTYPE\s*=\s*([''"])[^''"]*\1',
+        '',
+        'gi'
+    );
+
+    -- Remove %LOCALE% placeholders
+    cleaned_ddl := regexp_replace(
+        cleaned_ddl,
+        '\s*\S*LOCALE\S*\s*=?\s*([''"])[^''"]*\1',
+		'',
+		'gi'
+	);
+
+    -- Remove %COLLATION% placeholders
+    cleaned_ddl := regexp_replace(
+        cleaned_ddl,
+        '\s*\S*COLLATION\S*\s*=?\s*([''"])[^''"]*\1',
+        '',
+        'gi'
+    );
+
+    RETURN cleaned_ddl;
+END;
+$$ LANGUAGE plpgsql;
+
 CREATE DATABASE regression_tbd
 	ENCODING utf8 LC_COLLATE "C" LC_CTYPE "C" TEMPLATE template0;
 ALTER DATABASE regression_tbd RENAME TO regression_utf8;
@@ -19,6 +67,20 @@ CREATE ROLE regress_datdba_after;
 ALTER DATABASE regression_utf8 OWNER TO regress_datdba_before;
 REASSIGN OWNED BY regress_datdba_before TO regress_datdba_after;
 
+-- Test pg_get_database_ddl
+-- Database doesn't exists
+SELECT pg_get_database_ddl('regression_database', false);
+
+-- Test NULL value
+SELECT pg_get_database_ddl(NULL);
+
+-- Without pretty
+SELECT ddl_filter(pg_get_database_ddl('regression_utf8'));
+
+-- With Pretty formatted
+\pset format unaligned
+SELECT ddl_filter(pg_get_database_ddl('regression_utf8', true));
+
 DROP DATABASE regression_utf8;
 DROP ROLE regress_datdba_before;
 DROP ROLE regress_datdba_after;
-- 
2.51.0

From 65dd35ef5f3a91443d493f58dbc4cface31c8350 Mon Sep 17 00:00:00 2001
From: Akshay Joshi <akshay.joshi@enterprisedb.com>
Date: Wed, 24 Sep 2025 17:47:59 +0530
Subject: [PATCH v3] Add pg_get_database_ddl() function to reconstruct CREATE
 DATABASE statements.
MIME-Version: 1.0
Content-Type: text/plain; charset=UTF-8
Content-Transfer-Encoding: 8bit

This adds a new system function, pg_get_database_ddl(database_name/database_oid, pretty),
which reconstructs the CREATE DATABASE statement for a given database name or database oid.

Usage:
  SELECT pg_get_database_ddl('postgres'); // Non pretty-formatted DDL
  SELECT pg_get_database_ddl(16835); // Non pretty-formatted DDL
  SELECT pg_get_database_ddl('postgres', true); // pretty-formatted DDL
  SELECT pg_get_database_ddl(16835, true); // pretty-formatted DDL

Reference: PG-150
Author: Akshay Joshi <akshay.joshi@enterprisedb.com>
Reviewed-by: Álvaro Herrera <alvherre@kurilemu.de>
---
 doc/src/sgml/func/func-info.sgml         |  63 +++++++
 src/backend/catalog/system_functions.sql |  12 ++
 src/backend/utils/adt/ruleutils.c        | 219 +++++++++++++++++++++++
 src/include/catalog/pg_proc.dat          |   6 +
 src/test/regress/expected/database.out   |  76 ++++++++
 src/test/regress/sql/database.sql        |  62 +++++++
 6 files changed, 438 insertions(+)

diff --git a/doc/src/sgml/func/func-info.sgml b/doc/src/sgml/func/func-info.sgml
index d4508114a48..a918a1694c4 100644
--- a/doc/src/sgml/func/func-info.sgml
+++ b/doc/src/sgml/func/func-info.sgml
@@ -3797,4 +3797,67 @@ acl      | {postgres=arwdDxtm/postgres,foo=r/postgres}
 
   </sect2>
 
+  <sect2 id="functions-get-object-ddl">
+   <title>Get Object DDL Functions</title>
+
+   <para>
+    The functions described in <xref linkend="functions-get-object-ddl-table"/>
+    return the Data Definition Language (DDL) statement for any given database object.
+    This feature is implemented as a set of distinct functions for each object type.
+   </para>
+
+   <table id="functions-get-object-ddl-table">
+    <title>Get Object DDL Functions</title>
+    <tgroup cols="1">
+     <thead>
+      <row>
+       <entry role="func_table_entry"><para role="func_signature">
+        Function
+       </para>
+       <para>
+        Description
+       </para></entry>
+      </row>
+     </thead>
+
+     <tbody>
+      <row>
+       <entry role="func_table_entry"><para role="func_signature">
+        <indexterm>
+         <primary>pg_get_database_ddl</primary>
+        </indexterm>
+        <function>pg_get_database_ddl</function>
+        ( <parameter>database_name</parameter> <type>name</type>, <optional> <parameter>pretty</parameter> <type>boolean</type> </optional> )
+        <returnvalue>text</returnvalue>
+       </para>
+       <para role="func_signature">
+        <indexterm>
+         <primary>pg_get_database_ddl</primary>
+        </indexterm>
+        <function>pg_get_database_ddl</function>
+        ( <parameter>database_oid</parameter> <type>oid</type>, <optional> <parameter>pretty</parameter> <type>boolean</type> </optional> )
+        <returnvalue>text</returnvalue>
+       </para>
+       <para>
+        Reconstructs the <command>CREATE DATABASE</command> statement from the
+        system catalogs for a specified database name or database oid. The
+        result is a comprehensive <command>CREATE DATABASE</command> statement.
+       </para></entry>
+      </row>
+     </tbody>
+    </tgroup>
+   </table>
+
+  <para>
+   Most of the functions that reconstruct (decompile) database objects have an
+   optional <parameter>pretty</parameter> flag, which if
+   <literal>true</literal> causes the result to be
+   <quote>pretty-printed</quote>. Pretty-printing adds tab character and new
+   line character for legibility. Passing <literal>false</literal> for the
+   <parameter>pretty</parameter> parameter yields the same result as omitting
+   the parameter.
+  </para>
+
+  </sect2>
+
   </sect1>
diff --git a/src/backend/catalog/system_functions.sql b/src/backend/catalog/system_functions.sql
index 2d946d6d9e9..2db9d3bbcfc 100644
--- a/src/backend/catalog/system_functions.sql
+++ b/src/backend/catalog/system_functions.sql
@@ -657,6 +657,18 @@ LANGUAGE INTERNAL
 STRICT VOLATILE PARALLEL UNSAFE
 AS 'pg_replication_origin_session_setup';
 
+CREATE OR REPLACE FUNCTION
+  pg_get_database_ddl(database_name name, pretty bool DEFAULT false)
+RETURNS text
+LANGUAGE internal
+AS 'pg_get_database_ddl_name';
+
+CREATE OR REPLACE FUNCTION
+  pg_get_database_ddl(database_oid oid, pretty bool DEFAULT false)
+RETURNS text
+LANGUAGE internal
+AS 'pg_get_database_ddl_oid';
+
 --
 -- The default permissions for functions mean that anyone can execute them.
 -- A number of functions shouldn't be executable by just anyone, but rather
diff --git a/src/backend/utils/adt/ruleutils.c b/src/backend/utils/adt/ruleutils.c
index 556ab057e5a..714bc037067 100644
--- a/src/backend/utils/adt/ruleutils.c
+++ b/src/backend/utils/adt/ruleutils.c
@@ -28,6 +28,7 @@
 #include "catalog/pg_authid.h"
 #include "catalog/pg_collation.h"
 #include "catalog/pg_constraint.h"
+#include "catalog/pg_database.h"
 #include "catalog/pg_depend.h"
 #include "catalog/pg_language.h"
 #include "catalog/pg_opclass.h"
@@ -57,6 +58,7 @@
 #include "rewrite/rewriteHandler.h"
 #include "rewrite/rewriteManip.h"
 #include "rewrite/rewriteSupport.h"
+#include "utils/acl.h"
 #include "utils/array.h"
 #include "utils/builtins.h"
 #include "utils/fmgroids.h"
@@ -94,6 +96,10 @@
 	((pretty) ? (PRETTYFLAG_PAREN | PRETTYFLAG_INDENT | PRETTYFLAG_SCHEMA) \
 	 : PRETTYFLAG_INDENT)
 
+#define GET_DDL_PRETTY_FLAGS(pretty) \
+	((pretty) ? (PRETTYFLAG_PAREN | PRETTYFLAG_INDENT | PRETTYFLAG_SCHEMA) \
+	 : 0)
+
 /* Default line length for pretty-print wrapping: 0 means wrap always */
 #define WRAP_COLUMN_DEFAULT		0
 
@@ -546,6 +552,11 @@ static void get_json_table_nested_columns(TableFunc *tf, JsonTablePlan *plan,
 										  deparse_context *context,
 										  bool showimplicit,
 										  bool needcomma);
+static void get_formatted_string(StringInfo buf,
+								 int prettyFlags,
+								 int nTabChars,
+								 const char *fmt,...) pg_attribute_printf(4, 5);
+static char *pg_get_database_ddl_worker(Oid dbOid, int prettyFlags);
 
 #define only_marker(rte)  ((rte)->inh ? "" : "ONLY ")
 
@@ -13743,3 +13754,211 @@ get_range_partbound_string(List *bound_datums)
 
 	return buf.data;
 }
+
+/*
+ * get_formatted_string
+ *
+ * Return a formatted version of the string.
+ *
+ * prettyFlags - Based on prettyFlags the output includes tabs (\t) and
+ *               newlines (\n).
+ * nTabChars - indent with specified number of tab characters.
+ * fmt - printf-style format string used by appendStringInfoVA.
+ */
+static void
+get_formatted_string(StringInfo buf, int prettyFlags, int nTabChars, const char *fmt,...)
+{
+	va_list		args;
+
+	if (prettyFlags & PRETTYFLAG_INDENT)
+	{
+		appendStringInfoChar(buf, '\n');
+		/* Indent with tabs */
+		for (int i = 0; i < nTabChars; i++)
+		{
+			appendStringInfoChar(buf, '\t');
+		}
+	}
+	else
+		appendStringInfoChar(buf, ' ');
+
+	va_start(args, fmt);
+	appendStringInfoVA(buf, fmt, args);
+	va_end(args);
+}
+
+/*
+ * pg_get_database_ddl_name
+ *
+ * Generate a CREATE DATABASE statement for the specified database name.
+ *
+ * dbName - Name of the database for which to generate the DDL.
+ * pretty - If true, format the DDL with indentation and line breaks.
+ */
+Datum
+pg_get_database_ddl_name(PG_FUNCTION_ARGS)
+{
+	Name		dbName = PG_GETARG_NAME(0);
+	bool		pretty = PG_GETARG_BOOL(1);
+	int			prettyFlags;
+	char	   *res;
+
+	/* Get the database oid respective to the given database name */
+	Oid			dbOid = get_database_oid(NameStr(*dbName), false);
+
+	prettyFlags = GET_DDL_PRETTY_FLAGS(pretty);
+	res = pg_get_database_ddl_worker(dbOid, prettyFlags);
+
+	if (res == NULL)
+		PG_RETURN_NULL();
+
+	PG_RETURN_TEXT_P(string_to_text(res));
+}
+
+/*
+ * pg_get_database_ddl_oid
+ *
+ * Generate a CREATE DATABASE statement for the specified database oid.
+ *
+ * dbOid - OID of the database for which to generate the DDL.
+ * pretty - If true, format the DDL with indentation and line breaks.
+ */
+Datum
+pg_get_database_ddl_oid(PG_FUNCTION_ARGS)
+{
+	Oid			dbOid = PG_GETARG_OID(0);
+	bool		pretty = PG_GETARG_BOOL(1);
+	int			prettyFlags;
+	char	   *res;
+
+	prettyFlags = GET_DDL_PRETTY_FLAGS(pretty);
+	res = pg_get_database_ddl_worker(dbOid, prettyFlags);
+
+	if (res == NULL)
+		PG_RETURN_NULL();
+
+	PG_RETURN_TEXT_P(string_to_text(res));
+}
+
+static char *
+pg_get_database_ddl_worker(Oid dbOid, int prettyFlags)
+{
+	char	   *dbOwner = NULL;
+	char	   *dbTablespace = NULL;
+	bool		attrIsNull;
+	Datum		dbValue;
+	HeapTuple	tupleDatabase;
+	Form_pg_database dbForm;
+	StringInfoData buf;
+	AclResult	aclresult;
+
+	/*
+	 * User must have connect privilege for target database.
+	 */
+	aclresult = object_aclcheck(DatabaseRelationId, dbOid, GetUserId(),
+								ACL_CONNECT);
+	if (aclresult != ACLCHECK_OK)
+	{
+		aclcheck_error(aclresult, OBJECT_DATABASE,
+					   get_database_name(dbOid));
+	}
+
+	/* Look up the database in pg_database */
+	tupleDatabase = SearchSysCache1(DATABASEOID, ObjectIdGetDatum(dbOid));
+	if (!HeapTupleIsValid(tupleDatabase))
+		ereport(ERROR,
+				errcode(ERRCODE_UNDEFINED_OBJECT),
+				errmsg("database with oid %d does not exist", dbOid));
+
+	dbForm = (Form_pg_database) GETSTRUCT(tupleDatabase);
+
+	initStringInfo(&buf);
+
+	/* Look up the owner in the system catalog */
+	if (OidIsValid(dbForm->datdba))
+		dbOwner = GetUserNameFromId(dbForm->datdba, false);
+
+	/* Build the CREATE DATABASE statement */
+	appendStringInfo(&buf, "CREATE DATABASE %s",
+					 quote_identifier(dbForm->datname.data));
+	get_formatted_string(&buf, prettyFlags, 1, "WITH");
+	if (dbOwner)
+		get_formatted_string(&buf, prettyFlags, 1, "OWNER = %s",
+							 quote_identifier(dbOwner));
+
+	if (dbForm->encoding != 0)
+		get_formatted_string(&buf, prettyFlags, 1, "ENCODING = %s",
+							 quote_identifier(pg_encoding_to_char(dbForm->encoding)));
+
+	/* Fetch the value of LC_COLLATE */
+	dbValue = SysCacheGetAttr(DATABASEOID, tupleDatabase,
+							  Anum_pg_database_datcollate, &attrIsNull);
+	if (!attrIsNull)
+		get_formatted_string(&buf, prettyFlags, 1, "LC_COLLATE = %s",
+							 quote_identifier(TextDatumGetCString(dbValue)));
+
+	/* Fetch the value of LC_CTYPE */
+	dbValue = SysCacheGetAttr(DATABASEOID, tupleDatabase,
+							  Anum_pg_database_datctype, &attrIsNull);
+	if (!attrIsNull)
+		get_formatted_string(&buf, prettyFlags, 1, "LC_CTYPE = %s",
+							 quote_identifier(TextDatumGetCString(dbValue)));
+
+	/* Fetch the value of LOCALE */
+	dbValue = SysCacheGetAttr(DATABASEOID, tupleDatabase,
+							  Anum_pg_database_datlocale, &attrIsNull);
+	if (!attrIsNull && dbForm->datlocprovider == COLLPROVIDER_BUILTIN)
+		get_formatted_string(&buf, prettyFlags, 1, "BUILTIN_LOCALE = %s",
+							 quote_identifier(TextDatumGetCString(dbValue)));
+	else if (!attrIsNull && dbForm->datlocprovider == COLLPROVIDER_ICU)
+		get_formatted_string(&buf, prettyFlags, 1, "ICU_LOCALE = %s",
+							 quote_identifier(TextDatumGetCString(dbValue)));
+
+	/* Fetch the value of ICU_RULES */
+	dbValue = SysCacheGetAttr(DATABASEOID, tupleDatabase,
+							  Anum_pg_database_daticurules, &attrIsNull);
+	if (!attrIsNull)
+		get_formatted_string(&buf, prettyFlags, 1, "ICU_RULES = %s",
+							 quote_identifier(TextDatumGetCString(dbValue)));
+
+	/* Fetch the value of COLLATION_VERSION */
+	dbValue = SysCacheGetAttr(DATABASEOID, tupleDatabase,
+							  Anum_pg_database_datcollversion, &attrIsNull);
+	if (!attrIsNull)
+		get_formatted_string(&buf, prettyFlags, 1, "COLLATION_VERSION = %s",
+							 quote_identifier(TextDatumGetCString(dbValue)));
+
+	/* Set the appropriate LOCALE_PROVIDER */
+	if (dbForm->datlocprovider == COLLPROVIDER_BUILTIN)
+		get_formatted_string(&buf, prettyFlags, 1, "LOCALE_PROVIDER = 'builtin'");
+	else if (dbForm->datlocprovider == COLLPROVIDER_ICU)
+		get_formatted_string(&buf, prettyFlags, 1, "LOCALE_PROVIDER = 'icu'");
+	else
+		get_formatted_string(&buf, prettyFlags, 1, "LOCALE_PROVIDER = 'libc'");
+
+	/* Get the tablespace name respective to the given tablespace oid */
+	if (OidIsValid(dbForm->dattablespace))
+	{
+		dbTablespace = get_tablespace_name(dbForm->dattablespace);
+		if (dbTablespace)
+			get_formatted_string(&buf, prettyFlags, 1, "TABLESPACE = %s",
+								 quote_identifier(dbTablespace));
+	}
+
+	get_formatted_string(&buf, prettyFlags, 1, "ALLOW_CONNECTIONS = %s",
+						 dbForm->datallowconn ? "true" : "false");
+
+	if (dbForm->datconnlimit != 0)
+		get_formatted_string(&buf, prettyFlags, 1, "CONNECTION LIMIT = %d",
+							 dbForm->datconnlimit);
+
+	if (dbForm->datistemplate)
+		get_formatted_string(&buf, prettyFlags, 1, "IS_TEMPLATE = %s",
+							 dbForm->datistemplate ? "true" : "false");
+
+	appendStringInfoChar(&buf, ';');
+
+	ReleaseSysCache(tupleDatabase);
+
+	return buf.data;
+}
diff --git a/src/include/catalog/pg_proc.dat b/src/include/catalog/pg_proc.dat
index 5cf9e12fcb9..27fbb71297f 100644
--- a/src/include/catalog/pg_proc.dat
+++ b/src/include/catalog/pg_proc.dat
@@ -4021,6 +4021,12 @@
   proname => 'pg_get_function_sqlbody', provolatile => 's',
   prorettype => 'text', proargtypes => 'oid',
   prosrc => 'pg_get_function_sqlbody' },
+{ oid => '9492', descr => 'get CREATE statement for database name',
+  proname => 'pg_get_database_ddl', prorettype => 'text',
+  proargtypes => 'name bool', prosrc => 'pg_get_database_ddl_name' },
+{ oid => '9493', descr => 'get CREATE statement for database oid',
+  proname => 'pg_get_database_ddl', prorettype => 'text',
+  proargtypes => 'oid bool', prosrc => 'pg_get_database_ddl_oid' },
 
 { oid => '1686', descr => 'list of SQL keywords',
   proname => 'pg_get_keywords', procost => '10', prorows => '500',
diff --git a/src/test/regress/expected/database.out b/src/test/regress/expected/database.out
index 6b879b0f62a..df90fded42c 100644
--- a/src/test/regress/expected/database.out
+++ b/src/test/regress/expected/database.out
@@ -1,3 +1,49 @@
+--
+-- Reconsturct DDL
+--
+-- To produce stable regression test output, it's usually necessary to
+-- ignore collation and locale related details. This filter
+-- functions removes collation and locale related details.
+CREATE OR REPLACE FUNCTION ddl_filter(ddl_input TEXT)
+RETURNS TEXT AS $$
+DECLARE
+    cleaned_ddl TEXT;
+BEGIN
+	-- Remove LC_COLLATE assignments 
+    cleaned_ddl := regexp_replace(
+        ddl_input,
+        '\s*LC_COLLATE\s*=\s*([''"])[^''"]*\1',
+        '',
+        'gi'
+    );
+
+    -- Remove LC_CTYPE assignments
+    cleaned_ddl := regexp_replace(
+        cleaned_ddl,
+        '\s*LC_CTYPE\s*=\s*([''"])[^''"]*\1',
+        '',
+        'gi'
+    );
+
+    -- Remove %LOCALE% placeholders
+    cleaned_ddl := regexp_replace(
+        cleaned_ddl,
+        '\s*\S*LOCALE\S*\s*=?\s*([''"])[^''"]*\1',
+		'',
+		'gi'
+	);
+
+    -- Remove %COLLATION% placeholders
+    cleaned_ddl := regexp_replace(
+        cleaned_ddl,
+        '\s*\S*COLLATION\S*\s*=?\s*([''"])[^''"]*\1',
+        '',
+        'gi'
+    );
+
+    RETURN cleaned_ddl;
+END;
+$$ LANGUAGE plpgsql;
 CREATE DATABASE regression_tbd
 	ENCODING utf8 LC_COLLATE "C" LC_CTYPE "C" TEMPLATE template0;
 ALTER DATABASE regression_tbd RENAME TO regression_utf8;
@@ -16,6 +62,36 @@ CREATE ROLE regress_datdba_before;
 CREATE ROLE regress_datdba_after;
 ALTER DATABASE regression_utf8 OWNER TO regress_datdba_before;
 REASSIGN OWNED BY regress_datdba_before TO regress_datdba_after;
+-- Test pg_get_database_ddl
+-- Database doesn't exists
+SELECT pg_get_database_ddl('regression_database', false);
+ERROR:  database "regression_database" does not exist
+-- Test NULL value
+SELECT pg_get_database_ddl(NULL);
+ pg_get_database_ddl 
+---------------------
+ 
+(1 row)
+
+-- Without pretty
+SELECT ddl_filter(pg_get_database_ddl('regression_utf8'));
+                                                                          ddl_filter                                                                          
+--------------------------------------------------------------------------------------------------------------------------------------------------------------
+ CREATE DATABASE regression_utf8 WITH OWNER = regress_datdba_after ENCODING = "UTF8" TABLESPACE = pg_default ALLOW_CONNECTIONS = true CONNECTION LIMIT = 123;
+(1 row)
+
+-- With Pretty formatted
+\pset format unaligned
+SELECT ddl_filter(pg_get_database_ddl('regression_utf8', true));
+ddl_filter
+CREATE DATABASE regression_utf8
+	WITH
+	OWNER = regress_datdba_after
+	ENCODING = "UTF8"
+	TABLESPACE = pg_default
+	ALLOW_CONNECTIONS = true
+	CONNECTION LIMIT = 123;
+(1 row)
 DROP DATABASE regression_utf8;
 DROP ROLE regress_datdba_before;
 DROP ROLE regress_datdba_after;
diff --git a/src/test/regress/sql/database.sql b/src/test/regress/sql/database.sql
index 4ef36127291..392a4d96bb5 100644
--- a/src/test/regress/sql/database.sql
+++ b/src/test/regress/sql/database.sql
@@ -1,3 +1,51 @@
+--
+-- Reconsturct DDL
+--
+-- To produce stable regression test output, it's usually necessary to
+-- ignore collation and locale related details. This filter
+-- functions removes collation and locale related details.
+
+CREATE OR REPLACE FUNCTION ddl_filter(ddl_input TEXT)
+RETURNS TEXT AS $$
+DECLARE
+    cleaned_ddl TEXT;
+BEGIN
+	-- Remove LC_COLLATE assignments 
+    cleaned_ddl := regexp_replace(
+        ddl_input,
+        '\s*LC_COLLATE\s*=\s*([''"])[^''"]*\1',
+        '',
+        'gi'
+    );
+
+    -- Remove LC_CTYPE assignments
+    cleaned_ddl := regexp_replace(
+        cleaned_ddl,
+        '\s*LC_CTYPE\s*=\s*([''"])[^''"]*\1',
+        '',
+        'gi'
+    );
+
+    -- Remove %LOCALE% placeholders
+    cleaned_ddl := regexp_replace(
+        cleaned_ddl,
+        '\s*\S*LOCALE\S*\s*=?\s*([''"])[^''"]*\1',
+		'',
+		'gi'
+	);
+
+    -- Remove %COLLATION% placeholders
+    cleaned_ddl := regexp_replace(
+        cleaned_ddl,
+        '\s*\S*COLLATION\S*\s*=?\s*([''"])[^''"]*\1',
+        '',
+        'gi'
+    );
+
+    RETURN cleaned_ddl;
+END;
+$$ LANGUAGE plpgsql;
+
 CREATE DATABASE regression_tbd
 	ENCODING utf8 LC_COLLATE "C" LC_CTYPE "C" TEMPLATE template0;
 ALTER DATABASE regression_tbd RENAME TO regression_utf8;
@@ -19,6 +67,20 @@ CREATE ROLE regress_datdba_after;
 ALTER DATABASE regression_utf8 OWNER TO regress_datdba_before;
 REASSIGN OWNED BY regress_datdba_before TO regress_datdba_after;
 
+-- Test pg_get_database_ddl
+-- Database doesn't exists
+SELECT pg_get_database_ddl('regression_database', false);
+
+-- Test NULL value
+SELECT pg_get_database_ddl(NULL);
+
+-- Without pretty
+SELECT ddl_filter(pg_get_database_ddl('regression_utf8'));
+
+-- With Pretty formatted
+\pset format unaligned
+SELECT ddl_filter(pg_get_database_ddl('regression_utf8', true));
+
 DROP DATABASE regression_utf8;
 DROP ROLE regress_datdba_before;
 DROP ROLE regress_datdba_after;
-- 
2.51.0

From 664f14d030232607da1c644fbb808731f8648a2d Mon Sep 17 00:00:00 2001
From: Akshay Joshi <akshay.joshi@enterprisedb.com>
Date: Wed, 24 Sep 2025 17:47:59 +0530
Subject: [PATCH v4] Add pg_get_database_ddl() function to reconstruct CREATE
 DATABASE statements.
MIME-Version: 1.0
Content-Type: text/plain; charset=UTF-8
Content-Transfer-Encoding: 8bit

This adds a new system function, pg_get_database_ddl(database_name/database_oid, pretty),
which reconstructs the CREATE DATABASE statement for a given database name or database oid.

Usage:
  SELECT pg_get_database_ddl('postgres'); // Non pretty-formatted DDL
  SELECT pg_get_database_ddl(16835); // Non pretty-formatted DDL
  SELECT pg_get_database_ddl('postgres', true); // pretty-formatted DDL
  SELECT pg_get_database_ddl(16835, true); // pretty-formatted DDL

Reference: PG-150
Author: Akshay Joshi <akshay.joshi@enterprisedb.com>
Reviewed-by: Álvaro Herrera <alvherre@kurilemu.de>
---
 doc/src/sgml/func/func-info.sgml         |  63 +++++++
 src/backend/catalog/system_functions.sql |  12 ++
 src/backend/utils/adt/ruleutils.c        | 217 +++++++++++++++++++++++
 src/include/catalog/pg_proc.dat          |   6 +
 src/test/regress/expected/database.out   |  75 ++++++++
 src/test/regress/sql/database.sql        |  62 +++++++
 6 files changed, 435 insertions(+)

diff --git a/doc/src/sgml/func/func-info.sgml b/doc/src/sgml/func/func-info.sgml
index d4508114a48..a918a1694c4 100644
--- a/doc/src/sgml/func/func-info.sgml
+++ b/doc/src/sgml/func/func-info.sgml
@@ -3797,4 +3797,67 @@ acl      | {postgres=arwdDxtm/postgres,foo=r/postgres}
 
   </sect2>
 
+  <sect2 id="functions-get-object-ddl">
+   <title>Get Object DDL Functions</title>
+
+   <para>
+    The functions described in <xref linkend="functions-get-object-ddl-table"/>
+    return the Data Definition Language (DDL) statement for any given database object.
+    This feature is implemented as a set of distinct functions for each object type.
+   </para>
+
+   <table id="functions-get-object-ddl-table">
+    <title>Get Object DDL Functions</title>
+    <tgroup cols="1">
+     <thead>
+      <row>
+       <entry role="func_table_entry"><para role="func_signature">
+        Function
+       </para>
+       <para>
+        Description
+       </para></entry>
+      </row>
+     </thead>
+
+     <tbody>
+      <row>
+       <entry role="func_table_entry"><para role="func_signature">
+        <indexterm>
+         <primary>pg_get_database_ddl</primary>
+        </indexterm>
+        <function>pg_get_database_ddl</function>
+        ( <parameter>database_name</parameter> <type>name</type>, <optional> <parameter>pretty</parameter> <type>boolean</type> </optional> )
+        <returnvalue>text</returnvalue>
+       </para>
+       <para role="func_signature">
+        <indexterm>
+         <primary>pg_get_database_ddl</primary>
+        </indexterm>
+        <function>pg_get_database_ddl</function>
+        ( <parameter>database_oid</parameter> <type>oid</type>, <optional> <parameter>pretty</parameter> <type>boolean</type> </optional> )
+        <returnvalue>text</returnvalue>
+       </para>
+       <para>
+        Reconstructs the <command>CREATE DATABASE</command> statement from the
+        system catalogs for a specified database name or database oid. The
+        result is a comprehensive <command>CREATE DATABASE</command> statement.
+       </para></entry>
+      </row>
+     </tbody>
+    </tgroup>
+   </table>
+
+  <para>
+   Most of the functions that reconstruct (decompile) database objects have an
+   optional <parameter>pretty</parameter> flag, which if
+   <literal>true</literal> causes the result to be
+   <quote>pretty-printed</quote>. Pretty-printing adds tab character and new
+   line character for legibility. Passing <literal>false</literal> for the
+   <parameter>pretty</parameter> parameter yields the same result as omitting
+   the parameter.
+  </para>
+
+  </sect2>
+
   </sect1>
diff --git a/src/backend/catalog/system_functions.sql b/src/backend/catalog/system_functions.sql
index 2d946d6d9e9..2db9d3bbcfc 100644
--- a/src/backend/catalog/system_functions.sql
+++ b/src/backend/catalog/system_functions.sql
@@ -657,6 +657,18 @@ LANGUAGE INTERNAL
 STRICT VOLATILE PARALLEL UNSAFE
 AS 'pg_replication_origin_session_setup';
 
+CREATE OR REPLACE FUNCTION
+  pg_get_database_ddl(database_name name, pretty bool DEFAULT false)
+RETURNS text
+LANGUAGE internal
+AS 'pg_get_database_ddl_name';
+
+CREATE OR REPLACE FUNCTION
+  pg_get_database_ddl(database_oid oid, pretty bool DEFAULT false)
+RETURNS text
+LANGUAGE internal
+AS 'pg_get_database_ddl_oid';
+
 --
 -- The default permissions for functions mean that anyone can execute them.
 -- A number of functions shouldn't be executable by just anyone, but rather
diff --git a/src/backend/utils/adt/ruleutils.c b/src/backend/utils/adt/ruleutils.c
index 556ab057e5a..b39723b0855 100644
--- a/src/backend/utils/adt/ruleutils.c
+++ b/src/backend/utils/adt/ruleutils.c
@@ -28,6 +28,7 @@
 #include "catalog/pg_authid.h"
 #include "catalog/pg_collation.h"
 #include "catalog/pg_constraint.h"
+#include "catalog/pg_database.h"
 #include "catalog/pg_depend.h"
 #include "catalog/pg_language.h"
 #include "catalog/pg_opclass.h"
@@ -57,6 +58,7 @@
 #include "rewrite/rewriteHandler.h"
 #include "rewrite/rewriteManip.h"
 #include "rewrite/rewriteSupport.h"
+#include "utils/acl.h"
 #include "utils/array.h"
 #include "utils/builtins.h"
 #include "utils/fmgroids.h"
@@ -94,6 +96,10 @@
 	((pretty) ? (PRETTYFLAG_PAREN | PRETTYFLAG_INDENT | PRETTYFLAG_SCHEMA) \
 	 : PRETTYFLAG_INDENT)
 
+#define GET_DDL_PRETTY_FLAGS(pretty) \
+	((pretty) ? (PRETTYFLAG_PAREN | PRETTYFLAG_INDENT | PRETTYFLAG_SCHEMA) \
+	 : 0)
+
 /* Default line length for pretty-print wrapping: 0 means wrap always */
 #define WRAP_COLUMN_DEFAULT		0
 
@@ -546,6 +552,11 @@ static void get_json_table_nested_columns(TableFunc *tf, JsonTablePlan *plan,
 										  deparse_context *context,
 										  bool showimplicit,
 										  bool needcomma);
+static void get_formatted_string(StringInfo buf,
+								 int prettyFlags,
+								 int nTabChars,
+								 const char *fmt,...) pg_attribute_printf(4, 5);
+static char *pg_get_database_ddl_worker(Oid dbOid, int prettyFlags);
 
 #define only_marker(rte)  ((rte)->inh ? "" : "ONLY ")
 
@@ -13743,3 +13754,209 @@ get_range_partbound_string(List *bound_datums)
 
 	return buf.data;
 }
+
+/*
+ * get_formatted_string
+ *
+ * Return a formatted version of the string.
+ *
+ * prettyFlags - Based on prettyFlags the output includes tabs (\t) and
+ *               newlines (\n).
+ * nTabChars - indent with specified number of tab characters.
+ * fmt - printf-style format string used by appendStringInfoVA.
+ */
+static void
+get_formatted_string(StringInfo buf, int prettyFlags, int nTabChars, const char *fmt,...)
+{
+	va_list		args;
+
+	if (prettyFlags & PRETTYFLAG_INDENT)
+	{
+		appendStringInfoChar(buf, '\n');
+		/* Indent with tabs */
+		for (int i = 0; i < nTabChars; i++)
+		{
+			appendStringInfoChar(buf, '\t');
+		}
+	}
+	else
+		appendStringInfoChar(buf, ' ');
+
+	va_start(args, fmt);
+	appendStringInfoVA(buf, fmt, args);
+	va_end(args);
+}
+
+/*
+ * pg_get_database_ddl_name
+ *
+ * Generate a CREATE DATABASE statement for the specified database name.
+ *
+ * dbName - Name of the database for which to generate the DDL.
+ * pretty - If true, format the DDL with indentation and line breaks.
+ */
+Datum
+pg_get_database_ddl_name(PG_FUNCTION_ARGS)
+{
+	Name		dbName = PG_GETARG_NAME(0);
+	bool		pretty = PG_GETARG_BOOL(1);
+	int			prettyFlags;
+	char	   *res;
+
+	/* Get the database oid respective to the given database name */
+	Oid			dbOid = get_database_oid(NameStr(*dbName), false);
+
+	prettyFlags = GET_DDL_PRETTY_FLAGS(pretty);
+	res = pg_get_database_ddl_worker(dbOid, prettyFlags);
+
+	if (res == NULL)
+		PG_RETURN_NULL();
+
+	PG_RETURN_TEXT_P(string_to_text(res));
+}
+
+/*
+ * pg_get_database_ddl_oid
+ *
+ * Generate a CREATE DATABASE statement for the specified database oid.
+ *
+ * dbOid - OID of the database for which to generate the DDL.
+ * pretty - If true, format the DDL with indentation and line breaks.
+ */
+Datum
+pg_get_database_ddl_oid(PG_FUNCTION_ARGS)
+{
+	Oid			dbOid = PG_GETARG_OID(0);
+	bool		pretty = PG_GETARG_BOOL(1);
+	int			prettyFlags;
+	char	   *res;
+
+	prettyFlags = GET_DDL_PRETTY_FLAGS(pretty);
+	res = pg_get_database_ddl_worker(dbOid, prettyFlags);
+
+	if (res == NULL)
+		PG_RETURN_NULL();
+
+	PG_RETURN_TEXT_P(string_to_text(res));
+}
+
+static char *
+pg_get_database_ddl_worker(Oid dbOid, int prettyFlags)
+{
+	char	   *dbOwner = NULL;
+	char	   *dbTablespace = NULL;
+	bool		attrIsNull;
+	Datum		dbValue;
+	HeapTuple	tupleDatabase;
+	Form_pg_database dbForm;
+	StringInfoData buf;
+	AclResult	aclresult;
+
+	/*
+	 * User must have connect privilege for target database.
+	 */
+	aclresult = object_aclcheck(DatabaseRelationId, dbOid, GetUserId(),
+								ACL_CONNECT);
+	if (aclresult != ACLCHECK_OK &&
+		!has_privs_of_role(GetUserId(), ROLE_PG_READ_ALL_STATS))
+	{
+		aclcheck_error(aclresult, OBJECT_DATABASE,
+					   get_database_name(dbOid));
+	}
+
+	/* Look up the database in pg_database */
+	tupleDatabase = SearchSysCache1(DATABASEOID, ObjectIdGetDatum(dbOid));
+	if (!HeapTupleIsValid(tupleDatabase))
+		ereport(ERROR,
+				errcode(ERRCODE_UNDEFINED_OBJECT),
+				errmsg("database with oid %u does not exist", dbOid));
+
+	dbForm = (Form_pg_database) GETSTRUCT(tupleDatabase);
+
+	initStringInfo(&buf);
+
+	/* Look up the owner in the system catalog */
+	if (OidIsValid(dbForm->datdba))
+		dbOwner = GetUserNameFromId(dbForm->datdba, false);
+
+	/* Build the CREATE DATABASE statement */
+	appendStringInfo(&buf, "CREATE DATABASE %s",
+					 quote_identifier(dbForm->datname.data));
+	get_formatted_string(&buf, prettyFlags, 1, "WITH OWNER = %s",
+						 quote_identifier(dbOwner));
+
+	if (dbForm->encoding != 0)
+		get_formatted_string(&buf, prettyFlags, 2, "ENCODING = %s",
+							 quote_identifier(pg_encoding_to_char(dbForm->encoding)));
+
+	/* Fetch the value of LC_COLLATE */
+	dbValue = SysCacheGetAttr(DATABASEOID, tupleDatabase,
+							  Anum_pg_database_datcollate, &attrIsNull);
+	if (!attrIsNull)
+		get_formatted_string(&buf, prettyFlags, 2, "LC_COLLATE = %s",
+							 quote_identifier(TextDatumGetCString(dbValue)));
+
+	/* Fetch the value of LC_CTYPE */
+	dbValue = SysCacheGetAttr(DATABASEOID, tupleDatabase,
+							  Anum_pg_database_datctype, &attrIsNull);
+	if (!attrIsNull)
+		get_formatted_string(&buf, prettyFlags, 2, "LC_CTYPE = %s",
+							 quote_identifier(TextDatumGetCString(dbValue)));
+
+	/* Fetch the value of LOCALE */
+	dbValue = SysCacheGetAttr(DATABASEOID, tupleDatabase,
+							  Anum_pg_database_datlocale, &attrIsNull);
+	if (!attrIsNull && dbForm->datlocprovider == COLLPROVIDER_BUILTIN)
+		get_formatted_string(&buf, prettyFlags, 2, "BUILTIN_LOCALE = %s",
+							 quote_identifier(TextDatumGetCString(dbValue)));
+	else if (!attrIsNull && dbForm->datlocprovider == COLLPROVIDER_ICU)
+		get_formatted_string(&buf, prettyFlags, 2, "ICU_LOCALE = %s",
+							 quote_identifier(TextDatumGetCString(dbValue)));
+
+	/* Fetch the value of ICU_RULES */
+	dbValue = SysCacheGetAttr(DATABASEOID, tupleDatabase,
+							  Anum_pg_database_daticurules, &attrIsNull);
+	if (!attrIsNull && dbForm->datlocprovider == COLLPROVIDER_ICU)
+		get_formatted_string(&buf, prettyFlags, 2, "ICU_RULES = %s",
+							 quote_identifier(TextDatumGetCString(dbValue)));
+
+	/* Fetch the value of COLLATION_VERSION */
+	dbValue = SysCacheGetAttr(DATABASEOID, tupleDatabase,
+							  Anum_pg_database_datcollversion, &attrIsNull);
+	if (!attrIsNull)
+		get_formatted_string(&buf, prettyFlags, 2, "COLLATION_VERSION = %s",
+							 quote_identifier(TextDatumGetCString(dbValue)));
+
+	/* Set the appropriate LOCALE_PROVIDER */
+	if (dbForm->datlocprovider == COLLPROVIDER_BUILTIN)
+		get_formatted_string(&buf, prettyFlags, 2, "LOCALE_PROVIDER = 'builtin'");
+	else if (dbForm->datlocprovider == COLLPROVIDER_ICU)
+		get_formatted_string(&buf, prettyFlags, 2, "LOCALE_PROVIDER = 'icu'");
+	else
+		get_formatted_string(&buf, prettyFlags, 2, "LOCALE_PROVIDER = 'libc'");
+
+	/* Get the tablespace name respective to the given tablespace oid */
+	if (OidIsValid(dbForm->dattablespace))
+	{
+		dbTablespace = get_tablespace_name(dbForm->dattablespace);
+		if (dbTablespace)
+			get_formatted_string(&buf, prettyFlags, 2, "TABLESPACE = %s",
+								 quote_identifier(dbTablespace));
+	}
+
+	get_formatted_string(&buf, prettyFlags, 2, "ALLOW_CONNECTIONS = %s",
+						 dbForm->datallowconn ? "true" : "false");
+
+	get_formatted_string(&buf, prettyFlags, 2, "CONNECTION LIMIT = %d",
+						 dbForm->datconnlimit);
+
+	if (dbForm->datistemplate)
+		get_formatted_string(&buf, prettyFlags, 2, "IS_TEMPLATE = %s",
+							 dbForm->datistemplate ? "true" : "false");
+
+	appendStringInfoChar(&buf, ';');
+
+	ReleaseSysCache(tupleDatabase);
+
+	return buf.data;
+}
diff --git a/src/include/catalog/pg_proc.dat b/src/include/catalog/pg_proc.dat
index 5cf9e12fcb9..27fbb71297f 100644
--- a/src/include/catalog/pg_proc.dat
+++ b/src/include/catalog/pg_proc.dat
@@ -4021,6 +4021,12 @@
   proname => 'pg_get_function_sqlbody', provolatile => 's',
   prorettype => 'text', proargtypes => 'oid',
   prosrc => 'pg_get_function_sqlbody' },
+{ oid => '9492', descr => 'get CREATE statement for database name',
+  proname => 'pg_get_database_ddl', prorettype => 'text',
+  proargtypes => 'name bool', prosrc => 'pg_get_database_ddl_name' },
+{ oid => '9493', descr => 'get CREATE statement for database oid',
+  proname => 'pg_get_database_ddl', prorettype => 'text',
+  proargtypes => 'oid bool', prosrc => 'pg_get_database_ddl_oid' },
 
 { oid => '1686', descr => 'list of SQL keywords',
   proname => 'pg_get_keywords', procost => '10', prorows => '500',
diff --git a/src/test/regress/expected/database.out b/src/test/regress/expected/database.out
index 6b879b0f62a..fa0d1b01e67 100644
--- a/src/test/regress/expected/database.out
+++ b/src/test/regress/expected/database.out
@@ -1,3 +1,49 @@
+--
+-- Reconsturct DDL
+--
+-- To produce stable regression test output, it's usually necessary to
+-- ignore collation and locale related details. This filter
+-- functions removes collation and locale related details.
+CREATE OR REPLACE FUNCTION ddl_filter(ddl_input TEXT)
+RETURNS TEXT AS $$
+DECLARE
+    cleaned_ddl TEXT;
+BEGIN
+	-- Remove LC_COLLATE assignments 
+    cleaned_ddl := regexp_replace(
+        ddl_input,
+        '\s*LC_COLLATE\s*=\s*([''"])[^''"]*\1',
+        '',
+        'gi'
+    );
+
+    -- Remove LC_CTYPE assignments
+    cleaned_ddl := regexp_replace(
+        cleaned_ddl,
+        '\s*LC_CTYPE\s*=\s*([''"])[^''"]*\1',
+        '',
+        'gi'
+    );
+
+    -- Remove %LOCALE% placeholders
+    cleaned_ddl := regexp_replace(
+        cleaned_ddl,
+        '\s*\S*LOCALE\S*\s*=?\s*([''"])[^''"]*\1',
+		'',
+		'gi'
+	);
+
+    -- Remove %COLLATION% placeholders
+    cleaned_ddl := regexp_replace(
+        cleaned_ddl,
+        '\s*\S*COLLATION\S*\s*=?\s*([''"])[^''"]*\1',
+        '',
+        'gi'
+    );
+
+    RETURN cleaned_ddl;
+END;
+$$ LANGUAGE plpgsql;
 CREATE DATABASE regression_tbd
 	ENCODING utf8 LC_COLLATE "C" LC_CTYPE "C" TEMPLATE template0;
 ALTER DATABASE regression_tbd RENAME TO regression_utf8;
@@ -16,6 +62,35 @@ CREATE ROLE regress_datdba_before;
 CREATE ROLE regress_datdba_after;
 ALTER DATABASE regression_utf8 OWNER TO regress_datdba_before;
 REASSIGN OWNED BY regress_datdba_before TO regress_datdba_after;
+-- Test pg_get_database_ddl
+-- Database doesn't exists
+SELECT pg_get_database_ddl('regression_database', false);
+ERROR:  database "regression_database" does not exist
+-- Test NULL value
+SELECT pg_get_database_ddl(NULL);
+ pg_get_database_ddl 
+---------------------
+ 
+(1 row)
+
+-- Without pretty
+SELECT ddl_filter(pg_get_database_ddl('regression_utf8'));
+                                                                          ddl_filter                                                                          
+--------------------------------------------------------------------------------------------------------------------------------------------------------------
+ CREATE DATABASE regression_utf8 WITH OWNER = regress_datdba_after ENCODING = "UTF8" TABLESPACE = pg_default ALLOW_CONNECTIONS = true CONNECTION LIMIT = 123;
+(1 row)
+
+-- With Pretty formatted
+\pset format unaligned
+SELECT ddl_filter(pg_get_database_ddl('regression_utf8', true));
+ddl_filter
+CREATE DATABASE regression_utf8
+	WITH OWNER = regress_datdba_after
+		ENCODING = "UTF8"
+		TABLESPACE = pg_default
+		ALLOW_CONNECTIONS = true
+		CONNECTION LIMIT = 123;
+(1 row)
 DROP DATABASE regression_utf8;
 DROP ROLE regress_datdba_before;
 DROP ROLE regress_datdba_after;
diff --git a/src/test/regress/sql/database.sql b/src/test/regress/sql/database.sql
index 4ef36127291..392a4d96bb5 100644
--- a/src/test/regress/sql/database.sql
+++ b/src/test/regress/sql/database.sql
@@ -1,3 +1,51 @@
+--
+-- Reconsturct DDL
+--
+-- To produce stable regression test output, it's usually necessary to
+-- ignore collation and locale related details. This filter
+-- functions removes collation and locale related details.
+
+CREATE OR REPLACE FUNCTION ddl_filter(ddl_input TEXT)
+RETURNS TEXT AS $$
+DECLARE
+    cleaned_ddl TEXT;
+BEGIN
+	-- Remove LC_COLLATE assignments 
+    cleaned_ddl := regexp_replace(
+        ddl_input,
+        '\s*LC_COLLATE\s*=\s*([''"])[^''"]*\1',
+        '',
+        'gi'
+    );
+
+    -- Remove LC_CTYPE assignments
+    cleaned_ddl := regexp_replace(
+        cleaned_ddl,
+        '\s*LC_CTYPE\s*=\s*([''"])[^''"]*\1',
+        '',
+        'gi'
+    );
+
+    -- Remove %LOCALE% placeholders
+    cleaned_ddl := regexp_replace(
+        cleaned_ddl,
+        '\s*\S*LOCALE\S*\s*=?\s*([''"])[^''"]*\1',
+		'',
+		'gi'
+	);
+
+    -- Remove %COLLATION% placeholders
+    cleaned_ddl := regexp_replace(
+        cleaned_ddl,
+        '\s*\S*COLLATION\S*\s*=?\s*([''"])[^''"]*\1',
+        '',
+        'gi'
+    );
+
+    RETURN cleaned_ddl;
+END;
+$$ LANGUAGE plpgsql;
+
 CREATE DATABASE regression_tbd
 	ENCODING utf8 LC_COLLATE "C" LC_CTYPE "C" TEMPLATE template0;
 ALTER DATABASE regression_tbd RENAME TO regression_utf8;
@@ -19,6 +67,20 @@ CREATE ROLE regress_datdba_after;
 ALTER DATABASE regression_utf8 OWNER TO regress_datdba_before;
 REASSIGN OWNED BY regress_datdba_before TO regress_datdba_after;
 
+-- Test pg_get_database_ddl
+-- Database doesn't exists
+SELECT pg_get_database_ddl('regression_database', false);
+
+-- Test NULL value
+SELECT pg_get_database_ddl(NULL);
+
+-- Without pretty
+SELECT ddl_filter(pg_get_database_ddl('regression_utf8'));
+
+-- With Pretty formatted
+\pset format unaligned
+SELECT ddl_filter(pg_get_database_ddl('regression_utf8', true));
+
 DROP DATABASE regression_utf8;
 DROP ROLE regress_datdba_before;
 DROP ROLE regress_datdba_after;
-- 
2.51.0

From 8ea45bf50d6dd521bedaa40eaad88bcb35103aaa Mon Sep 17 00:00:00 2001
From: Akshay Joshi <akshay.joshi@enterprisedb.com>
Date: Wed, 24 Sep 2025 17:47:59 +0530
Subject: [PATCH v5] Add pg_get_database_ddl() function to reconstruct CREATE
 DATABASE statements.
MIME-Version: 1.0
Content-Type: text/plain; charset=UTF-8
Content-Transfer-Encoding: 8bit

This adds a new system function, pg_get_database_ddl(database_name/database_oid, pretty),
which reconstructs the CREATE DATABASE statement for a given database name or database oid.

Usage:
  SELECT pg_get_database_ddl('postgres'); // Non pretty-formatted DDL
  SELECT pg_get_database_ddl(16835); // Non pretty-formatted DDL
  SELECT pg_get_database_ddl('postgres', true); // pretty-formatted DDL
  SELECT pg_get_database_ddl(16835, true); // pretty-formatted DDL

Reference: PG-150
Author: Akshay Joshi <akshay.joshi@enterprisedb.com>
Reviewed-by: Álvaro Herrera <alvherre@kurilemu.de>
---
 doc/src/sgml/func/func-info.sgml         |  55 +++++++
 src/backend/catalog/system_functions.sql |   6 +
 src/backend/utils/adt/ruleutils.c        | 189 +++++++++++++++++++++++
 src/include/catalog/pg_proc.dat          |   3 +
 src/test/regress/expected/database.out   |  77 +++++++++
 src/test/regress/sql/database.sql        |  62 ++++++++
 6 files changed, 392 insertions(+)

diff --git a/doc/src/sgml/func/func-info.sgml b/doc/src/sgml/func/func-info.sgml
index d4508114a48..115724616cd 100644
--- a/doc/src/sgml/func/func-info.sgml
+++ b/doc/src/sgml/func/func-info.sgml
@@ -3797,4 +3797,59 @@ acl      | {postgres=arwdDxtm/postgres,foo=r/postgres}
 
   </sect2>
 
+  <sect2 id="functions-get-object-ddl">
+   <title>Get Object DDL Functions</title>
+
+   <para>
+    The functions described in <xref linkend="functions-get-object-ddl-table"/>
+    return the Data Definition Language (DDL) statement for any given database object.
+    This feature is implemented as a set of distinct functions for each object type.
+   </para>
+
+   <table id="functions-get-object-ddl-table">
+    <title>Get Object DDL Functions</title>
+    <tgroup cols="1">
+     <thead>
+      <row>
+       <entry role="func_table_entry"><para role="func_signature">
+        Function
+       </para>
+       <para>
+        Description
+       </para></entry>
+      </row>
+     </thead>
+
+     <tbody>
+      <row>
+       <entry role="func_table_entry"><para role="func_signature">
+        <indexterm>
+         <primary>pg_get_database_ddl</primary>
+        </indexterm>
+        <function>pg_get_database_ddl</function>
+        ( <parameter>databaseID</parameter> <type>regdatabase</type>, <optional> <parameter>pretty</parameter> <type>boolean</type> </optional> )
+        <returnvalue>text</returnvalue>
+       </para>
+       <para>
+        Reconstructs the <command>CREATE DATABASE</command> statement from the
+        system catalogs for a specified database name or database oid. The
+        result is a comprehensive <command>CREATE DATABASE</command> statement.
+       </para></entry>
+      </row>
+     </tbody>
+    </tgroup>
+   </table>
+
+  <para>
+   Most of the functions that reconstruct (decompile) database objects have an
+   optional <parameter>pretty</parameter> flag, which if
+   <literal>true</literal> causes the result to be
+   <quote>pretty-printed</quote>. Pretty-printing adds tab character and new
+   line character for legibility. Passing <literal>false</literal> for the
+   <parameter>pretty</parameter> parameter yields the same result as omitting
+   the parameter.
+  </para>
+
+  </sect2>
+
   </sect1>
diff --git a/src/backend/catalog/system_functions.sql b/src/backend/catalog/system_functions.sql
index 2d946d6d9e9..9fb02a2017d 100644
--- a/src/backend/catalog/system_functions.sql
+++ b/src/backend/catalog/system_functions.sql
@@ -657,6 +657,12 @@ LANGUAGE INTERNAL
 STRICT VOLATILE PARALLEL UNSAFE
 AS 'pg_replication_origin_session_setup';
 
+CREATE OR REPLACE FUNCTION
+  pg_get_database_ddl(databaseID regdatabase, pretty bool DEFAULT false)
+RETURNS text
+LANGUAGE internal
+AS 'pg_get_database_ddl';
+
 --
 -- The default permissions for functions mean that anyone can execute them.
 -- A number of functions shouldn't be executable by just anyone, but rather
diff --git a/src/backend/utils/adt/ruleutils.c b/src/backend/utils/adt/ruleutils.c
index 556ab057e5a..ded8247212a 100644
--- a/src/backend/utils/adt/ruleutils.c
+++ b/src/backend/utils/adt/ruleutils.c
@@ -28,6 +28,7 @@
 #include "catalog/pg_authid.h"
 #include "catalog/pg_collation.h"
 #include "catalog/pg_constraint.h"
+#include "catalog/pg_database.h"
 #include "catalog/pg_depend.h"
 #include "catalog/pg_language.h"
 #include "catalog/pg_opclass.h"
@@ -57,6 +58,7 @@
 #include "rewrite/rewriteHandler.h"
 #include "rewrite/rewriteManip.h"
 #include "rewrite/rewriteSupport.h"
+#include "utils/acl.h"
 #include "utils/array.h"
 #include "utils/builtins.h"
 #include "utils/fmgroids.h"
@@ -94,6 +96,10 @@
 	((pretty) ? (PRETTYFLAG_PAREN | PRETTYFLAG_INDENT | PRETTYFLAG_SCHEMA) \
 	 : PRETTYFLAG_INDENT)
 
+#define GET_DDL_PRETTY_FLAGS(pretty) \
+	((pretty) ? (PRETTYFLAG_PAREN | PRETTYFLAG_INDENT | PRETTYFLAG_SCHEMA) \
+	 : 0)
+
 /* Default line length for pretty-print wrapping: 0 means wrap always */
 #define WRAP_COLUMN_DEFAULT		0
 
@@ -546,6 +552,11 @@ static void get_json_table_nested_columns(TableFunc *tf, JsonTablePlan *plan,
 										  deparse_context *context,
 										  bool showimplicit,
 										  bool needcomma);
+static void get_formatted_string(StringInfo buf,
+								 int prettyFlags,
+								 int nTabChars,
+								 const char *fmt,...) pg_attribute_printf(4, 5);
+static char *pg_get_database_ddl_worker(Oid dbOid, int prettyFlags);
 
 #define only_marker(rte)  ((rte)->inh ? "" : "ONLY ")
 
@@ -13743,3 +13754,181 @@ get_range_partbound_string(List *bound_datums)
 
 	return buf.data;
 }
+
+/*
+ * get_formatted_string
+ *
+ * Return a formatted version of the string.
+ *
+ * prettyFlags - Based on prettyFlags the output includes tabs (\t) and
+ *               newlines (\n).
+ * nTabChars - indent with specified number of tab characters.
+ * fmt - printf-style format string used by appendStringInfoVA.
+ */
+static void
+get_formatted_string(StringInfo buf, int prettyFlags, int nTabChars, const char *fmt,...)
+{
+	va_list		args;
+
+	if (prettyFlags & PRETTYFLAG_INDENT)
+	{
+		appendStringInfoChar(buf, '\n');
+		/* Indent with tabs */
+		for (int i = 0; i < nTabChars; i++)
+		{
+			appendStringInfoChar(buf, '\t');
+		}
+	}
+	else
+		appendStringInfoChar(buf, ' ');
+
+	va_start(args, fmt);
+	appendStringInfoVA(buf, fmt, args);
+	va_end(args);
+}
+
+/*
+ * pg_get_database_ddl
+ *
+ * Generate a CREATE DATABASE statement for the specified database name or oid.
+ *
+ * databaseID - OID/Name of the database for which to generate the DDL.
+ * pretty - If true, format the DDL with indentation and line breaks.
+ */
+Datum
+pg_get_database_ddl(PG_FUNCTION_ARGS)
+{
+	Oid			dbOid = PG_GETARG_OID(0);
+	bool		pretty = PG_GETARG_BOOL(1);
+	int			prettyFlags;
+	char	   *res;
+
+	prettyFlags = GET_DDL_PRETTY_FLAGS(pretty);
+	res = pg_get_database_ddl_worker(dbOid, prettyFlags);
+
+	if (res == NULL)
+		PG_RETURN_NULL();
+
+	PG_RETURN_TEXT_P(string_to_text(res));
+}
+
+static char *
+pg_get_database_ddl_worker(Oid dbOid, int prettyFlags)
+{
+	char	   *dbOwner = NULL;
+	bool		attrIsNull;
+	Datum		dbValue;
+	HeapTuple	tupleDatabase;
+	Form_pg_database dbForm;
+	StringInfoData buf;
+	AclResult	aclresult;
+
+	/*
+	 * User must have connect privilege for target database.
+	 */
+	aclresult = object_aclcheck(DatabaseRelationId, dbOid, GetUserId(),
+								ACL_CONNECT);
+	if (aclresult != ACLCHECK_OK &&
+		!has_privs_of_role(GetUserId(), ROLE_PG_READ_ALL_STATS))
+	{
+		aclcheck_error(aclresult, OBJECT_DATABASE,
+					   get_database_name(dbOid));
+	}
+
+	/* Look up the database in pg_database */
+	tupleDatabase = SearchSysCache1(DATABASEOID, ObjectIdGetDatum(dbOid));
+	if (!HeapTupleIsValid(tupleDatabase))
+		ereport(ERROR,
+				errcode(ERRCODE_UNDEFINED_OBJECT),
+				errmsg("database with oid %u does not exist", dbOid));
+
+	dbForm = (Form_pg_database) GETSTRUCT(tupleDatabase);
+
+	initStringInfo(&buf);
+
+	/* Look up the owner in the system catalog */
+	if (OidIsValid(dbForm->datdba))
+		dbOwner = GetUserNameFromId(dbForm->datdba, false);
+
+	/* Build the CREATE DATABASE statement */
+	appendStringInfo(&buf, "CREATE DATABASE %s",
+					 quote_identifier(dbForm->datname.data));
+	get_formatted_string(&buf, prettyFlags, 1, "WITH OWNER = %s",
+						 quote_identifier(dbOwner));
+
+	if (dbForm->encoding != 0)
+		get_formatted_string(&buf, prettyFlags, 2, "ENCODING = %s",
+							 quote_identifier(pg_encoding_to_char(dbForm->encoding)));
+
+	/* Fetch the value of LC_COLLATE */
+	dbValue = SysCacheGetAttr(DATABASEOID, tupleDatabase,
+							  Anum_pg_database_datcollate, &attrIsNull);
+	if (!attrIsNull)
+		get_formatted_string(&buf, prettyFlags, 2, "LC_COLLATE = %s",
+							 quote_identifier(TextDatumGetCString(dbValue)));
+
+	/* Fetch the value of LC_CTYPE */
+	dbValue = SysCacheGetAttr(DATABASEOID, tupleDatabase,
+							  Anum_pg_database_datctype, &attrIsNull);
+	if (!attrIsNull)
+		get_formatted_string(&buf, prettyFlags, 2, "LC_CTYPE = %s",
+							 quote_identifier(TextDatumGetCString(dbValue)));
+
+	/* Fetch the value of LOCALE */
+	dbValue = SysCacheGetAttr(DATABASEOID, tupleDatabase,
+							  Anum_pg_database_datlocale, &attrIsNull);
+	if (!attrIsNull && dbForm->datlocprovider == COLLPROVIDER_BUILTIN)
+		get_formatted_string(&buf, prettyFlags, 2, "BUILTIN_LOCALE = %s",
+							 quote_identifier(TextDatumGetCString(dbValue)));
+	else if (!attrIsNull && dbForm->datlocprovider == COLLPROVIDER_ICU)
+		get_formatted_string(&buf, prettyFlags, 2, "ICU_LOCALE = %s",
+							 quote_identifier(TextDatumGetCString(dbValue)));
+
+	/* Fetch the value of ICU_RULES */
+	dbValue = SysCacheGetAttr(DATABASEOID, tupleDatabase,
+							  Anum_pg_database_daticurules, &attrIsNull);
+	if (!attrIsNull && dbForm->datlocprovider == COLLPROVIDER_ICU)
+		get_formatted_string(&buf, prettyFlags, 2, "ICU_RULES = %s",
+							 quote_identifier(TextDatumGetCString(dbValue)));
+
+	/* Fetch the value of COLLATION_VERSION */
+	dbValue = SysCacheGetAttr(DATABASEOID, tupleDatabase,
+							  Anum_pg_database_datcollversion, &attrIsNull);
+	if (!attrIsNull)
+		get_formatted_string(&buf, prettyFlags, 2, "COLLATION_VERSION = %s",
+							 quote_identifier(TextDatumGetCString(dbValue)));
+
+	/* Set the appropriate LOCALE_PROVIDER */
+	if (dbForm->datlocprovider == COLLPROVIDER_BUILTIN)
+		get_formatted_string(&buf, prettyFlags, 2, "LOCALE_PROVIDER = 'builtin'");
+	else if (dbForm->datlocprovider == COLLPROVIDER_ICU)
+		get_formatted_string(&buf, prettyFlags, 2, "LOCALE_PROVIDER = 'icu'");
+	else
+		get_formatted_string(&buf, prettyFlags, 2, "LOCALE_PROVIDER = 'libc'");
+
+	/* Get the tablespace name respective to the given tablespace oid */
+	if (OidIsValid(dbForm->dattablespace))
+	{
+		char	   *dbTablespace = get_tablespace_name(dbForm->dattablespace);
+
+		if (dbTablespace)
+			get_formatted_string(&buf, prettyFlags, 2, "TABLESPACE = %s",
+								 quote_identifier(dbTablespace));
+	}
+
+	get_formatted_string(&buf, prettyFlags, 2, "ALLOW_CONNECTIONS = %s",
+						 dbForm->datallowconn ? "true" : "false");
+
+	get_formatted_string(&buf, prettyFlags, 2, "CONNECTION LIMIT = %d",
+						 dbForm->datconnlimit);
+
+	if (dbForm->datistemplate)
+		get_formatted_string(&buf, prettyFlags, 2, "IS_TEMPLATE = %s",
+							 dbForm->datistemplate ? "true" : "false");
+
+	appendStringInfoChar(&buf, ';');
+
+	ReleaseSysCache(tupleDatabase);
+
+	return buf.data;
+}
diff --git a/src/include/catalog/pg_proc.dat b/src/include/catalog/pg_proc.dat
index aaadfd8c748..8ae49a0d35c 100644
--- a/src/include/catalog/pg_proc.dat
+++ b/src/include/catalog/pg_proc.dat
@@ -4021,6 +4021,9 @@
   proname => 'pg_get_function_sqlbody', provolatile => 's',
   prorettype => 'text', proargtypes => 'oid',
   prosrc => 'pg_get_function_sqlbody' },
+{ oid => '9492', descr => 'get CREATE statement for database name and oid',
+  proname => 'pg_get_database_ddl', prorettype => 'text',
+  proargtypes => 'regdatabase bool', prosrc => 'pg_get_database_ddl' },
 
 { oid => '1686', descr => 'list of SQL keywords',
   proname => 'pg_get_keywords', procost => '10', prorows => '500',
diff --git a/src/test/regress/expected/database.out b/src/test/regress/expected/database.out
index 6b879b0f62a..e0dac8d89e2 100644
--- a/src/test/regress/expected/database.out
+++ b/src/test/regress/expected/database.out
@@ -1,3 +1,49 @@
+--
+-- Reconsturct DDL
+--
+-- To produce stable regression test output, it's usually necessary to
+-- ignore collation and locale related details. This filter
+-- functions removes collation and locale related details.
+CREATE OR REPLACE FUNCTION ddl_filter(ddl_input TEXT)
+RETURNS TEXT AS $$
+DECLARE
+    cleaned_ddl TEXT;
+BEGIN
+	-- Remove LC_COLLATE assignments 
+    cleaned_ddl := regexp_replace(
+        ddl_input,
+        '\s*LC_COLLATE\s*=\s*([''"])[^''"]*\1',
+        '',
+        'gi'
+    );
+
+    -- Remove LC_CTYPE assignments
+    cleaned_ddl := regexp_replace(
+        cleaned_ddl,
+        '\s*LC_CTYPE\s*=\s*([''"])[^''"]*\1',
+        '',
+        'gi'
+    );
+
+    -- Remove %LOCALE% placeholders
+    cleaned_ddl := regexp_replace(
+        cleaned_ddl,
+        '\s*\S*LOCALE\S*\s*=?\s*([''"])[^''"]*\1',
+		'',
+		'gi'
+	);
+
+    -- Remove %COLLATION% placeholders
+    cleaned_ddl := regexp_replace(
+        cleaned_ddl,
+        '\s*\S*COLLATION\S*\s*=?\s*([''"])[^''"]*\1',
+        '',
+        'gi'
+    );
+
+    RETURN cleaned_ddl;
+END;
+$$ LANGUAGE plpgsql;
 CREATE DATABASE regression_tbd
 	ENCODING utf8 LC_COLLATE "C" LC_CTYPE "C" TEMPLATE template0;
 ALTER DATABASE regression_tbd RENAME TO regression_utf8;
@@ -16,6 +62,37 @@ CREATE ROLE regress_datdba_before;
 CREATE ROLE regress_datdba_after;
 ALTER DATABASE regression_utf8 OWNER TO regress_datdba_before;
 REASSIGN OWNED BY regress_datdba_before TO regress_datdba_after;
+-- Test pg_get_database_ddl
+-- Database doesn't exists
+SELECT pg_get_database_ddl('regression_database', false);
+ERROR:  database "regression_database" does not exist
+LINE 1: SELECT pg_get_database_ddl('regression_database', false);
+                                   ^
+-- Test NULL value
+SELECT pg_get_database_ddl(NULL);
+ pg_get_database_ddl 
+---------------------
+ 
+(1 row)
+
+-- Without pretty
+SELECT ddl_filter(pg_get_database_ddl('regression_utf8'));
+                                                                          ddl_filter                                                                          
+--------------------------------------------------------------------------------------------------------------------------------------------------------------
+ CREATE DATABASE regression_utf8 WITH OWNER = regress_datdba_after ENCODING = "UTF8" TABLESPACE = pg_default ALLOW_CONNECTIONS = true CONNECTION LIMIT = 123;
+(1 row)
+
+-- With Pretty formatted
+\pset format unaligned
+SELECT ddl_filter(pg_get_database_ddl('regression_utf8', true));
+ddl_filter
+CREATE DATABASE regression_utf8
+	WITH OWNER = regress_datdba_after
+		ENCODING = "UTF8"
+		TABLESPACE = pg_default
+		ALLOW_CONNECTIONS = true
+		CONNECTION LIMIT = 123;
+(1 row)
 DROP DATABASE regression_utf8;
 DROP ROLE regress_datdba_before;
 DROP ROLE regress_datdba_after;
diff --git a/src/test/regress/sql/database.sql b/src/test/regress/sql/database.sql
index 4ef36127291..392a4d96bb5 100644
--- a/src/test/regress/sql/database.sql
+++ b/src/test/regress/sql/database.sql
@@ -1,3 +1,51 @@
+--
+-- Reconsturct DDL
+--
+-- To produce stable regression test output, it's usually necessary to
+-- ignore collation and locale related details. This filter
+-- functions removes collation and locale related details.
+
+CREATE OR REPLACE FUNCTION ddl_filter(ddl_input TEXT)
+RETURNS TEXT AS $$
+DECLARE
+    cleaned_ddl TEXT;
+BEGIN
+	-- Remove LC_COLLATE assignments 
+    cleaned_ddl := regexp_replace(
+        ddl_input,
+        '\s*LC_COLLATE\s*=\s*([''"])[^''"]*\1',
+        '',
+        'gi'
+    );
+
+    -- Remove LC_CTYPE assignments
+    cleaned_ddl := regexp_replace(
+        cleaned_ddl,
+        '\s*LC_CTYPE\s*=\s*([''"])[^''"]*\1',
+        '',
+        'gi'
+    );
+
+    -- Remove %LOCALE% placeholders
+    cleaned_ddl := regexp_replace(
+        cleaned_ddl,
+        '\s*\S*LOCALE\S*\s*=?\s*([''"])[^''"]*\1',
+		'',
+		'gi'
+	);
+
+    -- Remove %COLLATION% placeholders
+    cleaned_ddl := regexp_replace(
+        cleaned_ddl,
+        '\s*\S*COLLATION\S*\s*=?\s*([''"])[^''"]*\1',
+        '',
+        'gi'
+    );
+
+    RETURN cleaned_ddl;
+END;
+$$ LANGUAGE plpgsql;
+
 CREATE DATABASE regression_tbd
 	ENCODING utf8 LC_COLLATE "C" LC_CTYPE "C" TEMPLATE template0;
 ALTER DATABASE regression_tbd RENAME TO regression_utf8;
@@ -19,6 +67,20 @@ CREATE ROLE regress_datdba_after;
 ALTER DATABASE regression_utf8 OWNER TO regress_datdba_before;
 REASSIGN OWNED BY regress_datdba_before TO regress_datdba_after;
 
+-- Test pg_get_database_ddl
+-- Database doesn't exists
+SELECT pg_get_database_ddl('regression_database', false);
+
+-- Test NULL value
+SELECT pg_get_database_ddl(NULL);
+
+-- Without pretty
+SELECT ddl_filter(pg_get_database_ddl('regression_utf8'));
+
+-- With Pretty formatted
+\pset format unaligned
+SELECT ddl_filter(pg_get_database_ddl('regression_utf8', true));
+
 DROP DATABASE regression_utf8;
 DROP ROLE regress_datdba_before;
 DROP ROLE regress_datdba_after;
-- 
2.51.0