RE: [EXTERNAL] Re: BUG #16131: pg_upgrade 9 -> 11.6 and a database ismissing - Mailing list pgsql-bugs

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
thetest machine to try to bring up 9 and upgrade again. 

-----Original Message-----
From: 'Bruce Momjian' <bruce@momjian.us>
Sent: Tuesday, December 17, 2019 5:18 PM
To: jedwa@comcast.net
Cc: pgsql-bugs@lists.postgresql.org; Walker, Jared (Contractor) <Jared_Walker2@comcast.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@comcast.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@momjian.us>
> Sent: Tuesday, December 17, 2019 4:54 PM
> To: jedwa@comcast.net; pgsql-bugs@lists.postgresql.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@comcast.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@momjian.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@momjian.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 +