search

timescale / timescaledb

An open-source time-series SQL database optimized for fast ingest and complex queries. Packaged as a PostgreSQL extension.
https://www.timescale.com/
Other
17.42k stars 872 forks source link

[Bug]: Backwards Compatibility Fails when Restoring 2.11 TimescaleDB Triggers to 2.12 with Patroni 3.1.x #6133

Open wdarosh-gp opened 11 months ago

wdarosh-gp commented 11 months ago

What type of bug is this?

Crash

What subsystems and features are affected?

Data ingestion, Multi-node, Node management, Partitioning, Platform/OS, Replication, Restore, Other

What happened?

In restoring pg_restore methods to restore a Postgres 14 backup, A system operating under TimescaleDB 2.11 was restored to TimescaleDB 2.12.

Once the database was started the system displayed WARNING messages discussing the movement of Functions to a new schema. The language in the WARNING indicated that this would still function; however, in a future release would not. The warnings resulted in a Segmentation Fault preventing the database from staying in an active state. This resulted in LSN related errors reported in Patroni (our HA manager) and the system entered into a continual loop.

The issue was isolated to the triggers that called timescaledb specific functions. Upon upgrading the triggers to the new schema locations the system continued to work with expectations.

TimescaleDB version affected

2.11.0, 2.12.0

PostgreSQL version used

14.9

What operating system did you use?

Ubuntu 22.04 x64

What installation method did you use?

Deb/Apt

What platform did you run on?

Amazon Web Services (AWS)

Relevant log output and stack trace

No response

How can we reproduce the bug?

Any variation of pg_dump/pg_restore including the direct .sql file read in will generate the same response.

Utilizing Patroni Clustered Postgres with ETCD3:
 - Create a database with hypertables under TimescaleDB 2.11.0
 - Create corresponding INSERT/UPDATE/DELETE triggers
 - Using pg_dump export the database.

Utilizing Patroni Clustered Postgres with ETCD3
 - With TimescaleDB 2.12 Installed - Restore using pg_restore
 - Initalize INSERTS against the Hypertables in standard operating succession
 - Monitor Logs.
svenklemm commented 11 months ago

If you executed those steps as described in the ticket your timescaledb is still in a broken state. If you do a dump/restore you must use the exact same timescaledb version for the dump and restore. To update timescaledb you must use ALTER EXTENSION UPDATE as this will do the necessary catalog updates. You can check whether your timescaledb catalog and code version are out of sync by looking at the schema of _timescaledb_catalog.hypertable. If the table has no status column then your schema has not been updated to 2.12.

If that is the case i suggest to run pg_dump again and then restore it into 2.11 and then run ALTER EXTENSION UPDATE. When the timescaledb catalog and the schema are out of sync any functionality relying on these schema changes to be present can potentially segfault.