pg_dump-and-pg_dumpall-database-handling-refactoring_v13.patch

From 98bac6a151e53d93d7a6bcaca9786fa325a0b2d3 Mon Sep 17 00:00:00 2001
From: Hari Babu <kommi.haribabu@gmail.com>
Date: Thu, 18 Jan 2018 10:10:20 +1100
Subject: [PATCH] pg_dump and pg_dumpall database handling refactoring

The Database handling especially with ACL's is not proper
between pg_dump and pg_dumpall. So to avoid these problems,
Now the entire database handling is moved to pg_dump and
the pg_dumpall just invokes pg_dump to print the database
attributes.
---
 doc/src/sgml/ref/pg_dump.sgml        |  33 ++-
 doc/src/sgml/ref/pg_dumpall.sgml     |   4 +
 src/bin/pg_dump/Makefile             |   4 +-
 src/bin/pg_dump/dumputils.c          |  67 +++++
 src/bin/pg_dump/dumputils.h          |   7 +
 src/bin/pg_dump/pg_backup.h          |   2 +
 src/bin/pg_dump/pg_backup_archiver.c |   2 +
 src/bin/pg_dump/pg_backup_utils.c    |   3 +
 src/bin/pg_dump/pg_backup_utils.h    |   1 +
 src/bin/pg_dump/pg_dump.c            | 309 +++++++++++++++++++---
 src/bin/pg_dump/pg_dumpall.c         | 488 ++---------------------------------
 src/bin/pg_upgrade/dump.c            |  12 +-
 src/bin/pg_upgrade/pg_upgrade.c      |  23 +-
 13 files changed, 420 insertions(+), 535 deletions(-)

diff --git a/doc/src/sgml/ref/pg_dump.sgml b/doc/src/sgml/ref/pg_dump.sgml
index 08cad68199..63934a75ec 100644
--- a/doc/src/sgml/ref/pg_dump.sgml
+++ b/doc/src/sgml/ref/pg_dump.sgml
@@ -192,8 +192,11 @@ PostgreSQL documentation
         database itself and reconnect to the created database.  (With a
         script of this form, it doesn't matter which database in the
         destination installation you connect to before running the script.)
-        If <option>--clean</option> is also specified, the script drops and
-        recreates the target database before reconnecting to it.
+        It also sets all the database-level properties such as ownership, 
+        ACLs, <command>ALTER DATABASE ... SET</command> commands, and 
+        <command>ALTER ROLE IN DATABASE ... SET</command> commands.
+        If <option>--clean</option> is also specified, the script drops
+        and recreates the target database before reconnecting to it.
        </para>
 
        <para>
@@ -706,6 +709,28 @@ PostgreSQL documentation
       </listitem>
      </varlistentry>
 
+     <varlistentry>
+      <term><option>--set-db-properties</option></term>
+      <listitem>
+       <para>
+        This option is to dump the database properties without issuing the
+        <command>CREATE DATABASE</command> command. The generated dump is 
+        used to restore the databases on a target location where the database
+        is already exists. This option cannot be used with either of
+        <option>-C/--create</option> or <option>-c/--clean</option> options.
+       </para>
+      
+       <note>
+        <para>
+         The Dump file generated with this option cannot be restored using
+         <option>-c/--clean</option> and <option>-C/--create</option> options of
+         <application>pg_restore</application> command. This is due to not available
+         of <command>CREATE DATABASE</command> command in the dump file.
+        </para>
+       </note>
+      </listitem>
+     </varlistentry>
+     
      <varlistentry>
       <term><option>--enable-row-security</option></term>
       <listitem>
@@ -1231,10 +1256,6 @@ CREATE DATABASE foo WITH TEMPLATE template0;
    <command>ANALYZE</command> after restoring from a dump file
    to ensure optimal performance; see <xref linkend="vacuum-for-statistics"/>
    and <xref linkend="autovacuum"/> for more information.
-   The dump file also does not
-   contain any <command>ALTER DATABASE ... SET</command> commands;
-   these settings are dumped by <xref linkend="app-pg-dumpall"/>,
-   along with database users and other installation-wide settings.
   </para>
 
   <para>
diff --git a/doc/src/sgml/ref/pg_dumpall.sgml b/doc/src/sgml/ref/pg_dumpall.sgml
index 5196a211b1..cb3957d669 100644
--- a/doc/src/sgml/ref/pg_dumpall.sgml
+++ b/doc/src/sgml/ref/pg_dumpall.sgml
@@ -129,6 +129,10 @@ PostgreSQL documentation
       <listitem>
        <para>
         Dump only global objects (roles and tablespaces), no databases.
+        Any database role configuration settings are not dumped with
+        this option. User needs to use <command>pg_dump</command> with
+        <option>--create</option> option to dump 
+        <command>ALTER ROLE IN DATABASE ... SET</command> commands.
        </para>
       </listitem>
      </varlistentry>
diff --git a/src/bin/pg_dump/Makefile b/src/bin/pg_dump/Makefile
index e3bfc95f16..99387ddd99 100644
--- a/src/bin/pg_dump/Makefile
+++ b/src/bin/pg_dump/Makefile
@@ -31,8 +31,8 @@ pg_dump: pg_dump.o common.o pg_dump_sort.o $(OBJS) | submake-libpq submake-libpg
 pg_restore: pg_restore.o $(OBJS) | submake-libpq submake-libpgport submake-libpgfeutils
 	$(CC) $(CFLAGS) pg_restore.o $(OBJS) $(LDFLAGS) $(LDFLAGS_EX) $(LIBS) -o $@$(X)
 
-pg_dumpall: pg_dumpall.o dumputils.o | submake-libpq submake-libpgport submake-libpgfeutils
-	$(CC) $(CFLAGS) pg_dumpall.o dumputils.o $(WIN32RES) $(LDFLAGS) $(LDFLAGS_EX) $(LIBS) -o $@$(X)
+pg_dumpall: pg_dumpall.o dumputils.o pg_backup_utils.o | submake-libpq submake-libpgport submake-libpgfeutils
+	$(CC) $(CFLAGS) pg_dumpall.o dumputils.o pg_backup_utils.o $(WIN32RES) $(LDFLAGS) $(LDFLAGS_EX) $(LIBS) -o $@$(X)
 
 install: all installdirs
 	$(INSTALL_PROGRAM) pg_dump$(X) '$(DESTDIR)$(bindir)'/pg_dump$(X)
diff --git a/src/bin/pg_dump/dumputils.c b/src/bin/pg_dump/dumputils.c
index 32ad600fd0..9ed4171b5d 100644
--- a/src/bin/pg_dump/dumputils.c
+++ b/src/bin/pg_dump/dumputils.c
@@ -807,3 +807,70 @@ buildACLQueries(PQExpBuffer acl_subquery, PQExpBuffer racl_subquery,
 		printfPQExpBuffer(init_racl_subquery, "NULL");
 	}
 }
