Subject: pg_get__*_ddl consolidation

From: Andrew Dunstan <[email-masked]>
Date: Thu, 19 Mar 2026 14:34:22 -0400

Greetings

Euler Taveira and I have been working on consolidating these patches.

These patches came out of a suggestion from me some time back [1], and I 
used it as the base for some work at an EDB internal program. Perhaps I 
was motivated a bit by Mao's dictum "Let a hundred flowers bloom; let a 
hundred schools of thought contend." I wanted to see what people would 
come up with. Therefore, if this has seemed a bit chaotic, I apologize, 
both to the authors and to the list. I won't do things quite this way in 
future.

Rather than adding to the already huge ruleutils.c, we decided to create 
a new ddlutils.c file to contain these functions and their associated 
infrastructure. There is in fact a fairly clean separation between these 
functions and ruleutils. We just need to expose one function in ruleutils.

We (Euler and I) decided to concentrate on setting up common 
infrastucture and ensuring a common argument and result structure. In 
this first round, we are proposing to add functions for getting the DDL 
for databases, tablespaces, and roles. We decided to stop there for now. 
This sets up a good basis for dealing with more object types in future. 
To the authors of the remaining patches - rest assured you have not been 
forgotten.

Patch 1 sets up the functions used by the rest for option parsing. see [2]
Patch 2 implements pg_get_role_dll see[3]
Patch 3 implements pg_get_tabespace_ddl see [4]
Patch 4 implements pg_get_database_ddl see [2]

cheers

andrew

[1] 
https://www.postgresql.org/message-id/flat/945db7c5-be75-45bf-b55b-cb1e56f2e3e9%40dunslane.net

[2] 
https://www.postgresql.org/message-id/flat/[email-masked]

[3] 
https://www.postgresql.org/message-id/flat/[email-masked]

[4] 
https://www.postgresql.org/message-id/flat/CAKWEB6rmnmGKUA87Zmq-s=[email-masked]

--
Andrew Dunstan
EDB: https://www.enterprisedb.com
From 0392c7a915dd12fbd264586cdcc42195d465dbe1 Mon Sep 17 00:00:00 2001
From: Andrew Dunstan <[email-masked]>
Date: Thu, 19 Mar 2026 09:50:41 -0400
Subject: [PATCH 1/4] Add DDL option parsing infrastructure for pg_get_*_ddl
 functions

Add parse_ddl_options() and append_ddl_option() helper functions in a
new ddlutils.c file that provide common option parsing for the
pg_get_*_ddl family of functions which will follow in later patches.
These accept VARIADIC text arguments as alternating name/value pairs.

Callers declare an array of DdlOption descriptors specifying the
accepted option names and their types (boolean, text, or integer).
parse_ddl_options() matches each supplied pair against the array,
validates the value, and fills in the result fields.  This
descriptor-based scheme is based on an idea from Euler Taveira.

This is placed in a new ddlutils.c file which will contain the
pg_get_*_ddl functions.

Author: Akshay Joshi <[email-masked]>
Co-authored-by: Andrew Dunstan <[email-masked]>
Reviewed-by: Euler Taveira <[email-masked]>
Discussion: https://www.postgresql.org/message-id/flat/CAKWEB6rmnmGKUA87Zmq-s=[email-masked]
Discussion: https://www.postgresql.org/message-id/flat/[email-masked]
Discussion: https://www.postgresql.org/message-id/flat/[email-masked]
---
 src/backend/utils/adt/Makefile    |   1 +
 src/backend/utils/adt/ddlutils.c  | 213 ++++++++++++++++++++++++++++++
 src/backend/utils/adt/meson.build |   1 +
 src/tools/pgindent/typedefs.list  |   2 +
 4 files changed, 217 insertions(+)
 create mode 100644 src/backend/utils/adt/ddlutils.c

diff --git a/src/backend/utils/adt/Makefile b/src/backend/utils/adt/Makefile
index a8fd680589f..0c7621957c1 100644
--- a/src/backend/utils/adt/Makefile
+++ b/src/backend/utils/adt/Makefile
@@ -31,6 +31,7 @@ OBJS = \
 	datetime.o \
 	datum.o \
 	dbsize.o \
+	ddlutils.o \
 	domains.o \
 	encode.o \
 	enum.o \
