PGSync for PostgreSQL, MySQL, and MariaDB Search Sync

What PGSync Solves for Search Backends

PGSync solves the problem of keeping a search index synchronized with data that remains in PostgreSQL, MySQL, or MariaDB. The source database stays the system of record, while Elasticsearch or OpenSearch receives documents designed for search and retrieval. The intended users are developers and data teams that want a relational-to-search synchronization path without writing a separate application-specific ETL pipeline.

The central design choice is configuration through a JSON document structure. A mapping identifies a source table, selected columns, and child tables that should be included in the resulting search document. Relationships are therefore expressed as data rather than implemented as custom synchronization code. This can be useful when an application has several tables whose combined representation needs to be searchable as one document.

PGSync is not a general-purpose bidirectional replication system. Its documented destination is Elasticsearch or OpenSearch, and its source is one of the three supported relational databases. The README also identifies separate projects or add-ons for other directions, including django-pgsync for Django integration and pgsync-clickhouse for PostgreSQL to ClickHouse. That boundary matters when choosing a tool, because a project needing those destinations should not treat PGSync itself as a universal connector.

Database CDC and Document Generation

The documented data path starts with database change-data capture. PGSync reads PostgreSQL WAL through logical replication, or reads MySQL and MariaDB binary logs. It then builds denormalized search documents from the configured tables and sends them to Elasticsearch or OpenSearch through bulk indexing.

The schema controls which columns are selected and which related records are nested. In the book example, the `book` table provides `isbn`, `title`, and `description`, while the `author` child table contributes author names. According to the README, PGSync generates queries for these relationships and transforms the result into a document containing fields such as:

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

That means a change in a related author table can affect the indexed representation of a book document. This is the main reason to use PGSync instead of maintaining separate index-building code for each search view. It also creates a design dependency: the source relationship must be represented clearly in `schema.json`, and the resulting nested document must match the search requirements.

The README describes commit-order processing and checkpoint-based resumption after crashes. Those are operational claims in the project documentation, not independent verification of delivery guarantees in a particular deployment. Operators should still account for the replication settings and database permissions required by their selected source.

PGSync Installation with pip

The shortest documented pip installation is the following command. It installs the `pgsync` package from PyPI.

pip install pgsync

After installation, the README describes a one-time bootstrap step for a configuration file, followed by the synchronization command. The bootstrap command creates or prepares the resources needed for the configured setup.

bootstrap --config schema.json

The first run uses the same schema file and enables daemon mode according to the README:

pgsync --config schema.json -d

The `-d` option is shown as the documented daemon mode. The README does not give a complete schema file that connects every required environment variable, nor does it document a separate command for creating the Elasticsearch mapping in this quick-start sequence. A first real use should therefore begin with a small `schema.json`, confirm that the database is configured for replication, and inspect the destination search engine for the expected document fields.

PGSync requires Python 3.10 or newer. The documented source requirements are PostgreSQL 9.6 or newer, MySQL 5.7.22 or newer, or MariaDB 10.5 or newer. The destination requirement is Elasticsearch 6.3.1 or newer, or OpenSearch 1.3.7 or newer. Redis 3.1 or newer, or Valkey 7.2 or newer, is listed as optional in WAL mode.

PGSync Docker Compose Services

The repository provides a Docker Compose path for a local stack. The README says that the default configuration starts PostgreSQL, Redis, Elasticsearch, Kibana, and PGSync. The services are exposed on specific host ports, so the local endpoints differ from the container ports used by Elasticsearch and PostgreSQL.

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

The repository's `docker-compose.yml` maps PostgreSQL to port `15432`, Elasticsearch to `9201`, Kibana to `5601`, and the OpenSearch service to `9400` under the OpenSearch profile. The OpenSearch setup uses a separate Compose command:

docker-compose --profile opensearch up

The Compose file configures PostgreSQL with logical WAL settings through its container command. It also defines Elasticsearch 7.17.13 and Kibana 7.17.13 images. These are repository configuration details, not a statement that the same versions are required for every PGSync deployment. The README lists a wider supported Elasticsearch range.

The default Docker command in the README demonstrates the environment variables used by PGSync:

