How to Sync Azure Postgres from one instance to other instance

Question

Hello Everyone,

My requirement, we have Managed Azure PostgreSQL instance running in Prod and we want to replicate the Dev PostgreSQL every day might, through Azure Function, how than be accomplished.

I want to use Python for achieving this, any help is appreciated.

Thanks

Niranjan.

Answer 1

Hi @Niranjan Jannapureddy ,

Welcome to the Microsoft Q&A Platform! Thank you for asking your question here.

To synchronize an Azure PostgreSQL instance with another instance, you can use logical replication. Here’s a general approach you can take:

• You need to set up logical replication between your production and development PostgreSQL instances. This process includes creating a publication on the source (Production) instance and establishing a subscription on the target (Development) instance.
• On the Production instance, create a publication for the tables you wish to replicate. For instance:

    CREATE PUBLICATION my_pub FOR TABLE your_table_name;
  

• On the Development instance, set up a subscription that links to the publication you created on the Production instance. For example:

    CREATE SUBSCRIPTION my_sub CONNECTION 'host=prod_instance.postgres.database.azure.com user=your_user dbname=your_db password=your_password' PUBLICATION my_pub;
  

• You can leverage Azure Functions to automate this process. Create a Python function that executes the SQL commands to create or refresh the subscription on a scheduled basis (e.g., daily).
• Make sure to monitor the replication process and address any errors that may occur during the synchronization. This method enables you to keep your Development instance regularly updated with the latest data from your Production instance.

Also, for your better understanding, please refer to the following documentations:

https://learn.microsoft.com/en-us/azure/postgresql/flexible-server/concepts-logical#use-logical-replication-and-logical-decoding

https://learn.microsoft.com/en-us/azure/azure-arc/data/migrate-postgresql-data#migrate-postgresql-database-to-azure-arc-enabled-postgresql-server

I hope this helps! Let me know if you have any further questions or need additional assistance.