+
+/*
+ * Helper function for dumpXXXConfig().
+ *
+ * Frame the ALTER ".." SET ".." commands and fill it in buf.
+ */
+void
+makeAlterConfigCommand(PGconn *conn, const char *configitem,
+					   const char *type, const char *name,
+					   const char *type2, const char *name2,
+					   PQExpBuffer buf)
+{
+	char	   *pos;
+	char	   *mine;
+
+	mine = pg_strdup(configitem);
+	pos = strchr(mine, '=');
+	if (pos == NULL)
+	{
+		pg_free(mine);
+		return;
+	}
+
+	*pos++ = '\0';
+	appendPQExpBuffer(buf, "ALTER %s %s ", type, fmtId(name));
+	if (type2 != NULL && name2 != NULL)
+		appendPQExpBuffer(buf, "IN %s %s ", type2, fmtId(name2));
+	appendPQExpBuffer(buf, "SET %s TO ", fmtId(mine));
+
+	/*
+	 * Some GUC variable names are 'LIST' type and hence must not be quoted.
+	 */
+	if (pg_strcasecmp(mine, "DateStyle") == 0
+		|| pg_strcasecmp(mine, "search_path") == 0)
+		appendPQExpBufferStr(buf, pos);
+	else
+		appendStringLiteralConn(buf, pos, conn);
+	appendPQExpBufferStr(buf, ";\n");
+
+	pg_free(mine);
+}
+
+/*
+ * Run a query, return the results, exit program on failure.
+ */
+PGresult *
+executeQuery(PGconn *conn, const char *query)
+{
+	PGresult   *res;
+
+	if (g_verbose)
+		fprintf(stderr, _("%s: executing %s\n"), progname, query);
+
+	res = PQexec(conn, query);
+	if (!res ||
+		PQresultStatus(res) != PGRES_TUPLES_OK)
+	{
+		fprintf(stderr, _("%s: query failed: %s"),
+				progname, PQerrorMessage(conn));
+		fprintf(stderr, _("%s: query was: %s\n"),
+				progname, query);
+		PQfinish(conn);
+		exit_nicely(1);
+	}
+
+	return res;
+}
diff --git a/src/bin/pg_dump/dumputils.h b/src/bin/pg_dump/dumputils.h
index d5f150dfa0..28b84f50d3 100644
--- a/src/bin/pg_dump/dumputils.h
+++ b/src/bin/pg_dump/dumputils.h
@@ -17,6 +17,7 @@
 
 #include "libpq-fe.h"
 #include "pqexpbuffer.h"
+#include "pg_backup_utils.h"
 
 /*
  * Preferred strftime(3) format specifier for printing timestamps in pg_dump
@@ -56,4 +57,10 @@ extern void buildACLQueries(PQExpBuffer acl_subquery, PQExpBuffer racl_subquery,
 				const char *acl_column, const char *acl_owner,
 				const char *obj_kind, bool binary_upgrade);
 
+extern void makeAlterConfigCommand(PGconn *conn, const char *arrayitem,
+					   const char *type, const char *name, const char *type2,
+					   const char *name2, PQExpBuffer buf);
+
+extern PGresult *executeQuery(PGconn *conn, const char *query);
+
 #endif							/* DUMPUTILS_H */
diff --git a/src/bin/pg_dump/pg_backup.h b/src/bin/pg_dump/pg_backup.h
index ce3100f09d..597c4f70e6 100644
--- a/src/bin/pg_dump/pg_backup.h
+++ b/src/bin/pg_dump/pg_backup.h
@@ -117,6 +117,7 @@ typedef struct _restoreOptions
 	int			suppressDumpWarnings;	/* Suppress output of WARNING entries
 										 * to stderr */
 	bool		single_txn;
+	int         set_db_properties;
 
 	bool	   *idWanted;		/* array showing which dump IDs to emit */
 	int			enable_row_security;
@@ -158,6 +159,7 @@ typedef struct _dumpOptions
 	int			use_setsessauth;
 	int			enable_row_security;
 	int			load_via_partition_root;
+	int         set_db_properties;
 
 	/* default, if no "inclusion" switches appear, is to dump everything */
 	bool		include_everything;
diff --git a/src/bin/pg_dump/pg_backup_archiver.c b/src/bin/pg_dump/pg_backup_archiver.c
index 41741aefbc..ea5fba830f 100644
--- a/src/bin/pg_dump/pg_backup_archiver.c
+++ b/src/bin/pg_dump/pg_backup_archiver.c
@@ -831,6 +831,8 @@ restore_toc_entry(ArchiveHandle *AH, TocEntry *te, bool is_parallel)
 
 			ahlog(AH, 1, "connecting to new database \"%s\"\n", te->tag);
 			_reconnectToDB(AH, te->tag);
+
+			ahprintf(AH, "SET default_transaction_read_only = off;\n\n");
 			ropt->dbname = connstr.data;
 		}
 	}
diff --git a/src/bin/pg_dump/pg_backup_utils.c b/src/bin/pg_dump/pg_backup_utils.c
index df5fe708ba..07f2a43e35 100644
--- a/src/bin/pg_dump/pg_backup_utils.c
+++ b/src/bin/pg_dump/pg_backup_utils.c
@@ -19,6 +19,9 @@
 /* Globals exported by this file */
 const char *progname = NULL;
 
+/* User wants verbose narration of our activities */
+bool		g_verbose = false;
+
 #define MAX_ON_EXIT_NICELY				20
 
 static struct
diff --git a/src/bin/pg_dump/pg_backup_utils.h b/src/bin/pg_dump/pg_backup_utils.h
index 6eaf302fc7..b55fe5e8c3 100644
--- a/src/bin/pg_dump/pg_backup_utils.h
+++ b/src/bin/pg_dump/pg_backup_utils.h
@@ -26,6 +26,7 @@ typedef enum					/* bits returned by set_dump_section */
 typedef void (*on_exit_nicely_callback) (int code, void *arg);
 
 extern const char *progname;
+extern bool g_verbose;
 
 extern void set_dump_section(const char *arg, int *dumpSections);
 extern void write_msg(const char *modulename, const char *fmt,...) pg_attribute_printf(2, 3);
diff --git a/src/bin/pg_dump/pg_dump.c b/src/bin/pg_dump/pg_dump.c
index 27628a397c..2f607abd1c 100644
--- a/src/bin/pg_dump/pg_dump.c
+++ b/src/bin/pg_dump/pg_dump.c
@@ -88,9 +88,6 @@ typedef enum OidOptions
 	zeroAsNone = 8
 } OidOptions;
 
-/* global decls */
-bool		g_verbose;			/* User wants verbose narration of our
-								 * activities. */
 static bool dosync = true;		/* Issue fsync() to make dump durable on disk. */
 
 /* subquery used to convert user ID (eg, datdba) to user name */
@@ -273,6 +270,8 @@ static void appendReloptionsArrayAH(PQExpBuffer buffer, const char *reloptions,
 static char *get_synchronized_snapshot(Archive *fout);
 static void setupDumpWorker(Archive *AHX);
 static TableInfo *getRootTableInfo(TableInfo *tbinfo);
+static void dumpDatabaseConfig(Archive *fout, PQExpBuffer creaQry, const char *dbname);
+static void dumpDbRoleConfig(Archive *AH, PQExpBuffer creaQry);
 
 
 int
@@ -362,6 +361,7 @@ main(int argc, char **argv)
 		{"no-unlogged-table-data", no_argument, &dopt.no_unlogged_table_data, 1},
 		{"no-subscriptions", no_argument, &dopt.no_subscriptions, 1},
 		{"no-sync", no_argument, NULL, 7},
+		{"set-db-properties", no_argument, &dopt.set_db_properties, 1},
 
 		{NULL, 0, NULL, 0}
 	};
@@ -581,6 +581,18 @@ main(int argc, char **argv)
 	if (dopt.binary_upgrade)
 		dopt.sequence_data = 1;
 
