Skip to main content

Postgres to elasticsearch sync

Project description

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/pg-sync/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

======= History

1.0.1 (2020-15-01)

  • First release on PyPI.

1.0.1 (2020-01-01)

  • RC1 release

1.1.0 (2020-04-13)

  • Postgres multi schema support for multi-tennant applications

  • Show resulting Query with verbose mode

  • this release required you to re-bootstrap your database with

    • bootstrap -t
    • bootstrap

1.1.1 (2020-05-18)

  • Fixed authentication with Redis
  • Fixed Docker build

1.1.2 (2020-06-11)

  • Sync multiple indices in the same schema
  • Test for replication or superuser
  • Fix PG_NOTIFY error when payload exceeds 8000 bytes limit

1.1.3 (2020-06-14)

  • Bug fix when syncing multiple indices in the same schema

1.1.4 (2020-06-15)

  • Only create triggers for tables present in schema

1.1.5 (2020-06-16)

  • Bug fix when creating multiple triggers in same schema

1.1.6 (2020-07-31)

  • Bug fix when tearing down secondary schema

1.1.7 (2020-08-16)

  • Fix issue #29: SQLAlchemy err: Neither 'BooleanClauseList' object nor 'Comparator' object has an attribute '_orig'

Project details


Download files

Download the file for your platform. If you're not sure which to choose, learn more about installing packages.

Source Distributions

No source distribution files available for this release.See tutorial on generating distribution archives.

Built Distributions

pgsync-1.1.7-cp38-cp38-manylinux2010_x86_64.whl (4.4 MB view details)

Uploaded CPython 3.8manylinux: glibc 2.12+ x86-64

pgsync-1.1.7-cp38-cp38-manylinux2010_x86_64.manylinux1_x86_64.whl (4.4 MB view details)

Uploaded CPython 3.8manylinux: glibc 2.12+ x86-64

pgsync-1.1.7-cp38-cp38-manylinux1_x86_64.whl (4.4 MB view details)

Uploaded CPython 3.8

pgsync-1.1.7-cp37-cp37m-manylinux2010_x86_64.whl (3.4 MB view details)

Uploaded CPython 3.7mmanylinux: glibc 2.12+ x86-64

pgsync-1.1.7-cp37-cp37m-manylinux1_x86_64.whl (3.4 MB view details)

Uploaded CPython 3.7m

pgsync-1.1.7-cp37-cp37m-manylinux1_x86_64.manylinux2010_x86_64.whl (3.4 MB view details)

Uploaded CPython 3.7mmanylinux: glibc 2.12+ x86-64

pgsync-1.1.7-cp37-cp37m-macosx_10_15_x86_64.whl (832.5 kB view details)

Uploaded CPython 3.7mmacOS 10.15+ x86-64

pgsync-1.1.7-cp36-cp36m-manylinux2010_x86_64.whl (3.4 MB view details)

Uploaded CPython 3.6mmanylinux: glibc 2.12+ x86-64

pgsync-1.1.7-cp36-cp36m-manylinux2010_x86_64.manylinux1_x86_64.whl (3.4 MB view details)

Uploaded CPython 3.6mmanylinux: glibc 2.12+ x86-64

pgsync-1.1.7-cp36-cp36m-manylinux1_x86_64.whl (3.4 MB view details)

Uploaded CPython 3.6m

File details

Details for the file pgsync-1.1.7-cp38-cp38-manylinux2010_x86_64.whl.

File metadata

  • Download URL: pgsync-1.1.7-cp38-cp38-manylinux2010_x86_64.whl
  • Upload date:
  • Size: 4.4 MB
  • Tags: CPython 3.8, manylinux: glibc 2.12+ x86-64
  • Uploaded using Trusted Publishing? No
  • Uploaded via: twine/3.2.0 pkginfo/1.5.0.1 requests/2.24.0 setuptools/46.0.0 requests-toolbelt/0.9.1 tqdm/4.48.2 CPython/3.7.7

File hashes

Hashes for pgsync-1.1.7-cp38-cp38-manylinux2010_x86_64.whl
Algorithm Hash digest
SHA256 c2f39e47b3f6f1beeaf9fe9631ebdd48b4c7fab3e6949108dfa6268760a9bc68
MD5 384a82f07fb5ef916d8b47b33cbf71f9
BLAKE2b-256 c036b81d8be3d5d4a4a760632f8a33c49064556ec839bf5d311be9edec04d593

See more details on using hashes here.

File details

Details for the file pgsync-1.1.7-cp38-cp38-manylinux2010_x86_64.manylinux1_x86_64.whl.

File metadata

  • Download URL: pgsync-1.1.7-cp38-cp38-manylinux2010_x86_64.manylinux1_x86_64.whl
  • Upload date:
  • Size: 4.4 MB
  • Tags: CPython 3.8, manylinux: glibc 2.12+ x86-64
  • Uploaded using Trusted Publishing? No
  • Uploaded via: twine/3.2.0 pkginfo/1.5.0.1 requests/2.24.0 setuptools/46.0.0 requests-toolbelt/0.9.1 tqdm/4.48.2 CPython/3.7.7

