mimeo

Overview

name conflict with pg_partman

Version

Build

You can build the RPM packages for mimeo using pig build:

pig build pkg mimeo         # build RPM packages

Install

You can install mimeo directly. First, make sure the PGDG and PIGSTY repositories are added and enabled:

pig repo add pgsql -u          # Add repo and update cache

Install the extension using pig or apt/yum/dnf:

pig install mimeo;          # Install for current active PG version

pig ext install -y mimeo -v 18  # PG 18
pig ext install -y mimeo -v 17  # PG 17
pig ext install -y mimeo -v 16  # PG 16
pig ext install -y mimeo -v 15  # PG 15
pig ext install -y mimeo -v 14  # PG 14

dnf install -y mimeo_18       # PG 18
dnf install -y mimeo_17       # PG 17
dnf install -y mimeo_16       # PG 16
dnf install -y mimeo_15       # PG 15
dnf install -y mimeo_14       # PG 14

apt install -y postgresql-18-mimeo   # PG 18
apt install -y postgresql-17-mimeo   # PG 17
apt install -y postgresql-16-mimeo   # PG 16
apt install -y postgresql-15-mimeo   # PG 15
apt install -y postgresql-14-mimeo   # PG 14

Create Extension:

CREATE EXTENSION mimeo CASCADE;  -- requires: dblink

Usage

mimeo: Extension for specialized, per-table replication between PostgreSQL instances

Provides per-table replication between PostgreSQL instances with snapshot (full copy), incremental (timestamp/id based), and DML (insert/update/delete) modes.

Enabling

CREATE SCHEMA mimeo;
CREATE EXTENSION mimeo SCHEMA mimeo;

Requires the dblink extension. Optionally install pg_jobmon for monitoring.

Setting Up a Data Source

-- Create a dblink connection to the source database
SELECT mimeo.dblink_mapping_create(
    p_mapping_name := 'source_db',
    p_data_source := 'host=sourcehost dbname=sourcedb user=replicator password=secret',
    p_superuser := true
);

Snapshot Replication (Full Table Copy)

Copies the entire source table each time it runs:

SELECT mimeo.snapshot_maker(
    p_src_table := 'public.my_table',
    p_dblink_id := 1  -- from dblink_mapping
);

-- Refresh the snapshot
SELECT mimeo.refresh_snap('public.my_table');

Incremental Replication (Timestamp-Based)

Replicates rows based on an incrementing timestamp column:

SELECT mimeo.inserter_maker(
    p_src_table := 'public.events',
    p_control := 'created_at',  -- timestamp column
    p_dblink_id := 1
);

-- Refresh incrementally
SELECT mimeo.refresh_inserter('public.events');

For tables with updates (not just inserts):

SELECT mimeo.updater_maker(
    p_src_table := 'public.orders',
    p_control := 'updated_at',
    p_dblink_id := 1
);

SELECT mimeo.refresh_updater('public.orders');

DML Replication (Insert/Update/Delete)

Full DML tracking via triggers on the source:

SELECT mimeo.dml_maker(
    p_src_table := 'public.accounts',
    p_dblink_id := 1
);

SELECT mimeo.refresh_dml('public.accounts');

Scheduling Refreshes

Use pg_jobmon or cron to schedule periodic calls to the appropriate refresh_* function.

Key Features

• Three replication modes: snapshot, incremental, DML
• Per-table replication (no need to replicate entire database)
• Works between different PostgreSQL versions
• Built on dblink for cross-database communication