PostgreSQL to Elasticsearch 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>.
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>_.

Requirements

Python <https://www.python.org>_ 3.6+
Postgres <https://www.postgresql.org>_ 9.4+
Redis <https://redis.io>_
Elasticsearch <https://www.elastic.co/products/elastic-stack>_ 6.3.1+

Postgres setup

Enable logical decoding <https://www.postgresql.org/docs/current/logicaldecoding.html>_ in your Postgres setting.

you would 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 <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/master/examples/airbnb/schema.json>_

Example spec

.. code-block::

[
    {
        "database": "[database name]",
        "index": "[elasticsearch 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"
                        },
                        ...
                    },
                    {
                        ... any other additional children
                    }
                ]
            }
        ]
    }
]

Environment variables

Setup required environment variables 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
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

Release files for pgsync 1.1.22

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 1.1.22
File	Size	Uploaded
pgsync-1.1.22.tar.gz	69.3 kB	Nov 18, 2020	Details

Built distribution (wheel)

Table of built distributions (wheels) for pgsync 1.1.22
File	Interpreter	ABI	Platform	Reset
pgsync-1.1.22-py2.py3-none-any.whl 37.0 kB Nov 18, 2020	Python 2, Python 3	none	any	Details

Total release size: 106.3 kB

Release files / pgsync-1.1.22.tar.gz

Download URL	pgsync-1.1.22.tar.gz
Size	69.3 kB
Tags	Source
SHA-256 checksum How to use checksums	`89928a1215f3879c6a579691b8a372afbc0ff880a91cc9df0dc74968c03adb27`
BLAKE2b-256 checksum How to use checksums	`f30d0638e5d821a5caef45e756ca40b4a4df6f55e8953262bc30c549e8b87b44`
Upload date	Nov 18, 2020
Uploaded using Trusted Publishing? What is trusted publishing?	No
Uploaded via	`twine/3.2.0 pkginfo/1.6.1 requests/2.25.0 setuptools/49.2.1 requests-toolbelt/0.9.1 tqdm/4.52.0 CPython/3.9.0`

Release files / pgsync-1.1.22-py2.py3-none-any.whl

Download URL	pgsync-1.1.22-py2.py3-none-any.whl
Size	37.0 kB
Tags	Python 2 Python 3
SHA-256 checksum How to use checksums	`f103c832ed3779b7091f44486c1b627c27c286ac596aa581d75f37bea18438dc`
BLAKE2b-256 checksum How to use checksums	`eb0745e68465b4540ae1edb89992c212fdeb91d015e58755fc6a3c6e3aee045e`
Upload date	Nov 18, 2020
Uploaded using Trusted Publishing? What is trusted publishing?	No
Uploaded via	`twine/3.2.0 pkginfo/1.6.1 requests/2.25.0 setuptools/49.2.1 requests-toolbelt/0.9.1 tqdm/4.52.0 CPython/3.9.0`