Best dev DB for agents

A test database cloned straight from your production. No seeding. No dumps.

Setup

Before you start

On your primary

wal_level has to be logical (on RDS and Aurora, rds.logical_replication = 1 — either way a restart), a replication slot has to be free, and the account needs superuser, rds_superuser, or REPLICATION. Superuser also decides whether the schema follows, because CREATE EVENT TRIGGER takes nothing less.

On the machine that will run it

Linux with docker and python 3.10 or newer, run as root, plus disk for the pool. On macOS the installer builds the Linux machine itself.

All of it is checked before anything is created, and a failure names the setting that is missing.

1. Install

   One line. It then asks for a connection URI to your primary — or type demo to start on a sample database instead.

   Linux macOS

   $ curl -fsSL https://raw.githubusercontent.com/bhpark1013/Snaplicator/main/deploy/install.sh | sudo bash copy

2. Give it the primary’s URI

   It has to be a superuser. Schema changes are captured with event triggers, and CREATE EVENT TRIGGER is superuser-only in PostgreSQL — there is no GRANT for it. Putting a newly created table into the publication also needs ownership of that table.

   # what it creates on your primary — all named _snaplicator*
   _snaplicator_ddl_log          table        one row per CREATE / ALTER / DROP
   _snaplicator_capture_ddl      trigger      writes that row
   _snaplicator_capture_drop     trigger      the same, for drops
   _snaplicator_auto_add_<pub>   trigger      a new table joins the publication
   <publication>                 publication  created, or one you already have
   <slot>                        slot         opened by the replica, as any has

   Your rows are read, never written. wal_level=logical and the replication role are still yours to set — the installer checks both before it changes anything. Every statement to remove it again is in the README.

3. Pick the tables

   The UI lists every table with its size and row count. Nothing is copied until you choose. You can add more later.

4. Done — replication starts

   The first copy is the one slow step, and it happens once. After that the replica follows the primary on its own, rows and schema both.

5. Create a clone

   A writable Postgres on its own port. The reply carries the connection string, and that string is the whole handoff.

   create_clone(description: "agent-a / migration-42")
   → postgres://snaplicator:…@host:5455/appdb

   Anonymization is set here. Write it into configs/anonymize.sql or edit it from the UI — it runs inside every clone before the port is handed out. If it fails, the clone is destroyed instead of served. If you are not sure how to write it, ask your agent to write it and upload it for you (set_anonymize_sql).

   UPDATE user_account SET email = 'dev+' || id || '@example.local';
   UPDATE device_info  SET push_token = 'invalid-dev-token', is_push_enabled = false;

6. Take a snapshot

   A snapshot is a named instant of the replica, taken in O(1) and costing no disk until something writes. Clones start from it and reset back to it, so every run begins on the same data. Take a new one when you want newer data.

   create_snapshot(description: "before-migration-42")
   reset_clone_to_snapshot(clone_id: "5455", snapshot_name: "before-migration-42")

Worth knowing

Treat the replica as read-only — Postgres will not

This is a logical replica, not a physical standby, so nothing here rejects a write: pg_is_in_recovery() is false and the login is a superuser. Write to it anyway and the row is invisible to the primary and never travels back, while the primary’s next change to that row may quietly fail to apply — and an INSERT that collides with a primary key arriving later stops the apply worker outright. Snapshots carry it with them, so every clone taken afterwards inherits the difference. Write to a clone; that is what a clone is for.

DDL can be deferred, or fail

Slow DDL such as a large CREATE INDEX is moved to _snaplicator_ddl_deferred, and the sync loop runs it outside the apply transaction on its own. DDL that errors is written to _snaplicator_ddl_failures and left there — not retried, not patched over, because a schema you can see is wrong beats one that looks right and is not. Watch for it: add a Slack app to your workspace and paste its incoming-webhook URL into Config, and every DDL apply failure is posted to that channel, with the failing statements. Subscription trouble and schema drift go to the same place. Retrying a failed DDL is still manual — automatic retry is planned.

Hand it to an agent

Register the MCP server and the loop becomes the agent’s own — it creates a clone, works against it, resets it, deletes it. Register it one of two ways, depending on what you are handing over.

Scoped to one clone

For an agent doing a job. It may reset, refresh, snapshot and delete the clones you list, by port — and nothing else. Point it at another clone and the call is refused by name.

// .mcp.json
{
  "mcpServers": {
    "snaplicator": {
      "url": "http://snaplicator-host/api/mcp/?clones=5455"
    }
  }
}

Unscoped

Drop the parameter and nothing is held back: every clone, every snapshot, the replication settings and the primary’s triggers. This is the operator’s connection — yours, not something you hand out.

// .mcp.json
{
  "mcpServers": {
    "snaplicator": {
      "url": "http://snaplicator-host/api/mcp/"
    }
  }
}

The loop

# in the agent's own words
create_clone(description: "try #1")   → port 5455
… run the migration, run the suite, it fails …
reset_clone_to_snapshot(clone_id: "5455", snapshot_name: "before-migration-42")
… try #2 …
delete_clone(clone_id: "5455")