Skip to main content

PGSync

PyPI Version Python Versions License Downloads

PostgreSQL/MySQL/MariaDB to Elasticsearch/OpenSearch sync

PGSync is a middleware for syncing data from PostgreSQL, MySQL, or MariaDB to Elasticsearch or OpenSearch.

Keep your relational database as the source of truth and expose structured denormalized documents in your search engine.

Django integration: Use django-pgsync to integrate PGSync with Django projects.

PGSync Pro: Add real-time semantic and hybrid search to Elasticsearch or OpenSearch, with on-device or hosted embeddings that re-embed only when a document’s meaning changes.

ClickHouse connector: Stream PostgreSQL to ClickHouse in real time with pgsync-clickhouse, a change-data-capture sink that mirrors tables or builds denormalized wide tables on ReplacingMergeTree (one-to-one children flattened into typed columns, one-to-many into native Array(Tuple(...))), with exactly-once-on-replay keyed on the source WAL LSN.

Key Features

  • Real-time sync via logical decoding (PostgreSQL) or binary log (MySQL/MariaDB)

  • Denormalize complex relational data into nested search documents

  • JSON schema-based configuration

  • Support for one-to-one, one-to-many relationships

  • Plugin system for document transformation

  • Multiple operation modes: daemon, polling, or direct WAL streaming

  • Terminal monitor of events, throughput, slot lag, and errors (--monitor)

Requirements

Installation

Install from PyPI:

pip install pgsync

Database Setup

PostgreSQL

Enable logical decoding in your PostgreSQL configuration (postgresql.conf):

wal_level = logical
max_replication_slots = 1

MySQL / MariaDB

Enable binary logging in your MySQL/MariaDB configuration (my.cnf):

server-id = 1
log_bin = mysql-bin
binlog_row_image = FULL
binlog_expire_logs_seconds = 604800

Create a replication user:

CREATE USER 'replicator'@'%' IDENTIFIED WITH mysql_native_password BY 'password';
GRANT REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO 'replicator'@'%';
FLUSH PRIVILEGES;

Configuration

Create a JSON schema file (e.g., schema.json) defining your sync mapping:

[
    {
        "database": "book",
        "index": "book",
        "nodes": {
            "table": "book",
            "columns": ["isbn", "title", "description"],
            "children": [
                {
                    "table": "publisher",
                    "columns": ["name"],
                    "relationship": {
                        "variant": "object",
                        "type": "one_to_one"
                    }
                },
                {
                    "table": "author",
                    "label": "authors",
                    "columns": ["name", "date_of_birth"],
                    "relationship": {
                        "variant": "object",
                        "type": "one_to_many",
                        "through_tables": ["book_author"]
                    }
                }
            ]
        }
    }
]

See the examples directory for more schema examples (airbnb, social, rental, etc.).

Environment Variables

Configure PGSync via environment variables:

# Schema
SCHEMA='/path/to/schema.json'

# PostgreSQL
PG_HOST=localhost
PG_PORT=5432
PG_USER=postgres
PG_PASSWORD=*****

# Elasticsearch / OpenSearch
ELASTICSEARCH_HOST=localhost
ELASTICSEARCH_PORT=9200

# Redis (optional in WAL mode)
REDIS_HOST=localhost
REDIS_PORT=6379

Running

Bootstrap (run once to set up triggers and replication slots):

bootstrap --config schema.json

Run as daemon:

pgsync --config schema.json --daemon

Monitor

Use --monitor (or -m) to see the sync events in your terminal while PGSync runs. The monitor replaces the usual status line with a full-screen panel.

pip install 'pgsync[monitor]'

# Trigger mode
pgsync --config schema.json --daemon --monitor

# WAL mode
pgsync --config schema.json --wal --monitor

The header shows the health: healthy, backlog (the Redis queue has 1,000 events or more), slot lag (the replication slot is 100 MB or more behind), checkpoint stale, or errors (an error in the last 60 seconds).

The monitor shows these sections:

  • Pipeline: one row for each index, with the number of events read, the Redis queue size, the replication slot lag, the documents sent, the documents in the index, and the last checkpoint. An arrow shows if the queue or the slot lag increases (↑) or decreases (↓). A column with no values does not show.

  • Progress: a progress bar, the rate, and the estimated time to the end for the initial sync. Also a progress bar while PGSync reads the changes that occurred while it was stopped (catch-up).

  • Throughput: events and documents each second for the last 30 seconds, the highest value, the time since the last event, and the number of errors.

  • By table: the number of INSERT, UPDATE, DELETE, and TRUNCATE events for the busiest tables.

  • Messages: the last 4 different warnings and errors, with a count for a repeated message.

  • Live events: the most recent change events, with the primary key, the transaction ID, and the changed columns. The list fills the free rows of the terminal.

