public inbox for [email protected]
help / color / mirror / Atom feedFrom: Jan Wieck <[email protected]>
To: Tom Lane <[email protected]>
To: Magnus Hagander <[email protected]>
Cc: Robins Tharakan <[email protected]>
Cc: Peter Eisentraut <[email protected]>
Cc: [email protected] <[email protected]>
Subject: Fix pg_upgrade to preserve datdba (was: Re: pg_upgrade failing for 200+ million Large Objects)
Date: Sun, 21 Mar 2021 12:50:46 -0400
Message-ID: <[email protected]> (raw)
In-Reply-To: <[email protected]>
References: <[email protected]>
<CABUevEwyLb9VE0D+bAQtUnaA7bffXYzBpopYuh7kGTQxY9T5_g@mail.gmail.com>
<CAEP4nAw2WA1wyb9LG7BOEuN3Xr-xWiZZ0w_hKtpyvdUPKmcAJA@mail.gmail.com>
<[email protected]>
<CABUevEzvU07CqwGdaOmxNfDtrkY-xEcLjiN3GAmurowyCnbG7w@mail.gmail.com>
<[email protected]>
<[email protected]>
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
Attachments:
[text/x-patch] pg_upgrade-preserve-datdba.v1.diff (3.6K, 2-pg_upgrade-preserve-datdba.v1.diff)
download | inline 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;
view thread (43+ messages) latest in thread
reply
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: [email protected]
Cc: [email protected], [email protected], [email protected], [email protected], [email protected]
Subject: Re: Fix pg_upgrade to preserve datdba (was: Re: pg_upgrade failing for 200+ million Large Objects)
In-Reply-To: <[email protected]>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox