pg.ddx.io pgsql-bugs@postgresql.org mailing list archive
help / color / mirror / Atom feedError pg_upgrade version 11 to 15
6+ messages / 4 participants
[nested] [flat]
* Error pg_upgrade version 11 to 15
@ 2025-08-18 14:41 IVAN HUMANES CABANAS (Fujitsu) <ivan.humanescabanas@fujitsu.com>
2025-08-18 16:10 ` Re: Error pg_upgrade version 11 to 15 Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 6+ messages in thread
From: IVAN HUMANES CABANAS (Fujitsu) @ 2025-08-18 14:41 UTC (permalink / raw)
To: pgsql-bugs
Hello!
-bash-4.2$ /usr/pgsql-15/bin/pg_upgrade --old-datadir=/var/lib/pgsql/11/data --new-datadir=/var/lib/pgsql/15/data --old-bindir=/usr/pgsql-11/bin --new-bindir=/usr/pgsql-15/bin
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 removed "abstime" data type in user tables ok
Checking for removed "reltime" data type in user tables ok
Checking for removed "tinterval" data type in user tables ok
Checking for user-defined encoding conversions ok
Checking for user-defined postfix operators ok
Checking for incompatible polymorphic functions ok
Checking for tables WITH OIDS ok
Checking for invalid "sql_identifier" user columns ok
Creating dump of global objects ok
Creating dump of database schemas
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
If pg_upgrade fails after this point, you must re-initdb the
new cluster before continuing.
Performing Upgrade
------------------
Analyzing all rows in the new cluster ok
Freezing all rows in the new cluster ok
Deleting files from new pg_xact ok
Copying old pg_xact to new server ok
Setting oldest XID for new cluster ok
Setting next transaction ID and epoch for new cluster ok
Deleting files from new pg_multixact/offsets ok
Copying old pg_multixact/offsets to new server ok
Deleting files from new pg_multixact/members ok
Copying old pg_multixact/members to new server ok
Setting next multixact ID and offset for new cluster ok
Resetting WAL archives ok
Setting frozenxid and minmxid counters in new cluster ok
Restoring global objects in the new cluster ok
Restoring database schemas in the new cluster
template1
*failure*
Consult the last few lines of "/var/lib/pgsql/15/data/pg_upgrade_output.d/20250818T162430.400/log/pg_upgrade_dump_18344416.log" for
the probable cause of the failure.
Failure, exiting
-bash-4.2$ cat /var/lib/pgsql/15/data/pg_upgrade_output.d/20250818T162430.400/log/pg_upgrade_dump_18344416.log
command: "/usr/pgsql-15/bin/pg_dump" --host /var/lib/pgsql/15/data --port 50432 --username postgres --schema-only --quote-all-identifiers --binary-upgrade --format=custom --file="/var/lib/pgsql/15/data/pg_upgrade_output.d/20250818T162430.400/dump/pg_upgrade_dump_18344416.custom" 'dbname=template1' >> "/var/lib/pgsql/15/data/pg_upgrade_output.d/20250818T162430.400/log/pg_upgrade_dump_18344416.log" 2>&1
command: "/usr/pgsql-15/bin/pg_restore" --host /var/lib/pgsql/15/data --port 50432 --username postgres --clean --create --exit-on-error --verbose --dbname postgres "/var/lib/pgsql/15/data/pg_upgrade_output.d/20250818T162430.400/dump/pg_upgrade_dump_18344416.custom" >> "/var/lib/pgsql/15/data/pg_upgrade_output.d/20250818T162430.400/log/pg_upgrade_dump_18344416.log" 2>&1
pg_restore: connecting to database for restore
pg_restore: dropping DATABASE PROPERTIES template1
pg_restore: dropping DATABASE template1
pg_restore: while PROCESSING TOC:
pg_restore: from TOC entry 3677; 1262 18344416 DATABASE template1 postgres
pg_restore: error: could not execute query: ERROR: cannot drop a template database
Command was: DROP DATABASE "template1";
Regards
Ivan Humanes
Database Administrator
[cid:2ed07e75-f2dd-4729-a46e-efb9b7f12e84]
Fujitsu Technology Solutions S.A.
Camino Cerro de los Gamos, 1 - 28224 - Pozuelo de Alarcón, Madrid, Spain
Móvil: +34 615 84 09 99
E-mail: ivan.humanescabanas@fujitsu.com<mailto:ivan.humanescabanas.external@fijitsu.com>
Attachments:
[image/gif] image.png (1.8K, ../../AMBP191MB2746B1A4B24D87330C7A028DFD31A@AMBP191MB2746.EURP191.PROD.OUTLOOK.COM/3-image.png)
download | view image
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Error pg_upgrade version 11 to 15
2025-08-18 14:41 Error pg_upgrade version 11 to 15 IVAN HUMANES CABANAS (Fujitsu) <ivan.humanescabanas@fujitsu.com>
@ 2025-08-18 16:10 ` Tom Lane <tgl@sss.pgh.pa.us>
2025-08-18 19:16 ` Re: Error pg_upgrade version 11 to 15 Andrew Dunstan <andrew@dunslane.net>
0 siblings, 1 reply; 6+ messages in thread
From: Tom Lane @ 2025-08-18 16:10 UTC (permalink / raw)
To: IVAN HUMANES CABANAS (Fujitsu) <ivan.humanescabanas@fujitsu.com>; +Cc: pgsql-bugs
"IVAN HUMANES CABANAS (Fujitsu)" <ivan.humanescabanas@fujitsu.com> writes:
> command: "/usr/pgsql-15/bin/pg_restore" --host /var/lib/pgsql/15/data --port 50432 --username postgres --clean --create --exit-on-error --verbose --dbname postgres "/var/lib/pgsql/15/data/pg_upgrade_output.d/20250818T162430.400/dump/pg_upgrade_dump_18344416.custom" >> "/var/lib/pgsql/15/data/pg_upgrade_output.d/20250818T162430.400/log/pg_upgrade_dump_18344416.log" 2>&1
> pg_restore: connecting to database for restore
> pg_restore: dropping DATABASE PROPERTIES template1
> pg_restore: dropping DATABASE template1
> pg_restore: while PROCESSING TOC:
> pg_restore: from TOC entry 3677; 1262 18344416 DATABASE template1 postgres
> pg_restore: error: could not execute query: ERROR: cannot drop a template database
> Command was: DROP DATABASE "template1";
Hm. The restore should have issued a command to make template1 not be
a template database before dropping it. Apparently it did not do so.
Looking at the code, a plausible theory for that is that template1
is not marked as a template database in the source cluster. Did you
change that, and if so why? It would have broken more things than
just pg_upgrade.
regards, tom lane
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Error pg_upgrade version 11 to 15
2025-08-18 14:41 Error pg_upgrade version 11 to 15 IVAN HUMANES CABANAS (Fujitsu) <ivan.humanescabanas@fujitsu.com>
2025-08-18 16:10 ` Re: Error pg_upgrade version 11 to 15 Tom Lane <tgl@sss.pgh.pa.us>
@ 2025-08-18 19:16 ` Andrew Dunstan <andrew@dunslane.net>
2025-08-18 20:07 ` Re: Error pg_upgrade version 11 to 15 Nathan Bossart <nathandbossart@gmail.com>
0 siblings, 1 reply; 6+ messages in thread
From: Andrew Dunstan @ 2025-08-18 19:16 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; IVAN HUMANES CABANAS (Fujitsu) <ivan.humanescabanas@fujitsu.com>; +Cc: pgsql-bugs
On 2025-08-18 Mo 12:10 PM, Tom Lane wrote:
> "IVAN HUMANES CABANAS (Fujitsu)"<ivan.humanescabanas@fujitsu.com> writes:
>> command: "/usr/pgsql-15/bin/pg_restore" --host /var/lib/pgsql/15/data --port 50432 --username postgres --clean --create --exit-on-error --verbose --dbname postgres "/var/lib/pgsql/15/data/pg_upgrade_output.d/20250818T162430.400/dump/pg_upgrade_dump_18344416.custom" >> "/var/lib/pgsql/15/data/pg_upgrade_output.d/20250818T162430.400/log/pg_upgrade_dump_18344416.log" 2>&1
>> pg_restore: connecting to database for restore
>> pg_restore: dropping DATABASE PROPERTIES template1
>> pg_restore: dropping DATABASE template1
>> pg_restore: while PROCESSING TOC:
>> pg_restore: from TOC entry 3677; 1262 18344416 DATABASE template1 postgres
>> pg_restore: error: could not execute query: ERROR: cannot drop a template database
>> Command was: DROP DATABASE "template1";
> Hm. The restore should have issued a command to make template1 not be
> a template database before dropping it. Apparently it did not do so.
> Looking at the code, a plausible theory for that is that template1
> is not marked as a template database in the source cluster. Did you
> change that, and if so why? It would have broken more things than
> just pg_upgrade.
>
>
Template1 normally has an OID of 1, but this database has an apparent
OID of 18344416. Maybe this has been the result of a previous upgrade -
we don't have enough information. I don't think pg_upgrade attempts to
preserve database OIDs.
cheers
andrew
--
Andrew Dunstan
EDB:https://www.enterprisedb.com
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Error pg_upgrade version 11 to 15
2025-08-18 14:41 Error pg_upgrade version 11 to 15 IVAN HUMANES CABANAS (Fujitsu) <ivan.humanescabanas@fujitsu.com>
2025-08-18 16:10 ` Re: Error pg_upgrade version 11 to 15 Tom Lane <tgl@sss.pgh.pa.us>
2025-08-18 19:16 ` Re: Error pg_upgrade version 11 to 15 Andrew Dunstan <andrew@dunslane.net>
@ 2025-08-18 20:07 ` Nathan Bossart <nathandbossart@gmail.com>
2025-08-18 20:18 ` Re: Error pg_upgrade version 11 to 15 Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 6+ messages in thread
From: Nathan Bossart @ 2025-08-18 20:07 UTC (permalink / raw)
To: Andrew Dunstan <andrew@dunslane.net>; +Cc: Tom Lane <tgl@sss.pgh.pa.us>; IVAN HUMANES CABANAS (Fujitsu) <ivan.humanescabanas@fujitsu.com>; pgsql-bugs
On Mon, Aug 18, 2025 at 03:16:46PM -0400, Andrew Dunstan wrote:
> Template1 normally has an OID of 1, but this database has an apparent OID of
> 18344416. Maybe this has been the result of a previous upgrade - we don't
> have enough information. I don't think pg_upgrade attempts to preserve
> database OIDs.
It does as of v15 [0].
I concur with Tom that this is most likely due to template1 not being
marked as a template database in the source cluster. We could presumably
hack pg_upgrade to deal with this for template1 and postgres databases
(e.g., by preemptively setting datistemplate = f on the new cluster).
Changes to template0 are likely harder to deal with. Since pg_dump uses it
as the template for databases it creates, we'd have to wait until all other
databases are restored before re-creating it. Plus, we'd probably have to
first create a fresh template0 clone (with the source template0's encoding
and locale settings) so that there was something to use as a template for
template0. There might be other problems, and I'm not sure it's worth the
effort, anyway.
[0] https://postgr.es/c/aa01051
--
nathan
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Error pg_upgrade version 11 to 15
2025-08-18 14:41 Error pg_upgrade version 11 to 15 IVAN HUMANES CABANAS (Fujitsu) <ivan.humanescabanas@fujitsu.com>
2025-08-18 16:10 ` Re: Error pg_upgrade version 11 to 15 Tom Lane <tgl@sss.pgh.pa.us>
2025-08-18 19:16 ` Re: Error pg_upgrade version 11 to 15 Andrew Dunstan <andrew@dunslane.net>
2025-08-18 20:07 ` Re: Error pg_upgrade version 11 to 15 Nathan Bossart <nathandbossart@gmail.com>
@ 2025-08-18 20:18 ` Tom Lane <tgl@sss.pgh.pa.us>
2025-08-18 21:53 ` Re: Error pg_upgrade version 11 to 15 Nathan Bossart <nathandbossart@gmail.com>
0 siblings, 1 reply; 6+ messages in thread
From: Tom Lane @ 2025-08-18 20:18 UTC (permalink / raw)
To: Nathan Bossart <nathandbossart@gmail.com>; +Cc: Andrew Dunstan <andrew@dunslane.net>; IVAN HUMANES CABANAS (Fujitsu) <ivan.humanescabanas@fujitsu.com>; pgsql-bugs
Nathan Bossart <nathandbossart@gmail.com> writes:
> I concur with Tom that this is most likely due to template1 not being
> marked as a template database in the source cluster. We could presumably
> hack pg_upgrade to deal with this for template1 and postgres databases
> (e.g., by preemptively setting datistemplate = f on the new cluster).
> Changes to template0 are likely harder to deal with. Since pg_dump uses it
> as the template for databases it creates, we'd have to wait until all other
> databases are restored before re-creating it. Plus, we'd probably have to
> first create a fresh template0 clone (with the source template0's encoding
> and locale settings) so that there was something to use as a template for
> template0. There might be other problems, and I'm not sure it's worth the
> effort, anyway.
Yeah. The logic about this is in pg_dump, actually: dumpDatabase()
decides whether or not to add "UPDATE ... SET datistemplate = false"
to the delQry. I was thinking about having it do that either if
the source DB has datistemplate or if its name is template1.
That would cover both (1) restoring a nonstandard set of databases
into the original installation with --clean, and (2) restoring a
nonstandard setup into a pristine installation. I don't think we
need to account for template0 because neither pg_dumpall nor
pg_upgrade will attempt to replace it.
However, first I'd like confirmation that this theory explains
the OP's problem.
regards, tom lane
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Error pg_upgrade version 11 to 15
2025-08-18 14:41 Error pg_upgrade version 11 to 15 IVAN HUMANES CABANAS (Fujitsu) <ivan.humanescabanas@fujitsu.com>
2025-08-18 16:10 ` Re: Error pg_upgrade version 11 to 15 Tom Lane <tgl@sss.pgh.pa.us>
2025-08-18 19:16 ` Re: Error pg_upgrade version 11 to 15 Andrew Dunstan <andrew@dunslane.net>
2025-08-18 20:07 ` Re: Error pg_upgrade version 11 to 15 Nathan Bossart <nathandbossart@gmail.com>
2025-08-18 20:18 ` Re: Error pg_upgrade version 11 to 15 Tom Lane <tgl@sss.pgh.pa.us>
@ 2025-08-18 21:53 ` Nathan Bossart <nathandbossart@gmail.com>
0 siblings, 0 replies; 6+ messages in thread
From: Nathan Bossart @ 2025-08-18 21:53 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: Andrew Dunstan <andrew@dunslane.net>; IVAN HUMANES CABANAS (Fujitsu) <ivan.humanescabanas@fujitsu.com>; pgsql-bugs
On Mon, Aug 18, 2025 at 04:18:56PM -0400, Tom Lane wrote:
> Yeah. The logic about this is in pg_dump, actually: dumpDatabase()
> decides whether or not to add "UPDATE ... SET datistemplate = false"
> to the delQry. I was thinking about having it do that either if
> the source DB has datistemplate or if its name is template1.
> That would cover both (1) restoring a nonstandard set of databases
> into the original installation with --clean, and (2) restoring a
> nonstandard setup into a pristine installation. I don't think we
> need to account for template0 because neither pg_dumpall nor
> pg_upgrade will attempt to replace it.
>
> However, first I'd like confirmation that this theory explains
> the OP's problem.
WFM, provided your theory is correct.
--
nathan
^ permalink raw reply [nested|flat] 6+ messages in thread
end of thread, other threads:[~2025-08-18 21:53 UTC | newest]
Thread overview: 6+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2025-08-18 14:41 Error pg_upgrade version 11 to 15 IVAN HUMANES CABANAS (Fujitsu) <ivan.humanescabanas@fujitsu.com>
2025-08-18 16:10 ` Tom Lane <tgl@sss.pgh.pa.us>
2025-08-18 19:16 ` Andrew Dunstan <andrew@dunslane.net>
2025-08-18 20:07 ` Nathan Bossart <nathandbossart@gmail.com>
2025-08-18 20:18 ` Tom Lane <tgl@sss.pgh.pa.us>
2025-08-18 21:53 ` Nathan Bossart <nathandbossart@gmail.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox