Skip to main content
Warning: You are using the test version of PyPI. This is a pre-production deployment of Warehouse. Changes made here affect the production instance of TestPyPI (testpypi.python.org).
Help us improve Python packaging - Donate today!

Connect PostgreSQL and Elastic Search with this Foreign Data Wrapper

Project Description

This allows you to index data in elastic search and then search it from postgres. You can write as well as read.

SYNOPSIS

Installation

This requires installation on the PostgreSQL server, and has system level dependencies. You can install the dependencies with:

sudo apt-get install postgresql-9.4-python-multicorn python python-pip

You should install the version of multicorn that is specific to your postgres version. The multicorn package is also only available from Ubuntu Xenial (16.04) onwards. If you cannot install multicorn in this way then you can use pgxn to install it.

Once the dependencies are installed you can install the foreign data wrapper using pip:

sudo pip install pg_es_fdw

Usage

A running configuration for this can be found in the docker-compose.yml within this folder.

The basic steps are:

  • Load the extension
  • Create the server
  • Create the foreign table
  • Populate the foreign table
  • Query the foreign table…

Load extension and Create server

CREATE EXTENSION multicorn;

CREATE SERVER multicorn_es FOREIGN DATA WRAPPER multicorn
OPTIONS (
  wrapper 'pg_es_fdw.ElasticsearchFDW'
);

Create the foreign table

CREATE FOREIGN TABLE articles_es
    (
        id BIGINT,
        title TEXT,
        body TEXT,
        query TEXT,
        score NUMERIC
    )
SERVER multicorn_es
OPTIONS
    (
        host 'elasticsearch',
        port '9200',
        index 'article-index',
        type 'article',
        rowid_column 'id',
        query_column 'query',
        score_column 'score'
    )
;

This corresponds to an Elastic Search index which contains a title and body fields. The other fields have special meaning:

  • The id field is mapped to the Elastic Search document id
  • The query field accepts Elastic Search queries to filter the rows
  • The score field returns the score for the document against the query

These are configured using the rowid_column, query_column and score_column options. All of these are optional.

Populate the foreign table

INSERT INTO articles_es
    (
        id,
        title,
        content
    )
VALUES
    (
        1,
        'foo',
        'spike'
    );

It is possible to write documents to Elastic Search using the foreign data wrapper. This feature was introduced in PostgreSQL 9.3.

Query the foreign table

To select all documents:

SELECT
    id,
    title,
    content
FROM
    articles_es
;

To filter the documents using a query:

SELECT
    id,
    title,
    content,
    score
FROM
    articles_es
WHERE
    query = 'body:chess'
;

This uses the URI Search from Elastic Search.

Caveats

Elastic Search does not support transactions, so the elasticsearch index is not guaranteed to be synchronized with the canonical version in PostgreSQL. Unfortunately this is the case even for serializable isolation level transactions. It would however be possible to check against Elastic Search version field and locking.

Rollback is currently not supported.

Tests

There are end to end tests that use docker to create a PostgreSQL and Elastic Search database. These are then populated with data and tests are run against them.

These require docker and docker-compose. These also require python packages which you can install with:

pip install -r tests/requirements.txt

You can then run the tests using tests/run.py, which takes the PostgreSQL version to test. The currently supported versions are 9.2 through to 9.6. You can pass multiple versions to test it against all of them:

➜ ./tests/run.py 9.2 9.3 9.4 9.5 9.6
Testing PostgreSQL 9.2
PostgreSQL 9.2: Test read - PASS
PostgreSQL 9.2: Test query - PASS
Testing PostgreSQL 9.3
PostgreSQL 9.3: Test read - PASS
PostgreSQL 9.3: Test query - PASS
Testing PostgreSQL 9.4
PostgreSQL 9.4: Test read - PASS
PostgreSQL 9.4: Test query - PASS
Testing PostgreSQL 9.5
PostgreSQL 9.5: Test read - PASS
PostgreSQL 9.5: Test query - PASS
Testing PostgreSQL 9.6
PostgreSQL 9.6: Test read - PASS
PostgreSQL 9.6: Test query - PASS
PASS
Release History

Release History

This version
History Node

0.4.1

History Node

0.4.0

History Node

0.3.1

Download Files

Download Files

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

File Name & Checksum SHA256 Checksum Help Version File Type Upload Date
pg_es_fdw-0.4.1.tar.gz (4.3 kB) Copy SHA256 Checksum SHA256 Source Apr 19, 2017

Supported By

WebFaction WebFaction Technical Writing Elastic Elastic Search Pingdom Pingdom Monitoring Dyn Dyn DNS Sentry Sentry Error Logging CloudAMQP CloudAMQP RabbitMQ Heroku Heroku PaaS Kabu Creative Kabu Creative UX & Design Fastly Fastly CDN DigiCert DigiCert EV Certificate Rackspace Rackspace Cloud Servers DreamHost DreamHost Log Hosting