diff --git a/src/backend/utils/adt/ddlutils.c b/src/backend/utils/adt/ddlutils.c
new file mode 100644
index 00000000000..1ed44cbcb1a
--- /dev/null
+++ b/src/backend/utils/adt/ddlutils.c
@@ -0,0 +1,213 @@
+/*-------------------------------------------------------------------------
+ *
+ * ddlutils.c
+ *		Utility functions for generating DDL statements
+ *
+ * This file contains the pg_get_*_ddl family of functions that generate
+ * DDL statements to recreate database objects such as roles, tablespaces,
+ * and databases, along with common infrastructure for option parsing and
+ * pretty-printing.
+ *
+ * Portions Copyright (c) 1996-2026, PostgreSQL Global Development Group
+ * Portions Copyright (c) 1994, Regents of the University of California
+ *
+ * IDENTIFICATION
+ *	  src/backend/utils/adt/ddlutils.c
+ *
+ *-------------------------------------------------------------------------
+ */
+#include "postgres.h"
+
+#include "utils/builtins.h"
+#include "utils/guc.h"
+#include "utils/varlena.h"
+
+/* Option value types for DDL option parsing */
+typedef enum
+{
+	DDL_OPT_BOOL,
+	DDL_OPT_TEXT,
+	DDL_OPT_INT
+} DdlOptType;
+
+/*
+ * A single DDL option descriptor: caller fills in name and type,
+ * parse_ddl_options fills in isset + the appropriate value field.
+ */
+typedef struct DdlOption
+{
+	const char *name;			/* option name (case-insensitive match) */
+	DdlOptType	type;			/* expected value type */
+	bool		isset;			/* true if caller supplied this option */
+	bool		boolval;		/* filled in for DDL_OPT_BOOL */
+	char	   *textval;		/* filled in for DDL_OPT_TEXT (palloc'd) */
+	int			intval;			/* filled in for DDL_OPT_INT */
+} DdlOption;
+
+
+static void parse_ddl_options(FunctionCallInfo fcinfo, int variadic_start,
+							  DdlOption *opts, int nopts);
+static void append_ddl_option(StringInfo buf, bool pretty, int indent,
+							  const char *fmt,...)
+			pg_attribute_printf(4, 5);
+
+
+/*
+ * parse_ddl_options
+ * 		Parse variadic name/value option pairs
+ *
+ * Options are passed as alternating key/value text pairs.  The caller
+ * provides an array of DdlOption descriptors specifying the accepted
+ * option names and their types; this function matches each supplied
+ * pair against the array, validates the value, and fills in the
+ * result fields.
+ */
+static void
+parse_ddl_options(FunctionCallInfo fcinfo, int variadic_start,
+				  DdlOption *opts, int nopts)
+{
+	Datum	   *args;
+	bool	   *nulls;
+	Oid		   *types;
+	int			nargs;
+
+	/* Clear all output fields */
+	for (int i = 0; i < nopts; i++)
+	{
+		opts[i].isset = false;
+		opts[i].boolval = false;
+		opts[i].textval = NULL;
+		opts[i].intval = 0;
+	}
+
+	nargs = extract_variadic_args(fcinfo, variadic_start, true,
+								  &args, &types, &nulls);
+
+	if (nargs <= 0)
+		return;
+
+	/* Handle DEFAULT NULL case */
+	if (nargs == 1 && nulls[0])
+		return;
+
+	if (nargs % 2 != 0)
+		ereport(ERROR,
+				(errcode(ERRCODE_INVALID_PARAMETER_VALUE),
+				 errmsg("variadic arguments must be name/value pairs"),
+				 errhint("Provide an even number of variadic arguments that can be divided into pairs.")));
+
+	/*
+	 * For each option name/value pair, find corresponding positional option
+	 * for the option name, and assign the option value.
+	 */
+	for (int i = 0; i < nargs; i += 2)
+	{
+		char	   *name;
+		char	   *valstr;
+		DdlOption  *opt = NULL;
+
+		if (nulls[i])
+			ereport(ERROR,
+					(errcode(ERRCODE_INVALID_PARAMETER_VALUE),
+					 errmsg("option name at variadic position %d is null", i + 1)));
+
+		name = TextDatumGetCString(args[i]);
+
+		if (nulls[i + 1])
+			ereport(ERROR,
+					(errcode(ERRCODE_INVALID_PARAMETER_VALUE),
+					 errmsg("value for option \"%s\" must not be null", name)));
+
+		/* Find matching option descriptor */
+		for (int j = 0; j < nopts; j++)
+		{
+			if (pg_strcasecmp(name, opts[j].name) == 0)
+			{
+				opt = &opts[j];
+				break;
+			}
+		}
+
+		if (opt == NULL)
+			ereport(ERROR,
+					(errcode(ERRCODE_INVALID_PARAMETER_VALUE),
+					 errmsg("unrecognized option: \"%s\"", name)));
+
+		if (opt->isset)
+			ereport(ERROR,
+					(errcode(ERRCODE_INVALID_PARAMETER_VALUE),
+					 errmsg("option \"%s\" is specified more than once",
+							name)));
+
+		valstr = TextDatumGetCString(args[i + 1]);
+
+		switch (opt->type)
+		{
+			case DDL_OPT_BOOL:
+				if (!parse_bool(valstr, &opt->boolval))
+					ereport(ERROR,
+							(errcode(ERRCODE_INVALID_PARAMETER_VALUE),
+							 errmsg("invalid value for boolean option \"%s\": %s",
+									name, valstr)));
+				break;
+
+			case DDL_OPT_TEXT:
+				opt->textval = valstr;
+				valstr = NULL;	/* don't pfree below */
+				break;
+
+			case DDL_OPT_INT:
+				{
+					char	   *endp;
+					long		val;
+
+					errno = 0;
+					val = strtol(valstr, &endp, 10);
+					if (*endp != '\0' || errno == ERANGE ||
+						val < PG_INT32_MIN || val > PG_INT32_MAX)
+						ereport(ERROR,
+								(errcode(ERRCODE_INVALID_PARAMETER_VALUE),
+								 errmsg("invalid value for integer option \"%s\": %s",
+										name, valstr)));
+					opt->intval = (int) val;
+				}
+				break;
+		}
+
+		opt->isset = true;
+
+		if (valstr)
+			pfree(valstr);
+		pfree(name);
+	}
+}
+
+/*
+ * Helper to append a formatted string with optional pretty-printing.
+ */
+static void
+append_ddl_option(StringInfo buf, bool pretty, int indent,
+				  const char *fmt,...)
+{
+	va_list		args;
+
+	if (pretty)
+	{
+		appendStringInfoChar(buf, '\n');
+		appendStringInfoSpaces(buf, indent);
+	}
+	else
+		appendStringInfoChar(buf, ' ');
+
+	for (;;)
+	{
+		int			needed;
+
+		va_start(args, fmt);
+		needed = appendStringInfoVA(buf, fmt, args);
+		va_end(args);
+		if (needed == 0)
+			break;
+		enlargeStringInfo(buf, needed);
+	}
+}
diff --git a/src/backend/utils/adt/meson.build b/src/backend/utils/adt/meson.build
index fb8294d7e4a..d793f8145f6 100644
--- a/src/backend/utils/adt/meson.build
+++ b/src/backend/utils/adt/meson.build
@@ -30,6 +30,7 @@ backend_sources += files(
   'datetime.c',
   'datum.c',
   'dbsize.c',
+  'ddlutils.c',
   'domains.c',
   'encode.c',
   'enum.c',
diff --git a/src/tools/pgindent/typedefs.list b/src/tools/pgindent/typedefs.list
index 4673eca9cd6..007391484a4 100644
--- a/src/tools/pgindent/typedefs.list
+++ b/src/tools/pgindent/typedefs.list
@@ -620,6 +620,8 @@ DSMREntryType
 DSMRegistryCtxStruct
 DSMRegistryEntry
 DWORD
+DdlOptType
+DdlOption
 DataDirSyncMethod
 DataDumperPtr
 DataPageDeleteStack
-- 
2.43.0

From b615f89b98b2e09f275cb82cdea557bed0a2b68d Mon Sep 17 00:00:00 2001
From: Andrew Dunstan <[email-masked]>
Date: Thu, 19 Mar 2026 09:52:25 -0400
Subject: [PATCH 2/4] 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 option is supported: pretty (boolean) for
formatted output. 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.

Author: Mario Gonzalez <[email-masked]>
Author: Bryan Green <[email-masked]>
Co-authored-by: Andrew Dunstan <[email-masked]>
Reviewed-by: Euler Taveira <[email-masked]>
Reviewed-by: Japin Li <[email-masked]>
Reviewed-by: Quan Zongliang <[email-masked]>
Reviewed-by: jian he <[email-masked]>
Discussion: https://www.postgresql.org/message-id/flat/[email-masked]
---
 doc/src/sgml/func/func-info.sgml       |  54 ++++
 src/backend/utils/adt/ddlutils.c       | 330 +++++++++++++++++++++++++
 src/include/catalog/pg_proc.dat        |   8 +
 src/test/regress/expected/role_ddl.out | 100 ++++++++
 src/test/regress/parallel_schedule     |   2 +
 src/test/regress/sql/role_ddl.sql      |  63 +++++
 6 files changed, 557 insertions(+)
 create mode 100644 src/test/regress/expected/role_ddl.out
 create mode 100644 src/test/regress/sql/role_ddl.sql

diff --git a/doc/src/sgml/func/func-info.sgml b/doc/src/sgml/func/func-info.sgml
index 5b5f1f3c5df..acd1a7cfeed 100644
--- a/doc/src/sgml/func/func-info.sgml
+++ b/doc/src/sgml/func/func-info.sgml
@@ -3860,4 +3860,58 @@ acl      | {postgres=arwdDxtm/postgres,foo=r/postgres}
 
   </sect2>
 
+  <sect2 id="functions-get-object-ddl">
+   <title>Get Object DDL Functions</title>
+
+   <para>
+    The functions shown in <xref linkend="functions-get-object-ddl-table"/>
+    reconstruct DDL statements for various global database objects.
+    Each function returns a set of text rows, one SQL statement per row.
+    (This is a decompiled reconstruction, not the original text of the
+    command.)  Functions that accept <literal>VARIADIC</literal> options
+    take alternating name/value text pairs; values are parsed as boolean,
+    integer or text.
+   </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_role_ddl</primary>
+        </indexterm>
+        <function>pg_get_role_ddl</function>
+        ( <parameter>role</parameter> <type>regrole</type>
+        <optional>, <literal>VARIADIC</literal> <parameter>options</parameter>
+        <type>text</type> </optional> )
+        <returnvalue>setof text</returnvalue>
+       </para>
+       <para>
+        Reconstructs the <command>CREATE ROLE</command> statement and any
+        <command>ALTER ROLE ... SET</command> statements for the given role.
+        Each statement is returned as a separate row.
+        Password information is never included in the output.
+        The following option is supported: <literal>pretty</literal> (boolean)
+        for pretty-printed output.
+       </para></entry>
+      </row>
+     </tbody>
+    </tgroup>
+   </table>
+
+  </sect2>
+
   </sect1>
diff --git a/src/backend/utils/adt/ddlutils.c b/src/backend/utils/adt/ddlutils.c
index 1ed44cbcb1a..c067de0a3d2 100644
--- a/src/backend/utils/adt/ddlutils.c
+++ b/src/backend/utils/adt/ddlutils.c
@@ -18,8 +18,24 @@
  */
 #include "postgres.h"
 
+#include "access/genam.h"
+#include "access/htup_details.h"
+#include "access/relation.h"
+#include "access/table.h"
+#include "catalog/pg_authid.h"
+#include "catalog/pg_db_role_setting.h"
+#include "funcapi.h"
+#include "miscadmin.h"
+#include "utils/acl.h"
+#include "utils/array.h"
 #include "utils/builtins.h"
+#include "utils/datetime.h"
+#include "utils/fmgroids.h"
 #include "utils/guc.h"
+#include "utils/lsyscache.h"
+#include "utils/rel.h"
+#include "utils/syscache.h"
+#include "utils/timestamp.h"
 #include "utils/varlena.h"
 
 /* Option value types for DDL option parsing */
@@ -50,6 +66,7 @@ static void parse_ddl_options(FunctionCallInfo fcinfo, int variadic_start,
 static void append_ddl_option(StringInfo buf, bool pretty, int indent,
 							  const char *fmt,...)
 			pg_attribute_printf(4, 5);
+static List *pg_get_role_ddl_internal(Oid roleid, bool pretty);

 /*
@@ -211,3 +228,316 @@ append_ddl_option(StringInfo buf, bool pretty, int indent,
 		enlargeStringInfo(buf, needed);
 	}
 }
+
+/*
+ * pg_get_role_ddl_internal
+ *		Generate DDL statements to recreate a role
+ *
+ * Returns a List of palloc'd strings, each being a complete SQL statement.
+ * The first list element is always the CREATE ROLE statement; subsequent
+ * elements are ALTER ROLE SET statements for any role-specific or
+ * role-in-database configuration settings.
+ */
+static List *
+pg_get_role_ddl_internal(Oid roleid, bool pretty)
+{
+	HeapTuple	tuple;
+	Form_pg_authid roleform;
+	StringInfoData buf;
+	char	   *rolname;
+	Datum		rolevaliduntil;
+	bool		isnull;
+	Relation	rel;
+	ScanKeyData scankey;
+	SysScanDesc scan;
+	List	   *statements = NIL;
+
+	tuple = SearchSysCache1(AUTHOID, ObjectIdGetDatum(roleid));
+	if (!HeapTupleIsValid(tuple))
+		ereport(ERROR,
+				(errcode(ERRCODE_UNDEFINED_OBJECT),
+				 errmsg("role with OID %u does not exist", roleid)));
+
+	roleform = (Form_pg_authid) GETSTRUCT(tuple);
+	rolname = pstrdup(NameStr(roleform->rolname));
+
+	/*
+	 * We don't support generating DDL for system roles.  The primary reason
+	 * for this is that users shouldn't be recreating them.
+	 */
+	if (IsReservedName(rolname))
+		ereport(ERROR,
+				(errcode(ERRCODE_RESERVED_NAME),
+				 errmsg("role name \"%s\" is reserved", rolname),
+				 errdetail("Role names starting with \"pg_\" are reserved for system roles.")));
+
+	initStringInfo(&buf);
+	appendStringInfo(&buf, "CREATE ROLE %s", quote_identifier(rolname));
+
+	/*
+	 * Append role attributes.  The order here follows the same sequence as
+	 * you'd typically write them in a CREATE ROLE command, though any order
+	 * is actually acceptable to the parser.
+	 */
+	append_ddl_option(&buf, pretty, 4, "%s",
+					  roleform->rolsuper ? "SUPERUSER" : "NOSUPERUSER");
+
+	append_ddl_option(&buf, pretty, 4, "%s",
+					  roleform->rolinherit ? "INHERIT" : "NOINHERIT");
+
+	append_ddl_option(&buf, pretty, 4, "%s",
+					  roleform->rolcreaterole ? "CREATEROLE" : "NOCREATEROLE");
+
+	append_ddl_option(&buf, pretty, 4, "%s",
+					  roleform->rolcreatedb ? "CREATEDB" : "NOCREATEDB");
+
+	append_ddl_option(&buf, pretty, 4, "%s",
+					  roleform->rolcanlogin ? "LOGIN" : "NOLOGIN");
+
+	append_ddl_option(&buf, pretty, 4, "%s",
+					  roleform->rolreplication ? "REPLICATION" : "NOREPLICATION");
+
+	append_ddl_option(&buf, pretty, 4, "%s",
+					  roleform->rolbypassrls ? "BYPASSRLS" : "NOBYPASSRLS");
+
+	/*
+	 * CONNECTION LIMIT is only interesting if it's not -1 (the default,
+	 * meaning no limit).
+	 */
+	if (roleform->rolconnlimit >= 0)
+		append_ddl_option(&buf, pretty, 4, "CONNECTION LIMIT %d",
+						  roleform->rolconnlimit);
+
+	rolevaliduntil = SysCacheGetAttr(AUTHOID, tuple,
+									 Anum_pg_authid_rolvaliduntil,
+									 &isnull);
+	if (!isnull)
+	{
+		TimestampTz ts;
+		int			tz;
+		struct pg_tm tm;
+		fsec_t		fsec;
+		const char *tzn;
+		char		ts_str[MAXDATELEN + 1];
+
+		ts = DatumGetTimestampTz(rolevaliduntil);
+		if (TIMESTAMP_NOT_FINITE(ts))
+			EncodeSpecialTimestamp(ts, ts_str);
+		else if (timestamp2tm(ts, &tz, &tm, &fsec, &tzn, NULL) == 0)
+			EncodeDateTime(&tm, fsec, true, tz, tzn, DateStyle, ts_str);
+		else
+			ereport(ERROR,
+					(errcode(ERRCODE_DATETIME_VALUE_OUT_OF_RANGE),
+					 errmsg("timestamp out of range")));
+
+		append_ddl_option(&buf, pretty, 4, "VALID UNTIL %s",
+						  quote_literal_cstr(ts_str));
+	}
+
+	ReleaseSysCache(tuple);
+
+	/*
+	 * We intentionally omit PASSWORD.  There's no way to retrieve the
+	 * original password text from the stored hash, and even if we could,
+	 * exposing passwords through a SQL function would be a security issue.
+	 * Users must set passwords separately after recreating roles.
+	 */
+
+	appendStringInfoChar(&buf, ';');
+
+	statements = lappend(statements, pstrdup(buf.data));
+
+	/*
+	 * Now scan pg_db_role_setting for ALTER ROLE SET configurations.
+	 *
+	 * These can be role-wide (setdatabase = 0) or specific to a particular
+	 * database (setdatabase = a valid DB OID).  It generates one ALTER
+	 * statement per setting.
+	 */
+	rel = table_open(DbRoleSettingRelationId, AccessShareLock);
+	ScanKeyInit(&scankey,
+				Anum_pg_db_role_setting_setrole,
+				BTEqualStrategyNumber, F_OIDEQ,
+				ObjectIdGetDatum(roleid));
+	scan = systable_beginscan(rel, DbRoleSettingDatidRolidIndexId, true,
+							  NULL, 1, &scankey);
+
+	while (HeapTupleIsValid(tuple = systable_getnext(scan)))
+	{
+		Form_pg_db_role_setting setting = (Form_pg_db_role_setting) GETSTRUCT(tuple);
+		Oid			datid = setting->setdatabase;
+		Datum		datum;
+		ArrayType  *reloptions;
+		Datum	   *settings;
+		bool	   *nulls;
+		int			nsettings;
+		char	   *datname = NULL;
+
+		/*
+		 * If setdatabase is valid, this is a role-in-database setting;
+		 * otherwise it's a role-wide setting.  Look up the database name once
+		 * for all settings in this row.
+		 */
+		if (OidIsValid(datid))
+		{
+			datname = get_database_name(datid);
+			/* Database has been dropped; skip all settings in this row. */
+			if (datname == NULL)
+				continue;
+		}
+
+		/*
+		 * The setconfig column is a text array in "name=value" format. It
+		 * should never be null for a valid row, but be defensive.
+		 */
+		datum = heap_getattr(tuple, Anum_pg_db_role_setting_setconfig,
+							 RelationGetDescr(rel), &isnull);
+		if (isnull)
+			continue;
+
+		reloptions = DatumGetArrayTypeP(datum);
+
+		deconstruct_array_builtin(reloptions, TEXTOID, &settings, &nulls, &nsettings);
+
+		for (int i = 0; i < nsettings; i++)
+		{
+			char	   *s,
+					   *p;
+
+			if (nulls[i])
+				continue;
+
+			s = TextDatumGetCString(settings[i]);
+			p = strchr(s, '=');
+			if (p == NULL)
+			{
+				pfree(s);
+				continue;
+			}
+			*p++ = '\0';
+
+			/* Build a fresh ALTER ROLE statement for this setting */
+			resetStringInfo(&buf);
+			appendStringInfo(&buf, "ALTER ROLE %s", quote_identifier(rolname));
+
+			if (datname != NULL)
+				appendStringInfo(&buf, " IN DATABASE %s",
+								 quote_identifier(datname));
+
+			appendStringInfo(&buf, " SET %s TO ",
+							 quote_identifier(s));
+
+			/*
+			 * Variables that are marked GUC_LIST_QUOTE were already fully
+			 * quoted before they were put into the setconfig array.  Break
+			 * the list value apart and then quote the elements as string
+			 * literals.
+			 */
+			if (GetConfigOptionFlags(s, true) & GUC_LIST_QUOTE)
+			{
+				List	   *namelist;
+				ListCell   *lc;
+
+				/* Parse string into list of identifiers */
+				if (!SplitGUCList(p, ',', &namelist))
+				{
+					/* this shouldn't fail really */
+					elog(ERROR, "invalid list syntax in setconfig item");
+				}
+				/* Special case: represent an empty list as NULL */
+				if (namelist == NIL)
+					appendStringInfoString(&buf, "NULL");
+				foreach(lc, namelist)
+				{
+					char	   *curname = (char *) lfirst(lc);
+
+					appendStringInfoString(&buf, quote_literal_cstr(curname));
+					if (lnext(namelist, lc))
+						appendStringInfoString(&buf, ", ");
+				}
+			}
+			else
+				appendStringInfoString(&buf, quote_literal_cstr(p));
+
+			appendStringInfoChar(&buf, ';');
+
+			statements = lappend(statements, pstrdup(buf.data));
+
+			pfree(s);
+		}
+
+		pfree(settings);
+		pfree(reloptions);
+
+		if (datname != NULL)
+			pfree(datname);
+	}
+
+	systable_endscan(scan);
+	table_close(rel, AccessShareLock);
+
+	pfree(buf.data);
+	pfree(rolname);
+
+	return statements;
+}
+
+/*
+ * pg_get_role_ddl
+ *		Return DDL to recreate a role as a set of text rows.
+ *
+ * Each row is a complete SQL statement.  The first row is always the
+ * CREATE ROLE statement; subsequent rows are ALTER ROLE SET statements.
+ * Returns no rows if the role argument is NULL.
+ */
+Datum
+pg_get_role_ddl(PG_FUNCTION_ARGS)
+{
+	FuncCallContext *funcctx;
+	List	   *statements;
+	ListCell   *lc;
+
+	if (SRF_IS_FIRSTCALL())
+	{
+		MemoryContext oldcontext;
+		Oid			roleid;
+		DdlOption	opts[] = {{"pretty", DDL_OPT_BOOL}};
+
+		funcctx = SRF_FIRSTCALL_INIT();
+		oldcontext = MemoryContextSwitchTo(funcctx->multi_call_memory_ctx);
+
+		if (PG_ARGISNULL(0))
+		{
+			MemoryContextSwitchTo(oldcontext);
+			SRF_RETURN_DONE(funcctx);
+		}
+
+		roleid = PG_GETARG_OID(0);
+		parse_ddl_options(fcinfo, 1, opts, lengthof(opts));
+
+		statements = pg_get_role_ddl_internal(roleid,
+											  opts[0].isset && opts[0].boolval);
+		funcctx->user_fctx = statements;
+		funcctx->max_calls = list_length(statements);
+
+		MemoryContextSwitchTo(oldcontext);
+	}
+
+	funcctx = SRF_PERCALL_SETUP();
+	statements = (List *) funcctx->user_fctx;
+
+	if (funcctx->call_cntr < funcctx->max_calls)
+	{
+		char	   *stmt;
+
+		lc = list_nth_cell(statements, funcctx->call_cntr);
+		stmt = (char *) lfirst(lc);
+
+		SRF_RETURN_NEXT(funcctx, CStringGetTextDatum(stmt));
+	}
+	else
+	{
+		list_free_deep(statements);
+		SRF_RETURN_DONE(funcctx);
+	}
+}
diff --git a/src/include/catalog/pg_proc.dat b/src/include/catalog/pg_proc.dat
index fc8d82665b8..e908146645f 100644
--- a/src/include/catalog/pg_proc.dat
+++ b/src/include/catalog/pg_proc.dat
@@ -8588,6 +8588,14 @@
 { oid => '2508', descr => 'constraint description with pretty-print option',
   proname => 'pg_get_constraintdef', provolatile => 's', prorettype => 'text',
   proargtypes => 'oid bool', prosrc => 'pg_get_constraintdef_ext' },
+{ oid => '8760', descr => 'get DDL to recreate a role',
+  proname => 'pg_get_role_ddl', provariadic => 'text', proisstrict => 'f',
+  provolatile => 's', proretset => 't', prorows => '10', prorettype => 'text',
+  proargtypes => 'regrole text',
+  proargmodes => '{i,v}',
+  proallargtypes => '{regrole,text}',
+  pronargdefaults => '1', proargdefaults => '{NULL}',
+  prosrc => 'pg_get_role_ddl' },
 { oid => '2509',
   descr => 'deparse an encoded expression with pretty-print option',
   proname => 'pg_get_expr', provolatile => 's', prorettype => 'text',
diff --git a/src/test/regress/expected/role_ddl.out b/src/test/regress/expected/role_ddl.out
new file mode 100644
index 00000000000..98ef42c9e28
--- /dev/null
+++ b/src/test/regress/expected/role_ddl.out
@@ -0,0 +1,100 @@
+-- Consistent test results
+SET timezone TO 'UTC';
+SET DateStyle TO 'ISO, YMD';
+-- Create test database
+CREATE DATABASE regression_role_ddl_test;
+-- Basic role
+CREATE ROLE regress_role_ddl_test1;
+SELECT * FROM pg_get_role_ddl('regress_role_ddl_test1');
+                                                  pg_get_role_ddl                                                  
+-------------------------------------------------------------------------------------------------------------------
+ CREATE ROLE regress_role_ddl_test1 NOSUPERUSER INHERIT NOCREATEROLE NOCREATEDB NOLOGIN NOREPLICATION NOBYPASSRLS;
+(1 row)
+
+-- Role with LOGIN
+CREATE ROLE regress_role_ddl_test2 LOGIN;
+SELECT * FROM pg_get_role_ddl('regress_role_ddl_test2');
+                                                 pg_get_role_ddl                                                 
+-----------------------------------------------------------------------------------------------------------------
+ CREATE ROLE regress_role_ddl_test2 NOSUPERUSER INHERIT NOCREATEROLE NOCREATEDB LOGIN NOREPLICATION NOBYPASSRLS;
+(1 row)
+
+-- Role with multiple privileges
+CREATE ROLE regress_role_ddl_test3
+  LOGIN
+  SUPERUSER
+  CREATEDB
+  CREATEROLE
+  CONNECTION LIMIT 5
+  VALID UNTIL '2030-12-31 23:59:59+00';
+SELECT * FROM pg_get_role_ddl('regress_role_ddl_test3');
+                                                                          pg_get_role_ddl                                                                          
+-------------------------------------------------------------------------------------------------------------------------------------------------------------------
+ CREATE ROLE regress_role_ddl_test3 SUPERUSER INHERIT CREATEROLE CREATEDB LOGIN NOREPLICATION NOBYPASSRLS CONNECTION LIMIT 5 VALID UNTIL '2030-12-31 23:59:59+00';
+(1 row)
+
+-- Role with configuration parameters
+CREATE ROLE regress_role_ddl_test4;
+ALTER ROLE regress_role_ddl_test4 SET work_mem TO '256MB';
+ALTER ROLE regress_role_ddl_test4 SET search_path TO 'myschema, public';
+SELECT * FROM pg_get_role_ddl('regress_role_ddl_test4');
+                                                  pg_get_role_ddl                                                  
+-------------------------------------------------------------------------------------------------------------------
+ CREATE ROLE regress_role_ddl_test4 NOSUPERUSER INHERIT NOCREATEROLE NOCREATEDB NOLOGIN NOREPLICATION NOBYPASSRLS;
+ ALTER ROLE regress_role_ddl_test4 SET work_mem TO '256MB';
+ ALTER ROLE regress_role_ddl_test4 SET search_path TO 'myschema, public';
+(3 rows)
+
+-- Role with database-specific configuration
+CREATE ROLE regress_role_ddl_test5;
+ALTER ROLE regress_role_ddl_test5 IN DATABASE regression_role_ddl_test SET work_mem TO '128MB';
+SELECT * FROM pg_get_role_ddl('regress_role_ddl_test5');
+                                                  pg_get_role_ddl                                                  
+-------------------------------------------------------------------------------------------------------------------
+ CREATE ROLE regress_role_ddl_test5 NOSUPERUSER INHERIT NOCREATEROLE NOCREATEDB NOLOGIN NOREPLICATION NOBYPASSRLS;
+ ALTER ROLE regress_role_ddl_test5 IN DATABASE regression_role_ddl_test SET work_mem TO '128MB';
+(2 rows)
+
+-- Role with special characters (requires quoting)
+CREATE ROLE "regress_role-with-dash";
+SELECT * FROM pg_get_role_ddl('regress_role-with-dash');
+                                                   pg_get_role_ddl                                                   
+---------------------------------------------------------------------------------------------------------------------
+ CREATE ROLE "regress_role-with-dash" NOSUPERUSER INHERIT NOCREATEROLE NOCREATEDB NOLOGIN NOREPLICATION NOBYPASSRLS;
+(1 row)
+
+-- Pretty-printed output
+\pset format unaligned
+SELECT * FROM pg_get_role_ddl('regress_role_ddl_test3', 'pretty', 'true');
+pg_get_role_ddl
+CREATE ROLE regress_role_ddl_test3
+    SUPERUSER
+    INHERIT
+    CREATEROLE
+    CREATEDB
+    LOGIN
+    NOREPLICATION
+    NOBYPASSRLS
+    CONNECTION LIMIT 5
+    VALID UNTIL '2030-12-31 23:59:59+00';
+(1 row)
+\pset format aligned
+-- Non-existent role (should return no rows)
+SELECT * FROM pg_get_role_ddl(9999999::oid);
+ERROR:  role with OID 9999999 does not exist
+-- NULL input (should return no rows)
+SELECT * FROM pg_get_role_ddl(NULL);
+ pg_get_role_ddl 
+-----------------
+(0 rows)
+
+-- Cleanup
+DROP ROLE regress_role_ddl_test1;
+DROP ROLE regress_role_ddl_test2;
+DROP ROLE regress_role_ddl_test3;
+DROP ROLE regress_role_ddl_test4;
+DROP ROLE regress_role_ddl_test5;
+DROP ROLE "regress_role-with-dash";
+DROP DATABASE regression_role_ddl_test;
+-- Reset timezone to default
+RESET timezone;
diff --git a/src/test/regress/parallel_schedule b/src/test/regress/parallel_schedule
index 734da057c34..7e059cef034 100644
--- a/src/test/regress/parallel_schedule
+++ b/src/test/regress/parallel_schedule
@@ -130,6 +130,8 @@ test: partition_merge partition_split partition_join partition_prune reloptions
 # oidjoins is read-only, though, and should run late for best coverage
 test: oidjoins event_trigger
 
+test: role_ddl
+
 # event_trigger_login cannot run concurrently with any other tests because
 # on-login event handling could catch connection of a concurrent test.
 test: event_trigger_login
diff --git a/src/test/regress/sql/role_ddl.sql b/src/test/regress/sql/role_ddl.sql
new file mode 100644
index 00000000000..c9509ae474e
--- /dev/null
+++ b/src/test/regress/sql/role_ddl.sql
@@ -0,0 +1,63 @@
+-- Consistent test results
+SET timezone TO 'UTC';
+SET DateStyle TO 'ISO, YMD';
+
+-- Create test database
+CREATE DATABASE regression_role_ddl_test;
+
+-- Basic role
+CREATE ROLE regress_role_ddl_test1;
+SELECT * FROM pg_get_role_ddl('regress_role_ddl_test1');
+
+-- Role with LOGIN
+CREATE ROLE regress_role_ddl_test2 LOGIN;
+SELECT * FROM pg_get_role_ddl('regress_role_ddl_test2');
+
+-- Role with multiple privileges
+CREATE ROLE regress_role_ddl_test3
+  LOGIN
+  SUPERUSER
+  CREATEDB
+  CREATEROLE
+  CONNECTION LIMIT 5
+  VALID UNTIL '2030-12-31 23:59:59+00';
+SELECT * FROM pg_get_role_ddl('regress_role_ddl_test3');
+
+-- Role with configuration parameters
+CREATE ROLE regress_role_ddl_test4;
+ALTER ROLE regress_role_ddl_test4 SET work_mem TO '256MB';
+ALTER ROLE regress_role_ddl_test4 SET search_path TO 'myschema, public';
+SELECT * FROM pg_get_role_ddl('regress_role_ddl_test4');
+
+-- Role with database-specific configuration
+CREATE ROLE regress_role_ddl_test5;
+ALTER ROLE regress_role_ddl_test5 IN DATABASE regression_role_ddl_test SET work_mem TO '128MB';
+SELECT * FROM pg_get_role_ddl('regress_role_ddl_test5');
+
+-- Role with special characters (requires quoting)
+CREATE ROLE "regress_role-with-dash";
+SELECT * FROM pg_get_role_ddl('regress_role-with-dash');
+
+-- Pretty-printed output
+\pset format unaligned
+SELECT * FROM pg_get_role_ddl('regress_role_ddl_test3', 'pretty', 'true');
+\pset format aligned
+
+-- Non-existent role (should return no rows)
+SELECT * FROM pg_get_role_ddl(9999999::oid);
+
+-- NULL input (should return no rows)
+SELECT * FROM pg_get_role_ddl(NULL);
+
+-- Cleanup
+DROP ROLE regress_role_ddl_test1;
+DROP ROLE regress_role_ddl_test2;
+DROP ROLE regress_role_ddl_test3;
+DROP ROLE regress_role_ddl_test4;
+DROP ROLE regress_role_ddl_test5;
+DROP ROLE "regress_role-with-dash";
+
+DROP DATABASE regression_role_ddl_test;
+
+-- Reset timezone to default
+RESET timezone;
-- 
2.43.0

From f344010073bb54c6935d7d7599ddb17444589880 Mon Sep 17 00:00:00 2001
From: Andrew Dunstan <[email-masked]>
Date: Thu, 19 Mar 2026 09:57:35 -0400
Subject: [PATCH 4/4] Add pg_get_database_ddl() function
MIME-Version: 1.0
Content-Type: text/plain; charset=UTF-8
Content-Transfer-Encoding: 8bit

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.

Author: Akshay Joshi <[email-masked]>
Co-authored-by: Andrew Dunstan <[email-masked]>
Reviewed-by: Japin Li <[email-masked]>
Reviewed-by: Chao Li <[email-masked]>
Reviewed-by: Álvaro Herrera <[email-masked]>
Reviewed-by: Euler Taveira <[email-masked]>
Reviewed-by: Quan Zongliang <[email-masked]>
Discussion: https://www.postgresql.org/message-id/flat/[email-masked]
---
 doc/src/sgml/func/func-info.sgml           |  23 ++
 src/backend/utils/adt/ddlutils.c           | 349 +++++++++++++++++++++
 src/include/catalog/pg_proc.dat            |   8 +
 src/test/regress/expected/database_ddl.out | 107 +++++++
 src/test/regress/parallel_schedule         |   2 +-
 src/test/regress/sql/database_ddl.sql      |  89 ++++++
 6 files changed, 577 insertions(+), 1 deletion(-)
 create mode 100644 src/test/regress/expected/database_ddl.out
 create mode 100644 src/test/regress/sql/database_ddl.sql

diff --git a/doc/src/sgml/func/func-info.sgml b/doc/src/sgml/func/func-info.sgml
index f44bd0d0f8b..ce6194f606d 100644
--- a/doc/src/sgml/func/func-info.sgml
+++ b/doc/src/sgml/func/func-info.sgml
@@ -3935,6 +3935,29 @@ acl      | {postgres=arwdDxtm/postgres,foo=r/postgres}
         for formatted output.
        </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</parameter> <type>regdatabase</type>
+        <optional>, <literal>VARIADIC</literal> <parameter>options</parameter>
+        <type>text</type> </optional> )
+        <returnvalue>setof text</returnvalue>
+       </para>
+       <para>
+        Reconstructs the <command>CREATE DATABASE</command> statement for the
+        specified database, followed by <command>ALTER DATABASE</command>
+        statements for connection limit, template status, and configuration
+        settings.  Each statement is returned as a separate row.
+        The following options are supported:
+        <literal>pretty</literal> (boolean) for formatted output,
+        <literal>owner</literal> (boolean) to include <literal>OWNER</literal>,
+        and <literal>tablespace</literal> (boolean) to include
+        <literal>TABLESPACE</literal>.
+       </para></entry>
+      </row>
      </tbody>
     </tgroup>
    </table>
diff --git a/src/backend/utils/adt/ddlutils.c b/src/backend/utils/adt/ddlutils.c
index 65df3c723cc..41838e403d3 100644
--- a/src/backend/utils/adt/ddlutils.c
+++ b/src/backend/utils/adt/ddlutils.c
@@ -22,11 +22,14 @@
 #include "access/htup_details.h"
 #include "access/table.h"
 #include "catalog/pg_authid.h"
+#include "catalog/pg_collation.h"
+#include "catalog/pg_database.h"
 #include "catalog/pg_db_role_setting.h"
 #include "catalog/pg_tablespace.h"
 #include "commands/tablespace.h"
 #include "common/relpath.h"
 #include "funcapi.h"
+#include "mb/pg_wchar.h"
 #include "miscadmin.h"
 #include "utils/acl.h"
 #include "utils/array.h"
@@ -35,6 +38,7 @@
 #include "utils/fmgroids.h"
 #include "utils/guc.h"
 #include "utils/lsyscache.h"
+#include "utils/pg_locale.h"
 #include "utils/rel.h"
 #include "utils/ruleutils.h"
 #include "utils/syscache.h"
@@ -72,6 +76,8 @@ static void append_ddl_option(StringInfo buf, bool pretty, int indent,
 static List *pg_get_role_ddl_internal(Oid roleid, bool pretty);
 static List *pg_get_tablespace_ddl_internal(Oid tsid, bool pretty);
 static Datum pg_get_tablespace_ddl_srf(FunctionCallInfo fcinfo, Oid tsid, bool isnull);
+static List *pg_get_database_ddl_internal(Oid dbid, bool pretty,
+										  bool no_owner, bool no_tablespace);

 /*
@@ -729,3 +735,346 @@ pg_get_tablespace_ddl_name(PG_FUNCTION_ARGS)
 
 	return pg_get_tablespace_ddl_srf(fcinfo, tsid, isnull);
 }
+
+/*
+ * pg_get_database_ddl_internal
+ *		Generate DDL statements to recreate a database.
+ *
+ * Returns a List of palloc'd strings.  The first element is the
+ * CREATE DATABASE statement; subsequent elements are ALTER DATABASE
+ * statements for properties and configuration settings.
+ */
+static List *
+pg_get_database_ddl_internal(Oid dbid, bool pretty,
+							 bool no_owner, bool no_tablespace)
+{
+	HeapTuple	tuple;
+	Form_pg_database dbform;
+	StringInfoData buf;
+	bool		isnull;
+	Datum		datum;
+	const char *encoding;
+	const char *dbname;
+	char	   *collate;
+	char	   *ctype;
+	Relation	rel;
+	ScanKeyData scankey;
+	SysScanDesc scan;
+	List	   *statements = NIL;
+
+	tuple = SearchSysCache1(DATABASEOID, ObjectIdGetDatum(dbid));
+	if (!HeapTupleIsValid(tuple))
+		ereport(ERROR,
+				(errcode(ERRCODE_UNDEFINED_OBJECT),
+				 errmsg("database with OID %u does not exist", dbid)));
+
+	dbform = (Form_pg_database) GETSTRUCT(tuple);
+	dbname = quote_identifier(NameStr(dbform->datname));
+
+	/*
+	 * We don't support generating DDL for system databases.  The primary
+	 * reason for this is that users shouldn't be recreating them.
+	 */
+	if (strcmp(dbname, "template0") == 0 || strcmp(dbname, "template1") == 0)
+		ereport(ERROR,
+				(errcode(ERRCODE_RESERVED_NAME),
+				 errmsg("database \"%s\" is a system database", dbname)));
+
+	initStringInfo(&buf);
+
+	/* --- Build CREATE DATABASE statement --- */
+	appendStringInfo(&buf, "CREATE DATABASE %s", dbname);
+
+	append_ddl_option(&buf, pretty, 4, "WITH TEMPLATE = template0");
+
+	/* ENCODING */
+	encoding = pg_encoding_to_char(dbform->encoding);
+	if (strlen(encoding) > 0)
+		append_ddl_option(&buf, pretty, 4, "ENCODING = %s",
+						  quote_literal_cstr(encoding));
+
+	/* LOCALE_PROVIDER */
+	if (dbform->datlocprovider == COLLPROVIDER_BUILTIN ||
+		dbform->datlocprovider == COLLPROVIDER_ICU ||
+		dbform->datlocprovider == COLLPROVIDER_LIBC)
+		append_ddl_option(&buf, pretty, 4, "LOCALE_PROVIDER = %s",
+						  collprovider_name(dbform->datlocprovider));
+	else
+		ereport(ERROR,
+				(errcode(ERRCODE_INVALID_OBJECT_DEFINITION),
+				 errmsg("unrecognized locale provider: %c",
+						dbform->datlocprovider)));
+
+	/* LOCALE, LC_COLLATE, LC_CTYPE */
+	datum = SysCacheGetAttr(DATABASEOID, tuple,
+							Anum_pg_database_datcollate, &isnull);
+	if (!isnull)
+		collate = TextDatumGetCString(datum);
+	else
+		collate = "";
+	datum = SysCacheGetAttr(DATABASEOID, tuple,
+							Anum_pg_database_datctype, &isnull);
+	if (!isnull)
+		ctype = TextDatumGetCString(datum);
+	else
+		ctype = "";
+	if (strlen(collate) > 0 && strcmp(collate, ctype) == 0)
+	{
+		append_ddl_option(&buf, pretty, 4, "LOCALE = %s",
+						  quote_literal_cstr(collate));
+	}
+	else
+	{
+		if (strlen(collate) > 0)
+			append_ddl_option(&buf, pretty, 4, "LC_COLLATE = %s",
+							  quote_literal_cstr(collate));
+		if (strlen(ctype) > 0)
+			append_ddl_option(&buf, pretty, 4, "LC_CTYPE = %s",
+							  quote_literal_cstr(ctype));
+	}
+
+	/* LOCALE (provider-specific) */
+	datum = SysCacheGetAttr(DATABASEOID, tuple,
+							Anum_pg_database_datlocale, &isnull);
+	if (!isnull)
+	{
+		const char *locale = TextDatumGetCString(datum);
+
+		if (dbform->datlocprovider == COLLPROVIDER_BUILTIN)
+			append_ddl_option(&buf, pretty, 4, "BUILTIN_LOCALE = %s",
+							  quote_literal_cstr(locale));
+		else if (dbform->datlocprovider == COLLPROVIDER_ICU)
+			append_ddl_option(&buf, pretty, 4, "ICU_LOCALE = %s",
+							  quote_literal_cstr(locale));
+	}
+
+	/* ICU_RULES */
+	datum = SysCacheGetAttr(DATABASEOID, tuple,
+							Anum_pg_database_daticurules, &isnull);
+	if (!isnull && dbform->datlocprovider == COLLPROVIDER_ICU)
+		append_ddl_option(&buf, pretty, 4, "ICU_RULES = %s",
+						  quote_literal_cstr(TextDatumGetCString(datum)));
+
+	/* TABLESPACE */
+	if (!no_tablespace && OidIsValid(dbform->dattablespace))
+	{
+		char	   *spcname = get_tablespace_name(dbform->dattablespace);
+
+		if (pg_strcasecmp(spcname, "pg_default") != 0)
+			append_ddl_option(&buf, pretty, 4, "TABLESPACE = %s",
+							  quote_identifier(spcname));
+	}
+
+	appendStringInfoChar(&buf, ';');
+	statements = lappend(statements, pstrdup(buf.data));
+
+	/* OWNER */
+	if (!no_owner && OidIsValid(dbform->datdba))
+	{
+		char	   *owner = GetUserNameFromId(dbform->datdba, false);
+
+		resetStringInfo(&buf);
+		appendStringInfo(&buf, "ALTER DATABASE %s OWNER = %s;",
+						 dbname, quote_identifier(owner));
+		pfree(owner);
+		statements = lappend(statements, pstrdup(buf.data));
+	}
+
+	/* CONNECTION LIMIT */
+	if (dbform->datconnlimit != -1)
+	{
+		resetStringInfo(&buf);
+		appendStringInfo(&buf, "ALTER DATABASE %s CONNECTION LIMIT = %d;",
+						 dbname, dbform->datconnlimit);
+		statements = lappend(statements, pstrdup(buf.data));
+	}
+
+	/* IS_TEMPLATE */
+	if (dbform->datistemplate)
+	{
+		resetStringInfo(&buf);
+		appendStringInfo(&buf, "ALTER DATABASE %s IS_TEMPLATE = true;",
+						 dbname);
+		statements = lappend(statements, pstrdup(buf.data));
+	}
+
+	/* ALLOW_CONNECTIONS */
+	if (!dbform->datallowconn)
+	{
+		resetStringInfo(&buf);
+		appendStringInfo(&buf, "ALTER DATABASE %s ALLOW_CONNECTIONS = false;",
+						 dbname);
+		statements = lappend(statements, pstrdup(buf.data));
+	}
+
+	ReleaseSysCache(tuple);
+
+	/*
+	 * Now scan pg_db_role_setting for ALTER DATABASE SET configurations.
+	 *
+	 * It is only database-wide (setrole = 0). It generates one ALTER
+	 * statement per setting.
+	 */
+	rel = table_open(DbRoleSettingRelationId, AccessShareLock);
+	ScanKeyInit(&scankey,
+				Anum_pg_db_role_setting_setdatabase,
+				BTEqualStrategyNumber, F_OIDEQ,
+				ObjectIdGetDatum(dbid));
+
+	scan = systable_beginscan(rel, DbRoleSettingDatidRolidIndexId, true,
+							  NULL, 1, &scankey);
+
+	while (HeapTupleIsValid(tuple = systable_getnext(scan)))
+	{
+		ArrayType  *reloptions;
+		Datum	   *settings;
+		bool	   *nulls;
+		int			nsettings;
+
+		/*
+		 * The setconfig column is a text array in "name=value" format. It
+		 * should never be null for a valid row, but be defensive.
+		 */
+		datum = heap_getattr(tuple, Anum_pg_db_role_setting_setconfig,
+							 RelationGetDescr(rel), &isnull);
+		if (isnull)
+			continue;
+
+		reloptions = DatumGetArrayTypeP(datum);
+
+		deconstruct_array_builtin(reloptions, TEXTOID, &settings, &nulls, &nsettings);
+
+		for (int i = 0; i < nsettings; i++)
+		{
+			char	   *s,
+					   *p;
+
+			if (nulls[i])
+				continue;
+
+			s = TextDatumGetCString(settings[i]);
+			p = strchr(s, '=');
+			if (p == NULL)
+			{
+				pfree(s);
+				continue;
+			}
+			*p++ = '\0';
+
+			resetStringInfo(&buf);
+			appendStringInfo(&buf, "ALTER DATABASE %s SET %s TO ",
+							 dbname,
+							 quote_identifier(s));
+
+			/*
+			 * Variables that are marked GUC_LIST_QUOTE were already fully
+			 * quoted before they were put into the setconfig array.  Break
+			 * the list value apart and then quote the elements as string
+			 * literals.
+			 */
+			if (GetConfigOptionFlags(s, true) & GUC_LIST_QUOTE)
+			{
+				List	   *namelist;
+				ListCell   *lc;
+
+				/* Parse string into list of identifiers */
+				if (!SplitGUCList(p, ',', &namelist))
+				{
+					/* this shouldn't fail really */
+					elog(ERROR, "invalid list syntax in setconfig item");
+				}
+				/* Special case: represent an empty list as NULL */
+				if (namelist == NIL)
+					appendStringInfoString(&buf, "NULL");
+				foreach(lc, namelist)
+				{
+					char	   *curname = (char *) lfirst(lc);
+
+					appendStringInfoString(&buf, quote_literal_cstr(curname));
+					if (lnext(namelist, lc))
+						appendStringInfoString(&buf, ", ");
+				}
+			}
+			else
+				appendStringInfoString(&buf, quote_literal_cstr(p));
+
+			appendStringInfoChar(&buf, ';');
+
+			statements = lappend(statements, pstrdup(buf.data));
+
+			pfree(s);
+		}
+
+		pfree(settings);
+		pfree(reloptions);
+	}
+
+	systable_endscan(scan);
+	table_close(rel, AccessShareLock);
+
+	pfree(buf.data);
+
+	return statements;
+}
+
+/*
+ * pg_get_database_ddl
+ *		Return DDL to recreate a database as a set of text rows.
+ */
+Datum
+pg_get_database_ddl(PG_FUNCTION_ARGS)
+{
+	FuncCallContext *funcctx;
+	List	   *statements;
+	ListCell   *lc;
+
+	if (SRF_IS_FIRSTCALL())
+	{
+		MemoryContext oldcontext;
+		Oid			dbid;
+		DdlOption	opts[] = {
+			{"pretty", DDL_OPT_BOOL},
+			{"owner", DDL_OPT_BOOL},
+			{"tablespace", DDL_OPT_BOOL},
+		};
+
+		funcctx = SRF_FIRSTCALL_INIT();
+		oldcontext = MemoryContextSwitchTo(funcctx->multi_call_memory_ctx);
+
+		if (PG_ARGISNULL(0))
+		{
+			MemoryContextSwitchTo(oldcontext);
+			SRF_RETURN_DONE(funcctx);
+		}
+
+		dbid = PG_GETARG_OID(0);
+		parse_ddl_options(fcinfo, 1, opts, lengthof(opts));
+
+		statements = pg_get_database_ddl_internal(dbid,
+												  opts[0].isset && opts[0].boolval,
+												  opts[1].isset && !opts[1].boolval,
+												  opts[2].isset && !opts[2].boolval);
+		funcctx->user_fctx = statements;
+		funcctx->max_calls = list_length(statements);
+
+		MemoryContextSwitchTo(oldcontext);
+	}
+
+	funcctx = SRF_PERCALL_SETUP();
+	statements = (List *) funcctx->user_fctx;
+
+	if (funcctx->call_cntr < funcctx->max_calls)
+	{
+		char	   *stmt;
+
+		lc = list_nth_cell(statements, funcctx->call_cntr);
+		stmt = (char *) lfirst(lc);
+
+		SRF_RETURN_NEXT(funcctx, CStringGetTextDatum(stmt));
+	}
+	else
+	{
+		list_free_deep(statements);
+		SRF_RETURN_DONE(funcctx);
+	}
+}
diff --git a/src/include/catalog/pg_proc.dat b/src/include/catalog/pg_proc.dat
index d25b6966d5b..b5ad34ccf2c 100644
--- a/src/include/catalog/pg_proc.dat
+++ b/src/include/catalog/pg_proc.dat
@@ -8612,6 +8612,14 @@
   proallargtypes => '{name,text}',
   pronargdefaults => '1', proargdefaults => '{NULL}',
   prosrc => 'pg_get_tablespace_ddl_name' },
+{ oid => '8762', descr => 'get DDL to recreate a database',
+  proname => 'pg_get_database_ddl', provariadic => 'text', proisstrict => 'f',
+  provolatile => 's', proretset => 't', prorows => '10', prorettype => 'text',
+  proargtypes => 'regdatabase text',
+  proargmodes => '{i,v}',
+  proallargtypes => '{regdatabase,text}',
+  pronargdefaults => '1', proargdefaults => '{NULL}',
+  prosrc => 'pg_get_database_ddl' },
 { oid => '2509',
   descr => 'deparse an encoded expression with pretty-print option',
   proname => 'pg_get_expr', provolatile => 's', prorettype => 'text',
diff --git a/src/test/regress/expected/database_ddl.out b/src/test/regress/expected/database_ddl.out
new file mode 100644
index 00000000000..60f933f57f1
--- /dev/null
+++ b/src/test/regress/expected/database_ddl.out
@@ -0,0 +1,107 @@
+--
+-- Tests for pg_get_database_ddl()
+--
+-- To produce stable regression test output, it's usually necessary to
+-- ignore collation and locale related details. This filter
+-- function removes collation and locale related details.
+CREATE OR REPLACE FUNCTION ddl_filter(ddl_input TEXT)
+RETURNS TEXT AS $$
+DECLARE
+    cleaned_ddl TEXT;
+BEGIN
+    -- Remove LOCALE_PROVIDER placeholders
+    cleaned_ddl := regexp_replace(
+        ddl_input,
+        '\s*\mLOCALE_PROVIDER\M\s*=\s*([''"]?[^''"\s]+[''"]?)',
+        '',
+        'gi'
+    );
+
+	-- Remove LC_COLLATE assignments
+    cleaned_ddl := regexp_replace(
+        cleaned_ddl,
+        '\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 ROLE regress_datdba;
+CREATE DATABASE regress_database_ddl
+	ENCODING utf8 LC_COLLATE "C" LC_CTYPE "C" TEMPLATE template0
+	OWNER regress_datdba;
+ALTER DATABASE regress_database_ddl CONNECTION_LIMIT 123;
+-- Database doesn't exist
+SELECT * FROM pg_get_database_ddl('regression_database');
+ERROR:  database "regression_database" does not exist
+LINE 1: SELECT * FROM pg_get_database_ddl('regression_database');
+                                          ^
+-- NULL value
+SELECT * FROM pg_get_database_ddl(NULL);
+ pg_get_database_ddl 
+---------------------
+(0 rows)
+
+-- Invalid option value (should error)
+SELECT * FROM pg_get_database_ddl('regress_database_ddl', 'owner', 'invalid');
+ERROR:  invalid value for boolean option "owner": invalid
+-- Duplicate option (should error)
+SELECT * FROM pg_get_database_ddl('regress_database_ddl', 'owner', 'false', 'owner', 'true');
+ERROR:  option "owner" is specified more than once
+-- Without options
+SELECT ddl_filter(pg_get_database_ddl) FROM pg_get_database_ddl('regress_database_ddl');
+                                    ddl_filter                                     
+-----------------------------------------------------------------------------------
+ CREATE DATABASE regress_database_ddl WITH TEMPLATE = template0 ENCODING = 'UTF8';
+ ALTER DATABASE regress_database_ddl OWNER = regress_datdba;
+ ALTER DATABASE regress_database_ddl CONNECTION LIMIT = 123;
+(3 rows)
+
+-- Without owner
+SELECT ddl_filter(pg_get_database_ddl) FROM pg_get_database_ddl('regress_database_ddl', 'owner', 'false');
+                                    ddl_filter                                     
+-----------------------------------------------------------------------------------
+ CREATE DATABASE regress_database_ddl WITH TEMPLATE = template0 ENCODING = 'UTF8';
+ ALTER DATABASE regress_database_ddl CONNECTION LIMIT = 123;
+(2 rows)
+
+-- Pretty-printed output
+\pset format unaligned
+SELECT ddl_filter(pg_get_database_ddl) FROM pg_get_database_ddl('regress_database_ddl', 'pretty', 'true', 'tablespace', 'false');
+ddl_filter
+CREATE DATABASE regress_database_ddl
+    WITH TEMPLATE = template0
+    ENCODING = 'UTF8';
+ALTER DATABASE regress_database_ddl OWNER = regress_datdba;
+ALTER DATABASE regress_database_ddl CONNECTION LIMIT = 123;
+(3 rows)
+\pset format aligned
+DROP DATABASE regress_database_ddl;
+DROP FUNCTION ddl_filter(text);
+DROP ROLE regress_datdba;
diff --git a/src/test/regress/parallel_schedule b/src/test/regress/parallel_schedule
index f3a01aecf04..d97b9f16908 100644
--- a/src/test/regress/parallel_schedule
+++ b/src/test/regress/parallel_schedule
@@ -130,7 +130,7 @@ test: partition_merge partition_split partition_join partition_prune reloptions
 # oidjoins is read-only, though, and should run late for best coverage
 test: oidjoins event_trigger
 
-test: role_ddl tablespace_ddl
+test: role_ddl tablespace_ddl database_ddl
 
 # event_trigger_login cannot run concurrently with any other tests because
 # on-login event handling could catch connection of a concurrent test.
diff --git a/src/test/regress/sql/database_ddl.sql b/src/test/regress/sql/database_ddl.sql
new file mode 100644
index 00000000000..4460dfedcd7
--- /dev/null
+++ b/src/test/regress/sql/database_ddl.sql
@@ -0,0 +1,89 @@
+--
+-- Tests for pg_get_database_ddl()
+--
+
+-- To produce stable regression test output, it's usually necessary to
+-- ignore collation and locale related details. This filter
+-- function removes collation and locale related details.
+
+CREATE OR REPLACE FUNCTION ddl_filter(ddl_input TEXT)
+RETURNS TEXT AS $$
+DECLARE
+    cleaned_ddl TEXT;
+BEGIN
+    -- Remove LOCALE_PROVIDER placeholders
+    cleaned_ddl := regexp_replace(
+        ddl_input,
+        '\s*\mLOCALE_PROVIDER\M\s*=\s*([''"]?[^''"\s]+[''"]?)',
+        '',
+        'gi'
+    );
+
+	-- Remove LC_COLLATE assignments
+    cleaned_ddl := regexp_replace(
+        cleaned_ddl,
+        '\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 ROLE regress_datdba;
+CREATE DATABASE regress_database_ddl
+	ENCODING utf8 LC_COLLATE "C" LC_CTYPE "C" TEMPLATE template0
+	OWNER regress_datdba;
+ALTER DATABASE regress_database_ddl CONNECTION_LIMIT 123;
+
+-- Database doesn't exist
+SELECT * FROM pg_get_database_ddl('regression_database');
+
+-- NULL value
+SELECT * FROM pg_get_database_ddl(NULL);
+
+-- Invalid option value (should error)
+SELECT * FROM pg_get_database_ddl('regress_database_ddl', 'owner', 'invalid');
+
+-- Duplicate option (should error)
+SELECT * FROM pg_get_database_ddl('regress_database_ddl', 'owner', 'false', 'owner', 'true');
+
+-- Without options
+SELECT ddl_filter(pg_get_database_ddl) FROM pg_get_database_ddl('regress_database_ddl');
+
+-- Without owner
+SELECT ddl_filter(pg_get_database_ddl) FROM pg_get_database_ddl('regress_database_ddl', 'owner', 'false');
+
+-- Pretty-printed output
+\pset format unaligned
+SELECT ddl_filter(pg_get_database_ddl) FROM pg_get_database_ddl('regress_database_ddl', 'pretty', 'true', 'tablespace', 'false');
+\pset format aligned
+
+DROP DATABASE regress_database_ddl;
+DROP FUNCTION ddl_filter(text);
+DROP ROLE regress_datdba;
-- 
2.43.0

From c82a8c634fa828d2ae1aebf9b8394b3a42e1fe65 Mon Sep 17 00:00:00 2001
From: Andrew Dunstan <[email-masked]>
Date: Thu, 19 Mar 2026 09:55:16 -0400
Subject: [PATCH 3/4] Add pg_get_tablespace_ddl() function
MIME-Version: 1.0
Content-Type: text/plain; charset=UTF-8
Content-Transfer-Encoding: 8bit

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 option is supported: pretty
(boolean) for formatted output. (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.

get_reloptions() in ruleutils.c is made non-static so it can be called
from the new ddlutils.c file.

Author: Nishant Sharma <[email-masked]>
Author: Manni Wood <[email-masked]>
Co-authored-by: Andrew Dunstan <[email-masked]>
Reviewed-by: Jim Jones <[email-masked]>
Reviewed-by: Álvaro Herrera <[email-masked]>
Reviewed-by: Chao Li <[email-masked]>
Reviewed-by: Euler Taveira <[email-masked]>
Discussion: https://www.postgresql.org/message-id/flat/CAKWEB6rmnmGKUA87Zmq-s=[email-masked]
---
 doc/src/sgml/func/func-info.sgml             |  27 +++
 src/backend/utils/adt/ddlutils.c             | 190 ++++++++++++++++++-
 src/backend/utils/adt/ruleutils.c            |   4 +-
 src/include/catalog/pg_proc.dat              |  16 ++
 src/include/utils/ruleutils.h                |   1 +
 src/test/regress/expected/tablespace_ddl.out |  65 +++++++
 src/test/regress/parallel_schedule           |   2 +-
 src/test/regress/sql/tablespace_ddl.sql      |  43 +++++
 8 files changed, 344 insertions(+), 4 deletions(-)
 create mode 100644 src/test/regress/expected/tablespace_ddl.out
 create mode 100644 src/test/regress/sql/tablespace_ddl.sql

diff --git a/doc/src/sgml/func/func-info.sgml b/doc/src/sgml/func/func-info.sgml
index acd1a7cfeed..f44bd0d0f8b 100644
--- a/doc/src/sgml/func/func-info.sgml
+++ b/doc/src/sgml/func/func-info.sgml
@@ -3908,6 +3908,33 @@ acl      | {postgres=arwdDxtm/postgres,foo=r/postgres}
         for pretty-printed output.
        </para></entry>
       </row>
+      <row>
+       <entry role="func_table_entry"><para role="func_signature">
+        <indexterm>
+         <primary>pg_get_tablespace_ddl</primary>
+        </indexterm>
+        <function>pg_get_tablespace_ddl</function>
+        ( <parameter>tablespace</parameter> <type>oid</type>
+        <optional>, <literal>VARIADIC</literal> <parameter>options</parameter>
+        <type>text</type> </optional> )
+        <returnvalue>setof text</returnvalue>
+       </para>
+       <para>
+        <function>pg_get_tablespace_ddl</function>
+        ( <parameter>tablespace</parameter> <type>name</type>
+        <optional>, <literal>VARIADIC</literal> <parameter>options</parameter>
+        <type>text</type> </optional> )
+        <returnvalue>setof text</returnvalue>
+       </para>
+       <para>
+        Reconstructs the <command>CREATE TABLESPACE</command> statement for
+        the specified tablespace (by OID or name).  If the tablespace has
+        options set, an <command>ALTER TABLESPACE ... SET</command> statement
+        is also returned.  Each statement is returned as a separate row.
+        The following option is supported: <literal>pretty</literal> (boolean)
+        for formatted output.
+       </para></entry>
+      </row>
      </tbody>
     </tgroup>
    </table>
diff --git a/src/backend/utils/adt/ddlutils.c b/src/backend/utils/adt/ddlutils.c
index c067de0a3d2..65df3c723cc 100644
--- a/src/backend/utils/adt/ddlutils.c
+++ b/src/backend/utils/adt/ddlutils.c
@@ -20,10 +20,12 @@
 
 #include "access/genam.h"
 #include "access/htup_details.h"
-#include "access/relation.h"
 #include "access/table.h"
 #include "catalog/pg_authid.h"
 #include "catalog/pg_db_role_setting.h"
+#include "catalog/pg_tablespace.h"
+#include "commands/tablespace.h"
+#include "common/relpath.h"
 #include "funcapi.h"
 #include "miscadmin.h"
 #include "utils/acl.h"
@@ -34,6 +36,7 @@
 #include "utils/guc.h"
 #include "utils/lsyscache.h"
 #include "utils/rel.h"
+#include "utils/ruleutils.h"
 #include "utils/syscache.h"
 #include "utils/timestamp.h"
 #include "utils/varlena.h"
@@ -67,6 +70,8 @@ static void append_ddl_option(StringInfo buf, bool pretty, int indent,
 							  const char *fmt,...)
 			pg_attribute_printf(4, 5);
 static List *pg_get_role_ddl_internal(Oid roleid, bool pretty);
+static List *pg_get_tablespace_ddl_internal(Oid tsid, bool pretty);
+static Datum pg_get_tablespace_ddl_srf(FunctionCallInfo fcinfo, Oid tsid, bool isnull);

 /*
@@ -541,3 +546,186 @@ pg_get_role_ddl(PG_FUNCTION_ARGS)
 		SRF_RETURN_DONE(funcctx);
 	}
 }
+
+/*
+ * pg_get_tablespace_ddl_internal
+ *		Generate DDL statements to recreate a tablespace.
+ *
+ * Returns a List of palloc'd strings.  The first element is the
+ * CREATE TABLESPACE statement; if the tablespace has reloptions,
+ * a second element with ALTER TABLESPACE SET (...) is appended.
+ */
+static List *
+pg_get_tablespace_ddl_internal(Oid tsid, bool pretty)
+{
+	HeapTuple	tuple;
+	Form_pg_tablespace tspForm;
+	StringInfoData buf;
+	char	   *spcname;
+	char	   *spcowner;
+	char	   *path;
+	bool		isNull;
+	Datum		datum;
+	List	   *statements = NIL;
+
+	tuple = SearchSysCache1(TABLESPACEOID, ObjectIdGetDatum(tsid));
+	if (!HeapTupleIsValid(tuple))
+		ereport(ERROR,
+				(errcode(ERRCODE_UNDEFINED_OBJECT),
+				 errmsg("tablespace with OID %u does not exist",
+						tsid)));
+
+	tspForm = (Form_pg_tablespace) GETSTRUCT(tuple);
+	spcname = pstrdup(NameStr(tspForm->spcname));
+
+	/*
+	 * We don't support generating DDL for system tablespaces.  The primary
+	 * reason for this is that users shouldn't be recreating them.
+	 */
+	if (IsReservedName(spcname))
+		ereport(ERROR,
+				(errcode(ERRCODE_RESERVED_NAME),
+				 errmsg("tablespace name \"%s\" is reserved", spcname),
+				 errdetail("Tablespace names starting with \"pg_\" are reserved for system tablespaces.")));
+
+	initStringInfo(&buf);
+
+	/* Start building the CREATE TABLESPACE statement */
+	appendStringInfo(&buf, "CREATE TABLESPACE %s", quote_identifier(spcname));
+
+	/* Add OWNER clause */
+	spcowner = GetUserNameFromId(tspForm->spcowner, false);
+	append_ddl_option(&buf, pretty, 4, "OWNER %s",
+					  quote_identifier(spcowner));
+	pfree(spcowner);
+
+	/* Find tablespace directory path */
+	path = get_tablespace_location(tsid);
+
+	/* Add directory LOCATION (path), if it exists */
+	if (path[0] != '\0')
+	{
+		/*
+		 * Special case: if the tablespace was created with GUC
+		 * "allow_in_place_tablespaces = true" and "LOCATION ''", path will
+		 * begin with "pg_tblspc/". In that case, show "LOCATION ''" as the
+		 * user originally specified.
+		 */
+		if (strncmp(PG_TBLSPC_DIR_SLASH, path, strlen(PG_TBLSPC_DIR_SLASH)) == 0)
+			append_ddl_option(&buf, pretty, 4, "LOCATION ''");
+		else
+			append_ddl_option(&buf, pretty, 4, "LOCATION %s",
+							  quote_literal_cstr(path));
+	}
+	pfree(path);
+
+	appendStringInfoChar(&buf, ';');
+	statements = lappend(statements, pstrdup(buf.data));
+
+	/* Check for tablespace options */
+	datum = SysCacheGetAttr(TABLESPACEOID, tuple,
+							Anum_pg_tablespace_spcoptions, &isNull);
+	if (!isNull)
+	{
+		resetStringInfo(&buf);
+		appendStringInfo(&buf, "ALTER TABLESPACE %s SET (",
+						 quote_identifier(spcname));
+		get_reloptions(&buf, datum);
+		appendStringInfoString(&buf, ");");
+		statements = lappend(statements, pstrdup(buf.data));
+	}
+
+	ReleaseSysCache(tuple);
+	pfree(buf.data);
+
+	return statements;
+}
+
+/*
+ * pg_get_tablespace_ddl_srf - common SRF logic for tablespace DDL
+ */
+static Datum
+pg_get_tablespace_ddl_srf(FunctionCallInfo fcinfo, Oid tsid, bool isnull)
+{
+	FuncCallContext *funcctx;
+	List	   *statements;
+
+	if (SRF_IS_FIRSTCALL())
+	{
+		MemoryContext oldcontext;
+		DdlOption	opts[] = {{"pretty", DDL_OPT_BOOL}};
+
+		funcctx = SRF_FIRSTCALL_INIT();
+		oldcontext = MemoryContextSwitchTo(funcctx->multi_call_memory_ctx);
+
+		if (isnull)
+		{
+			MemoryContextSwitchTo(oldcontext);
+			SRF_RETURN_DONE(funcctx);
+		}
+
+		parse_ddl_options(fcinfo, 1, opts, lengthof(opts));
+
+		statements = pg_get_tablespace_ddl_internal(tsid,
+													opts[0].isset && opts[0].boolval);
+		funcctx->user_fctx = statements;
+		funcctx->max_calls = list_length(statements);
+
+		MemoryContextSwitchTo(oldcontext);
+	}
+
+	funcctx = SRF_PERCALL_SETUP();
+	statements = (List *) funcctx->user_fctx;
+
+	if (funcctx->call_cntr < funcctx->max_calls)
+	{
+		char	   *stmt;
+
+		stmt = (char *) list_nth(statements, funcctx->call_cntr);
+
+		SRF_RETURN_NEXT(funcctx, CStringGetTextDatum(stmt));
+	}
+	else
+	{
+		list_free_deep(statements);
+		SRF_RETURN_DONE(funcctx);
+	}
+}
+
+/*
+ * pg_get_tablespace_ddl_oid
+ *		Return DDL to recreate a tablespace, taking OID.
+ */
+Datum
+pg_get_tablespace_ddl_oid(PG_FUNCTION_ARGS)
+{
+	Oid			tsid = InvalidOid;
+	bool		isnull;
+
+	isnull = PG_ARGISNULL(0);
+	tsid = PG_GETARG_OID(0);
+
+	return pg_get_tablespace_ddl_srf(fcinfo, tsid, isnull);
+}
+
+/*
+ * pg_get_tablespace_ddl_name
+ *		Return DDL to recreate a tablespace, taking name.
+ */
+Datum
+pg_get_tablespace_ddl_name(PG_FUNCTION_ARGS)
+{
+	Oid			tsid = InvalidOid;
+	Name		tspname;
+	bool		isnull;
+
+	isnull = PG_ARGISNULL(0);
+
+	if (!isnull)
+	{
+		tspname = PG_GETARG_NAME(0);
+		tsid = get_tablespace_oid(NameStr(*tspname), false);
+	}
+
+	return pg_get_tablespace_ddl_srf(fcinfo, tsid, isnull);
+}
diff --git a/src/backend/utils/adt/ruleutils.c b/src/backend/utils/adt/ruleutils.c
index 7bc12589e40..1450c101e9e 100644
--- a/src/backend/utils/adt/ruleutils.c
+++ b/src/backend/utils/adt/ruleutils.c
@@ -545,7 +545,7 @@ static void add_cast_to(StringInfo buf, Oid typid);
 static char *generate_qualified_type_name(Oid typid);
 static text *string_to_text(char *str);
 static char *flatten_reloptions(Oid relid);
-static void get_reloptions(StringInfo buf, Datum reloptions);
+void		get_reloptions(StringInfo buf, Datum reloptions);
 static void get_json_path_spec(Node *path_spec, deparse_context *context,
 							   bool showimplicit);
 static void get_json_table_columns(TableFunc *tf, JsonTablePathScan *scan,
@@ -14199,7 +14199,7 @@ string_to_text(char *str)
 /*
  * Generate a C string representing a relation options from text[] datum.
  */
-static void
+void
 get_reloptions(StringInfo buf, Datum reloptions)
 {
 	Datum	   *options;
diff --git a/src/include/catalog/pg_proc.dat b/src/include/catalog/pg_proc.dat
index e908146645f..d25b6966d5b 100644
--- a/src/include/catalog/pg_proc.dat
+++ b/src/include/catalog/pg_proc.dat
@@ -8596,6 +8596,22 @@
   proallargtypes => '{regrole,text}',
   pronargdefaults => '1', proargdefaults => '{NULL}',
   prosrc => 'pg_get_role_ddl' },
+{ oid => '8758', descr => 'get DDL to recreate a tablespace',
+  proname => 'pg_get_tablespace_ddl', provariadic => 'text', proisstrict => 'f',
+  provolatile => 's', proretset => 't', prorows => '10', prorettype => 'text',
+  proargtypes => 'oid text',
+  proargmodes => '{i,v}',
+  proallargtypes => '{oid,text}',
+  pronargdefaults => '1', proargdefaults => '{NULL}',
+  prosrc => 'pg_get_tablespace_ddl_oid' },
+{ oid => '8759', descr => 'get DDL to recreate a tablespace',
+  proname => 'pg_get_tablespace_ddl', provariadic => 'text', proisstrict => 'f',
+  provolatile => 's', proretset => 't', prorows => '10', prorettype => 'text',
+  proargtypes => 'name text',
+  proargmodes => '{i,v}',
+  proallargtypes => '{name,text}',
+  pronargdefaults => '1', proargdefaults => '{NULL}',
+  prosrc => 'pg_get_tablespace_ddl_name' },
 { oid => '2509',
   descr => 'deparse an encoded expression with pretty-print option',
   proname => 'pg_get_expr', provolatile => 's', prorettype => 'text',
diff --git a/src/include/utils/ruleutils.h b/src/include/utils/ruleutils.h
index 908b2708ed4..ac40d4c714e 100644
--- a/src/include/utils/ruleutils.h
+++ b/src/include/utils/ruleutils.h
@@ -51,6 +51,7 @@ extern char *get_window_frame_options_for_explain(int frameOptions,
 extern char *generate_collation_name(Oid collid);
 extern char *generate_opclass_name(Oid opclass);
 extern char *get_range_partbound_string(List *bound_datums);
+extern void get_reloptions(StringInfo buf, Datum reloptions);
 
 extern char *pg_get_statisticsobjdef_string(Oid statextid);
 
diff --git a/src/test/regress/expected/tablespace_ddl.out b/src/test/regress/expected/tablespace_ddl.out
new file mode 100644
index 00000000000..993841a7de1
--- /dev/null
+++ b/src/test/regress/expected/tablespace_ddl.out
@@ -0,0 +1,65 @@
+--
+-- Tests for pg_get_tablespace_ddl()
+--
+SET allow_in_place_tablespaces = true;
+CREATE ROLE regress_tblspc_ddl_user;
+-- error: non-existent tablespace by name
+SELECT * FROM pg_get_tablespace_ddl('regress_nonexistent_tblsp');
+ERROR:  tablespace "regress_nonexistent_tblsp" does not exist
+-- error: non-existent tablespace by OID
+SELECT * FROM pg_get_tablespace_ddl(0::oid);
+ERROR:  tablespace with OID 0 does not exist
+-- NULL input returns no rows (name variant)
+SELECT * FROM pg_get_tablespace_ddl(NULL::name);
+ pg_get_tablespace_ddl 
+-----------------------
+(0 rows)
+
+-- NULL input returns no rows (OID variant)
+SELECT * FROM pg_get_tablespace_ddl(NULL::oid);
+ pg_get_tablespace_ddl 
+-----------------------
+(0 rows)
+
+-- tablespace name requiring quoting
+CREATE TABLESPACE "regress_ tblsp" OWNER regress_tblspc_ddl_user LOCATION '';
+SELECT * FROM pg_get_tablespace_ddl('regress_ tblsp');
+                             pg_get_tablespace_ddl                             
+-------------------------------------------------------------------------------
+ CREATE TABLESPACE "regress_ tblsp" OWNER regress_tblspc_ddl_user LOCATION '';
+(1 row)
+
+DROP TABLESPACE "regress_ tblsp";
+-- tablespace with multiple options
+CREATE TABLESPACE regress_allopt_tblsp OWNER regress_tblspc_ddl_user LOCATION ''
+  WITH (seq_page_cost = '1.5', random_page_cost = '1.1234567890',
+        effective_io_concurrency = '17', maintenance_io_concurrency = '18');
+SELECT * FROM pg_get_tablespace_ddl('regress_allopt_tblsp');
+                                                                       pg_get_tablespace_ddl                                                                       
+-------------------------------------------------------------------------------------------------------------------------------------------------------------------
+ CREATE TABLESPACE regress_allopt_tblsp OWNER regress_tblspc_ddl_user LOCATION '';
+ ALTER TABLESPACE regress_allopt_tblsp SET (seq_page_cost='1.5', random_page_cost='1.1234567890', effective_io_concurrency='17', maintenance_io_concurrency='18');
+(2 rows)
+
+-- pretty-printed output
+\pset format unaligned
+SELECT * FROM pg_get_tablespace_ddl('regress_allopt_tblsp', 'pretty', 'true');
+pg_get_tablespace_ddl
+CREATE TABLESPACE regress_allopt_tblsp
+    OWNER regress_tblspc_ddl_user
+    LOCATION '';
+ALTER TABLESPACE regress_allopt_tblsp SET (seq_page_cost='1.5', random_page_cost='1.1234567890', effective_io_concurrency='17', maintenance_io_concurrency='18');
+(2 rows)
+\pset format aligned
+DROP TABLESPACE regress_allopt_tblsp;
+-- test by OID
+CREATE TABLESPACE regress_oid_tblsp OWNER regress_tblspc_ddl_user LOCATION '';
+SELECT oid AS tsid FROM pg_tablespace WHERE spcname = 'regress_oid_tblsp' \gset
+SELECT * FROM pg_get_tablespace_ddl(:tsid);
+                             pg_get_tablespace_ddl                              
+--------------------------------------------------------------------------------
+ CREATE TABLESPACE regress_oid_tblsp OWNER regress_tblspc_ddl_user LOCATION '';
+(1 row)
+
+DROP TABLESPACE regress_oid_tblsp;
+DROP ROLE regress_tblspc_ddl_user;
diff --git a/src/test/regress/parallel_schedule b/src/test/regress/parallel_schedule
index 7e059cef034..f3a01aecf04 100644
--- a/src/test/regress/parallel_schedule
+++ b/src/test/regress/parallel_schedule
@@ -130,7 +130,7 @@ test: partition_merge partition_split partition_join partition_prune reloptions
 # oidjoins is read-only, though, and should run late for best coverage
 test: oidjoins event_trigger
 
-test: role_ddl
+test: role_ddl tablespace_ddl
 
 # event_trigger_login cannot run concurrently with any other tests because
 # on-login event handling could catch connection of a concurrent test.
diff --git a/src/test/regress/sql/tablespace_ddl.sql b/src/test/regress/sql/tablespace_ddl.sql
new file mode 100644
index 00000000000..90ee6c1d703
--- /dev/null
+++ b/src/test/regress/sql/tablespace_ddl.sql
@@ -0,0 +1,43 @@
+--
+-- Tests for pg_get_tablespace_ddl()
+--
+
+SET allow_in_place_tablespaces = true;
+CREATE ROLE regress_tblspc_ddl_user;
+
+-- error: non-existent tablespace by name
+SELECT * FROM pg_get_tablespace_ddl('regress_nonexistent_tblsp');
+
+-- error: non-existent tablespace by OID
+SELECT * FROM pg_get_tablespace_ddl(0::oid);
+
+-- NULL input returns no rows (name variant)
+SELECT * FROM pg_get_tablespace_ddl(NULL::name);
+
+-- NULL input returns no rows (OID variant)
+SELECT * FROM pg_get_tablespace_ddl(NULL::oid);
+
+-- tablespace name requiring quoting
+CREATE TABLESPACE "regress_ tblsp" OWNER regress_tblspc_ddl_user LOCATION '';
+SELECT * FROM pg_get_tablespace_ddl('regress_ tblsp');
+DROP TABLESPACE "regress_ tblsp";
+
+-- tablespace with multiple options
+CREATE TABLESPACE regress_allopt_tblsp OWNER regress_tblspc_ddl_user LOCATION ''
+  WITH (seq_page_cost = '1.5', random_page_cost = '1.1234567890',
+        effective_io_concurrency = '17', maintenance_io_concurrency = '18');
+SELECT * FROM pg_get_tablespace_ddl('regress_allopt_tblsp');
+
+-- pretty-printed output
+\pset format unaligned
+SELECT * FROM pg_get_tablespace_ddl('regress_allopt_tblsp', 'pretty', 'true');
+\pset format aligned
+DROP TABLESPACE regress_allopt_tblsp;
+
+-- test by OID
+CREATE TABLESPACE regress_oid_tblsp OWNER regress_tblspc_ddl_user LOCATION '';
+SELECT oid AS tsid FROM pg_tablespace WHERE spcname = 'regress_oid_tblsp' \gset
+SELECT * FROM pg_get_tablespace_ddl(:tsid);
+DROP TABLESPACE regress_oid_tblsp;
+
+DROP ROLE regress_tblspc_ddl_user;
-- 
2.43.0

---

From: Mahendra Singh Thalor <[email-masked]>
Date: Fri, 20 Mar 2026 01:25:31 +0530

> https://www.postgresql.org/message-id/flat/CAKWEB6rmnmGKUA87Zmq-s=[email-masked]

Hi all,
I was reading these patches and found that any user can get the
definition of database/roles by pg_get__*_ddl. I think these functions
should be restricted only to super users as these are cluster level
objects.

 TAB is not working for these functions. I think these functions
should be displayed with TAB. I will do some more study and will do
some more tests.

-- 
Thanks and Regards
Mahendra Singh Thalor
EnterpriseDB: http://www.enterprisedb.com

---

From: Andrew Dunstan <[email-masked]>
Date: Thu, 19 Mar 2026 16:14:41 -0400

On 2026-03-19 Th 3:55 PM, Mahendra Singh Thalor wrote:
> On Fri, 20 Mar 2026 at 00:04, Andrew Dunstan<[email-masked]> wrote:
>> Greetings
>>
>> EDB:https://www.enterprisedb.com
> Hi all,

You could construct these functions using plpgsql. The information isn't 
hidden from non-superusers. So what exactly would making these functions 
superuser-only achieve? Now it's true that the user might not be able to 
execute the DDL. But that's not the point.

cheers

andrew

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

---

From: Mahendra Singh Thalor <[email-masked]>
Date: Fri, 20 Mar 2026 01:58:06 +0530

On Fri, 20 Mar 2026 at 01:44, Andrew Dunstan <[email-masked]> wrote:
> You could construct these functions using plpgsql. The information isn't hidden from non-superusers. So what exactly would making these functions superuser-only achieve? Now it's true that the user might not be able to execute the DDL. But that's not the point.

Thanks Andrew for the clarification.

> andrew

Sorry, my question might be stupid. Can we use these functions
directly into our dump utilities so that common code can be removed
and if there is any syntax change for a particular object, then no
need to change into different-2 places, rather we just need to change
it into ddlutils.c file only.

---

From: Andrew Dunstan <[email-masked]>
Date: Thu, 19 Mar 2026 17:30:06 -0400

> it into ddlutils.c file only.

Well, we have tried to make the output like pg_dump. We might later 
provide options for more compact output. But it remains to be seen if it 
can be used in the pg_dump utilities. After all, it is going to produce 
output suitable for the source version, since there is no knowledge in 
the server of future versions. But pg_dump emits SQL suitable for the 
target version.

My original motivation was quite different. It was to give the user a 
piece of SQL that they could modify as they saw fit, rather than making 
them start with a blank canvas.

cheers

andrew

---

From: Zsolt Parragi <[email-masked]>
Date: Thu, 19 Mar 2026 21:32:21 +0000

Hello!

I found a few problematic corner cases while testing the patches,
please look at the following:

Doesn't pg_get_database_ddl need more filtering for roles?

See example:

CREATE DATABASE testdb;
CREATE ROLE testrole;
ALTER DATABASE testdb SET work_mem TO '256MB';
ALTER ROLE testrole IN DATABASE testdb SET work_mem TO '512MB';
SELECT pg_get_database_ddl('testdb');

Another issue is that the data style isn't fixed:

CREATE ROLE regress_datestyle_test VALID UNTIL '2030-12-31 23:59:59+00';
SET DateStyle TO 'SQL, DMY';
SELECT * FROM pg_get_role_ddl('regress_datestyle_test');
-- returned statement fails with invalid input syntax for timestamp

Shouldn't that be OWNER TO? Similarly this will result in an error
when executed.

Role memberships seem to be missing. I would expect those to be included?

CREATE ROLE regress_parent;
CREATE ROLE regress_child;
GRANT regress_parent TO regress_child;
SELECT * FROM pg_get_role_ddl('regress_child');

Isn't an pstrdup missing from here? dbname is used after ReleaseSysCache.

---

From: Japin Li <[email-masked]>
Date: Sat, 21 Mar 2026 00:38:44 +0800

Hi, Andrew

On Thu, 19 Mar 2026 at 14:34, Andrew Dunstan <[email-masked]> wrote:
> These patches came out of a suggestion from me some time back [1], and
> I used it as the base for some work at an EDB internal
> program. Perhaps I was motivated a bit by Mao's dictum "Let a hundred
> flowers bloom; let a hundred schools of thought contend." I wanted to
> see what people would come up with. Therefore, if this has seemed a
> bit chaotic, I apologize, both to the authors and to the list. I won't
> do things quite this way in future.
>
> Rather than adding to the already huge ruleutils.c, we decided to
> create a new ddlutils.c file to contain these functions and their
> associated infrastructure. There is in fact a fairly clean separation
> between these functions and ruleutils. We just need to expose one
> function in ruleutils.
>
> this first round, we are proposing to add functions for getting the
> DDL for databases, tablespaces, and roles. We decided to stop there
> for now. This sets up a good basis for dealing with more object types
> in future. To the authors of the remaining patches - rest assured you
> have not been forgotten.
>

Thanks for updating the patches.  Here are some initial comments.

Patch 1
=======

1.
+       DDL_OPT_INT
+} DdlOptType;

A trailing comma should be added to match our coding conventions.

2.

Is it feasible to use a union for these three fields?

3.

IMO, limiting the variable scope to the for loop would be better.

Patch 2
=======

1.
+                foreach(lc, namelist)
+                {

We can use a boolean variable, such as first, to avoid calling lnext(),
and then replace foreach() with foreach_ptr().

2.

Could we use list_nth() in a similar manner to patch 3?

Patch 4
=======

Same as patch 2.

> [2. text/x-patch; 0001-Add-DDL-option-parsing-infrastructure-for-pg_get_-_d.patch]...
>
> [3. text/x-patch; 0002-Add-pg_get_role_ddl-function.patch]...
>
> [4. text/x-patch; 0004-Add-pg_get_database_ddl-function.patch]...
>
> [5. text/x-patch; 0003-Add-pg_get_tablespace_ddl-function.patch]...

-- 
Regards,
Japin Li
ChengDu WenWu Information Technology Co., Ltd.

---

From: Zsolt Parragi <[email-masked]>
Date: Fri, 20 Mar 2026 22:29:29 +0000

> I couldn't reproduce the failure. The non-fixed DateStyle is by design. It
> mimics pg_dumpall.

Sorry, I made a mistake while only copying the relevant parts of the
test case here. This is the correct test case:

SET DateStyle TO 'SQL, DMY'; -- change from default MDY to DMY
CREATE ROLE regress_datestyle_test VALID UNTIL '2030-12-31 23:59:59+00';
SELECT * FROM pg_get_role_ddl('regress_datestyle_test');
DROP ROLE regress_datestyle_test;
SET DateStyle TO 'SQL, MDY'; -- go back to default, or open a new
connection to use the generated command
-- try to run statement generated by pg_get_role_ddl

I'm not saying that this is necessarily wrong, but perhaps it's worth
mentioning somewhere?

> Since I don't know if you will use the testrole role to create testdb
> database or even if the testrole exists in the cluster, it shouldn't return the
> ALTER DATABASE testdb SET work_mem TO '512MB' (because that property belongs to
> testrole role).

Sorry, I wasn't specific previously, it returns the role specific
role, without the role specific condition:

CREATE DATABASE testdb;
CREATE ROLE testrole;
ALTER DATABASE testdb SET work_mem TO '256MB';
ALTER ROLE testrole IN DATABASE testdb SET work_mem TO '512MB';
SELECT pg_get_database_ddl('testdb');
                                                pg_get_database_ddl
-------------------------------------------------------------------------------------------------------------------
 CREATE DATABASE testdb WITH TEMPLATE = template0 ENCODING = 'UTF8'
LOCALE_PROVIDER = libc LOCALE = 'en_US.UTF-8';
 ALTER DATABASE testdb OWNER = dutow;
 ALTER DATABASE testdb SET work_mem TO '256MB';
 ALTER DATABASE testdb SET work_mem TO '512MB';

> one way is to
> compare the output with pg_dump(all). Another point is that these functions can
> be used by a dump tool.)

I did my previous testing with this in mind.

Both the role specific database options and the role memberships are
related: I understand if you say that this is a design decision /
limitation. On the other hand if the goal is that users should be able
to replicate dump(all) with these functions,  then we should have a
way to also get that information, either by providing two different
outputs, or by specific additional getter functions.