Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1nL4EZ-0007d8-8i for pgsql-admin@arkaria.postgresql.org; Fri, 18 Feb 2022 14:27:43 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1nL4EX-00036r-Rc for pgsql-admin@arkaria.postgresql.org; Fri, 18 Feb 2022 14:27:41 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1nL4EX-00036g-CB for pgsql-admin@lists.postgresql.org; Fri, 18 Feb 2022 14:27:41 +0000 Received: from relay8-d.mail.gandi.net ([217.70.183.201]) by magus.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1nL4ET-00070x-L2 for pgsql-admin@lists.postgresql.org; Fri, 18 Feb 2022 14:27:40 +0000 Received: (Authenticated sender: marco.mailbox@debooy.eu) by mail.gandi.net (Postfix) with ESMTPSA id B471C1BF208 for ; Fri, 18 Feb 2022 14:27:35 +0000 (UTC) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=debooy.eu; s=gm1; t=1645194455; h=from:from:reply-to:subject:subject:date:date:message-id:message-id: to:to:cc:mime-version:mime-version:content-type:content-type: in-reply-to:in-reply-to:references:references; bh=7ZL2VRJLtydQlOBx5ANA6Ae5++L+rFuXbB8sPAcaloU=; b=bQUpmstKRAtxzhk33i9PxRmJMZABygM1CSeztmV9KU9DO4tG2w3SXhr49d1kWz9IlJuQPI xe+1zWSsjGIfSrJBOP3G9jyZzhI7Xh0hmPG57bvgTivV8xlQd+Ta8SfipGuzbyRevpgYjc 45j+IV6Cn5kifoYwTgRXKk/94+rI0os5ZtAOtGwruDUzEXUCshAA+NU0Yk/knHPENSkBwy vdlXvbsym5fHu7UmISrj4FFkw6irr5rC1u+srau0IctUMecCvq5zZP5IZvm46uuI5LYdER 488osHc7q0hBWEv0J0O9+JvUC7/dGDhOz7I2FB1/zdMtwgK6Hb8MXVeal5kLiA== Content-Type: multipart/alternative; boundary="------------tR0j3ysrQaisGg96YjYABZSn" Message-ID: <0d7c8622-0ca2-a186-aa72-5829268275d2@debooy.eu> Date: Fri, 18 Feb 2022 15:27:34 +0100 MIME-Version: 1.0 User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:91.0) Gecko/20100101 Thunderbird/91.5.0 Subject: Re: pg_upgrade from Postgresql-12 to Postgresql-13 fails with "Creating dump of database schemas postgres *failure*" Content-Language: nl To: pgsql-admin@lists.postgresql.org References: From: Marco DE BOOIJ In-Reply-To: List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk This is a multi-part message in MIME format. --------------tR0j3ysrQaisGg96YjYABZSn Content-Type: text/plain; charset=UTF-8; format=flowed Content-Transfer-Encoding: 8bit In the past I also used pg_upgrade but then somebody advised me to use pg_upgradecluster. Since then I use this tool. pg_dropcluster main pg_upgradecluster main /bnas/pgdata/postgresql/ pg_dropcluster main Regards, Marco Op 18/02/2022 om 06:17 schreef Yoong S. Chow: > > Hi I hope I send this to the right mailing list and able to get some > help. > > > I was trying to perform `pg_upgrade` from Postgresql-12 to > Postgresql-13 (after being advised that upgrading from Postgresql-12 > to Postgresql-14 is biting off much more than I could chew... ). > > The `pg_upgrade --check` went through OK. But the actual `pg_upgrade` > always get error at the step of /Creating dump of database > schemas/ for `postgres` database. > > ``` > > /usr/lib/postgresql/13/bin/pg_upgrade > --old-datadir=/bnas/pgdata/postgresql/12/main > --new-datadir=/bnas/pgdata/postgresql/13/main > --old-bindir=/usr/lib/postgresql/12/bin > --new-bindir=/usr/lib/postgresql/13/bin --old-options '-c > config_file=/etc/postgresql/12/main/postgresql.conf' --new-options '-c > config_file=/etc/postgresql/13/main/postgresql.conf' --check > > Performing Consistency Checks > > ----------------------------- > > Checking cluster versions ok > > Checking database user is the install user ok > > Checking database connection settings ok > > Checking for prepared transactions ok > > Checking for system-defined composite types in user tables ok > > Checking for reg* data types in user tables ok > > Checking for contrib/isn with bigint-passing mismatch ok > > Checking for presence of required libraries ok > > Checking database user is the install user ok > > Checking for prepared transactions ok > > Checking for new cluster tablespace directories ok > > > *Clusters are compatible* > > postgres@ual:~$ /usr/lib/postgresql/13/bin/pg_upgrade > --old-datadir=/bnas/pgdata/postgresql/12/main > --new-datadir=/bnas/pgdata/postgresql/13/main > --old-bindir=/usr/lib/postgresql/12/bin > --new-bindir=/usr/lib/postgresql/13/bin --old-options '-c > config_file=/etc/postgresql/12/main/postgresql.conf' --new-options '-c > config_file=/etc/postgresql/13/main/postgresql.conf' > > Performing Consistency Checks > > ----------------------------- > > Checking cluster versions ok > > Checking database user is the install user ok > > Checking database connection settings ok > > Checking for prepared transactions ok > > Checking for system-defined composite types in user tables ok > > Checking for reg* data types in user tables ok > > Checking for contrib/isn with bigint-passing mismatch ok > > Creating dump of global objects ok > > Creating dump of database schemas > > postgres > > *failure* > > > Consult the last few lines of "pg_upgrade_dump_13427.log" for > > the probable cause of the failure. > > Failure, exiting > > ``` > > Here are the logs: pg_upgrade_server.log > , pg_upgrade_dump_13427.log >  and > tail_postgresql-12-2022-02-16_190344.log . > > Additional information: > > 1. > > An observation on `pg_upgrade --check`. I could not run actual > `pg_upgrade` directly after the `--check` operation as I will > always get some `server not shut down properly` error. > > 2. > > I have PostGIS and a couple other audit/monitoring extensions > installed on Postgresql-12. I have installed the similar Ubuntu > PostGIS packages for Postgresql-13. Do I need to run `CREATE > EXTENSIONS xxx` before performing the `pg_upgrade`? > > Have been stuck with this for over a week now, and sincerely hope > someone could help me. TIA. > > chow > --------------tR0j3ysrQaisGg96YjYABZSn Content-Type: text/html; charset=UTF-8 Content-Transfer-Encoding: 8bit

