Subject: Fix pg_upgrade to preserve datdba (was: Re: pg_upgrade failing for
 200+ million Large Objects)
From: Jan Wieck <jan@wi3ck.info>
To: Tom Lane <tgl@sss.pgh.pa.us>, Magnus Hagander <magnus@hagander.net>
Cc: Robins Tharakan <tharakan@gmail.com>,
 Peter Eisentraut <peter.eisentraut@enterprisedb.com>,
 "pgsql-hackers@postgresql.org" <pgsql-hackers@postgresql.org>
References: <a9f9376f1c3343a6bb319dce294e20ac@EX13D05UWC001.ant.amazon.com>
 <CABUevEwyLb9VE0D+bAQtUnaA7bffXYzBpopYuh7kGTQxY9T5_g@mail.gmail.com>
 <CAEP4nAw2WA1wyb9LG7BOEuN3Xr-xWiZZ0w_hKtpyvdUPKmcAJA@mail.gmail.com>
 <1742698.1615221182@sss.pgh.pa.us>
 <CABUevEzvU07CqwGdaOmxNfDtrkY-xEcLjiN3GAmurowyCnbG7w@mail.gmail.com>
 <1743618.1615222719@sss.pgh.pa.us>
 <4fbf92f2-ec91-f4fa-a259-f0968e34f3d7@wi3ck.info>
Message-ID: <0b5d506d-70d2-1506-bbdf-89e6453c0289@wi3ck.info>
Date: Sun, 21 Mar 2021 12:50:46 -0400
User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:78.0) Gecko/20100101
 Thunderbird/78.8.0
MIME-Version: 1.0
In-Reply-To: <4fbf92f2-ec91-f4fa-a259-f0968e34f3d7@wi3ck.info>
Content-Type: multipart/mixed;
 boundary="------------D5C941EC94A28ED26792F499"
Content-Language: en-US
Archived-At: 
 <https://www.postgresql.org/message-id/0b5d506d-70d2-1506-bbdf-89e6453c0289%40wi3ck.info>
Precedence: bulk

This is a multi-part message in MIME format.
--------------D5C941EC94A28ED26792F499
Content-Type: text/plain; charset=utf-8; format=flowed
Content-Transfer-Encoding: 7bit

On 3/20/21 12:39 AM, Jan Wieck wrote:
> On the way pg_upgrade also mangles the pg_database.datdba
> (all databases are owned by postgres after an upgrade; will submit a
> separate patch for that as I consider that a bug by itself).

Patch attached.


Regards, Jan

-- 
Jan Wieck
Principle Database Engineer
Amazon Web Services

--------------D5C941EC94A28ED26792F499
Content-Type: text/x-patch; charset=UTF-8;
 name="pg_upgrade-preserve-datdba.v1.diff"
Content-Transfer-Encoding: 7bit
Content-Disposition: attachment;
 filename="pg_upgrade-preserve-datdba.v1.diff"

diff --git a/src/bin/pg_upgrade/info.c b/src/bin/pg_upgrade/info.c
index 5d9a26c..38f7202 100644
--- a/src/bin/pg_upgrade/info.c
+++ b/src/bin/pg_upgrade/info.c
@@ -344,6 +344,7 @@ get_db_infos(ClusterInfo *cluster)
 	DbInfo	   *dbinfos;
 	int			i_datname,
 				i_oid,
+				i_datdba,
 				i_encoding,
 				i_datcollate,
 				i_datctype,
