[Bug]: failed upgrade postgresql 13 to 14

[empereira]

What type of bug is this?

Incorrect result

What subsystems and features are affected?

Other

What happened?

New cluster database "postgres" is not empty: found relation "_timescaledb_catalog.hypertable"
Failure, exiting

TimescaleDB version affected

2.15.3

PostgreSQL version used

13, 14

What operating system did you use?

Ubuntu 22 x64

What installation method did you use?

Deb/Apt

What platform did you run on?

On prem/Self-hosted

Relevant log output and stack trace

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 user-defined encoding conversions              ok
Checking for user-defined postfix operators                 ok
Checking for incompatible polymorphic functions             ok
Creating dump of global objects                             ok
Creating dump of database schemas
  postgres
  template1
  zabbix
                                                            ok

New cluster database "postgres" is not empty: found relation "_timescaledb_catalog.hypertable"
Failure, exiting

How can we reproduce the bug?

/usr/lib/postgresql/14/bin/pg_upgrade -d /etc/postgresql/13/data -D /etc/postgresql/14/data -b /usr/lib/postgresql/13/bin -B /usr/lib/postgresql/14/bin

[svenklemm]

Do you have some timescaledb leftovers in the template database?

[jflambert]

I went through major upgrade hell a few months ago and I think I've got it under control. Please: make sure all extensions are the same version in the "old" database! If you are using pg14-ts2.15.3 as a "NEW" DB, then also make sure all extensions in the "OLD" DB are all 2.15.3.

[suikast42]

Have the same issue here .

Old DB pg16.4-ts2.17.2-all
New DB pg17.2-ts2.17.2-all

All extenstions in source and dest are in version 2.17.2

[Krajda0]

pg_upgrade works fine. I used it twice.

Here are my notes from the upgrades.

Upgrade from PostgreSQL-14 to PostgreSQL-16 on image timescale/timescaledb-ha
1) PostgreSQL-14 database
	a)Upgrade to latest pg14 image - Same version of timescaledb required
	a)Upgrade all databases(include system databases template1 and postgres)
		timescaledb - https://docs.timescale.com/self-hosted/latest/upgrades/minor-upgrade/
			print current verion - "\dx timescaledb"
		toolkit - https://docs.timescale.com/self-hosted/latest/tooling/install-toolkit/
			 print current verion - "\dx timescaledb_toolkit"
	b)Stop database server correctly - "pg_ctl stop"

2)PostgreSQL-16 database
	a)Star new PostgreSQL-16 database server with new empty persistent data volume
	b)pgsql - (include system databases template1 and postgres) DROP EXTENSION timescaledb; DROP EXTENSION timescaledb_toolkit;
	c)Stop database server correctly - "pg_ctl stop"

3) Run pg_upgrade XXXX

[suikast42]

pg_upgrade works fine. I used it twice.

Here are my notes from the upgrades.

Do you that within docker ? I try to update from pg16.4-ts2.17.2 to pg17.2-ts2.17.2 from this base image https://hub.docker.com/r/timescale/timescaledb-ha/tags?name=pg17

$ pg_upgrade -b $OLD_BIN_DIR -B $NEW_BIN_DIR -d $OLD_DATA_DIR -D $NEW_DATA_DIR -U bi
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 contrib/isn with bigint-passing mismatch         ok
Checking data type usage                                      ok
Creating dump of global objects                               ok
Creating dump of database schemas                             
                                                              ok

New cluster database "template1" is not empty: found relation "_timescaledb_catalog.hypertable"
Failure, exiting

[Krajda0]

pg_upgrade works fine. I used it twice.
Here are my notes from the upgrades.

Do you that within docker ? I try to update from pg16.4-ts2.17.2 to pg17.2-ts2.17.2 from this base image https://hub.docker.com/r/timescale/timescaledb-ha/tags?name=pg17

$ pg_upgrade -b $OLD_BIN_DIR -B $NEW_BIN_DIR -d $OLD_DATA_DIR -D $NEW_DATA_DIR -U bi
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 contrib/isn with bigint-passing mismatch         ok
Checking data type usage                                      ok
Creating dump of global objects                               ok
Creating dump of database schemas                             
                                                              ok

New cluster database "template1" is not empty: found relation "_timescaledb_catalog.hypertable"
Failure, exiting

Yes, we probably have a similar environment.

I think you have an mistake in 2a). You need to remove the timescaledb extension for each database (template1 and postgres) in the new empty pg16 instance.

3. I ran pg_upgrade inside third temporary container with its own volume and both pg14 and pg16 volumes attached.

[suikast42]

You need to remove the timescaledb extension for each database

Ok. Thanks. I am moving on.

I am endup with

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 contrib/isn with bigint-passing mismatch         ok
Checking data type usage                                      ok
Creating dump of global objects                               ok
Creating dump of database schemas                             
                                                              ok
Checking for presence of required libraries                   fatal

Your installation references loadable libraries that are missing from the
new installation.  You can add these libraries to the new installation,
or remove the functions using them from the old installation.  A list of
problem libraries is in the file:
    /var/lib/postgresql/data/pgdata/pg_upgrade_output.d/20241211T193608.521/loadable_libraries.txt
Failure, exiting

I tinhk the reason is dropping the exenstions right ?

[suikast42]

I revise my earlier post.

The migration step does effectifly nothing.

-- Chunkk sizes
SELECT
    *,
   pg_total_relation_size(c.chunk_schema || '.' || c.chunk_name) / 1024 / 1024 AS  total_size
FROM
    timescaledb_information.hypertables h
JOIN
    timescaledb_information.chunks c
ON
    h.hypertable_name = c.hypertable_name
ORDER BY
    total_size DESC;

The result is empty. I see no schema is migrated.

cat /var/lib/postgresql/data/pgdata/pg_upgrade_output.d/20241211T200647.786/loadable_libraries.txt
could not load library "$libdir/timescaledb-2.15.2": ERROR:  could not access file "$libdir/timescaledb-2.15.2": No such file or directory
In database: template1
could not load library "$libdir/timescaledb_toolkit-1.18.0": ERROR:  could not access file "$libdir/timescaledb_toolkit-1.18.0": No such file or directory
In database: template1
In database: postgres
In database: bi
In database: wmsreporting

3. I ran pg_upgrade inside third temporary container with its own volume and both pg14 and pg16 volumes attached.

I am doing the same. Either pg16 or pg17 is runing. The migration container has access to the volume of 16 and 17

[Krajda0]

1) PostgreSQL-14 database
	a)Upgrade to latest pg14 image - Same version of timescaledb required
	a)Upgrade all databases(include system databases template1 and postgres)
		timescaledb - https://docs.timescale.com/self-hosted/latest/upgrades/minor-upgrade/
			print current verion - "\dx timescaledb"
		toolkit - https://docs.timescale.com/self-hosted/latest/tooling/install-toolkit/
			 print current verion - "\dx timescaledb_toolkit"
	b)Stop database server correctly - "pg_ctl stop"

Check "1a)Update all databases".
You are using an old version of timescaledb. Updating the container does not update the version actually in use.

[suikast42]

toolkit - https://docs.timescale.com/self-hosted/latest/tooling/install-toolkit/
print current verion - "\dx timescaledb_toolkit"

I missed that part. You are my hero for this week. Thanks a bunch