BUG #16131: pg_upgrade 9 -> 11.6 and a database is missing

Started by PG Bug reporting formover 6 years ago5 messagesbugs

noreply@postgresql.org

over 6 years ago

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://download.postgresql.org/pub/repos/yum/reporpms/EL-7-x86_64/pgdg-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

over 6 years ago

In reply to: PG Bug reporting form (#1)

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://download.postgresql.org/pub/repos/yum/reporpms/EL-7-x86_64/pgdg-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> http://momjian.us
EnterpriseDB http://enterprisedb.com

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

Jed Walker

jedwa@comcast.net

over 6 years ago

In reply to: Bruce Momjian (#2)

RE: BUG #16131: pg_upgrade 9 -> 11.6 and a database is missing

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.
Did I send you the logs? (I didn't see anything in them)
If they will let me I might use my own time to rollback and try again and
will let you know.
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://download.postgresql.org/pub/repos/yum/reporpms/EL-7-x86_64/pgd
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> http://momjian.us
EnterpriseDB http://enterprisedb.com

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

Bruce Momjian

bruce@momjian.us

over 6 years ago

In reply to: Jed Walker (#3)

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://download.postgresql.org/pub/repos/yum/reporpms/EL-7-x86_64/pgd
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> http://momjian.us
EnterpriseDB http://enterprisedb.com
+ As you are, so once was I.  As I am, so you will be. +
+                      Ancient Roman grave inscription +

--
Bruce Momjian <bruce@momjian.us> http://momjian.us
EnterpriseDB http://enterprisedb.com

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

Walker, Jared (Contractor)

Jared_Walker2@comcast.com

about 6 years ago

In reply to: Bruce Momjian (#4)

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@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 +