Обсуждение: Re: pg_upgrade from Postgresql-12 to Postgresql-13 fails with "Creating dump of database schemas postgres *failure*"
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
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:
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.
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
Ooops. The last pg_dropcluster must be for the <previous version> :-(
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> mainRegards,
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:
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.
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