Обсуждение: Error pg_upgrade version 11 to 15

"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

"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

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

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

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