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
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;