In the past I also used pg_upgrade but then somebody advised me to use pg_upgradecluster. Since then I use this tool.

pg_dropcluster <new version> main
pg_upgradecluster <previous version> main /bnas/pgdata/postgresql/<new version>
pg_dropcluster <new version> main

Regards,

Marco

Op 18/02/2022 om 06:17 schreef Yoong S. Chow:

Hi I hope I send this to the right mailing list and able to get some help. 


I was trying to perform `pg_upgrade` from Postgresql-12 to Postgresql-13 (after being advised that upgrading from Postgresql-12 to Postgresql-14 is biting off much more than I could chew... ).

The `pg_upgrade --check` went through OK. But the actual `pg_upgrade` always get error at the step of Creating dump of database schemas for `postgres` database.

```

/usr/lib/postgresql/13/bin/pg_upgrade --old-datadir=/bnas/pgdata/postgresql/12/main --new-datadir=/bnas/pgdata/postgresql/13/main --old-bindir=/usr/lib/postgresql/12/bin --new-bindir=/usr/lib/postgresql/13/bin --old-options '-c config_file=/etc/postgresql/12/main/postgresql.conf' --new-options '-c config_file=/etc/postgresql/13/main/postgresql.conf' --check

Performing Consistency Checks

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

Checking cluster versions ok

Checking database user is the install user ok

Checking database connection settings ok

Checking for prepared transactions ok

Checking for system-defined composite types in user tables ok

Checking for reg* data types in user tables ok

Checking for contrib/isn with bigint-passing mismatch ok

Checking for presence of required libraries ok

Checking database user is the install user ok

Checking for prepared transactions ok

Checking for new cluster tablespace directories ok


*Clusters are compatible*

postgres@ual:~$ /usr/lib/postgresql/13/bin/pg_upgrade --old-datadir=/bnas/pgdata/postgresql/12/main --new-datadir=/bnas/pgdata/postgresql/13/main --old-bindir=/usr/lib/postgresql/12/bin --new-bindir=/usr/lib/postgresql/13/bin --old-options '-c config_file=/etc/postgresql/12/main/postgresql.conf' --new-options '-c config_file=/etc/postgresql/13/main/postgresql.conf'

Performing Consistency Checks

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

Checking cluster versions ok

Checking database user is the install user ok

Checking database connection settings ok

Checking for prepared transactions ok

Checking for system-defined composite types in user tables ok

Checking for reg* data types in user tables ok

Checking for contrib/isn with bigint-passing mismatch ok

Creating dump of global objects ok

Creating dump of database schemas

postgres

*failure*


Consult the last few lines of "pg_upgrade_dump_13427.log" for

the probable cause of the failure.

Failure, exiting

```

Here are the logs: pg_upgrade_server.log, pg_upgrade_dump_13427.log and tail_postgresql-12-2022-02-16_190344.log.

Additional information:

  1. An observation on `pg_upgrade --check`. I could not run actual `pg_upgrade` directly after the `--check` operation as I will always get some `server not shut down properly` error.

  2. I have PostGIS and a couple other audit/monitoring extensions installed on Postgresql-12. I have installed the similar Ubuntu PostGIS packages for Postgresql-13. Do I need to run `CREATE EXTENSIONS xxx` before performing the `pg_upgrade`?

Have been stuck with this for over a week now, and sincerely hope someone could help me. TIA.

chow

--------------tR0j3ysrQaisGg96YjYABZSn--