db-sync

Pull-based incremental PostgreSQL mirror. Point it at a source (typically production, read-only) and a destination (typically localhost). Destination becomes a true copy of source for one schema, without a logical-replication slot.

source (read)                          dest (write)
─────────────                          ────────────
SELECT every public table    COPY      INSERT … ON CONFLICT (pk)
                             ──────►   DELETE dest rows missing on source
no slot, no WAL retained               _sync_watermarks high-water marks

You decide when to pull. Source does not know dest exists.

Install

pip install mega-db-sync

From a checkout:

pip install -e .

Either way you get the db-sync executable on your PATH:

db-sync --help
db-sync --version
python -m db_sync --help

Requires Python 3.9+ and PostgreSQL on both ends. The only runtime dependency is psycopg2.

Quick start

In any project with prod + local Postgres:

export DB_SYNC_SOURCE_PASSWORD='...'
export DB_SYNC_DEST_PASSWORD='...'

db-sync --source postgresql://reader@prod.example:5432/app \
        --dest   postgresql://app@localhost:5432/app \
        --list

db-sync --source postgresql://reader@prod.example:5432/app \
        --dest   postgresql://app@localhost:5432/app

--list prints the table plan and does not write. A real run upserts, then deletes dest rows whose primary key is gone on source, then resyncs sequences.

Copy db-sync.toml.example to db-sync.toml in the project root so you do not have to pass URLs every time. Do not commit passwords.

schema = "public"

[source]
host = "prod.example"
user = "app_readonly"
db = "app"

[dest]
host = "localhost"
user = "app"
db = "app"

cd /path/to/that/project
db-sync --list
db-sync --dry-run
db-sync

How a run works

1. Discover every ordinary / partitioned-parent table in the schema (not _sync_* bookkeeping, not partition children).
2. Classify each table (see below). Optional hints override this.
3. Upsert parents before children (foreign-key order): stream rows with binary COPY, merge with INSERT … ON CONFLICT (pk) DO UPDATE. Local rows that share a unique key with a source row but a different PK are deleted first so surrogate-id drift cannot fail the insert.
4. Delete extras, children first: copy source primary keys, then DELETE FROM dest WHERE pk NOT IN source.
5. Refresh materialized views that exist on both sides.
6. setval every dest sequence to MAX(owning column).

Crash safety: each table commits on its own. A watermark advances only after that table succeeds. Ctrl-C and re-run; finished tables are skipped or incremental.

Table classes

Class	When	What happens
A	Timestamp column (updated_at, last_modified, last_updated, modified_at) or a single integer PK on a large table (default ≥ 50 000 estimated rows)	Incremental. Dest stores a high-water mark in _sync_watermarks. Next run is WHERE watermark > last. First run with no mark copies the whole table.
B	Everything else with a primary key	Full refresh every run (still upsert, not truncate).
T	No primary key	TRUNCATE dest, refill from source. Rare; add a PK if you can.

--hints / [hints] in the toml override auto-classification.

Tables named _sync_* (the watermark table and staging names) are never mirrored.

Configuration

Precedence, highest first:

1. CLI flags
2. Environment variables
3. db-sync.toml or db-sync.json in the current directory (--config FILE or DB_SYNC_CONFIG to point elsewhere)

Environment

Variable	Meaning
DB_SYNC_SOURCE_URL	postgresql://user:pass@host:port/db
DB_SYNC_DEST_URL	Same for dest
DB_SYNC_SOURCE_HOST / _PORT / _USER / _PASSWORD / _DB	Split form
DB_SYNC_DEST_HOST / _PORT / _USER / _PASSWORD / _DB	Split form
DB_SYNC_SCHEMA	Schema to mirror (default public)
DB_SYNC_SIZE_THRESHOLD	Row estimate that promotes integer-PK tables to class A (default 50000)
DB_SYNC_CONFIG	Path to a toml/json config file

Passwords belong in the environment, not in git.

Project file

See docs/configuration.md and db-sync.toml.example.

schema = "public"
exclude = ["sessions"]

[source]
host = "prod.example"
user = "app_readonly"
db = "app"

[dest]
host = "localhost"
user = "app"
db = "app"

[hints.events]
class = "A"
watermark = "id"

CLI

db-sync --help
db-sync --version

