README.rst

PGSync

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

Requirements

• Python 3.10+
• PostgreSQL 9.6+ or MySQL 8.0.0+ or MariaDB 12.0.0+
• Redis 3.1.0+ or Valkey 7.2.0+ (optional in WAL mode)
• Elasticsearch 6.3.1+ or OpenSearch 1.3.7+

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

Links

• Documentation: https://pgsync.com
• Source Code: https://github.com/toluaina/pgsync
• Bug Reports: https://github.com/toluaina/pgsync/issues
• Sponsor: https://github.com/sponsors/toluaina

License

MIT License - see LICENSE for details.