A PostgreSQL mirror that loses nothing, even when the load fails
You have an application database and you want a copy of one of its tables somewhere else, for reporting. Copying the whole table every night works until the table gets big, or until you need the deletes too. The usual answer is change data capture: read the inserts, updates and deletes from PostgreSQL's write-ahead log and apply them to the copy. The usual tool for that is Debezium, which…
This account describes a PostgreSQL mirror that reliably copies table changes, even in the event of a load failure. The system uses PostgreSQL's built-in logical replication features, which are leveraged by the Hydra ETL tool. The implementation is simple, requiring only Docker and Python to set up. The process begins by cloning a repository that contains the necessary scripts and SQL files.
Upon running the system for the first time, it creates a publication and a replication slot in the source database, followed by the creation of a mirror table in the destination database. The destination then applies the received changes using an upsert mode, which ensures that all insertions and updates overwrite existing rows, while deletions are also applied.
If a failure occurs during the load process, the mirror maintains its integrity by adhering to the constraints defined in the application database, such as checking for non-negative amounts. The system demonstrates this robustness through a scenario where a new rule is added to the mirror database that prohibits negative amounts, and a bad order with a negative amount is intentionally inserted into the source database.
Despite this failure, the mirror successfully reflects the majority of the changes in the destination database, maintaining data consistency and reliability.
Written by urgent.news from Dev.to's reporting — not their text. Machine-written — may contain errors; check the original before relying on it.
