RE: [EXTERNAL] Re: BUG #16131: pg_upgrade 9 -> 11.6 and a database is missing

I'd close this off. All the production upgrades are complete and working fine. So far, it doesn't seem I'll get time on the test machine to try to bring up 9 and upgrade again.

-----Original Message-----
From: 'Bruce Momjian' <bruce(at)momjian(dot)us>
Sent: Tuesday, December 17, 2019 5:18 PM
To: jedwa(at)comcast(dot)net
Cc: pgsql-bugs(at)lists(dot)postgresql(dot)org; Walker, Jared (Contractor) <Jared_Walker2(at)comcast(dot)com>
Subject: [EXTERNAL] Re: BUG #16131: pg_upgrade 9 -> 11.6 and a database is missing

On Tue, Dec 17, 2019 at 04:58:28PM -0700, jedwa(at)comcast(dot)net wrote:
> It only happened on one system and it was a 9.2. I've done three other
> test systems (9.2 and 9.3 to 11.6 now) and one of the production and
> not seen it again.
> I would have to ask for time to roll the system back and try it again,
> and in my company's brilliance (as a contractor) I'll be furloughed in
> a day til after New Year's.
> I would love to figure this out, but if you haven't seen it from
> anyone else I'm guessing it was a one time oddity.

Yeah, it is hard to justify a rollback.

> Did I send you the logs? (I didn't see anything in them)

NO.

> If they will let me I might use my own time to rollback and try again
> and will let you know.

OK, thanks. I just can't imagine how this would skip an entire database.

---------------------------------------------------------------------------

> Thanks,
> Jed
>
>
>
> -----Original Message-----
> From: Bruce Momjian <bruce(at)momjian(dot)us>
> Sent: Tuesday, December 17, 2019 4:54 PM
> To: jedwa(at)comcast(dot)net; pgsql-bugs(at)lists(dot)postgresql(dot)org
> Subject: Re: BUG #16131: pg_upgrade 9 -> 11.6 and a database is
> missing
>
>
> Sorry for the late reply, but I have no idea what would cause this. I
> don't think the 11.5->11.6 changes would have. Is there any chance
> you can re-run the upgrade to test if it happens again?
>
> ----------------------------------------------------------------------
> -----
>
> On Thu, Nov 21, 2019 at 07:01:07PM +0000, PG Bug reporting form wrote:
> > The following bug has been logged on the website:
> >
> > Bug reference: 16131
> > Logged by: Jed Walker
> > Email address: jedwa(at)comcast(dot)net
> > PostgreSQL version: 11.6
> > Operating system: CentOS Linux release 7.7.1908 (Core)
> > Description:
> >
> > I did an upgrade of two matching servers, one from 9.2 -> 11.5 and
> > had no problems, but yesterday I did the other 9.2 -> 11.6 and a
> > database and user were missing from the upgraded database.
> >
> > I saw no errors, other than the unix_socket_directories issue, but
> > one of our databases is not in the new 11 server. We had a similar
> > system with the same databases that I upgraded a week ago and all
> > databases came
> across.
> > There is no reference to this database in the upgrade logs.
> > The key difference between the system that I did previously that
> > worked and this is that the one that worked was 9.2 -> 11.5, and
> > this was 9.2 -> 11.6
> >
> > Upgrade process:
> > yum install -y
> > https://urldefense.com/v3/__https://download.postgresql.org/pub/repo
> > s/yum/reporpms/EL-7-x86_64/pgd__;!!CQl3mcHX2A!T1gOOs5VFnYCFBEvK2ZJ4z
> > 2yW9Xa1zKWtAmOHeWpC2tQVEDLHxB6fMk-psMEIKN3yRmzcg$
> > g-redhat-repo-latest.noarch.rpm
> > yum install -y postgresql11
> > yum install -y postgresql11-server
> > shutdown -r now
> > systemctl stop postgresql
> > /usr/pgsql-11/bin/postgresql-11-setup initdb
> > /usr/pgsql-11/bin/pg_upgrade -v -r -d /var/lib/pgsql/data -D
> > /var/lib/pgsql/11/data -b "/usr/bin" -B "/usr/pgsql-11/bin"
> > systemctl disable postgresql
> > systemctl enable postgresql-11
> > systemctl start postgresql-11
> > ~/analyze_new_cluster.sh
> >
> > These are the databases, after upgrade database wasp and user
> > wasp_user were missing.
> > postgres=# \l
> > List of databases
> > Name | Owner | Encoding | Collate | Ctype | Access
> > privileges
> > -----------+-----------+----------+-------------+-------------+-----
> > -----------+-----------+----------+-------------+-------------+--
> > -----------+-----------+----------+-------------+-------------+-----
> > -----------+-----------+----------+-------------+-------------+--
> > -----------+-----------+----------+-------------+-------------+-----
> > -----------+-----------+----------+-------------+-------------+--
> > -----------+-----------+----------+-------------+-------------+----
> > nasdb | nasuser | UTF8 | en_US.UTF-8 | en_US.UTF-8 |
> > postgres | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 |
> > template0 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 |
> =c/postgres
> > +
> > | | | | |
> > postgres=CTc/postgres
> > template1 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 |
> > postgres=CTc/postgres +
> > | | | | |
> > =c/postgres
> > wasp | wasp_user | UTF8 | en_US.UTF-8 | en_US.UTF-8 |
> > =Tc/wasp_user +
> > | | | | |
> > wasp_user=CTc/wasp_user
> >
> > The users are using the system, but I'd be happy to send logs etc.
> >
>
>
> --
> Bruce Momjian <bruce(at)momjian(dot)us> https://urldefense.com/v3/__http://momjian.us__;!!CQl3mcHX2A!T1gOOs5VFnYCFBEvK2ZJ4z2yW9Xa1zKWtAmOHeWpC2tQVEDLHxB6fMk-psMEIKM-5eRdow$
> EnterpriseDB https://urldefense.com/v3/__http://enterprisedb.com__;!!CQl3mcHX2A!T1gOOs5VFnYCFBEvK2ZJ4z2yW9Xa1zKWtAmOHeWpC2tQVEDLHxB6fMk-psMEIKN1DMo_Ug$
>
> + As you are, so once was I. As I am, so you will be. +
> + Ancient Roman grave inscription +
>

--
Bruce Momjian <bruce(at)momjian(dot)us> https://urldefense.com/v3/__http://momjian.us__;!!CQl3mcHX2A!T1gOOs5VFnYCFBEvK2ZJ4z2yW9Xa1zKWtAmOHeWpC2tQVEDLHxB6fMk-psMEIKM-5eRdow$
EnterpriseDB https://urldefense.com/v3/__http://enterprisedb.com__;!!CQl3mcHX2A!T1gOOs5VFnYCFBEvK2ZJ4z2yW9Xa1zKWtAmOHeWpC2tQVEDLHxB6fMk-psMEIKN1DMo_Ug$

+ As you are, so once was I. As I am, so you will be. +
+ Ancient Roman grave inscription +