db-sync --list
db-sync --dry-run
db-sync --only some_table
db-sync --class A
db-sync --exclude sessions --exclude cache
db-sync --no-delete
db-sync --no-sequences
db-sync --no-matviews
db-sync --schema app
db-sync --config /path/to/db-sync.toml
db-sync --hints hints.json

--dry-run counts rows that would be pulled and checks schema drift; it does not write. --no-delete upserts only (dest keeps rows source deleted). --only is useful while debugging one table.

Exit status: 0 all tables ok, 2 one or more table errors (others still ran), 130 interrupted, 1 unexpected crash.

Permissions

Source role: CONNECT, USAGE on the schema, SELECT on every table and sequence you want mirrored. A typical prod-readonly grant:

GRANT USAGE ON SCHEMA public TO app_readonly;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO app_readonly;
GRANT USAGE, SELECT ON ALL SEQUENCES IN SCHEMA public TO app_readonly;
ALTER DEFAULT PRIVILEGES FOR ROLE app IN SCHEMA public
    GRANT SELECT ON TABLES TO app_readonly;

Default privileges without FOR ROLE only cover objects created by the role that ran ALTER DEFAULT PRIVILEGES (often postgres). Tables created by the application role need ALTER DEFAULT PRIVILEGES FOR ROLE app … or new tables will 403.

Dest role: table owner, or INSERT / UPDATE / DELETE plus the right to CREATE the _sync_watermarks table and temp staging tables. Dest schema must already exist and match source (run migrations first). db-sync does not apply DDL.

Python API

from db_sync import ConnectionSpec, DatabaseSync

sync = DatabaseSync(
    source=ConnectionSpec.from_url("postgresql://reader@prod/app"),
    dest=ConnectionSpec.from_url("postgresql://app@localhost/app"),
    schema="public",
    exclude=["sessions"],
    hints={"events": ("A", "id", "id")},
)
status = sync.run()           # 0 ok, 2 some tables failed
sync.run(only="events", dry_run=True)

hints maps table name → (class, pk, watermark). pk is unused at runtime (live primary keys come from the catalog); watermark is the class-A column.

What this is not

• Not logical or streaming replication. There is no slot, no apply worker, no WAL retained on source for an offline dest.
• Not a schema migrator. Column drift (source has a column dest lacks) skips that table with a message. Dest-only columns are left alone.
• Not multi-schema. One schema per run (--schema).
• Not a backup tool. Dest is a working copy, not a PITR archive.

Development

./test.sh

That uses the same per-machine venv as ./publish.sh (.env-<hostname>, created on first run) and runs python -m unittest discover -s tests. A venv is not portable across hosts, so each machine gets its own. Pass extra arguments to target a subset:

./test.sh tests.test_cli
./test.sh tests.test_sql.QuoteIdentTest

GitLab CI runs ruff + pylint, then the same suite on Python 3.9–3.12, for every push to master and every merge request. See .gitlab-ci.yml.

Lint

Ruff and pylint run as git pre-commit hooks and as a GitLab CI lint job. One-time setup on each machine you commit from:

./install-pre-commit-hooks.sh

That installs pre-commit via pipx and writes a hook into .git/hooks/ (not part of the repo). Commits then run ruff and pylint with no project venv activated. Do not run pre-commit install; it hardcodes a Python path and breaks the next shell that does not have it.

Run them against the whole tree with:

pre-commit run --all-files

Or from the project venv (pip install -e ".[dev]"):

ruff check src tests
ruff format --check src tests
pylint src/db_sync tests

Publishing

Bump __version__ in src/db_sync/__init__.py, then:

./publish.sh

That builds an sdist and uploads it with twine upload --repository db-sync. Twine only loads repositories listed under [distutils] index-servers. A [db-sync] heading by itself is not enough. ~/.pypirc should look like:

[distutils]
index-servers =
    pypi
    django-chroniker
    db-sync

[db-sync]
repository = https://upload.pypi.org/legacy/
username = __token__
password = pypi-...

username is the literal string __token__; password is a PyPI API token. chmod 600 ~/.pypirc. The script creates a per-machine .env-<hostname> venv on first run and installs build + twine there.

Layout:

src/db_sync/     package (cli, engine, sql builders)
tests/           unittest, no database required
docs/            configuration and algorithm notes

License

MIT. See LICENSE.