Metadata-Version: 2.4
Name: mega-db-sync
Version: 0.1.4
Summary: Pull-based incremental PostgreSQL table mirror (upsert + delete extras).
Author: Chris Spenner
License: MIT License
        
        Copyright (c) 2026 Chris Spenner
        
        Permission is hereby granted, free of charge, to any person obtaining a copy
        of this software and associated documentation files (the "Software"), to deal
        in the Software without restriction, including without limitation the rights
        to use, copy, modify, merge, publish, distribute, sublicense, and/or sell
        copies of the Software, and to permit persons to whom the Software is
        furnished to do so, subject to the following conditions:
        
        The above copyright notice and this permission notice shall be included in all
        copies or substantial portions of the Software.
        
        THE SOFTWARE IS PROVIDED "AS IS", WITHOUT WARRANTY OF ANY KIND, EXPRESS OR
        IMPLIED, INCLUDING BUT NOT LIMITED TO THE WARRANTIES OF MERCHANTABILITY,
        FITNESS FOR A PARTICULAR PURPOSE AND NONINFRINGEMENT. IN NO EVENT SHALL THE
        AUTHORS OR COPYRIGHT HOLDERS BE LIABLE FOR ANY CLAIM, DAMAGES OR OTHER
        LIABILITY, WHETHER IN AN ACTION OF CONTRACT, TORT OR OTHERWISE, ARISING FROM,
        OUT OF OR IN CONNECTION WITH THE SOFTWARE OR THE USE OR OTHER DEALINGS IN THE
        SOFTWARE.
        
Project-URL: Homepage, https://gitlab.com/chrisspen/db-sync
Project-URL: Documentation, https://gitlab.com/chrisspen/db-sync/-/blob/master/README.md
Project-URL: Source, https://gitlab.com/chrisspen/db-sync
Project-URL: Changelog, https://gitlab.com/chrisspen/db-sync/-/blob/master/CHANGELOG.md
Keywords: postgresql,sync,mirror,replication,database
Classifier: Development Status :: 4 - Beta
Classifier: Environment :: Console
Classifier: Intended Audience :: Developers
Classifier: License :: OSI Approved :: MIT License
Classifier: Operating System :: OS Independent
Classifier: Programming Language :: Python :: 3
Classifier: Programming Language :: Python :: 3.9
Classifier: Programming Language :: Python :: 3.10
Classifier: Programming Language :: Python :: 3.11
Classifier: Programming Language :: Python :: 3.12
Classifier: Topic :: Database
Classifier: Topic :: System :: Archiving :: Mirroring
Requires-Python: >=3.9
Description-Content-Type: text/markdown
License-File: LICENSE
Requires-Dist: psycopg2-binary>=2.9
Provides-Extra: dev
Requires-Dist: pytest; extra == "dev"
Requires-Dist: ruff>=0.16; extra == "dev"
Requires-Dist: pylint>=3.0; extra == "dev"
Requires-Dist: pre-commit>=3.5; extra == "dev"
Dynamic: license-file

# db-sync

[![master pipeline](https://gitlab.com/chrisspen/db-sync/badges/master/pipeline.svg)](https://gitlab.com/chrisspen/db-sync/-/commits/master)

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.

```text
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

```bash
pip install mega-db-sync
```

From a checkout:

```bash
pip install -e .
```

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

```bash
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:

```bash
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`](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.**

```toml
schema = "public"

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

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

```bash
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. That delete uses indexed equality (not
   `IS NOT DISTINCT FROM`), skips empty sides, and on large tables
   `ANALYZE`s stale dest stats and forces a hash join.
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`](docs/configuration.md) and
[`db-sync.toml.example`](db-sync.toml.example).

```toml
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:

```sql
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

```python
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

```bash
./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:

```bash
./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`](.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:

```bash
./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:

```bash
pre-commit run --all-files
```

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

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

## Publishing

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

```bash
./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:

```ini
[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](LICENSE).
