I am writing a program in Golang where I need to set up redundant databases. Here’s the requirement:
I have two databases that act as redundant to each other.
The application first tries to write data into the primary database.
If the data is successfully recorded in the primary database, that database should update the secondary database.
If for any reason the application cannot write to the primary database, it should attempt to write the data into the secondary database.
The secondary database should also synchronize back to the primary database whenever primary database becomes available.
In other words, both databases must be able to accept writes and keep each other up to date.
Additional details:
The data ingestion rate is high, around 100 records per minutes.
One of the databases may be unavailable for up to one week, and when it comes back online it needs to be updated with all the missing data.
I’m looking for a way to implement this setup in PostgreSQL or SQL Server.
I tried PostgreSQL logical replication, but I’m not sure if it’s the best solution for my scenario.
2 Answers 2
Given the low amount of writes (9100 records/min isn't much), and on the other hand being offline-time for a week, you could also look at SQL Server Merge-Replication. https://learn.microsoft.com/en-us/sql/relational-databases/replication/merge/merge-replication?view=sql-server-ver17 However, SQL Server Availability Groups, which J.D. points to, are really easy to work with and do all the failover logic and pointing to the right target for you. So if you can architect it using those, you may be better off. But there may be more requirements, hence do a proper comparison for what you need.
In SQL Server, this is a good fit for AlwaysOn Availability Groups:
The Always On availability groups feature is a high-availability and disaster-recovery solution that provides an enterprise-level alternative to database mirroring. Always On availability groups maximizes the availability of a set of user databases for an enterprise. An availability group supports a failover environment for a discrete set of user databases, known as availability databases, that fail over together. An availability group supports a set of read-write primary databases and one to eight sets of corresponding secondary databases. Optionally, secondary databases can be made available for read-only access and/or some backup operations.
FWIW, this is not a lot of data ingestion:
The data ingestion rate is high, around 100 records per minutes.
I wouldn't be concerned about the amount or rate of data ingestion.
One of the databases may be unavailable for up to one week, and when it comes back online it needs to be updated with all the missing data.
That's quite a while but the goal of AlwaysOn Availability Groups is to keep all replicas in sync. So once that secondary comes back online, it would automatically begin syncing again.