PostgreSQL to Elasticsearch/OpenSearch sync

PGSync is a middleware for syncing data from Postgres to Elasticsearch/OpenSearch or OpenSearch.
It allows you to keep Postgres as your source of truth data source and expose structured denormalized documents in Elasticsearch/OpenSearch.

Requirements

Python 3.7+
Postgres 9.6+
Redis 3.1.0
Elasticsearch 6.3.1+ or OpenSearch 1.3.7+
SQlAlchemy 1.3.4+

Postgres setup

Enable logical decoding 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

Installation

You can install PGSync from PyPI:

$ pip install pgsync

Config

Create a schema for the application named e.g schema.json

Example schema

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

Metadata

Release files for pgsync-fork 2.7.3

For a detailed explanation of source distributions (sdists) and built distributions (wheels), please see the package formats documentation.

Source distribution (sdist)

Source distribution for pgsync-fork 2.7.3
File	Size	Uploaded
pgsync-fork-2.7.3.tar.gz	110.2 kB	Aug 13, 2023	Details

Built distribution (wheel)

Table of built distributions (wheels) for pgsync-fork 2.7.3
File	Interpreter	ABI	Platform	Reset
pgsync_fork-2.7.3-py3-none-any.whl 57.9 kB Aug 13, 2023	Python 3	none	any	Details

Total release size: 168.1 kB

Release files / pgsync-fork-2.7.3.tar.gz

Download URL	pgsync-fork-2.7.3.tar.gz
Size	110.2 kB
Tags	Source
SHA-256 checksum How to use checksums	`29d210f5b365829c8336412920591161cccaeaf2fd4aa6c077ab32a03e768d1d`
BLAKE2b-256 checksum How to use checksums	`4810cfd341da8b2e94fb87e3c4a3038e00179c681e772cc53adfa134201288f8`
Upload date	Aug 13, 2023
Uploaded using Trusted Publishing? What is trusted publishing?	No
Uploaded via	`twine/4.0.2 CPython/3.11.4`

Release files / pgsync_fork-2.7.3-py3-none-any.whl

Download URL	pgsync_fork-2.7.3-py3-none-any.whl
Size	57.9 kB
Tags	Python 3
SHA-256 checksum How to use checksums	`8c4bf49d6dd5c827143e5c0749086881fd62a96a269f7c3d116419ef709e28ea`
BLAKE2b-256 checksum How to use checksums	`d7b1bb22fdc06127a569c61aa91b9c2e0e677cf44e553b4041e7f946e88b30c4`
Upload date	Aug 13, 2023
Uploaded using Trusted Publishing? What is trusted publishing?	No
Uploaded via	`twine/4.0.2 CPython/3.11.4`