pgsync 7.0.5

Postgres/MySQL/MariaDB to Elasticsearch/OpenSearch sync

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.

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.9+

• 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.