Subject: Re: Fix pg_upgrade to preserve datdba
From: Jan Wieck <jan@wi3ck.info>
To: Tom Lane <tgl@sss.pgh.pa.us>
Cc: Magnus Hagander <magnus@hagander.net>,
 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>
 <0b5d506d-70d2-1506-bbdf-89e6453c0289@wi3ck.info>
 <384713.1616345832@sss.pgh.pa.us>
 <2284b71d-af55-2625-f8c2-cedf1f1a70ca@wi3ck.info>
Message-ID: <ff77882d-1b1f-1735-402a-f4583f1b2f58@wi3ck.info>
Date: Sun, 21 Mar 2021 13:50:45 -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: <2284b71d-af55-2625-f8c2-cedf1f1a70ca@wi3ck.info>
Content-Type: multipart/mixed;
 boundary="------------405F13DB9E789D279F146CBA"
Content-Language: en-US
Archived-At: 
 <https://www.postgresql.org/message-id/ff77882d-1b1f-1735-402a-f4583f1b2f58%40wi3ck.info>
Precedence: bulk

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

On 3/21/21 1:15 PM, Jan Wieck wrote:
> On 3/21/21 12:57 PM, Tom Lane wrote:
>> Jan Wieck <jan@wi3ck.info> writes:
>>> 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.
>> 
>> Hmm, doesn't this lose all *other* database-level properties?
>> 
>> I think maybe what we have here is a bug in pg_restore, its
>> --create switch ought to be trying to update the database's
>> ownership.
> 
> Possibly. I didn't look into that route.

Thanks for that. I like this patch a lot better.


Regards, Jan

-- 
Jan Wieck
Principle Database Engineer
Amazon Web Services

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

diff --git a/src/bin/pg_dump/pg_dump.c b/src/bin/pg_dump/pg_dump.c
index f8bec3f..19c1e71 100644
--- a/src/bin/pg_dump/pg_dump.c
+++ b/src/bin/pg_dump/pg_dump.c
@@ -3030,6 +3030,8 @@ dumpDatabase(Archive *fout)
 	resetPQExpBuffer(creaQry);
 	resetPQExpBuffer(delQry);
 
+	appendPQExpBuffer(creaQry, "ALTER DATABASE %s OWNER TO %s;\n", qdatname, dba);
+
 	if (strlen(datconnlimit) > 0 && strcmp(datconnlimit, "-1") != 0)
 		appendPQExpBuffer(creaQry, "ALTER DATABASE %s CONNECTION LIMIT = %s;\n",
 						  qdatname, datconnlimit);

--------------405F13DB9E789D279F146CBA--