DOCSEARCHbyAlgolia
Submit Docs

PGSync Documentation

repository·main·Indexed 23 days ago

https://github.com/toluaina/pgsync

A real-time Change Data Capture (CDC) tool that synchronizes data from PostgreSQL, MySQL, and MariaDB to Elasticsearch or OpenSearch. PGSync uses JSON schemas to automate the denormalization of relational data into searchable documents via logical replication (WAL/Binlog). It includes a daemon for continuous replication, a bootstrap CLI for environment setup, an es_mapping tool for NGram analyzers, and a parallel_sync CLI for high-throughput initial synchronization.

Tokens
6.5K
Snippets
20
Records
37
Agent score
79%

What's inside PGSync

  1. What is PGSync?

    main
    PGSync is a change data capture (CDC) tool designed to sync data from PostgreSQL, MySQL, or MariaDB to Elasticsearch or OpenSearch in real-time. It allows you to maintain your relational database as the single source of truth while powering search engines with denormalized, nested documents. You define your document structure using a JSON schema, and PGSync automatically handles the complex JOINs and data propagation via logical replication (WAL/Binlog).

  2. Define a PGSync schema

    main

    A schema is a JSON file that maps database tables to document structures. You can define which columns to include and specify children to automatically denormalize related tables into nested documents.

    Example Schema:

    {
  "table": "book",
  "columns": ["isbn", "title", "description"],
  "children": [{
    "table": "author",
    "columns": ["name"]
  }]
}

    This schema will produce documents like:

    {
  "isbn": "9785811243570",
  "title": "Charlie and the Chocolate Factory",
  "authors": ["Roald Dahl"]
}

  3. How PGSync works and its core components

    main

    PGSync is a middleware designed to synchronize data from PostgreSQL to Elasticsearch or OpenSearch. It allows you to maintain PostgreSQL as your primary source of truth while exposing denormalized, structured documents in a search engine.

    Core Components

    • PostgreSQL: The source relational database. PGSync uses PostgreSQL's logical decoding to capture real-time changes.
    • Redis/Valkey: Acts as a message broker to manage sync state, queues, checkpointing, and locking.
    • Elasticsearch/OpenSearch: The destination search engine where documents are indexed.
    • PGSync Daemon: The core service that orchestrates the process by reading changes from PostgreSQL and indexing them into the search engine.

    Data Flow

    1. Schema Definition: Users provide a schema.json to map PostgreSQL tables to search engine documents.
    2. Bootstrap: PGSync initializes the necessary replication slots and triggers in PostgreSQL.
    3. Change Capture: The daemon listens for inserts, updates, and deletes via logical decoding.
    4. Transformation: Data is transformed into structured documents based on the provided schema.
    5. Indexing: Documents are sent to Elasticsearch/OpenSearch in near real-time.

  4. Configure PGSync using a JSON schema

    main
    To map PostgreSQL tables to Elasticsearch/OpenSearch documents, you must define a JSON schema (schema.json). This schema handles the mapping and transformations required to turn relational rows into structured, denormalized documents. PGSync supports complex nested relationships and joins through this schema-based approach.

  5. Configure MySQL or MariaDB for binary logging

    main

    To enable sync via binary logs in MySQL or MariaDB, update your my.cnf configuration and create a dedicated replication user.

    1. Update my.cnf:

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

    2. Create replication user:

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

  7. Run PGSync from a Python script

    main

    To integrate PGSync into a Python project, import the sync module from the pgsync package and call sync.main(). This serves as the entry point to initiate the synchronization process.

    from pgsync import sync
sync.main()

  8. Configure MySQL / MariaDB for binary logging

    main

    To use PGSync with MySQL or MariaDB, enable binary logging in your my.cnf and create a replication user.

    1. Update my.cnf:

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

    2. Create replication user:

    CREATE USER 'replicator'@'%' IDENTIFIED WITH mysql_native_password BY 'password';
GRANT REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO 'replicator'@'%';
FLUSH PRIVILEGES;
    server-id = 1
log_bin = mysql-bin
binlog_row_image = FULL
binlog_expire_logs_seconds = 604800
    CREATE USER 'replicator'@'%' IDENTIFIED WITH mysql_native_password BY 'password';
GRANT REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO 'replicator'@'%';
FLUSH PRIVILEGES;

  9. Configure PostgreSQL for logical replication

    main

    To use PGSync with PostgreSQL, you must enable logical decoding in your postgresql.conf file:

    wal_level = logical
max_replication_slots = 1

    Optionally, you can limit the WAL size to prevent disk exhaustion:

    max_slot_wal_keep_size = 100GB
    wal_level = logical
max_replication_slots = 1

# Optional
max_slot_wal_keep_size = 100GB

  10. Configure PostgreSQL for logical decoding

    main

    To use real-time sync via logical decoding in PostgreSQL, you must update your postgresql.conf file with the following settings:

    • wal_level = logical
    • max_replication_slots = 1 (or higher depending on your needs)
    wal_level = logical
max_replication_slots = 1

  11. Run PGSync with Docker Compose

    main

    PGSync provides Docker Compose configurations for quick local development.

    For Elasticsearch + Kibana (Default):

    git clone https://github.com/toluaina/pgsync
cd pgsync
docker-compose up

    For OpenSearch:

    docker-compose --profile opensearch up

    Default Ports:

    • PostgreSQL: 15432
    • Elasticsearch: 9201
    • Kibana: 5601
    • OpenSearch: 9400 (when using the opensearch profile)
    # Default (Elasticsearch + Kibana)
git clone https://github.com/toluaina/pgsync
cd pgsync
docker-compose up

# For OpenSearch
docker-compose --profile opensearch up

  12. Bootstrap and run PGSync

    main

    To set up your sync environment, follow these two steps:

    1. Bootstrap: Run this once to set up database triggers and replication slots.
    2. Run as daemon: Start the continuous sync process.

    Note: Both commands require the --config flag pointing to your JSON schema file.