File hashes

Hashes for pgsync-1.1.7-cp38-cp38-manylinux2010_x86_64.manylinux1_x86_64.whl
Algorithm Hash digest
SHA256 6d260e1b2111612b3f18e7098e748868971393662b19c1ec153b41b9da80ef37
MD5 5fca27994dc62ba4d8956521c3f22be9
BLAKE2b-256 0f08b696ecf31b60b12d203fb9f805eb87e3b724b79ab1f64e07ef51e6641a5d

See more details on using hashes here.

File details

Details for the file pgsync-1.1.7-cp38-cp38-manylinux1_x86_64.whl.

File metadata

  • Download URL: pgsync-1.1.7-cp38-cp38-manylinux1_x86_64.whl
  • Upload date:
  • Size: 4.4 MB
  • Tags: CPython 3.8
  • Uploaded using Trusted Publishing? No
  • Uploaded via: twine/3.2.0 pkginfo/1.5.0.1 requests/2.24.0 setuptools/46.0.0 requests-toolbelt/0.9.1 tqdm/4.48.2 CPython/3.7.7

File hashes

Hashes for pgsync-1.1.7-cp38-cp38-manylinux1_x86_64.whl
Algorithm Hash digest
SHA256 a415cb3e2bd7bcc855b17456b39f4523b9e23552a39fd61c4f067d1674bdc090
MD5 576bf173693dbc6f6f5a21849e66e188
BLAKE2b-256 dbd178309d4900380679a87123250effe0bf31ec886e522ea28f087198f3d033

See more details on using hashes here.

File details

Details for the file pgsync-1.1.7-cp37-cp37m-manylinux2010_x86_64.whl.

File metadata

  • Download URL: pgsync-1.1.7-cp37-cp37m-manylinux2010_x86_64.whl
  • Upload date:
  • Size: 3.4 MB
  • Tags: CPython 3.7m, manylinux: glibc 2.12+ x86-64
  • Uploaded using Trusted Publishing? No
  • Uploaded via: twine/3.2.0 pkginfo/1.5.0.1 requests/2.24.0 setuptools/46.0.0 requests-toolbelt/0.9.1 tqdm/4.48.2 CPython/3.7.7

File hashes

Hashes for pgsync-1.1.7-cp37-cp37m-manylinux2010_x86_64.whl
Algorithm Hash digest
SHA256 e33871f3c362e10deedea45d0add8ab2dbc9cc6ce839ba7a65fbfc71ddc2eec4
MD5 e35ea0948e421a813a99851ca5e0085d
BLAKE2b-256 3ab0909c3c7705a6c8b971bccfcc69413be402782c1e51d10cf887f1a9976bf1

See more details on using hashes here.

File details

Details for the file pgsync-1.1.7-cp37-cp37m-manylinux1_x86_64.whl.

File metadata

  • Download URL: pgsync-1.1.7-cp37-cp37m-manylinux1_x86_64.whl
  • Upload date:
  • Size: 3.4 MB
  • Tags: CPython 3.7m
  • Uploaded using Trusted Publishing? No
  • Uploaded via: twine/3.2.0 pkginfo/1.5.0.1 requests/2.24.0 setuptools/46.0.0 requests-toolbelt/0.9.1 tqdm/4.48.2 CPython/3.7.7

File hashes

Hashes for pgsync-1.1.7-cp37-cp37m-manylinux1_x86_64.whl
Algorithm Hash digest
SHA256 800447602d61e2a5d2fdefb8edc67adf94788240e3e1617f8c48d9c51ef18331
MD5 bf7f157cba38c7653a6f46cb5adc952e
BLAKE2b-256 9d3e650e0c979412a0fe96b66552a25454399529816c4acefa3c142cc4772e52

See more details on using hashes here.

File details

Details for the file pgsync-1.1.7-cp37-cp37m-manylinux1_x86_64.manylinux2010_x86_64.whl.

File metadata

  • Download URL: pgsync-1.1.7-cp37-cp37m-manylinux1_x86_64.manylinux2010_x86_64.whl
  • Upload date:
  • Size: 3.4 MB
  • Tags: CPython 3.7m, manylinux: glibc 2.12+ x86-64
  • Uploaded using Trusted Publishing? No
  • Uploaded via: twine/3.2.0 pkginfo/1.5.0.1 requests/2.24.0 setuptools/46.0.0 requests-toolbelt/0.9.1 tqdm/4.48.2 CPython/3.7.7

File hashes

Hashes for pgsync-1.1.7-cp37-cp37m-manylinux1_x86_64.manylinux2010_x86_64.whl
Algorithm Hash digest
SHA256 aeb3bfa6f665a1856511c19b0753a3dee043aeae75c4dbf4f6c6b7dfcfd77b6c
MD5 bdaab55d8bb697f21067d91e06d829d0
BLAKE2b-256 27b8e8047cb7c29a4d92c9ac29887db761d231014211530a29b69a3cc6ab37bb

