Error While Migrating from Azure PostgreSQL Single Server to Flexible Server

SoniaD_74 0

Hi Community,

I’m encountering an issue while migrating from Azure PostgreSQL Single Server to Flexible Server using the Azure Database Migration Service.

Error Details

During the migration of a database (e.g., database_name), the process failed with the following error:

One or more errors occurred while migrating '***database_name***': pgcopydb failed with unknown errors. Please contact Microsoft support.    Logs from the migration:   2024-11-28 13:37:27.111 58 ERROR pgsql.c:2299 [TARGET 636] ERROR: permission denied for function pg_replication_origin_oid   2024-11-28 13:37:27.111 58 ERROR pgsql.c:2299 [TARGET 636] ERROR: permission denied for function pg_replication_origin_oid   2024-11-28 13:37:27.111 58 ERROR pgsql.c:2310 [TARGET 636] SQL query: select pg_replication_origin_oid($1)   2024-11-28 13:37:27.111 58 ERROR pgsql.c:2318 [TARGET 636] SQL params: 'azpgmigorigin_16517_20241128_133725'   2024-11-28 13:37:27.111 58 ERROR pgsql.c:4705 Failed to get replication origin oid for "azpgmigorigin_16517_20241128_133725"

What I Was Doing

I followed the standard migration process for moving a database from Single Server to Flexible Server. However, the migration failed when attempting to access the pg_replication_origin_oid function.

Steps Taken So Far

Verified that I am using the Azure Database Migration Service.
Confirmed that the user role in use is azure_pg_admin, as superuser access is not available.
Attempted to troubleshoot the issue by reviewing documentation and forums but could not resolve it.

Questions

Is there a known workaround for resolving permission issues related to the pg_replication_origin_oid function during migration?
Could this be a limitation of the Azure Database for PostgreSQL Flexible Server’s permission model?
Are there additional steps I should take to bypass this error or tools to use?

Any guidance or suggestions would be greatly appreciated. If anyone from Microsoft has insights into this issue, your input would be incredibly helpful!

Thanks in advance for your help!

Mahesh Kurva 1,070 Reputation points Microsoft Vendor

2024-11-29T19:46:08.92+00:00

Hi @SoniaD_74,

Welcome to the Microsoft Q&A and thank you for posting your questions here.

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 facing pg_replication_origin_oid function during your migration from Azure PostgreSQL Single Server to Flexible Server.

Use of functions for replication origin is only allowed to the superuser by default, but may be allowed to other users by using the GRANT command.Use of functions for replication slots is restricted to superusers and users having REPLICATION privilege.

For more information, please refer the document: https://www.postgresql.org/docs/current/functions-admin.html#FUNCTIONS-REPLICATION

If the Azure Database Migration Service is causing issues, consider using other tools like pg_dump and pg_restore for the migration. These tools might bypass some of the permission issues encountered.

I hope this information helps. Please do let us know if you have any further queries.
SoniaD_74 0 Reputation points

2024-12-02T08:30:45.9+00:00

Hi Mahesh,

Thanks for your reply. Unfortunately, adding replication role to the postgres user didn't work. It gives the same error. So, apart from dump/restore, is there any other way we can perform this migration?
Mahesh Kurva 1,070 Reputation points Microsoft Vendor

2024-12-02T10:27:54.8233333+00:00
Hi @SoniaD_74,

Thanks for your response.

Instead of performing a dump and restore, try using the migration service provided by Azure.

Logical Replication: Use PostgreSQL’s logical replication to replicate data from the Single Server to the Flexible Server.

Steps:

Enable logical replication on both source and target servers.

Create a publication on the source server.

Create a subscription on the target server.

Monitor the replication process.

For more information, please refer the document: https://learn.microsoft.com/en-us/azure/postgresql/flexible-server/concepts-logical#use-logical-replication-and-logical-decoding.

I hope this information helps. Please do let us know if you have any further queries.
SoniaD_74 0 Reputation points

2024-12-03T06:11:21.1533333+00:00
Hello Mahesh,

I have tried below steps:

Configured a PUBLISHER in the source database for a test table in a test database, enabling replication.

Provisioned a new flexible server in the azure environment to host the target database.

Set up a SUBSCRIBER in the target database, establishing the connection for data replication from the source.

However, no data is being copied from the source to the target, and the replication process does not appear to be functioning as expected. Further investigation is needed to resolve the issue.
SoniaD_74 0 Reputation points

2024-12-03T08:01:30.2833333+00:00

Also, one more point, I was using Azure migration service for this migration from the beginning and was getting the error which I have mentioned before.
Mahesh Kurva 1,070 Reputation points Microsoft Vendor

2024-12-03T09:44:56.88+00:00

Hi @SoniaD_74,

There may be a few areas to check in order to troubleshoot why the data isn't being replicated.

1.Check Replication Slots: Ensure that the replication slot is active and not inactive or dropped. You can check this using the pg_replication_slots view on the source server.

2.Network Connectivity: Verify that there are no network issues between the source and target servers. Ensure that the necessary ports are open and that there are no firewall rules blocking the replication traffic.

3.Replication Configuration: Double-check the configuration settings on both the source and target servers. Ensure that wal_level is set to logical, and that max_replication_slots and max_wal_senders are configured correctly on the source server.

4.Publication and Subscription: Ensure that the publication on the source server includes the correct tables and that the subscription on the target server is correctly set up to subscribe to the publication. You can use the following commands to verify:

On the source server: SELECT * FROM pg_publication;

On the target server: SELECT * FROM pg_subscription;

5.Logs and Errors: Check the PostgreSQL logs on both the source and target servers for any error messages or warnings that might indicate what is going wrong.

6.Initial Data Synchronization: Ensure that the initial data synchronization has completed successfully. Sometimes, large datasets can take a while to synchronize initially.

For detailed information, please refer to the following link: https://knowledge.enterprisedb.com/hc/en-us/articles/13522563395356-Logical-Replication-Steps-to-setup-and-maintain

I hope this information helps. Please do let us know if you have any further queries.
Mahesh Kurva 1,070 Reputation points Microsoft Vendor

2024-12-04T16:25:38.3033333+00:00

Hi @SoniaD_74,

We haven’t heard from you on the last response and was just checking back to see if you have a resolution yet. In case if you have any resolution please do share that same with the community as it can be helpful to others. Otherwise, will respond with more details and we will try to help.
SoniaD_74 0 Reputation points

2024-12-05T07:13:08.41+00:00

Hello Mahesh,

I am still facing the same issue. I have followed the steps you have mentioned but still data is not flowing from single server to flexible server.
Mahesh Kurva 1,070 Reputation points Microsoft Vendor

2024-12-05T09:25:45.9933333+00:00

Hi @SoniaD_74,

Could you please provide the specific error message you are receiving? This will help me better understand the issue and offer a more accurate solution.
Mahesh Kurva 1,070 Reputation points Microsoft Vendor

2024-12-06T09:29:53.17+00:00

Hi @SoniaD_74,

We haven’t heard from you on the last response and was just checking back to see if you have a resolution yet. In case if you have any resolution please do share that same with the community as it can be helpful to others. Otherwise, will respond with more details and we will try to help.
SoniaD_74 0 Reputation points

2024-12-09T06:44:08.71+00:00

Hi Mahesh,

Sorry for the delay in response. Unfortunately, for the publication and subscription, there was no error that I could see, only the data was not available in the target database even after checking all the points you have mentioned above.

Share via

Error While Migrating from Azure PostgreSQL Single Server to Flexible Server

Your answer