UAT and PROD - Azure Postgres DB Flexible Servers

Question

Hi Team,

We are currently evaluating on how we can ensure that UAT and PROD DB(Postgres DB Flexible Servers) can reflect the same, through automation (Complete Migration and Change only), like to idenitfy the best suggested tools, like Data Migration Service which unfortunatey is only available for single sever not flexible.

We have evaluated pgdump and restore as a option but given the massive DB size, it might be too inefficient.

Appreciate any feedback.

Answer 1

Hi Jack Louvre,

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

As we understand that, you are looking for efficient tools to automate the migration and synchronization of your UAT and PROD PostgreSQL Flexible Servers.

Please refer the below suggested tools and approaches:

• pgcopydb: This open-source tool can automate the process of copying a database between PostgreSQL servers. It offers several benefits over native PostgreSQL tools, such as removing the need for intermediate files, parallelizing index creation, and multi-threaded table copying. It also supports Change Data Capture for data synchronization, which can minimize downtime. Please refer to this document https://pgcopydb.readthedocs.io/en/latest/
• Logical Replication: PostgreSQL's native logical replication allows selective data replication (table-based) and can be used for ongoing synchronization without needing a full dump. Please refer to this document. https://www.enterprisedb.com/postgres-tutorials/logical-replication-postgresql-explained https://neon.tech/docs/guides/logical-replication-concepts
• pgBackRest: An advanced backup and restore tool that supports incremental backups and parallel processing for large data volumes.
• Change Data Capture (CDC): Using Debezium or other CDC tools with Kafka or Azure Event Hubs to replicate changes in near real-time.

Please refer to the below mentioned links for more information.

https://www.matillion.com/learn/blog/postgres-database-migration-platforms

https://airbyte.com/top-etl-tools-for-sources/postgres-migration-tool

https://hevodata.com/learn/best-postgres-database-migration-tools/

I hope, This response will address your query and helped you to overcome on your challenges.

If this answers your query, do click Accept Answer and Yes for was this answer helpful. And, if you have any further query do let us know.