PostgreSQL single server to flexible server migration error - permission denied for admin user

Nowakowski Krzysztof 21

When I am doing migration of 3 databses(schemas), between single server and flexible server postgresql I am getting error shown below

Data migration could not be started for one or more of the DBSets. Error details: PGv2RestoreError: PG Restore failed for database 'postgres' with exit code '1' and error message 'error: could not execute query: ERROR: permission denied to create "pg_catalog.hypopg_list_indexes"'.

It clearly says that problem lies with hypopg extension (which is enabled on both single and flexible server) which needs to create something in pg_catalog, but as admin user I don't have right to create there therefore operation isn't possible. Also this operation needs superuser and I am unable to get it as documentation states.

Also as preparation I am performing every step in this guides:
https://video2.skills-academy.com/en-us/azure/postgresql/migrate/how-to-migrate-single-to-flexible-portal
https://video2.skills-academy.com/en-us/azure/postgresql/migrate/concepts-single-to-flexible
https://video2.skills-academy.com/en-us/azure/postgresql/migrate/concepts-single-to-flexible#migration-prerequisites

GeethaThatipatri-MSFT 29,007 Reputation points Microsoft Employee

2022-11-04T18:17:55.38+00:00

Hi, @Nowakowski Krzysztof Welcome to the Microsoft Q&A platform, thanks for posting the question and for using Azure Services.
As per my understanding, you are facing an issue while migrating from the Azure PostgreSQL single server to the Azure PostgreSQL flexible server.

From the error provided, I understand that you are creating an Index “'pg_catalog.hypopg_list_indexes'' on the destination server. Could you please check one more time if the “hypopg extension” exists on the destination server or not . If not, please go through the below document and add the extension “hypopg” on your destination flexible server.

https://video2.skills-academy.com/en-us/azure/postgresql/flexible-server/concepts-extensions

Once the extension is added, please check the account\username who is performing the task of the migration and who needs to have proper admin access on both Source and Destination servers and then execute the query. Please go through the below document for more details regarding the permissions on Azure PostgreSQL server.

https://video2.skills-academy.com/EN-us/azure/postgresql/single-server/how-to-create-users#the-server-admin-account

Regards
Geetha
Nowakowski Krzysztof 21 Reputation points

2022-11-04T19:08:48.34+00:00

Hi, yes, HYPOPG is enabled on flexible server and I am using admin account to perform migration.

Accepted answer

GeethaThatipatri-MSFT 29,007 Reputation points Microsoft Employee

2022-11-04T19:27:28.563+00:00

@Nowakowski Krzysztof Please drop the hypopg extension on the source server before migration (for each DB the extension was created in): and give it a try.
DROP EXTENSION IF EXISTS hypopg;
If needed, it could then be recreated after migration on the source:
CREATE EXTENSION hypopg;

Regards
Geetha
Please sign in to rate this answer.
GeethaThatipatri-MSFT 29,007 Reputation points Microsoft Employee

2022-11-07T14:28:46.707+00:00

@Nowakowski Krzysztof Did you get a chance to try as mentioned above?

Regards
Geetha

Nowakowski Krzysztof 21 Reputation points

2022-11-09T21:58:06.937+00:00

Hi, sorry for late response but i didnt had time to test it before. Dropping extension in soruce database and creating it in destination database resolved this issue

GeethaThatipatri-MSFT 29,007 Reputation points Microsoft Employee

2022-11-10T01:02:38.933+00:00

@Nowakowski Krzysztof Thanks for confirming that your issue got resolved.
Please do not forget to "Accept the answer" wherever the information provided helps you to help others in the community

Regards
Geetha
Sign in to comment

1 additional answer

devopsfj 196 Reputation points

2023-11-03T13:45:31.37+00:00

Did you get a solution to this?

I am facing the same issue now.
Please sign in to rate this answer.

0 comments No comments
Sign in to comment

Share via

PostgreSQL single server to flexible server migration error - permission denied for admin user

1 additional answer