Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.89) (envelope-from ) id 1ihMfW-0001jT-Kj for pgsql-bugs@arkaria.postgresql.org; Tue, 17 Dec 2019 23:54:22 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.89) (envelope-from ) id 1ihMfT-0003WB-OD for pgsql-bugs@arkaria.postgresql.org; Tue, 17 Dec 2019 23:54:19 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]) by malur.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.89) (envelope-from ) id 1ihMfT-0003UP-Ce for pgsql-bugs@lists.postgresql.org; Tue, 17 Dec 2019 23:54:19 +0000 Received: from momjian.us ([72.94.173.45]) by makus.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1ihMfM-00027C-JC for pgsql-bugs@lists.postgresql.org; Tue, 17 Dec 2019 23:54:18 +0000 Received: from bruce by momjian.us with local (Exim 4.92) (envelope-from ) id 1ihMfL-0001V7-HN; Tue, 17 Dec 2019 18:54:11 -0500 Date: Tue, 17 Dec 2019 18:54:11 -0500 From: Bruce Momjian To: jedwa@comcast.net, pgsql-bugs@lists.postgresql.org Subject: Re: BUG #16131: pg_upgrade 9 -> 11.6 and a database is missing Message-ID: <20191217235411.GC30116@momjian.us> References: <16131-953e6ab7cb264396@postgresql.org> MIME-Version: 1.0 Content-Type: text/plain; charset=us-ascii Content-Disposition: inline In-Reply-To: <16131-953e6ab7cb264396@postgresql.org> User-Agent: Mutt/1.10.1 (2018-07-13) List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Precedence: bulk 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 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 +