From f344010073bb54c6935d7d7599ddb17444589880 Mon Sep 17 00:00:00 2001 From: Andrew Dunstan 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 Co-authored-by: Andrew Dunstan Reviewed-by: Japin Li Reviewed-by: Chao Li Reviewed-by: Álvaro Herrera Reviewed-by: Euler Taveira Reviewed-by: Quan Zongliang Discussion: https://www.postgresql.org/message-id/flat/CANxoLDc6FHBYJvcgOnZyS+jF0NUo3Lq_83-rttBuJgs9id_UDg@mail.gmail.com --- 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. + + + + pg_get_database_ddl + + pg_get_database_ddl + ( database regdatabase + , VARIADIC options + text ) + setof text + + + Reconstructs the CREATE DATABASE statement for the + specified database, followed by ALTER DATABASE + statements for connection limit, template status, and configuration + settings. Each statement is returned as a separate row. + The following options are supported: + pretty (boolean) for formatted output, + owner (boolean) to include OWNER, + and tablespace (boolean) to include + TABLESPACE. + + 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