relaynetwork/irmgard

Postgres Data Replication Framework

Irmgard

PostgreSQL Change Detection and Replication Framework for Clojure.

Irmgard uses PostgreSQL Triggers to watch for changes to row data, and leverages LISTEN and NOTIFY to notify your code so you can react as quickly as possible.

Installing Irmgard in your database

Run install.sh

 bash install.sh [[database_name]] sql/plpgsql/irmgard.sql 

This will:

• Create irmgard schema
• Create irmgard.row_changes table
• Create irmgard.stats table
• Create irmgard.process_log table
• Installs PL/PGSQL procedures

To enable Irmgard to track a table, execute the following in your database:

SELECT irmgard.enable_tracking_int_pk('your_schema_name.your_table_name', 'id');

Starting the Irmgard process in your JVM

The Irmgard process is implemented as a background thread. The thread attaches to the database and executes the PostgreSQL LISTEN command. This thread will re-connect to the databse and re-execute LISTEN if any error occurrs. Irmgard also performs a poll every few seconds in case the LISTEN infrastructre does not notify it of changes.

When notified, the Irmgard process attempts to obtain a lock on irmgard.row_changes with NOWAIT. If it obtains the lock, it then processes the records in irmgard.row_changes, triggering any registered listeners for groups of those rows.

Registering Listeners

Listeners are registered via the following tuple:

 [ listener-name, database-name, schema-name, table-name, implementation-fn ]

Maintenence

Suspending Triggers/Events during ALTER TABLE or bulk UPDATE

NOT YET IMPLEMENTED

For very large tables, if you perform an ALTER or bulk UPDATE commands, you may want to suppress event generation during those operations. To suspend the trigger’s, call the following pl/pgsql function:

 irmgard.disable_all_events()
      irmgard.disable_all_events(table_name)

To re-enable, call the following pl/psql function:

 irmgard.enable_all_events()
      irmgard.enable_all_events(table_name)

Etymology of Irmgard, see: “Irmgard Replicant”.

irmgard's Issues

Allow granting of permissions to service_user when irmgard is installed

service_user will need grants on irmgard schema/tables in order to actually use irmgard...

create helper function to install schema/tables and grant permissions to some user

unregister should not throw an NPE

Currently it throws an NPE if the listener name is not registered (via calling name with nil).

Swap watcher's control ref for java.util.concurrent.AtomicBoolean

This should result in a minor reduction in overhead.

remove 'config' and other test helpers from main code

There are several helper functions in core.clj that are there to help with the example and test code. They should be removed before a first official release.

Handle not getting the lock in irmgard clojure listener

process-row-changes: group records by schema/table

Implement the reduction to pass observer-fn a seq of records.

Enhance irmgard.upsert_row_change to do an UPSERT

It currently just does an insert. Also add the functional indexes to ensure uniqueness of tracked rows.

Convert observer-fn to take a seq

process-row-changes currently sends 1 record at a time into the observer-fn, when it selects a page of records from row_changes, it should group them by schema/table and hand those to the observer-fn's as a seq, allowing for smarter bulk operations.

Reconnect to Database If Connection Breaks

The watcher is not robust against its database connection breaking. Add a loop/recur and try/catch around the establishment of the connection before checking for changes.

implement tracking functions for text primary keys

Parallel the int primary key implementation.

Irmgard should log an error if no listener is registered

Currently if row changes are identified, and there is no listener for the changes, Irmgard discards the records. It should also issue a log/error indicating the database, schema and table name for which it was unable to find a listener.