# PostgreSQL to Elasticsearch/OpenSearch sync
- [PGSync](https://pgsync.com) is a middleware for syncing data from [Postgres](https://www.postgresql.org) to [Elasticsearch](https://www.elastic.co/products/elastic-stack)/[OpenSearch](https://opensearch.org/) or [OpenSearch](https://opensearch.org/).
- It allows you to keep [Postgres](https://www.postgresql.org) as your source of truth data source and
expose structured denormalized documents in [Elasticsearch](https://www.elastic.co/products/elastic-stack)/[OpenSearch](https://opensearch.org/).
### Requirements
- [Python](https://www.python.org) 3.9+
- [Postgres](https://www.postgresql.org) 9.6+ or [MySQL](https://www.mysql.com/) 8.0.0+ or [MariaDB](https://mariadb.org/) 12.0.0+
- [Redis](https://redis.io) 3.1.0+ or [Valkey](https://valkey.io) 7.2.0+
- [Elasticsearch](https://www.elastic.co/products/elastic-stack) 6.3.1+ or [OpenSearch](https://opensearch.org/) 1.3.7+
- [SQLAlchemy](https://www.sqlalchemy.org) 1.3.4+
### Postgres Setup
Enable [logical decoding](https://www.postgresql.org/docs/current/logicaldecoding.html) in your
Postgres setting.
- You also need to set up two parameters in your Postgres config postgresql.conf
```wal_level = logical```
```max_replication_slots = 1```
### MySQL / MariaDB setup
- Enable binary logging in your MySQL / MariaDB setting.
- You also need to set up the following parameters in your MySQL / MariaDB config my.cnf, then restart the database server.
```server-id = 1``` # any non-zero unique ID
```log_bin = mysql-bin```
```binlog_row_image = FULL``` # recommended; if not supported on older MariaDB, omit
- optional housekeeping:
```binlog_expire_logs_seconds = 604800``` # 7 days
- You need to create a replication user with REPLICATION SLAVE and REPLICATION CLIENT privileges
```sql
CREATE USER 'replicator'@'%' IDENTIFIED WITH mysql_native_password BY 'password';
GRANT REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO 'replicator'@'%';
FLUSH PRIVILEGES;
```
### Installation
You can install PGSync from [PyPI](https://pypi.org):
$ pip install pgsync
### Config
Create a schema for the application named e.g **schema.json**
[Example schema](https://github.com/toluaina/pgsync/blob/main/examples/airbnb/schema.json)
Example spec
.. code-block::
[
{
"database": "[database name]",
"index": "[Elasticsearch or OpenSearch index]",
"nodes": {
"table": "[table A]",
"schema": "[table A schema]",
"columns": [
"column 1 from table A",
"column 2 from table A",
... additional columns
],
"children": [
{
"table": "[table B with relationship to table A]",
"schema": "[table B schema]",
"columns": [
"column 1 from table B",
"column 2 from table B",
... additional columns
],
"relationship": {
"variant": "object",
"type": "one_to_many"
},
...
},
{
... additional children
}
]
}
}
]
### Environment variables
Setup environment variables required for the application
SCHEMA='/path/to/schema.json'
ELASTICSEARCH_HOST=localhost
ELASTICSEARCH_PORT=9200
PG_HOST=localhost
PG_USER=i-am-root # this must be a postgres superuser or replication user
PG_PORT=5432
PG_PASSWORD=*****
REDIS_HOST=redis
REDIS_PORT=6379
REDIS_DB=0
REDIS_AUTH=*****
### Running
Bootstrap the database (one time only)
- $ bootstrap --config schema.json
Run pgsync as a daemon
- $ pgsync --config schema.json --daemon
Raw data
{
"_id": null,
"home_page": "https://github.com/toluaina/pgsync",
"name": "pgsync",
"maintainer": "Tolu Aina",
"docs_url": null,
"requires_python": ">=3.9.0",
"maintainer_email": "tolu@pgsync.com",
"keywords": "change data capture, elasticsearch, opensearch, pgsync, postgres, mysql, mariadb",
"author": "Tolu Aina",
"author_email": "tolu@pgsync.com",
"download_url": "https://files.pythonhosted.org/packages/58/12/4476354d0e125d946554b030a2f53c391934d8021e6637fab3c5d08eaf87/pgsync-5.0.0.tar.gz",
"platform": null,
"description": "# PostgreSQL to Elasticsearch/OpenSearch sync\n\n\n- [PGSync](https://pgsync.com) is a middleware for syncing data from [Postgres](https://www.postgresql.org) to [Elasticsearch](https://www.elastic.co/products/elastic-stack)/[OpenSearch](https://opensearch.org/) or [OpenSearch](https://opensearch.org/).\n- It allows you to keep [Postgres](https://www.postgresql.org) as your source of truth data source and\nexpose structured denormalized documents in [Elasticsearch](https://www.elastic.co/products/elastic-stack)/[OpenSearch](https://opensearch.org/).\n\n\n### Requirements\n\n- [Python](https://www.python.org) 3.9+\n- [Postgres](https://www.postgresql.org) 9.6+ or [MySQL](https://www.mysql.com/) 8.0.0+ or [MariaDB](https://mariadb.org/) 12.0.0+ \n- [Redis](https://redis.io) 3.1.0+ or [Valkey](https://valkey.io) 7.2.0+\n- [Elasticsearch](https://www.elastic.co/products/elastic-stack) 6.3.1+ or [OpenSearch](https://opensearch.org/) 1.3.7+\n- [SQLAlchemy](https://www.sqlalchemy.org) 1.3.4+\n\n### Postgres Setup\n \n Enable [logical decoding](https://www.postgresql.org/docs/current/logicaldecoding.html) in your \n Postgres setting.\n\n - You also need to set up two parameters in your Postgres config postgresql.conf\n\n ```wal_level = logical```\n\n ```max_replication_slots = 1```\n\n\n### MySQL / MariaDB setup\n\n- Enable binary logging in your MySQL / MariaDB setting.\n\n- You also need to set up the following parameters in your MySQL / MariaDB config my.cnf, then restart the database server.\n\n ```server-id = 1``` # any non-zero unique ID\n\n ```log_bin = mysql-bin```\n\n ```binlog_row_image = FULL``` # recommended; if not supported on older MariaDB, omit\n\n- optional housekeeping:\n ```binlog_expire_logs_seconds = 604800``` # 7 days\n\n- You need to create a replication user with REPLICATION SLAVE and REPLICATION CLIENT privileges\n \n ```sql\n CREATE USER 'replicator'@'%' IDENTIFIED WITH mysql_native_password BY 'password';\n GRANT REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO 'replicator'@'%';\n FLUSH PRIVILEGES;\n ```\n\n### Installation\n\nYou can install PGSync from [PyPI](https://pypi.org):\n\n $ pip install pgsync\n\n### Config\n\nCreate a schema for the application named e.g **schema.json**\n\n[Example schema](https://github.com/toluaina/pgsync/blob/main/examples/airbnb/schema.json)\n\nExample spec\n\n.. code-block::\n\n [\n {\n \"database\": \"[database name]\",\n \"index\": \"[Elasticsearch or OpenSearch index]\",\n \"nodes\": {\n \"table\": \"[table A]\",\n \"schema\": \"[table A schema]\",\n \"columns\": [\n \"column 1 from table A\",\n \"column 2 from table A\",\n ... additional columns\n ],\n \"children\": [\n {\n \"table\": \"[table B with relationship to table A]\",\n \"schema\": \"[table B schema]\",\n \"columns\": [\n \"column 1 from table B\",\n \"column 2 from table B\",\n ... additional columns\n ],\n \"relationship\": {\n \"variant\": \"object\",\n \"type\": \"one_to_many\"\n },\n ...\n },\n {\n ... additional children\n }\n ]\n }\n }\n ]\n\n### Environment variables \n\nSetup environment variables required for the application\n\n SCHEMA='/path/to/schema.json'\n\n ELASTICSEARCH_HOST=localhost\n ELASTICSEARCH_PORT=9200\n\n PG_HOST=localhost\n PG_USER=i-am-root # this must be a postgres superuser or replication user\n PG_PORT=5432\n PG_PASSWORD=*****\n\n REDIS_HOST=redis\n REDIS_PORT=6379\n REDIS_DB=0\n REDIS_AUTH=*****\n\n\n### Running\n\nBootstrap the database (one time only)\n - $ bootstrap --config schema.json\n\nRun pgsync as a daemon\n - $ pgsync --config schema.json --daemon\n",
"bugtrack_url": null,
"license": "MIT",
"summary": "Postgres/MySQL/MariaDB to Elasticsearch/OpenSearch sync",
"version": "5.0.0",
"project_urls": {
"Bug Reports": "https://github.com/toluaina/pgsync/issues",
"Documentation": "https://pgsync.com",
"Funding": "https://github.com/sponsors/toluaina",
"Homepage": "https://github.com/toluaina/pgsync",
"Source": "https://github.com/toluaina/pgsync",
"Web": "https://pgsync.com"
},
"split_keywords": [
"change data capture",
" elasticsearch",
" opensearch",
" pgsync",
" postgres",
" mysql",
" mariadb"
],
"urls": [
{
"comment_text": null,
"digests": {
"blake2b_256": "972fa47c23e358af48db25876be47a3c7ef6c203e307ddb4e1ff824f5491f7bb",
"md5": "2e4c06d67dad490f8ca06204b4b52710",
"sha256": "aefc26988b7cbc00c2dccafeac730215eea1704cb12985b40d30cb9c139aa9f0"
},
"downloads": -1,
"filename": "pgsync-5.0.0-py3-none-any.whl",
"has_sig": false,
"md5_digest": "2e4c06d67dad490f8ca06204b4b52710",
"packagetype": "bdist_wheel",
"python_version": "py3",
"requires_python": ">=3.9.0",
"size": 74706,
"upload_time": "2025-10-12T14:36:19",
"upload_time_iso_8601": "2025-10-12T14:36:19.607192Z",
"url": "https://files.pythonhosted.org/packages/97/2f/a47c23e358af48db25876be47a3c7ef6c203e307ddb4e1ff824f5491f7bb/pgsync-5.0.0-py3-none-any.whl",
"yanked": false,
"yanked_reason": null
},
{
"comment_text": null,
"digests": {
"blake2b_256": "58124476354d0e125d946554b030a2f53c391934d8021e6637fab3c5d08eaf87",
"md5": "8c55cbe4e028d4edd7dd8ca04e319628",
"sha256": "de1af7ceb6c28f092c9891efc35189668afafd8e1945dee0963f6bbd10ea280c"
},
"downloads": -1,
"filename": "pgsync-5.0.0.tar.gz",
"has_sig": false,
"md5_digest": "8c55cbe4e028d4edd7dd8ca04e319628",
"packagetype": "sdist",
"python_version": "source",
"requires_python": ">=3.9.0",
"size": 131314,
"upload_time": "2025-10-12T14:36:20",
"upload_time_iso_8601": "2025-10-12T14:36:20.742184Z",
"url": "https://files.pythonhosted.org/packages/58/12/4476354d0e125d946554b030a2f53c391934d8021e6637fab3c5d08eaf87/pgsync-5.0.0.tar.gz",
"yanked": false,
"yanked_reason": null
}
],
"upload_time": "2025-10-12 14:36:20",
"github": true,
"gitlab": false,
"bitbucket": false,
"codeberg": false,
"github_user": "toluaina",
"github_project": "pgsync",
"travis_ci": false,
"coveralls": true,
"github_actions": true,
"lcname": "pgsync"
}
JSON
