timescaledb
timescaledb copied to clipboard
timescale
[Bug]: failed upgrade postgresql 13 to 14
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
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.
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
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
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
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.
- I ran pg_upgrade inside third temporary container with its own volume and both pg14 and pg16 volumes attached.
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 ?
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
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.
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
Same here with 2.15.3 when upgrading from Pg 13 to 16 with table chunk_constraint: New cluster database "foo" is not empty: found relation "_timescaledb_catalog.chunk_constraint" There is only 1 Database in the Cluster with the Extension.
If you are running pg_upgrade you need to ensure timescaledb is available in for source and target postgres version. Closing this due to inactivity. Feel free to reopen if you are still encountering this.