I am not able to create a role with superuser privileges and I do not have the credentials for the 2 existing superuser roles (postgres and _aiven).
Superuser privileges are necessary to use properly pg_dump and pg_restore as indicated in Timescale documentation:
This is needed, for example, to dump a database from the Cloud to my local Linux instance for development purposes.
By following the instructions above without accessing with superuser privileges, I am able to dump and restore the hypertables but something weird happens with the continuous aggregates.
The materialized hypertables are listed as hypertables (SELECT * FROM timescaledb_information.hypertables) rather than as materialization hypertables (SELECT * FROM timescaledb_information.continuous_aggregates);
Also, when trying to access the content of the materialized views, an error is shown: invalid materialized hypertable ID.
Hey @Bernardo_Di_Chiara! The way I understand it is that having superuser rights would allow one to make changes to the filesystem, which as you might expect is a no-go from a security perspective.
With a bit help from timescale.support I found a way to obtain the creation of all hypertables in the target database correctly.
Basically what I did was:
To create a new database in Timescale to be used as target database (rather than using the cleaned up defaultdb)
To create in the target database the same user with the same user privileges as in the source database
To upgrade the Timescale version in the source database to the same version as the target database: 2.11.0
Otherwise the target database is empty
Doing this way I got few notices, warning and even errors. This is due to the missing superuser privileges. The warnings are harmless. I am still checking about the errors with timescale support.