Postgresql HYBRID database

The process

Bi-directional replication:

The local database is hosted via Docker Desktop, while the cloud database runs on an AWS RDS instance. To maintain consistent replication between the two, I developed a script that dynamically enables or disables bi-directional replication based on the status of the local database. For instance, when the local database is offline, replication on AWS is paused to prevent a backlog of transactions, avoiding potential high storage costs. Once the local database is back online, the script verifies the replication status and ensures all nodes are synchronized. This script runs every five minutes on an AWS EC2 instance.

Code for interacting with the database:

To ensure consistent updates across both databases, we created a standardized script for uploading and retrieving data. This script helps maintain uniformity in data handling and guarantees synchronization between the local and cloud databases.

Docker database setup:

The local database is deployed in a PostgreSQL Docker container, with the connection between AWS and the local database secured via SSL and hosted on a private VPN. As a redundancy measure, a Python script within the PostgreSQL container toggles replication based on the local database’s status. I also developed a Dockerfile that automates the entire database setup, including the configuration of replication scripts, user accounts, and system settings.

Tools/Frameworks I used: PostgreSQL, Python, Docker Desktop