Thread: BUG #16131: pg_upgrade 9 -> 11.6 and a database is missing
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.
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 +
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 +
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 +
RE: [EXTERNAL] Re: BUG #16131: pg_upgrade 9 -> 11.6 and a database ismissing
From
"Walker, Jared (Contractor)"
Date:
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 +