Is there a way to nightly sync one DB with another?

mitya33 1

Is there a way to set up a nightly sync between two (Azure-hosted) Postgres databases?

I've read about Azure Data Sync for SQL, but from what I can tell it doesn't apply to Postgres DBs.

Basically we have a live DB, and a stage DB, and we would like the contents of the live DB to be copied to the stage DB every 24 hours.

Thanks in advance

Oury Ba-MSFT 18,616 Reputation points Microsoft Employee

2024-08-21T15:22:56.6333333+00:00

@mitya33 Thank you for reaching out.

Yes, there are ways to sync between two Azure-hosted Postgres databases. Since Azure Data Sync for SQL doesn't apply to Postgres DBs, you can consider using logical replication in Azure PostgreSQL Flexible server. It is a built-in feature in PostgreSQL that allows you to replicate data between tables in two separate Postgres databases. It follows a publisher and subscriber model where the publisher database sends incremental changes to the subscriber database.Logical replication and logical decoding - Azure Database for PostgreSQL - Flexible Server
Oury Ba-MSFT 18,616 Reputation points Microsoft Employee

2024-08-22T23:15:13.7966667+00:00

@mitya33

We haven't not heard back from you since my last comment. Could you please share the status of the issue. Please feel free to reach out if you need more assistance or further clarification.

Regards,

Oury
mitya33 1 Reputation point

2024-08-23T15:25:20.18+00:00

Thank you for this. I'll have a read. With native replication, do I need to "schedule" this replication somehow, or does it happen automatically, whenever changes are made to the publisher DB, i.e. they trickle down to the subscriber? Does this add latency to requests if this job has to happen after each write?

1 answer