docker run --rm -it \
  -e PG_URL=postgres://user:pass@host/db \
  -e ELASTICSEARCH_URL=http://localhost:9200 \
  -e REDIS_HOST=localhost \
  -v "$(pwd)/schema.json:/app/schema.json" \
  toluaina1/pgsync:latest -c schema.json -d -b

This command mounts the schema into the container, supplies source and destination connection settings, and passes `-c schema.json -d -b` to the image. The README documents this as the fastest Docker path, but it does not explain every option in that command line. Teams should treat the repository example and environment names as the reference for their own deployment.

Database Replication Boundaries

PGSync cannot capture database changes until the source is configured for change-data capture. PostgreSQL needs logical decoding in `postgresql.conf`, including logical WAL level and replication slot capacity.

wal_level = logical
max_replication_slots = 1

The README also documents an optional WAL retention setting:

max_slot_wal_keep_size = 100GB

For MySQL and MariaDB, binary logging must be enabled. The documented settings include a server identifier, an enabled binary log, full row images, and log expiration:

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

The replication account needs replication privileges, so it is created separately from the application account in the example:

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

These settings introduce a deployment boundary. PGSync is not just a Python process that opens a normal SQL connection. The source database must be willing to expose its change stream, and retention settings must match how quickly the consumer processes changes. The README does not document a compensating design for a source that cannot enable logical replication or binary logging.

PGSync Configuration Trade-offs and Alternatives

PGSync's JSON mapping is its main convenience and its clearest constraint. It lets a team describe a root table and child tables without writing synchronization code, but the documentation does not describe a visual mapping editor, a general transformation language, or a documented mechanism for arbitrary application-specific document assembly. Complex search documents therefore need to fit the table and child mapping model.

For Django projects, the README names `django-pgsync` as the integration path. That is a different adoption route because it packages the PGSync integration for Django rather than requiring the standalone CLI workflow to be wired into the project. For PostgreSQL to ClickHouse, the README points to `pgsync-clickhouse`, whose stated purpose is streaming PostgreSQL changes into ClickHouse tables or denormalized wide tables on `ReplacingMergeTree`. Choosing PGSync directly would leave ClickHouse-specific data modeling and delivery requirements outside this project.

The README also describes PGSync Pro as adding semantic and hybrid search with embeddings. That is not a replacement for the core search synchronization feature. It is an additional capability aimed at projects that need meaning-based retrieval. The core project should be judged on relational change capture and Elasticsearch or OpenSearch indexing, not assumed to include embedding generation or hybrid ranking.

PGSync is the wrong choice when the destination is ClickHouse, when semantic or hybrid search is mandatory, or when the application needs a document shape that cannot be expressed through the documented table and child mappings. Those cases belong to the separately documented integrations or require a different synchronization design.

PGSync Maintenance and License

The repository is not archived. Its last push was on 2026-10-14, and the listed recent releases include 7.3.0 on 2026-08-13, 7.2.0 on 2026-07-17, and 7.1.0 on 2026-06-25. This indicates recent release activity in the repository record, but it does not by itself document the cost of upgrading a production deployment.

The upgrade surface includes the Python runtime, the source database and its replication settings, Redis or Valkey when used, and Elasticsearch or OpenSearch. Version requirements are stated in the README, but the excerpt does not provide a release-by-release migration procedure or compatibility matrix for every intermediate version. An adopter should therefore check the release notes and test schema parsing, checkpoint recovery, and destination mappings when moving between PGSync releases.

The project is distributed under the MIT license. That identifies the license declared by the repository and is relevant to adoption review. It does not answer operational questions such as who operates the source database, who owns index mappings, or how an application should recover from an interrupted upgrade. Those responsibilities need to remain visible in the deployment plan.

Editorial conclusion

PGSync is appropriate for teams that need search documents derived directly from relational tables, especially when Elasticsearch or OpenSearch is the target and JSON mappings are acceptable as the configuration model. It is a poor fit for projects requiring ClickHouse output, semantic or hybrid search, or a Django integration without using the separate django-pgsync project. Verify source replication permissions, destination connectivity, and the exact document shape in schema.json first, because those determine whether changes can be captured and indexed correctly.

Frequently asked questions

Official sources

1. License: MIT
2. Project website
3. README
4. Releases
5. toluaina/pgsync on GitHub