We run multiple TimescaleDB/PostgreSQL database on remote computers which are maintained using Ansible scripts. One of the tasks of the Ansible script is to update TimescaleDB to a new version. To make that version available in the database we need to run the query ALTER EXTENSION timescaledb UPDATE; See the TimescaleDB docs
In one of our databases the extension was created using the postgres user instead of the database owner. When we try to update the extension using this database owner it fails because the database owner is not the owner of the extension. How can we change the owner of the extension without destroying the data in the timeseries tables? I already tried to drop the extension as postgres and create it using the database owner but then al data is gone.
I would like to prevent to create a backup of the data and then restore it because we do not have enough disk space left on the remote machine. Backups are made with ZFS snapshots but those include the extension and permissions.