permission denied for schema _timescaledb_internal on Azure PostgreSQL flexible server

Question

I tried to migrate db from Azure PostgreSQL single server to flexible server using migration tool from Azure portal.
But there is an error on validation check because migration tool doesn't support timescaledb extension.
Portal message said that please use pg_dump and pg_restore.

So I did pg_dump and pg_restore for migration.
I did pg_restore as user pgsql_admin but I got an error "permission denied for schema _timescaledb_internal"
ChatGPT said for internal schema like _timescaledb_internal, it will need superuser privilege.
so I checked privilege for each users, and I realized only azuresu user has superuser privilege.
How to use azuresu user?
I know only credential for pgsql_admin user.

Best regards.
Michiteru

Answer 1

The problem is dire.

There is no way to pg_dump and pg_restore a database with TimescaleDB enabled, because of the following error:

pg_restore: error: could not execute query: ERROR:  permission denied for schema _timescaledb_internal LINE 1: CREATE TABLE _timescaledb_internal._hyper_7_11_chunk

And the reason is obvious - Azure gives the ownership of timescaledb schemas to azuresu user:

            Name          |          Owner   
--------------------------+------------------------
 _timescaledb_cache       | azuresu
 _timescaledb_catalog     | azuresu
 _timescaledb_config      | azuresu
 _timescaledb_internal    | azuresu
 my_schema                | my_azure_pg_admin_user
 public                   | azuresu
 timescaledb_experimental | azuresu
 timescaledb_information  | azuresu

while the restore has to create tables (the chunks) inside _timescaledb_internal

I consider this a BUG IN AZURE and believe this must have changed recently.
I looked into some of my older installations and this is what I found:

           Name           |  Owner   
--------------------------+---------
 _timescaledb_cache       | my_user
 _timescaledb_catalog     | my_user
 _timescaledb_config      | my_user
 _timescaledb_internal    | my_user
 my_schema                | my_user
 public                   | azuresu
 timescaledb_experimental | my_user
 timescaledb_information  | my_user

Azure used to give the ownership of timescaldb-specific schemas to the user who enabled the extension inside the database:

CREATE EXTENSION timescaledb;

but it is not the case anymore.

Any solution, idea, or advice to overcome the issue?

Answer 2

Hi, @Michiteru Sugitani Thanks for posting your question in the Microsoft Q&A forum.

As per the document you can't move extensions not supported by the Azure Database for PostgreSQL – Flexible server. The supported extensions are in Extensions - Azure Database for PostgreSQL.

https://learn.microsoft.com/en-us/azure/postgresql/migrate/migration-service/concepts-known-issues-migration-service

Please let me know if you have any additional questions.

Regards

Geetha