Hello everyone,
we have an instance of TimescaleDB hosted on Azure Database Flexible Server (PG 15.7, TS 2.10.0). As other DB services we have no superuser access, only user of admin level.
We want to upgrade so we tried to backup our database and restore to a new instance of the same service (PG 16.4, TS 2.13.0).
We encountered problems during restore of _timescale_catalog and _timescale_internal objects because of missing permission: as mentioned we have no superuser access and the owner of the timescale objects is the Azure Superuser (azuresu). We already opened a support ticket to Microsoft because in the actual instance PG 15.7 the owner of the timescale objects is our admin user and the migration from on-prem worked fine.
But in this kind of upgrade there is also the problem that the timescale extension versions are different. I would like to know if there is a procedure, different from pg_restore, that permit the copy of the data inside the hypertable/chunks to another database of different version. Can you help with this?
Some research has led us down the road of trying to use this approach:
psql -U user -h source_server -d source_db -c "COPY (SELECT * FROM <hypertable>) TO STDOUT WITH BINARY;" | psql -U user -h destination_server -d destinatione_db -c "COPY <hypertable> FROM STDIN WITH BINARY;"
We have some large hypertables (300GB each) so the command above I think would be better with a WHERE clause in the timestamp column to copy only a certain amount of data and obviously repeat n-time to retrieve all the data. We understand that this process can take several hours and we can deal with a few hours of downtime.
What other kind of drawbacks do you see in this procedure? Can we consider this kind of copy safe?
In conclusion what we are trying to achieve is to copy hypertables data with this constraints:
- different major version of postgresql
- different timescale versions
- no superuser access
Thanks,
Giorgio
–
Please remember our community forum is no substitute for our professional support! Please always contact support if you need help with your Timescale Cloud service. To help speed up their response, please include your project/service ID with your initial email request. You are welcome to delete this message!