PostgreSQL Migration Tool - Single Server TO Flexible Server - Different Tenants

Question

PostgreSQL Migration Tool - Single Server TO Flexible Server - Different Tenants

devopsfj 136

Is there anyway to use the PostgreSQL Migration tool when the Source & Target servers are in different tenants?

Answer 1

Rahul Randive 5,656 Microsoft Employee

Hi @devopsfj

As this moment, it is not possible to use the PostgreSQL Migration tool when the source and target servers are in different tenants.

Even the source and target server must be in the same Azure region. Cross region migrations are enabled only for servers in China regions.

Thank you!

devopsfj 136 Reputation points

2023-11-03T12:27:35.78+00:00

@Rahul Randive Thanks for the response.

I have tried migrating using pg_restore and I am getting the below error:

pg_restore: creating EXTENSION "hypopg" pg_restore: while PROCESSING TOC: pg_restore: from TOC entry 1; 3079 1256981 EXTENSION hypopg (no owner) pg_restore: error: could not execute query: ERROR: permission denied to create "pg_catalog.hypopg_list_indexes" DETAIL: System catalog modifications are currently disallowed. Command was: CREATE EXTENSION IF NOT EXISTS hypopg WITH SCHEMA pg_catalog;

Any idea how to solve this? I have enabled all the relevant extensions on my Flexible server.
Rahul Randive 5,656 Reputation points Microsoft Employee

2023-11-03T12:57:53.8933333+00:00

Hi devopsfj,

Look like 'hypopg' extension is not white-listed for 'azure_pg_admin' users in Azure Database for PostgreSQL.

As you are getting permission error, you will need to grant the necessary permissions to the user.

Thank you!
devopsfj 136 Reputation points

2023-11-03T13:43:22.9+00:00

@Rahul Randive

I am using the admin user which has full admin access to everything, so this is not the issue.

Do you have any other idea what it could be?
Rahul Randive 5,656 Reputation points Microsoft Employee

2023-11-03T14:08:35.7233333+00:00

Hi devopsfj

Please check on your single server if the hypopg extension installed in pg_catalog? if yes, the restore process is trying to create the hypopg extension in pg_catalog on Flexible server and hence the error.

By default, hypopg extension gets installed on public schema. Please check on your servers and create hypopg extension under public schema on the single and Flexible server.

Run the following query to change hypopg schema to public on the Single server. Then pg_dump will generate "CREATE EXTENSION hypopg" query with the public schema and will be able to restore in FSPG server:

"ALTER EXTENSION hypopg SET SCHEMA public;"

If the above step errors out and if you think if this extension is not so important, you can drop the extension from source, then you could migrate without that extension and enable it later in target.

I hope this helps!

Thank you!
devopsfj 136 Reputation points

2023-11-03T14:36:22.38+00:00

Hi,

This is the output of \dx

This is a default setting with Single Server, we haven't changed anything, I am not sure if the extension is in use

I would rather not change the extensions as I do not know the impact it will have on the system, I just want to lift and shift the database.
devopsfj 136 Reputation points

2023-11-03T14:57:26.71+00:00

@Rahul Randive

Hi,

This issue is definitely related to flexible server.

I have just created a brand new instance of PostgreSQL Single Server, restored the database with no errors, everything restored fine.

This looks like some sort of limitation/change to Flexible server which is causing the issue.

Any guidance please, I do not really want to change DB settings as I do not know the effect it will have on the app.

I just want to simply lift and shift the database.
Rahul Randive 5,656 Reputation points Microsoft Employee

2023-11-03T16:19:50.88+00:00

Hi devopsfj •

Well, the workaround would be 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;
and if needed, it could then be recreated after migration on the source:
CREATE EXTENSION hypopg;

You can try your other nonprod server which has a same setting.
Rahul Randive 5,656 Reputation points Microsoft Employee

2023-11-03T16:38:13.95+00:00

Here is similar thread as well- https://learn.microsoft.com/en-us/answers/questions/1076136/postgresql-single-server-to-flexible-server-migrat
Rahul Randive 5,656 Reputation points Microsoft Employee

2023-11-07T14:04:49.0866667+00:00

Hi devopsfj,

Just checking if there's any progress or updates?

Thank you!
Rahul Randive 5,656 Reputation points Microsoft Employee

2023-11-09T17:30:55.6933333+00:00

Hi devopsfj,

I hope the answer is helpful. If you find useful, please remember to mark them as the accepted answer.
If you need any additional information, please feel free to let us know.

Thank you!

PostgreSQL Migration Tool - Single Server TO Flexible Server - Different Tenants

1 answer

Activity