See more details on using hashes here.

File details

Details for the file pgsync-1.1.7-cp37-cp37m-macosx_10_15_x86_64.whl.

File metadata

  • Download URL: pgsync-1.1.7-cp37-cp37m-macosx_10_15_x86_64.whl
  • Upload date:
  • Size: 832.5 kB
  • Tags: CPython 3.7m, macOS 10.15+ x86-64
  • Uploaded using Trusted Publishing? No
  • Uploaded via: twine/3.2.0 pkginfo/1.5.0.1 requests/2.24.0 setuptools/46.0.0 requests-toolbelt/0.9.1 tqdm/4.48.2 CPython/3.7.7

File hashes

Hashes for pgsync-1.1.7-cp37-cp37m-macosx_10_15_x86_64.whl
Algorithm Hash digest
SHA256 8f6b22e19da297174d265fb3a176be35d9a51f3f6bd55c8e7f905b5b639d805c
MD5 7127227d8df9c08c3d2d1a251ccaccc6
BLAKE2b-256 2dbd6587ddd84fd88d542f7dd262cab43847e6771c1db90302e3b3d4e84d3df6

See more details on using hashes here.

File details

Details for the file pgsync-1.1.7-cp36-cp36m-manylinux2010_x86_64.whl.

File metadata

  • Download URL: pgsync-1.1.7-cp36-cp36m-manylinux2010_x86_64.whl
  • Upload date:
  • Size: 3.4 MB
  • Tags: CPython 3.6m, manylinux: glibc 2.12+ x86-64
  • Uploaded using Trusted Publishing? No
  • Uploaded via: twine/3.2.0 pkginfo/1.5.0.1 requests/2.24.0 setuptools/46.0.0 requests-toolbelt/0.9.1 tqdm/4.48.2 CPython/3.7.7

File hashes

Hashes for pgsync-1.1.7-cp36-cp36m-manylinux2010_x86_64.whl
Algorithm Hash digest
SHA256 f60f5e4b8e45a0d9f536ed180180cde7923ae151a0f56040f6d5df3b1e2c2e33
MD5 52faf4814f23a29946dc6efdecaae575
BLAKE2b-256 934a2f1f8a7675b4bc770304e48befcb902283307638887a3fbfa02055f86193

See more details on using hashes here.

File details

Details for the file pgsync-1.1.7-cp36-cp36m-manylinux2010_x86_64.manylinux1_x86_64.whl.

File metadata

  • Download URL: pgsync-1.1.7-cp36-cp36m-manylinux2010_x86_64.manylinux1_x86_64.whl
  • Upload date:
  • Size: 3.4 MB
  • Tags: CPython 3.6m, manylinux: glibc 2.12+ x86-64
  • Uploaded using Trusted Publishing? No
  • Uploaded via: twine/3.2.0 pkginfo/1.5.0.1 requests/2.24.0 setuptools/46.0.0 requests-toolbelt/0.9.1 tqdm/4.48.2 CPython/3.7.7

File hashes

Hashes for pgsync-1.1.7-cp36-cp36m-manylinux2010_x86_64.manylinux1_x86_64.whl
Algorithm Hash digest
SHA256 4c905bbf416158e9da94f7b216cf8fe914017190c97b4c8647e5279fbcb489e8
MD5 32aae101e6fa3e086a26cbd851e1776c
BLAKE2b-256 4fa763efc24fcf42ce49113f9f7a1f7abfcd0ae2037b462b199c56406e5ae940

See more details on using hashes here.

File details

Details for the file pgsync-1.1.7-cp36-cp36m-manylinux1_x86_64.whl.

File metadata

  • Download URL: pgsync-1.1.7-cp36-cp36m-manylinux1_x86_64.whl
  • Upload date:
  • Size: 3.4 MB
  • Tags: CPython 3.6m
  • Uploaded using Trusted Publishing? No
  • Uploaded via: twine/3.2.0 pkginfo/1.5.0.1 requests/2.24.0 setuptools/46.0.0 requests-toolbelt/0.9.1 tqdm/4.48.2 CPython/3.7.7

File hashes

Hashes for pgsync-1.1.7-cp36-cp36m-manylinux1_x86_64.whl
Algorithm Hash digest
SHA256 6734a4ea19cfbf5d0e1b8a20d6798ca3d80bd67dbfeb67296c13231568d35df0
MD5 9a4731ec878524ba54afe0e58c0e2ad6
BLAKE2b-256 50c6828f56783e9b58e4cb5c93d6999af957d0bfe9460f697d4728c0e65415ca

See more details on using hashes here.

Supported by

AWS Cloud computing and Security Sponsor Datadog Monitoring Fastly CDN Google Download Analytics Pingdom Monitoring Sentry Error logging StatusPage Status page