this program is used to replicate a postgres server using triggers for inserts and deletes and rabbitmq to broadcast the changes to all database replicators.
postgres supports LISTEN
and NOTIFY
commands. meaning that one client can listen to events on a certain 'channel'
and the server can notify the listeners using the NOTIFY
command.
we define SQL triggers on inserts and updates(and possibly more) so that on each change we notify the listeners about the changes done.
changes_publisher.py is a listener to these database notifications. and when it receives the incoming data it broadcasts them to replicators through rabbitmq messaging service. this way only one connection is made with the postgres server and it uses async function.
messages about changes are persisted in the rabbitmq server.
changes are then received by changes_listener.py
which then commit the changes to the replica database.also using
async functions.
- first run the postgres server
- add the triggers for the tables (in this case the table is called users)
CREATE OR REPLACE FUNCTION notify_account_changes()
RETURNS trigger AS
$$
BEGIN
PERFORM pg_notify(
'users_changed',
json_build_object(
'table', TG_TABLE_NAME,
'operation', TG_OP,
'new_record', row_to_json(NEW),
'old_record', row_to_json(OLD)
)::text
);
RETURN NEW;
END;
$$ LANGUAGE plpgsql;
CREATE TRIGGER users_changed
AFTER INSERT OR UPDATE
ON users
FOR EACH ROW
EXECUTE PROCEDURE notify_account_changes();
- start the rabbitmq server and configure the credentials in the python files
- run
pipenv install
andpipenv shell
(install pipenv if not found) - start the changes publisher
python3 changes_publisher.py
- start the change listener programs for as many replicas as you want
- insert or update rows and observer the listener logs and the databases