Press q to stop PGSync (the same as Ctrl+C). Press p to pause the event list, or to start it again. Press f to show the events of one table. Press d to see all the columns of the newest event. Press ? to see the help. Press Esc to go back to the event list.

PGSync stops immediately when you press q or Ctrl+C, also while a query waits for a lock. This is the same with and without --monitor.

Set MONITOR_THEME to default (dark background), light, classic (the terminal colors), or mono. Set MONITOR_COLORS to change single colors, for example MONITOR_COLORS="border=magenta,insert=#00ff00".

--monitor needs --daemon or --wal and an interactive terminal. It does not support polling mode, MySQL, or MariaDB. See the full description.

License

MIT License - see LICENSE for details.

Metadata

Release files for pgsync 8.0.0

For a detailed explanation of source distributions (sdists) and built distributions (wheels), please see the package formats documentation.

Source distribution (sdist)

Source distribution for pgsync 8.0.0
File Size Uploaded Provenance Actions
pgsync-8.0.0.tar.gz 234.2 kB With provenance Details for pgsync-8.0.0.tar.gz

Built distribution (wheel)

Table of built distributions (wheels) for pgsync 8.0.0
File Interpreter ABI Platform Actions
pgsync-8.0.0-py3-none-any.whl Python 3 none any Details for pgsync-8.0.0-py3-none-any.whl

Total release size: 348.9 kB

Release files / pgsync-8.0.0.tar.gz

Download URL pgsync-8.0.0.tar.gz
Size 234.2 kB
Tags Source
SHA-256 checksum
How to use checksums
2f9bdd9361b131a7363c3e21a8ea6ef592fcda3e55f3c928b1478a8b9ccc94fd
BLAKE2b-256 checksum
How to use checksums
03130e57e3f58662ec01791aace80a35e06fad15dfed8a58a7be6eb9ae4835e8
Upload date
Uploaded using Trusted Publishing?
What is trusted publishing?
Yes
Uploaded via twine/7.0.0 CPython/3.13.14

Provenance

Provenance describes where a file came from. On PyPI, provenance is shared via attestations, which provide a verifiable record of the build or publishing details. View details, limitations and caveats.

PyPI Publish Attestation

PyPI verified that this artifact, at this checksum, originated from the publisher listed below.

Signed by GitHub Actions, verified by PyPI on Nov 20, 2026.

Transparency log

Release files / pgsync-8.0.0-py3-none-any.whl

Download URL pgsync-8.0.0-py3-none-any.whl
Size 114.6 kB
Tags Python 3
SHA-256 checksum
How to use checksums
83a43166a8b60e21da14fbe893a5c2d27a7f8c8f07c90c138d7d2efd9a63f581
BLAKE2b-256 checksum
How to use checksums
b3c17d0414d9b6f3becde40c4952d423eb627043c0f5c7b68ff58f21068beac5
Upload date
Uploaded using Trusted Publishing?
What is trusted publishing?
Yes
Uploaded via twine/7.0.0 CPython/3.13.14

Provenance

Provenance describes where a file came from. On PyPI, provenance is shared via attestations, which provide a verifiable record of the build or publishing details. View details, limitations and caveats.

PyPI Publish Attestation

PyPI verified that this artifact, at this checksum, originated from the publisher listed below.

Signed by GitHub Actions, verified by PyPI on Nov 20, 2026.

Transparency log

Security for pgsync 8.0.0

Provenance

Provenance describes where a file came from. On PyPI, provenance is shared via attestations, which provide a verifiable record of the build or publishing details. View details, limitations and caveats.

Full provenance

Both files in this release have been uploaded with provenance metadata.

Select from 2 files

Attestation for pgsync-8.0.0.tar.gz

PyPI Publish Attestation

PyPI verified that this artifact, at this checksum, originated from the publisher listed below.

Signed by GitHub Actions, verified by PyPI on Nov 20, 2026.

Transparency log

A note on package security

PyPI is a repository for distributing packages, not a service for auditing them. While third-party security researchers and our staff do their best to detect and remove malicious packages, PyPI cannot guarantee the quality, security or suitability of any given package.

To learn more about protecting yourself from malicious actors, visit our package security documentation.

Release history Release notifications | RSS feed

This release

8.0.0 This release

2 release files Full provenance

Anthropic, PBC Visionary sponsor Bloomberg Visionary sponsor Hudson River Trading Visionary sponsor Meta Visionary sponsor NVIDIA Visionary sponsor Microsoft Sustainability sponsor Depot Continuous Integration AWS Cloud computing and Security Sponsor Datadog Monitoring Fastly CDN Google Download Analytics Sentry Error logging StatusPage Status page