@@ -351,9 +352,12 @@ get_db_infos(ClusterInfo *cluster)
 	char		query[QUERY_ALLOC];
 
 	snprintf(query, sizeof(query),
-			 "SELECT d.oid, d.datname, d.encoding, d.datcollate, d.datctype, "
+			 "SELECT d.oid, d.datname, u.rolname, d.encoding, "
+			 "d.datcollate, d.datctype, "
 			 "%s AS spclocation "
 			 "FROM pg_catalog.pg_database d "
+			 " JOIN pg_catalog.pg_authid u "
+			 " ON d.datdba = u.oid "
 			 " LEFT OUTER JOIN pg_catalog.pg_tablespace t "
 			 " ON d.dattablespace = t.oid "
 			 "WHERE d.datallowconn = true "
@@ -367,6 +371,7 @@ get_db_infos(ClusterInfo *cluster)
 
 	i_oid = PQfnumber(res, "oid");
 	i_datname = PQfnumber(res, "datname");
+	i_datdba = PQfnumber(res, "rolname");
 	i_encoding = PQfnumber(res, "encoding");
 	i_datcollate = PQfnumber(res, "datcollate");
 	i_datctype = PQfnumber(res, "datctype");
@@ -379,6 +384,7 @@ get_db_infos(ClusterInfo *cluster)
 	{
 		dbinfos[tupnum].db_oid = atooid(PQgetvalue(res, tupnum, i_oid));
 		dbinfos[tupnum].db_name = pg_strdup(PQgetvalue(res, tupnum, i_datname));
+		dbinfos[tupnum].db_owner = pg_strdup(PQgetvalue(res, tupnum, i_datdba));
 		dbinfos[tupnum].db_encoding = atoi(PQgetvalue(res, tupnum, i_encoding));
 		dbinfos[tupnum].db_collate = pg_strdup(PQgetvalue(res, tupnum, i_datcollate));
 		dbinfos[tupnum].db_ctype = pg_strdup(PQgetvalue(res, tupnum, i_datctype));
diff --git a/src/bin/pg_upgrade/pg_upgrade.c b/src/bin/pg_upgrade/pg_upgrade.c
index e23b8ca..8fd9a13 100644
--- a/src/bin/pg_upgrade/pg_upgrade.c
+++ b/src/bin/pg_upgrade/pg_upgrade.c
@@ -378,18 +378,36 @@ create_new_objects(void)
 		 * propagate its database-level properties.
 		 */
 		if (strcmp(old_db->db_name, "postgres") == 0)
-			create_opts = "--clean --create";
+		{
+			parallel_exec_prog(log_file_name,
+							   NULL,
+							   "\"%s/pg_restore\" %s --exit-on-error "
+							   "--verbose --clean --create "
+							   "--dbname template1 \"%s\"",
+							   new_cluster.bindir,
+							   cluster_conn_opts(&new_cluster),
+							   sql_file_name);
+		}
 		else
-			create_opts = "--create";
-
-		parallel_exec_prog(log_file_name,
-						   NULL,
-						   "\"%s/pg_restore\" %s %s --exit-on-error --verbose "
-						   "--dbname template1 \"%s\"",
-						   new_cluster.bindir,
-						   cluster_conn_opts(&new_cluster),
-						   create_opts,
-						   sql_file_name);
+		{
+			exec_prog(log_file_name, NULL, true, true,
+					  "\"%s/createdb\" -O \"%s\" %s \"%s\"",
+					  new_cluster.bindir,
+					  old_db->db_owner,
+					  cluster_conn_opts(&new_cluster),
+					  old_db->db_name);
+			parallel_exec_prog(log_file_name,
+							   NULL,
+							   "\"%s/pg_restore\" %s --exit-on-error "
+							   "--verbose "
+							   "--dbname \"%s\" \"%s\"",
+							   new_cluster.bindir,
+							   cluster_conn_opts(&new_cluster),
+							   old_db->db_name,
+							   sql_file_name);
+		}
+
+
 	}
 
 	/* reap all children */
diff --git a/src/bin/pg_upgrade/pg_upgrade.h b/src/bin/pg_upgrade/pg_upgrade.h
index 919a784..a3cda97 100644
--- a/src/bin/pg_upgrade/pg_upgrade.h
+++ b/src/bin/pg_upgrade/pg_upgrade.h
@@ -177,6 +177,7 @@ typedef struct
 {
 	Oid			db_oid;			/* oid of the database */
 	char	   *db_name;		/* database name */
+	char	   *db_owner;		/* database owner */
 	char		db_tablespace[MAXPGPATH];	/* database default tablespace
 											 * path */
 	char	   *db_collate;

--------------D5C941EC94A28ED26792F499--