Permission denied for function timescaledb_pre_restore in Azure PostgreSQL Flexible server

Soham Rajeshbhai Soneji 21 Reputation points
2022-04-04T19:56:06.44+00:00

We are working on restoring a Timescale database on Azure PostgreSQL Flexible server 11. Before running the restore command we were trying to enable timescaledb.restoring via timescaledb_pre_restore function but it is throwing following error:

ERROR: permission denied for function timescaledb_pre_restore
SQL state: 42501

While trying to check available parameters on Azure PostgreSQL Flexible server 11, I could not locate timescaledb.restoring but it seems it does exist on the server (just not available as an option) as the error is a permission denied error.

Can someone please help me resolve this permission denied error so that I can restore Timescale database on Azure PostgreSQL Flexible server 11?

Azure Database Migration service
Azure Database for PostgreSQL

1 answer

Sort by: Most helpful
Most helpful Newest Oldest
  1. GeethaThatipatri-MSFT 27,642 Reputation points Microsoft Employee
    2022-04-05T20:48:05.477+00:00

    Hi, @Soham Rajeshbhai Soneji There is an issue with time-scaled extension integration with flexible servers PostgreSQL 13 related to internal role ownership. We will fix this in a later release, we do not have steps to mitigate at this time however Potential Adhoc mitigation that you can perform if you would like to, also the support team will provide you with more information.

    The steps would roughly be..

    1. created a database as azure_pg_admin user
    2. enabled timescaledb as azure_pg_admin user
    3. created a table in the database as azure_pg_admin user
    4. converted the table to timescaledb hypertable as azure_pg_admin user
    5. executed "pg_dump -Fc -f exampledb.bak exampledb" as azure_pg_admin user to backup database
    6. created a new database as azure_pg_admin user
    7. enabled timescaledb as azure_pg_admin user
    8. executed "select public.timescaledb_pre_restore();" as azuresu
    9. executed "pg_restore -Fc -d new_exampledb exampledb.bak" as azure_pg_admin user
    10. executed "select public.timescaledb_post_restore();" as azuresu

    while using the pg_dump and pg_restore to restore a Timescale database is a known issue, our product team is still working to fix this issue.

    They have also suggested to try using the timescledb-backup option to restore the Timescale database by following the link below.

    Restoring a Timescale database using timescaledb-backup
    https://learn.microsoft.com/en-us/azure/postgresql/flexible-server/concepts-extensions#restoring-a-timescale-database-using-timescaledb-backup
    Please let us know if this information is helpful.

    Regards
    Geetha