Horton

Thread

  1. Error pg_upgrade version 11 to 15

    IVAN HUMANES CABANAS (Fujitsu) <ivan.humanescabanas@fujitsu.com> — 2025-08-18T14:41:50Z

    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>

  2. Re: Error pg_upgrade version 11 to 15

    Tom Lane <tgl@sss.pgh.pa.us> — 2025-08-18T16:10:05Z

    "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

  3. Re: Error pg_upgrade version 11 to 15

    Andrew Dunstan <andrew@dunslane.net> — 2025-08-18T19:16:46Z

    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

  4. Re: Error pg_upgrade version 11 to 15

    Nathan Bossart <nathandbossart@gmail.com> — 2025-08-18T20:07:01Z

    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

  5. Re: Error pg_upgrade version 11 to 15

    Tom Lane <tgl@sss.pgh.pa.us> — 2025-08-18T20:18:56Z

    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

  6. Re: Error pg_upgrade version 11 to 15

    Nathan Bossart <nathandbossart@gmail.com> — 2025-08-18T21:53:50Z

    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