+	if (dopt.set_db_properties && dopt.outputCreateDB)
+	{
+		write_msg(NULL, "option --set-db-properties and -C/--create cannot be used together\n");
+		exit_nicely(1);
+	}
+
+	if (dopt.set_db_properties && dopt.outputClean)
+	{
+		write_msg(NULL, "option --set-db-properties and -c/--clean cannot be used together\n");
+		exit_nicely(1);
+	}
+
 	if (dopt.dataOnly && dopt.schemaOnly)
 	{
 		write_msg(NULL, "options -s/--schema-only and -a/--data-only cannot be used together\n");
@@ -860,7 +872,12 @@ main(int argc, char **argv)
 	ropt->dumpSections = dopt.dumpSections;
 	ropt->aclsSkip = dopt.aclsSkip;
 	ropt->superuser = dopt.outputSuperuser;
-	ropt->createDB = dopt.outputCreateDB;
+
+	/*
+	 * treat --set-db-properties are also as DATABASE commands like
+	 * -C/--create
+	 */
+	ropt->createDB = dopt.outputCreateDB || dopt.set_db_properties;
 	ropt->noOwner = dopt.outputNoOwner;
 	ropt->noTablespace = dopt.outputNoTablespaces;
 	ropt->disable_triggers = dopt.disable_triggers;
@@ -875,6 +892,7 @@ main(int argc, char **argv)
 	ropt->enable_row_security = dopt.enable_row_security;
 	ropt->sequence_data = dopt.sequence_data;
 	ropt->binary_upgrade = dopt.binary_upgrade;
+	ropt->set_db_properties = dopt.set_db_properties;
 
 	if (compressLevel == -1)
 		ropt->compression = 0;
@@ -973,6 +991,7 @@ help(const char *progname)
 	printf(_("  --use-set-session-authorization\n"
 			 "                               use SET SESSION AUTHORIZATION commands instead of\n"
 			 "                               ALTER OWNER commands to set ownership\n"));
+	printf(_("  --set-db-properties          dump database properties without CREATE DATABASE command\n"));
 
 	printf(_("\nConnection options:\n"));
 	printf(_("  -d, --dbname=DBNAME      database to dump\n"));
@@ -2528,25 +2547,34 @@ dumpDatabase(Archive *fout)
 	PQExpBuffer dbQry = createPQExpBuffer();
 	PQExpBuffer delQry = createPQExpBuffer();
 	PQExpBuffer creaQry = createPQExpBuffer();
+	PQExpBuffer aclQry = createPQExpBuffer();
 	PGconn	   *conn = GetConnection(fout);
 	PGresult   *res;
 	int			i_tableoid,
 				i_oid,
 				i_dba,
+				i_dbacl,
+				i_rdbacl,
 				i_encoding,
 				i_collate,
 				i_ctype,
 				i_frozenxid,
 				i_minmxid,
-				i_tablespace;
+				i_tablespace,
+				i_dbistemplate,
+				i_dbconnlimit;
 	CatalogId	dbCatId;
 	DumpId		dbDumpId;
 	const char *datname,
 			   *dba,
+			   *dbacl,
+			   *rdbacl,
 			   *encoding,
 			   *collate,
 			   *ctype,
-			   *tablespace;
+			   *tablespace,
+			   *dbistemplate,
+			   *dbconnlimit;
 	uint32		frozenxid,
 				minmxid;
 
@@ -2558,13 +2586,49 @@ dumpDatabase(Archive *fout)
 	/* Make sure we are in proper schema */
 	selectSourceSchema(fout, "pg_catalog");
 
-	/* Get the database owner and parameters from pg_database */
-	if (fout->remoteVersion >= 90300)
+	/*
+	 * Now collect all the information about databases to dump.
+	 *
+	 * For the database ACLs, as of 9.6, we extract both the positive (as
+	 * datacl) and negative (as rdatacl) ACLs, relative to the default ACL for
+	 * databases, which are then passed to buildACLCommands() below.
+	 *
+	 * See buildACLQueries() and buildACLCommands().
+	 *
+	 * Note that we do not support initial privileges (pg_init_privs) on
+	 * databases.
+	 */
+
+	if (fout->remoteVersion >= 90600)
+	{
+		appendPQExpBuffer(dbQry,
+						  "SELECT tableoid, oid, "
+						  "(%s datdba) AS dba, "
+						  "pg_encoding_to_char(encoding) AS encoding, "
+						  "datcollate, datctype, datfrozenxid, datminmxid, datistemplate, "
+						  "(SELECT pg_catalog.array_agg(acl ORDER BY acl::text COLLATE \"C\") FROM ( "
+						  "  SELECT pg_catalog.unnest(coalesce(datacl,pg_catalog.acldefault('d',datdba))) AS acl "
+						  "  EXCEPT SELECT pg_catalog.unnest(pg_catalog.acldefault('d',datdba))) as datacls)"
+						  " AS datacl, "
+						  "(SELECT pg_catalog.array_agg(acl ORDER BY acl::text COLLATE \"C\") FROM ( "
+						  "  SELECT pg_catalog.unnest(pg_catalog.acldefault('d',datdba)) AS acl "
+						  "  EXCEPT SELECT pg_catalog.unnest(coalesce(datacl,pg_catalog.acldefault('d',datdba)))) as rdatacls)"
+						  " AS rdatacl, "
+						  "datconnlimit, "
+						  "(SELECT spcname FROM pg_tablespace t WHERE t.oid = dattablespace) AS tablespace, "
+						  "shobj_description(oid, 'pg_database') AS description "
+
+						  "FROM pg_database "
+						  "WHERE datname = ",
+						  username_subquery);
+		appendStringLiteralAH(dbQry, datname, fout);
+	}
+	else if (fout->remoteVersion >= 90300)
 	{
 		appendPQExpBuffer(dbQry, "SELECT tableoid, oid, "
 						  "(%s datdba) AS dba, "
 						  "pg_encoding_to_char(encoding) AS encoding, "
-						  "datcollate, datctype, datfrozenxid, datminmxid, "
+						  "datcollate, datctype, datfrozenxid, datminmxid, datistemplate, datacl,'' as rdatacl, datconnlimit,"
 						  "(SELECT spcname FROM pg_tablespace t WHERE t.oid = dattablespace) AS tablespace, "
 						  "shobj_description(oid, 'pg_database') AS description "
 
@@ -2578,7 +2642,7 @@ dumpDatabase(Archive *fout)
 		appendPQExpBuffer(dbQry, "SELECT tableoid, oid, "
 						  "(%s datdba) AS dba, "
 						  "pg_encoding_to_char(encoding) AS encoding, "
-						  "datcollate, datctype, datfrozenxid, 0 AS datminmxid, "
+						  "datcollate, datctype, datfrozenxid, 0 AS datminmxid, datistemplate, datacl, '' as rdatacl, datconnlimit,"
 						  "(SELECT spcname FROM pg_tablespace t WHERE t.oid = dattablespace) AS tablespace, "
 						  "shobj_description(oid, 'pg_database') AS description "
 
@@ -2592,7 +2656,7 @@ dumpDatabase(Archive *fout)
 		appendPQExpBuffer(dbQry, "SELECT tableoid, oid, "
 						  "(%s datdba) AS dba, "
 						  "pg_encoding_to_char(encoding) AS encoding, "
-						  "NULL AS datcollate, NULL AS datctype, datfrozenxid, 0 AS datminmxid, "
+						  "NULL AS datcollate, NULL AS datctype, datfrozenxid, 0 AS datminmxid, datistemplate, datacl,'' as rdatacl, datconnlimit,"
 						  "(SELECT spcname FROM pg_tablespace t WHERE t.oid = dattablespace) AS tablespace, "
 						  "shobj_description(oid, 'pg_database') AS description "
 
@@ -2606,7 +2670,8 @@ dumpDatabase(Archive *fout)
 		appendPQExpBuffer(dbQry, "SELECT tableoid, oid, "
 						  "(%s datdba) AS dba, "
 						  "pg_encoding_to_char(encoding) AS encoding, "
-						  "NULL AS datcollate, NULL AS datctype, datfrozenxid, 0 AS datminmxid, "
+						  "NULL AS datcollate, NULL AS datctype, datfrozenxid, 0 AS datminmxid, datistemplate, datacl,'' as rdatacl,"
+						  "-1 as datconnlimit,"
 						  "(SELECT spcname FROM pg_tablespace t WHERE t.oid = dattablespace) AS tablespace "
 						  "FROM pg_database "
 						  "WHERE datname = ",
@@ -2625,6 +2690,10 @@ dumpDatabase(Archive *fout)
 	i_frozenxid = PQfnumber(res, "datfrozenxid");
 	i_minmxid = PQfnumber(res, "datminmxid");
 	i_tablespace = PQfnumber(res, "tablespace");
+	i_dbacl = PQfnumber(res, "datacl");
+	i_rdbacl = PQfnumber(res, "rdatacl");
+	i_dbistemplate = PQfnumber(res, "datistemplate");
+	i_dbconnlimit = PQfnumber(res, "datconnlimit");
 
 	dbCatId.tableoid = atooid(PQgetvalue(res, 0, i_tableoid));
 	dbCatId.oid = atooid(PQgetvalue(res, 0, i_oid));
@@ -2635,40 +2704,63 @@ dumpDatabase(Archive *fout)
 	frozenxid = atooid(PQgetvalue(res, 0, i_frozenxid));
 	minmxid = atooid(PQgetvalue(res, 0, i_minmxid));
 	tablespace = PQgetvalue(res, 0, i_tablespace);
+	dbacl = PQgetvalue(res, 0, i_dbacl);
+	rdbacl = PQgetvalue(res, 0, i_rdbacl);
+	dbistemplate = PQgetvalue(res, 0, i_dbistemplate);
+	dbconnlimit = PQgetvalue(res, 0, i_dbconnlimit);
 
-	appendPQExpBuffer(creaQry, "CREATE DATABASE %s WITH TEMPLATE = template0",
-					  fmtId(datname));
-	if (strlen(encoding) > 0)
-	{
-		appendPQExpBufferStr(creaQry, " ENCODING = ");
-		appendStringLiteralAH(creaQry, encoding, fout);
-	}
-	if (strlen(collate) > 0)
+	/*
+	 * Skip the CREATE DATABASE commands --set-db-properties is enabled, since
+	 * they are presumably already there in the destination cluster. We do
+	 * want to emit their ACLs and config options if any, however.
+	 */
+	if (!dopt->set_db_properties)
 	{
-		appendPQExpBufferStr(creaQry, " LC_COLLATE = ");
-		appendStringLiteralAH(creaQry, collate, fout);
+		appendPQExpBuffer(creaQry, "CREATE DATABASE %s WITH TEMPLATE = template0",
+						  fmtId(datname));
+
+		if (strlen(encoding) > 0)
+		{
+			appendPQExpBufferStr(creaQry, " ENCODING = ");
+			appendStringLiteralAH(creaQry, encoding, fout);
+		}
+		if (strlen(collate) > 0)
+		{
+			appendPQExpBufferStr(creaQry, " LC_COLLATE = ");
+			appendStringLiteralAH(creaQry, collate, fout);
+		}
+		if (strlen(ctype) > 0)
+		{
+			appendPQExpBufferStr(creaQry, " LC_CTYPE = ");
+			appendStringLiteralAH(creaQry, ctype, fout);
+		}
+		if (strlen(tablespace) > 0 && strcmp(tablespace, "pg_default") != 0 &&
+			!dopt->outputNoTablespaces)
+		{
+			appendPQExpBuffer(creaQry, " TABLESPACE = %s", fmtId(tablespace));
+		}
+
+		if (strlen(dbistemplate) > 0 && strcmp(dbistemplate, "t") == 0)
+			appendPQExpBuffer(creaQry, " IS_TEMPLATE = true");
+
+		if (strlen(dbconnlimit) > 0 && strcmp(dbconnlimit, "-1") != 0)
+			appendPQExpBuffer(creaQry, " CONNECTION LIMIT = %s",
+							  dbconnlimit);
+
+		appendPQExpBufferStr(creaQry, ";\n");
 	}
-	if (strlen(ctype) > 0)
+	else if (strlen(tablespace) > 0 && strcmp(tablespace, "pg_default") != 0 && !dopt->outputNoTablespaces)
 	{
-		appendPQExpBufferStr(creaQry, " LC_CTYPE = ");
-		appendStringLiteralAH(creaQry, ctype, fout);
-	}
-	if (strlen(tablespace) > 0 && strcmp(tablespace, "pg_default") != 0 &&
-		!dopt->outputNoTablespaces)
-		appendPQExpBuffer(creaQry, " TABLESPACE = %s",
-						  fmtId(tablespace));
-	appendPQExpBufferStr(creaQry, ";\n");
+		if (strcmp(datname, "postgres") == 0)
+			appendPQExpBuffer(creaQry, "\\connect template1\n");
+		else if (strcmp(datname, "template1") == 0)
+			appendPQExpBuffer(creaQry, "\\connect postgres\n");
 
-	if (dopt->binary_upgrade)
-	{
-		appendPQExpBufferStr(creaQry, "\n-- For binary upgrade, set datfrozenxid and datminmxid.\n");
-		appendPQExpBuffer(creaQry, "UPDATE pg_catalog.pg_database\n"
-						  "SET datfrozenxid = '%u', datminmxid = '%u'\n"
-						  "WHERE datname = ",
-						  frozenxid, minmxid);
-		appendStringLiteralAH(creaQry, datname, fout);
-		appendPQExpBufferStr(creaQry, ";\n");
+		appendPQExpBuffer(creaQry, "ALTER DATABASE %s SET TABLESPACE %s;\n",
+						  datname, fmtId(tablespace));
 
+		/* connect to original database */
+		appendPsqlMetaConnect(creaQry, datname);
 	}
 
 	appendPQExpBuffer(delQry, "DROP DATABASE %s;\n",
@@ -2694,6 +2786,58 @@ dumpDatabase(Archive *fout)
 				 NULL,			/* Dumper */
 				 NULL);			/* Dumper Arg */
 
+	resetPQExpBuffer(creaQry);
+
+	if (dopt->binary_upgrade)
+	{
+		appendPQExpBufferStr(creaQry, "\n-- For binary upgrade, set datfrozenxid and datminmxid.\n");
+		appendPQExpBuffer(creaQry, "UPDATE pg_catalog.pg_database\n"
+						  "SET datfrozenxid = '%u', datminmxid = '%u'\n"
+						  "WHERE	datname = ",
+						  frozenxid, minmxid);
+		appendStringLiteralAH(creaQry, datname, fout);
+		appendPQExpBufferStr(creaQry, ";\n");
+
+		ArchiveEntry(fout, dbCatId, createDumpId(),
+					 datname, NULL, NULL, dba,
+					 false, "DATABASE", SECTION_PRE_DATA,
+					 creaQry->data, "", NULL,
+					 NULL, 0,
+					 NULL, NULL);
+		resetPQExpBuffer(creaQry);
+	}
+
+	if (!dopt->aclsSkip &&
+		!buildACLCommands(datname, NULL, "DATABASE",
+						  dbacl, rdbacl, dba,
+						  "", fout->remoteVersion, aclQry))
+	{
+		exit_horribly(NULL, _("%s: could not parse ACL list (%s) for database \"%s\"\n"), progname, dbacl, datname);
+	}
+
+	if (strlen(aclQry->data))
+		ArchiveEntry(fout, dbCatId, createDumpId(),
+					 datname, NULL, NULL, dba,
+					 false, "DATABASE", SECTION_PRE_DATA,
+					 aclQry->data, "", NULL,
+					 NULL, 0,
+					 NULL, NULL);
+
+	/* Dump database specific configuration */
+	dumpDatabaseConfig(fout, creaQry, datname);
+
+	/* Dump user and database specific configuration */
+	if (fout->remoteVersion >= 90000)
+		dumpDbRoleConfig(fout, creaQry);
+
+	if (strlen(creaQry->data))
+		ArchiveEntry(fout, dbCatId, createDumpId(),
+					 datname, NULL, NULL, dba,
+					 false, "DATABASE", SECTION_PRE_DATA,
+					 creaQry->data, "", NULL,
+					 NULL, 0,
+					 NULL, NULL);
+
 	/*
 	 * pg_largeobject and pg_largeobject_metadata come from the old system
 	 * intact, so set their relfrozenxids and relminmxids.
@@ -2847,6 +2991,7 @@ dumpDatabase(Archive *fout)
 	destroyPQExpBuffer(dbQry);
 	destroyPQExpBuffer(delQry);
 	destroyPQExpBuffer(creaQry);
+	destroyPQExpBuffer(aclQry);
 }
 
 /*
@@ -18091,3 +18236,87 @@ appendReloptionsArrayAH(PQExpBuffer buffer, const char *reloptions,
 	if (!res)
 		write_msg(NULL, "WARNING: could not parse reloptions array\n");
 }
+
+/*
+ * Dump database-specific configuration
+ */
+static void
+dumpDatabaseConfig(Archive *AH, PQExpBuffer creaQry, const char *dbname)
+{
+	PGconn	   *conn = GetConnection(AH);
+	PQExpBuffer buf = createPQExpBuffer();
+	int			count = 1;
+
+	for (;;)
+	{
+		PGresult   *res;
+
+		if (AH->remoteVersion >= 90000)
+			printfPQExpBuffer(buf, "SELECT setconfig[%d] FROM pg_db_role_setting WHERE "
+							  "setrole = 0 AND setdatabase = (SELECT oid FROM pg_database WHERE datname = ", count);
+		else
+			printfPQExpBuffer(buf, "SELECT datconfig[%d] FROM pg_database WHERE datname = ", count);
+
+		appendStringLiteralConn(buf, dbname, conn);
+
+		if (AH->remoteVersion >= 90000)
+			appendPQExpBufferChar(buf, ')');
+
+		res = executeQuery(conn, buf->data);
+		resetPQExpBuffer(buf);
+
+		if (PQntuples(res) == 1 &&
+			!PQgetisnull(res, 0, 0))
+		{
+			makeAlterConfigCommand(conn, PQgetvalue(res, 0, 0),
+								   "DATABASE", dbname, NULL, NULL, buf);
+
+			appendPQExpBuffer(creaQry, "%s", buf->data);
+			PQclear(res);
+			count++;
+		}
+		else
+		{
+			PQclear(res);
+			break;
+		}
+	}
+
+	destroyPQExpBuffer(buf);
+}
+
+/*
+ * Dump user-and-database-specific configuration
+ */
+static void
+dumpDbRoleConfig(Archive *AH, PQExpBuffer creaQry)
+{
+	PGconn	   *conn = GetConnection(AH);
+	PQExpBuffer buf = createPQExpBuffer();
+	PGresult   *res;
+	int			i;
+
+	printfPQExpBuffer(buf, "SELECT rolname, datname, unnest(setconfig) "
+					  "FROM pg_db_role_setting, pg_roles, pg_database "
+					  "WHERE setrole = pg_roles.oid AND setdatabase = pg_database.oid");
+	res = executeQuery(conn, buf->data);
+
+	if (PQntuples(res) > 0)
+	{
+		appendPQExpBufferStr(creaQry, "\n\n--\n-- Per-Database Role Settings \n--\n\n");
+
+		resetPQExpBuffer(buf);
+		for (i = 0; i < PQntuples(res); i++)
+		{
+			makeAlterConfigCommand(conn, PQgetvalue(res, i, 2),
+								   "ROLE", PQgetvalue(res, i, 0),
+								   "DATABASE", PQgetvalue(res, i, 1), buf);
+		}
+
+		appendPQExpBuffer(creaQry, "%s", buf->data);
+		appendPQExpBufferStr(creaQry, "\n\n");
+	}
+
+	PQclear(res);
+	destroyPQExpBuffer(buf);
+}
diff --git a/src/bin/pg_dump/pg_dumpall.c b/src/bin/pg_dump/pg_dumpall.c
index 3dd2c3871e..ae2c5fa17a 100644
--- a/src/bin/pg_dump/pg_dumpall.c
+++ b/src/bin/pg_dump/pg_dumpall.c
@@ -38,16 +38,9 @@ static void dumpGroups(PGconn *conn);
 static void dropTablespaces(PGconn *conn);
 static void dumpTablespaces(PGconn *conn);
 static void dropDBs(PGconn *conn);
-static void dumpCreateDB(PGconn *conn);
-static void dumpDatabaseConfig(PGconn *conn, const char *dbname);
 static void dumpUserConfig(PGconn *conn, const char *username);
-static void dumpDbRoleConfig(PGconn *conn);
-static void makeAlterConfigCommand(PGconn *conn, const char *arrayitem,
-					   const char *type, const char *name, const char *type2,
-					   const char *name2);
 static void dumpDatabases(PGconn *conn);
 static void dumpTimestamp(const char *msg);
-
 static int	runPgDump(const char *dbname);
 static void buildShSecLabels(PGconn *conn, const char *catalog_name,
 				 uint32 objectId, PQExpBuffer buffer,
@@ -55,15 +48,12 @@ static void buildShSecLabels(PGconn *conn, const char *catalog_name,
 static PGconn *connectDatabase(const char *dbname, const char *connstr, const char *pghost, const char *pgport,
 				const char *pguser, trivalue prompt_password, bool fail_on_error);
 static char *constructConnStr(const char **keywords, const char **values);
-static PGresult *executeQuery(PGconn *conn, const char *query);
 static void executeCommand(PGconn *conn, const char *query);
 
 static char pg_dump_bin[MAXPGPATH];
-static const char *progname;
 static PQExpBuffer pgdumpopts;
 static char *connstr = "";
 static bool skip_acls = false;
-static bool verbose = false;
 static bool dosync = true;
 
 static int	binary_upgrade = 0;
@@ -281,7 +271,7 @@ main(int argc, char *argv[])
 				break;
 
 			case 'v':
-				verbose = true;
+				g_verbose = true;
 				appendPQExpBufferStr(pgdumpopts, " -v");
 				break;
 
@@ -498,7 +488,7 @@ main(int argc, char *argv[])
 		executeCommand(conn, "SET quote_all_identifiers = true");
 
 	fprintf(OPF, "--\n-- PostgreSQL database cluster dump\n--\n\n");
-	if (verbose)
+	if (g_verbose)
 		dumpTimestamp("Started on");
 
 	/*
@@ -558,17 +548,6 @@ main(int argc, char *argv[])
 		/* Dump tablespaces */
 		if (!roles_only && !no_tablespaces)
 			dumpTablespaces(conn);
-
-		/* Dump CREATE DATABASE commands */
-		if (binary_upgrade || (!globals_only && !roles_only && !tablespaces_only))
-			dumpCreateDB(conn);
-
-		/* Dump role/database settings */
-		if (!tablespaces_only && !roles_only)
-		{
-			if (server_version >= 90000)
-				dumpDbRoleConfig(conn);
-		}
 	}
 
 	if (!globals_only && !roles_only && !tablespaces_only)
@@ -576,7 +555,7 @@ main(int argc, char *argv[])
 
 	PQfinish(conn);
 
-	if (verbose)
+	if (g_verbose)
 		dumpTimestamp("Completed on");
 	fprintf(OPF, "--\n-- PostgreSQL database cluster dump complete\n--\n\n");
 
@@ -1302,323 +1281,6 @@ dropDBs(PGconn *conn)
 	fprintf(OPF, "\n\n");
 }
 
-/*
- * Dump commands to create each database.
- *
- * To minimize the number of reconnections (and possibly ensuing
- * password prompts) required by the output script, we emit all CREATE
- * DATABASE commands during the initial phase of the script, and then
- * run pg_dump for each database to dump the contents of that
- * database.  We skip databases marked not datallowconn, since we'd be
- * unable to connect to them anyway (and besides, we don't want to
- * dump template0).
- */
-static void
-dumpCreateDB(PGconn *conn)
-{
-	PQExpBuffer buf = createPQExpBuffer();
-	char	   *default_encoding = NULL;
-	char	   *default_collate = NULL;
-	char	   *default_ctype = NULL;
-	PGresult   *res;
-	int			i;
-
-	fprintf(OPF, "--\n-- Database creation\n--\n\n");
-
-	/*
-	 * First, get the installation's default encoding and locale information.
-	 * We will dump encoding and locale specifications in the CREATE DATABASE
-	 * commands for just those databases with values different from defaults.
-	 *
-	 * We consider template0's encoding and locale to define the installation
-	 * default.  Pre-8.4 installations do not have per-database locale
-	 * settings; for them, every database must necessarily be using the
-	 * installation default, so there's no need to do anything.
-	 */
-	if (server_version >= 80400)
-		res = executeQuery(conn,
-						   "SELECT pg_encoding_to_char(encoding), "
-						   "datcollate, datctype "
-						   "FROM pg_database "
-						   "WHERE datname = 'template0'");
-	else
-		res = executeQuery(conn,
-						   "SELECT pg_encoding_to_char(encoding), "
-						   "null::text AS datcollate, null::text AS datctype "
-						   "FROM pg_database "
-						   "WHERE datname = 'template0'");
-
-	/* If for some reason the template DB isn't there, treat as unknown */
-	if (PQntuples(res) > 0)
-	{
-		if (!PQgetisnull(res, 0, 0))
-			default_encoding = pg_strdup(PQgetvalue(res, 0, 0));
-		if (!PQgetisnull(res, 0, 1))
-			default_collate = pg_strdup(PQgetvalue(res, 0, 1));
-		if (!PQgetisnull(res, 0, 2))
-			default_ctype = pg_strdup(PQgetvalue(res, 0, 2));
-	}
-
-	PQclear(res);
-
-
-	/*
-	 * Now collect all the information about databases to dump.
-	 *
-	 * For the database ACLs, as of 9.6, we extract both the positive (as
-	 * datacl) and negative (as rdatacl) ACLs, relative to the default ACL for
-	 * databases, which are then passed to buildACLCommands() below.
-	 *
-	 * See buildACLQueries() and buildACLCommands().
-	 *
-	 * Note that we do not support initial privileges (pg_init_privs) on
-	 * databases.
-	 */
-	if (server_version >= 90600)
-		printfPQExpBuffer(buf,
-						  "SELECT datname, "
-						  "coalesce(rolname, (select rolname from %s where oid=(select datdba from pg_database where datname='template0'))), "
-						  "pg_encoding_to_char(d.encoding), "
-						  "datcollate, datctype, datfrozenxid, datminmxid, "
-						  "datistemplate, "
-						  "(SELECT pg_catalog.array_agg(acl ORDER BY acl::text COLLATE \"C\") FROM ( "
-						  "  SELECT pg_catalog.unnest(coalesce(datacl,pg_catalog.acldefault('d',datdba))) AS acl "
-						  "  EXCEPT SELECT pg_catalog.unnest(pg_catalog.acldefault('d',datdba))) as datacls)"
-						  "AS datacl, "
-						  "(SELECT pg_catalog.array_agg(acl ORDER BY acl::text COLLATE \"C\") FROM ( "
-						  "  SELECT pg_catalog.unnest(pg_catalog.acldefault('d',datdba)) AS acl "
-						  "  EXCEPT SELECT pg_catalog.unnest(coalesce(datacl,pg_catalog.acldefault('d',datdba)))) as rdatacls)"
-						  "AS rdatacl, "
-						  "datconnlimit, "
-						  "(SELECT spcname FROM pg_tablespace t WHERE t.oid = d.dattablespace) AS dattablespace "
-						  "FROM pg_database d LEFT JOIN %s u ON (datdba = u.oid) "
-						  "WHERE datallowconn ORDER BY 1", role_catalog, role_catalog);
-	else if (server_version >= 90300)
-		printfPQExpBuffer(buf,
-						  "SELECT datname, "
-						  "coalesce(rolname, (select rolname from %s where oid=(select datdba from pg_database where datname='template0'))), "
-						  "pg_encoding_to_char(d.encoding), "
-						  "datcollate, datctype, datfrozenxid, datminmxid, "
-						  "datistemplate, datacl, '' as rdatacl, "
-						  "datconnlimit, "
-						  "(SELECT spcname FROM pg_tablespace t WHERE t.oid = d.dattablespace) AS dattablespace "
-						  "FROM pg_database d LEFT JOIN %s u ON (datdba = u.oid) "
-						  "WHERE datallowconn ORDER BY 1", role_catalog, role_catalog);
-	else if (server_version >= 80400)
-		printfPQExpBuffer(buf,
-						  "SELECT datname, "
-						  "coalesce(rolname, (select rolname from %s where oid=(select datdba from pg_database where datname='template0'))), "
-						  "pg_encoding_to_char(d.encoding), "
-						  "datcollate, datctype, datfrozenxid, 0 AS datminmxid, "
-						  "datistemplate, datacl, '' as rdatacl, "
-						  "datconnlimit, "
-						  "(SELECT spcname FROM pg_tablespace t WHERE t.oid = d.dattablespace) AS dattablespace "
-						  "FROM pg_database d LEFT JOIN %s u ON (datdba = u.oid) "
-						  "WHERE datallowconn ORDER BY 1", role_catalog, role_catalog);
-	else if (server_version >= 80100)
-		printfPQExpBuffer(buf,
-						  "SELECT datname, "
-						  "coalesce(rolname, (select rolname from %s where oid=(select datdba from pg_database where datname='template0'))), "
-						  "pg_encoding_to_char(d.encoding), "
-						  "null::text AS datcollate, null::text AS datctype, datfrozenxid, 0 AS datminmxid, "
-						  "datistemplate, datacl, '' as rdatacl, "
-						  "datconnlimit, "
-						  "(SELECT spcname FROM pg_tablespace t WHERE t.oid = d.dattablespace) AS dattablespace "
-						  "FROM pg_database d LEFT JOIN %s u ON (datdba = u.oid) "
-						  "WHERE datallowconn ORDER BY 1", role_catalog, role_catalog);
-	else
-		printfPQExpBuffer(buf,
-						  "SELECT datname, "
-						  "coalesce(usename, (select usename from pg_shadow where usesysid=(select datdba from pg_database where datname='template0'))), "
-						  "pg_encoding_to_char(d.encoding), "
-						  "null::text AS datcollate, null::text AS datctype, datfrozenxid, 0 AS datminmxid, "
-						  "datistemplate, datacl, '' as rdatacl, "
-						  "-1 as datconnlimit, "
-						  "(SELECT spcname FROM pg_tablespace t WHERE t.oid = d.dattablespace) AS dattablespace "
-						  "FROM pg_database d LEFT JOIN pg_shadow u ON (datdba = usesysid) "
-						  "WHERE datallowconn ORDER BY 1");
-
-	res = executeQuery(conn, buf->data);
-
-	for (i = 0; i < PQntuples(res); i++)
-	{
-		char	   *dbname = PQgetvalue(res, i, 0);
-		char	   *dbowner = PQgetvalue(res, i, 1);
-		char	   *dbencoding = PQgetvalue(res, i, 2);
-		char	   *dbcollate = PQgetvalue(res, i, 3);
-		char	   *dbctype = PQgetvalue(res, i, 4);
-		uint32		dbfrozenxid = atooid(PQgetvalue(res, i, 5));
-		uint32		dbminmxid = atooid(PQgetvalue(res, i, 6));
-		char	   *dbistemplate = PQgetvalue(res, i, 7);
-		char	   *dbacl = PQgetvalue(res, i, 8);
-		char	   *rdbacl = PQgetvalue(res, i, 9);
-		char	   *dbconnlimit = PQgetvalue(res, i, 10);
-		char	   *dbtablespace = PQgetvalue(res, i, 11);
-		char	   *fdbname;
-
-		fdbname = pg_strdup(fmtId(dbname));
-
-		resetPQExpBuffer(buf);
-
-		/*
-		 * Skip the CREATE DATABASE commands for "template1" and "postgres",
-		 * since they are presumably already there in the destination cluster.
-		 * We do want to emit their ACLs and config options if any, however.
-		 */
-		if (strcmp(dbname, "template1") != 0 &&
-			strcmp(dbname, "postgres") != 0)
-		{
-			appendPQExpBuffer(buf, "CREATE DATABASE %s", fdbname);
-
-			appendPQExpBufferStr(buf, " WITH TEMPLATE = template0");
-
-			if (strlen(dbowner) != 0)
-				appendPQExpBuffer(buf, " OWNER = %s", fmtId(dbowner));
-
-			if (default_encoding && strcmp(dbencoding, default_encoding) != 0)
-			{
-				appendPQExpBufferStr(buf, " ENCODING = ");
-				appendStringLiteralConn(buf, dbencoding, conn);
-			}
-
-			if (default_collate && strcmp(dbcollate, default_collate) != 0)
-			{
-				appendPQExpBufferStr(buf, " LC_COLLATE = ");
-				appendStringLiteralConn(buf, dbcollate, conn);
-			}
-
-			if (default_ctype && strcmp(dbctype, default_ctype) != 0)
-			{
-				appendPQExpBufferStr(buf, " LC_CTYPE = ");
-				appendStringLiteralConn(buf, dbctype, conn);
-			}
-
-			/*
-			 * Output tablespace if it isn't the default.  For default, it
-			 * uses the default from the template database.  If tablespace is
-			 * specified and tablespace creation failed earlier, (e.g. no such
-			 * directory), the database creation will fail too.  One solution
-			 * would be to use 'SET default_tablespace' like we do in pg_dump
-			 * for setting non-default database locations.
-			 */
-			if (strcmp(dbtablespace, "pg_default") != 0 && !no_tablespaces)
-				appendPQExpBuffer(buf, " TABLESPACE = %s",
-								  fmtId(dbtablespace));
-
-			if (strcmp(dbistemplate, "t") == 0)
-				appendPQExpBuffer(buf, " IS_TEMPLATE = true");
-
-			if (strcmp(dbconnlimit, "-1") != 0)
-				appendPQExpBuffer(buf, " CONNECTION LIMIT = %s",
-								  dbconnlimit);
-
-			appendPQExpBufferStr(buf, ";\n");
-		}
-		else if (strcmp(dbtablespace, "pg_default") != 0 && !no_tablespaces)
-		{
-			/*
-			 * Cannot change tablespace of the database we're connected to, so
-			 * to move "postgres" to another tablespace, we connect to
-			 * "template1", and vice versa.
-			 */
-			if (strcmp(dbname, "postgres") == 0)
-				appendPQExpBuffer(buf, "\\connect template1\n");
-			else
-				appendPQExpBuffer(buf, "\\connect postgres\n");
-
-			appendPQExpBuffer(buf, "ALTER DATABASE %s SET TABLESPACE %s;\n",
-							  fdbname, fmtId(dbtablespace));
-
-			/* connect to original database */
-			appendPsqlMetaConnect(buf, dbname);
-		}
-
-		if (binary_upgrade)
-		{
-			appendPQExpBufferStr(buf, "-- For binary upgrade, set datfrozenxid and datminmxid.\n");
-			appendPQExpBuffer(buf, "UPDATE pg_catalog.pg_database "
-							  "SET datfrozenxid = '%u', datminmxid = '%u' "
-							  "WHERE datname = ",
-							  dbfrozenxid, dbminmxid);
-			appendStringLiteralConn(buf, dbname, conn);
-			appendPQExpBufferStr(buf, ";\n");
-		}
-
-		if (!skip_acls &&
-			!buildACLCommands(fdbname, NULL, "DATABASE",
-							  dbacl, rdbacl, dbowner,
-							  "", server_version, buf))
-		{
-			fprintf(stderr, _("%s: could not parse ACL list (%s) for database \"%s\"\n"),
-					progname, dbacl, fdbname);
-			PQfinish(conn);
-			exit_nicely(1);
-		}
-
-		fprintf(OPF, "%s", buf->data);
-
-		dumpDatabaseConfig(conn, dbname);
-
-		free(fdbname);
-	}
-
-	if (default_encoding)
-		free(default_encoding);
-	if (default_collate)
-		free(default_collate);
-	if (default_ctype)
-		free(default_ctype);
-
-	PQclear(res);
-	destroyPQExpBuffer(buf);
-
-	fprintf(OPF, "\n\n");
-}
-
-
-/*
- * Dump database-specific configuration
- */
-static void
-dumpDatabaseConfig(PGconn *conn, const char *dbname)
-{
-	PQExpBuffer buf = createPQExpBuffer();
-	int			count = 1;
-
-	for (;;)
-	{
-		PGresult   *res;
-
-		if (server_version >= 90000)
-			printfPQExpBuffer(buf, "SELECT setconfig[%d] FROM pg_db_role_setting WHERE "
-							  "setrole = 0 AND setdatabase = (SELECT oid FROM pg_database WHERE datname = ", count);
-		else
-			printfPQExpBuffer(buf, "SELECT datconfig[%d] FROM pg_database WHERE datname = ", count);
-		appendStringLiteralConn(buf, dbname, conn);
-
-		if (server_version >= 90000)
-			appendPQExpBufferChar(buf, ')');
-
-		res = executeQuery(conn, buf->data);
-		if (PQntuples(res) == 1 &&
-			!PQgetisnull(res, 0, 0))
-		{
-			makeAlterConfigCommand(conn, PQgetvalue(res, 0, 0),
-								   "DATABASE", dbname, NULL, NULL);
-			PQclear(res);
-			count++;
-		}
-		else
-		{
-			PQclear(res);
-			break;
-		}
-	}
-
-	destroyPQExpBuffer(buf);
-}
-
-
 
 /*
  * Dump user-specific configuration
@@ -1646,11 +1308,14 @@ dumpUserConfig(PGconn *conn, const char *username)
 			appendPQExpBufferChar(buf, ')');
 
 		res = executeQuery(conn, buf->data);
+		resetPQExpBuffer(buf);
+
 		if (PQntuples(res) == 1 &&
 			!PQgetisnull(res, 0, 0))
 		{
 			makeAlterConfigCommand(conn, PQgetvalue(res, 0, 0),
-								   "ROLE", username, NULL, NULL);
+								   "ROLE", username, NULL, NULL, buf);
+			fprintf(OPF, "%s", buf->data);
 			PQclear(res);
 			count++;
 		}
@@ -1665,85 +1330,6 @@ dumpUserConfig(PGconn *conn, const char *username)
 }
 
 
-/*
- * Dump user-and-database-specific configuration
- */
-static void
-dumpDbRoleConfig(PGconn *conn)
-{
-	PQExpBuffer buf = createPQExpBuffer();
-	PGresult   *res;
-	int			i;
-
-	printfPQExpBuffer(buf, "SELECT rolname, datname, unnest(setconfig) "
-					  "FROM pg_db_role_setting, %s u, pg_database "
-					  "WHERE setrole = u.oid AND setdatabase = pg_database.oid", role_catalog);
-	res = executeQuery(conn, buf->data);
-
-	if (PQntuples(res) > 0)
-	{
-		fprintf(OPF, "--\n-- Per-Database Role Settings \n--\n\n");
-
-		for (i = 0; i < PQntuples(res); i++)
-		{
-			makeAlterConfigCommand(conn, PQgetvalue(res, i, 2),
-								   "ROLE", PQgetvalue(res, i, 0),
-								   "DATABASE", PQgetvalue(res, i, 1));
-		}
-
-		fprintf(OPF, "\n\n");
-	}
-
-	PQclear(res);
-	destroyPQExpBuffer(buf);
-}
-
-
-/*
- * Helper function for dumpXXXConfig().
- */
-static void
-makeAlterConfigCommand(PGconn *conn, const char *arrayitem,
-					   const char *type, const char *name,
-					   const char *type2, const char *name2)
-{
-	char	   *pos;
-	char	   *mine;
-	PQExpBuffer buf;
-
-	mine = pg_strdup(arrayitem);
-	pos = strchr(mine, '=');
-	if (pos == NULL)
-	{
-		free(mine);
-		return;
-	}
-
-	buf = createPQExpBuffer();
-
-	*pos = 0;
-	appendPQExpBuffer(buf, "ALTER %s %s ", type, fmtId(name));
-	if (type2 != NULL && name2 != NULL)
-		appendPQExpBuffer(buf, "IN %s %s ", type2, fmtId(name2));
-	appendPQExpBuffer(buf, "SET %s TO ", fmtId(mine));
-
-	/*
-	 * Some GUC variable names are 'LIST' type and hence must not be quoted.
-	 */
-	if (pg_strcasecmp(mine, "DateStyle") == 0
-		|| pg_strcasecmp(mine, "search_path") == 0)
-		appendPQExpBufferStr(buf, pos + 1);
-	else
-		appendStringLiteralConn(buf, pos + 1, conn);
-	appendPQExpBufferStr(buf, ";\n");
-
-	fprintf(OPF, "%s", buf->data);
-	destroyPQExpBuffer(buf);
-	free(mine);
-}
-
-
-
 /*
  * Dump contents of databases.
  */
@@ -1758,29 +1344,11 @@ dumpDatabases(PGconn *conn)
 	for (i = 0; i < PQntuples(res); i++)
 	{
 		int			ret;
-
 		char	   *dbname = PQgetvalue(res, i, 0);
-		PQExpBufferData connectbuf;
 
-		if (verbose)
+		if (g_verbose)
 			fprintf(stderr, _("%s: dumping database \"%s\"...\n"), progname, dbname);
 
-		initPQExpBuffer(&connectbuf);
-		appendPsqlMetaConnect(&connectbuf, dbname);
-		fprintf(OPF, "%s\n", connectbuf.data);
-		termPQExpBuffer(&connectbuf);
-
-		/*
-		 * Restore will need to write to the target cluster.  This connection
-		 * setting is emitted for pg_dumpall rather than in the code also used
-		 * by pg_dump, so that a cluster with databases or users which have
-		 * this flag turned on can still be replicated through pg_dumpall
-		 * without editing the file or stream.  With pg_dump there are many
-		 * other ways to allow the file to be used, and leaving it out allows
-		 * users to protect databases from being accidental restore targets.
-		 */
-		fprintf(OPF, "SET default_transaction_read_only = off;\n\n");
-
 		if (filename)
 			fclose(OPF);
 
@@ -1822,6 +1390,18 @@ runPgDump(const char *dbname)
 	appendPQExpBuffer(cmd, "\"%s\" %s", pg_dump_bin,
 					  pgdumpopts->data);
 
+
+	/*
+	 * The create individual database dump, pass the the create database option
+	 * to pg_dump. But skip generating create database commands for default
+	 * databases (template1, postgres).
+	 */
+	if (strcmp(dbname, "template1") == 0 || strcmp(dbname, "postgres") == 0)
+		appendPQExpBufferStr(cmd, " --set-db-properties ");
+	else
+		appendPQExpBufferStr(cmd, " -C ");
+
+
 	/*
 	 * If we have a filename, use the undocumented plain-append pg_dump
 	 * format.
@@ -1840,7 +1420,7 @@ runPgDump(const char *dbname)
 
 	appendShellString(cmd, connstrbuf->data);
 
-	if (verbose)
+	if (g_verbose)
 		fprintf(stderr, _("%s: running \"%s\"\n"), progname, cmd->data);
 
 	fflush(stdout);
@@ -2133,31 +1713,7 @@ constructConnStr(const char **keywords, const char **values)
 	return connstr;
 }
 
-/*
- * Run a query, return the results, exit program on failure.
- */
-static PGresult *
-executeQuery(PGconn *conn, const char *query)
-{
-	PGresult   *res;
 
-	if (verbose)
-		fprintf(stderr, _("%s: executing %s\n"), progname, query);
-
-	res = PQexec(conn, query);
-	if (!res ||
-		PQresultStatus(res) != PGRES_TUPLES_OK)
-	{
-		fprintf(stderr, _("%s: query failed: %s"),
-				progname, PQerrorMessage(conn));
-		fprintf(stderr, _("%s: query was: %s\n"),
-				progname, query);
-		PQfinish(conn);
-		exit_nicely(1);
-	}
-
-	return res;
-}
 
 /*
  * As above for a SQL command (which returns nothing).
@@ -2167,7 +1723,7 @@ executeCommand(PGconn *conn, const char *query)
 {
 	PGresult   *res;
 
-	if (verbose)
+	if (g_verbose)
 		fprintf(stderr, _("%s: executing %s\n"), progname, query);
 
 	res = PQexec(conn, query);
diff --git a/src/bin/pg_upgrade/dump.c b/src/bin/pg_upgrade/dump.c
index 8a662e9865..276262475c 100644
--- a/src/bin/pg_upgrade/dump.c
+++ b/src/bin/pg_upgrade/dump.c
@@ -48,6 +48,7 @@ generate_old_dump(void)
 		DbInfo	   *old_db = &old_cluster.dbarr.dbs[dbnum];
 		PQExpBufferData connstr,
 					escaped_connstr;
+		PQExpBuffer create_opts = createPQExpBuffer();
 
 		initPQExpBuffer(&connstr);
 		appendPQExpBuffer(&connstr, "dbname=");
@@ -60,13 +61,20 @@ generate_old_dump(void)
 		snprintf(sql_file_name, sizeof(sql_file_name), DB_DUMP_FILE_MASK, old_db->db_oid);
 		snprintf(log_file_name, sizeof(log_file_name), DB_DUMP_LOG_FILE_MASK, old_db->db_oid);
 
+		if (strcmp(old_db->db_name, "template1") == 0 || strcmp(old_db->db_name, "postgres") == 0)
+			appendPQExpBufferStr(create_opts, " --set-db-properties ");
+		else
+			appendPQExpBufferStr(create_opts, " -C ");
+
 		parallel_exec_prog(log_file_name, NULL,
 						   "\"%s/pg_dump\" %s --schema-only --quote-all-identifiers "
-						   "--binary-upgrade --format=custom %s --file=\"%s\" %s",
+						   "--binary-upgrade %s "
+						   "--format=custom %s --file=\"%s\" %s",
 						   new_cluster.bindir, cluster_conn_opts(&old_cluster),
-						   log_opts.verbose ? "--verbose" : "",
+						   create_opts->data, log_opts.verbose ? "--verbose" : "",
 						   sql_file_name, escaped_connstr.data);
 
+		termPQExpBuffer(create_opts);
 		termPQExpBuffer(&escaped_connstr);
 	}
 
diff --git a/src/bin/pg_upgrade/pg_upgrade.c b/src/bin/pg_upgrade/pg_upgrade.c
index 872621489f..c233e68dbd 100644
--- a/src/bin/pg_upgrade/pg_upgrade.c
+++ b/src/bin/pg_upgrade/pg_upgrade.c
@@ -46,7 +46,7 @@
 #endif
 
 static void prepare_new_cluster(void);
-static void prepare_new_databases(void);
+static void prepare_new_globals(void);
 static void create_new_objects(void);
 static void copy_xact_xlog_xid(void);
 static void set_frozenxids(bool minmxid_only);
@@ -124,7 +124,7 @@ main(int argc, char **argv)
 	/* -- NEW -- */
 	start_postmaster(&new_cluster, true);
 
-	prepare_new_databases();
+	prepare_new_globals();
 
 	create_new_objects();
 
@@ -271,7 +271,7 @@ prepare_new_cluster(void)
 
 
 static void
-prepare_new_databases(void)
+prepare_new_globals(void)
 {
 	/*
 	 * We set autovacuum_freeze_max_age to its maximum value so autovacuum
@@ -294,9 +294,6 @@ prepare_new_databases(void)
 			  new_cluster.bindir, cluster_conn_opts(&new_cluster),
 			  GLOBALS_DUMP_FILE);
 	check_ok();
-
-	/* we load this to get a current list of databases */
-	get_db_and_rel_infos(&new_cluster);
 }
 
 
@@ -312,15 +309,6 @@ create_new_objects(void)
 		char		sql_file_name[MAXPGPATH],
 					log_file_name[MAXPGPATH];
 		DbInfo	   *old_db = &old_cluster.dbarr.dbs[dbnum];
-		PQExpBufferData connstr,
-					escaped_connstr;
-
-		initPQExpBuffer(&connstr);
-		appendPQExpBuffer(&connstr, "dbname=");
-		appendConnStrVal(&connstr, old_db->db_name);
-		initPQExpBuffer(&escaped_connstr);
-		appendShellString(&escaped_connstr, connstr.data);
-		termPQExpBuffer(&connstr);
 
 		pg_log(PG_STATUS, "%s", old_db->db_name);
 		snprintf(sql_file_name, sizeof(sql_file_name), DB_DUMP_FILE_MASK, old_db->db_oid);
@@ -332,13 +320,10 @@ create_new_objects(void)
 		 */
 		parallel_exec_prog(log_file_name,
 						   NULL,
-						   "\"%s/pg_restore\" %s --exit-on-error --verbose --dbname %s \"%s\"",
+						   "\"%s/pg_restore\" %s --create --exit-on-error --verbose --dbname template1 \"%s\"",
 						   new_cluster.bindir,
 						   cluster_conn_opts(&new_cluster),
-						   escaped_connstr.data,
 						   sql_file_name);
-
-		termPQExpBuffer(&escaped_connstr);
 	}
 
 	/* reap all children */
-- 
2.15.0.windows.1