{
    "info": {
        "author": "Matthew Franglen",
        "author_email": "matthew@franglen.org",
        "bugtrack_url": null,
        "classifiers": [],
        "description": "PostgreSQL Elastic Search foreign data wrapper\n==============================================\n\nThis allows you to index data in Elastic Search and then search it from\nPostgreSQL. You can write as well as read.\n\nSYNOPSIS\n--------\n\n### Supported Versions\n\n| Elastic Search | Dependency Installation Command |\n|----------------|---------------------------------|\n| 5 | `sudo pip install \"elasticsearch>=5,<6\"` |\n| 6 | `sudo pip install \"elasticsearch>=6,<7\"` |\n| 7 | `sudo pip install \"elasticsearch>=7,<8\"` |\n\n| PostgreSQL | Dependency Installation Command |\n|------------|---------------------------------|\n| 9.4 | `sudo apt-get install postgresql-9.4-python-multicorn` |\n| 9.5 | `sudo apt-get install postgresql-9.5-python-multicorn` |\n| 9.6 | `sudo apt-get install postgresql-9.6-python-multicorn` |\n| 10 | `sudo apt-get install postgresql-10-python-multicorn` |\n| 11 | `sudo apt-get install postgresql-11-python-multicorn` |\n\n### Installation\n\nThis requires installation on the PostgreSQL server, and has system level dependencies.\nYou can install the dependencies with:\n\n```\nsudo apt-get install python python-pip\n```\n\nYou should install the version of multicorn that is specific to your postgres\nversion. See the table in _Supported Versions_ for installation commands. The\nmulticorn package is also only available from Ubuntu Xenial (16.04) onwards. If\nyou cannot install multicorn in this way then you can use\n[pgxn](http://pgxnclient.projects.pgfoundry.org/) to install it.\n\nThis uses the Elastic Search client which has release versions that correspond\nto the major version of the Elastic Search server. You should install the\n`elasticsearch` dependency separately. See the table in _Supported Versions_\nfor installation commands.\n\nOnce the dependencies are installed you can install the foreign data wrapper\nusing pip:\n\n```\nsudo pip install pg_es_fdw\n```\n\n### Usage\n\nA running configuration for this can be found in the `docker-compose.yml`\nwithin this folder.\n\nThe basic steps are:\n\n * Load the extension\n * Create the server\n * Create the foreign table\n * Populate the foreign table\n * Query the foreign table...\n\n#### Load extension and Create server\n\n```sql\nCREATE EXTENSION multicorn;\n\nCREATE SERVER multicorn_es FOREIGN DATA WRAPPER multicorn\nOPTIONS (\n  wrapper 'pg_es_fdw.ElasticsearchFDW'\n);\n```\n\n#### Create the foreign table\n\n```sql\nCREATE FOREIGN TABLE articles_es\n    (\n        id BIGINT,\n        title TEXT,\n        body TEXT,\n        query TEXT,\n        score NUMERIC\n    )\nSERVER multicorn_es\nOPTIONS\n    (\n        host 'elasticsearch',\n        port '9200',\n        index 'article-index',\n        type 'article',\n        rowid_column 'id',\n        query_column 'query',\n        score_column 'score'\n    )\n;\n```\n\nElastic Search 7 and greater does not require the `type` option, which\ncorresponds to the `doc_type` used in prior versions of Elastic Search.\n\nThis corresponds to an Elastic Search index which contains a `title` and `body`\nfields. The other fields have special meaning:\n\n * The `id` field is mapped to the Elastic Search document id\n * The `query` field accepts Elastic Search queries to filter the rows\n * The `score` field returns the score for the document against the query\n\nThese are configured using the `rowid_column`, `query_column` and\n`score_column` options. All of these are optional.\n\n#### Populate the foreign table\n\n```sql\nINSERT INTO articles_es\n    (\n        id,\n        title,\n        body\n    )\nVALUES\n    (\n        1,\n        'foo',\n        'spike'\n    );\n```\n\nIt is possible to write documents to Elastic Search using the foreign data\nwrapper. This feature was introduced in PostgreSQL 9.3.\n\n#### Query the foreign table\n\nTo select all documents:\n\n```sql\nSELECT\n    id,\n    title,\n    body\nFROM\n    articles_es\n;\n```\n\nTo filter the documents using a query:\n\n```sql\nSELECT\n    id,\n    title,\n    body,\n    score\nFROM\n    articles_es\nWHERE\n    query = 'body:chess'\n;\n```\n\nThis uses the [URI Search](https://www.elastic.co/guide/en/elasticsearch/reference/current/search-uri-request.html) from Elastic Search.\n\nCaveats\n-------\n\nElastic Search does not support transactions, so the elasticsearch index\nis not guaranteed to be synchronized with the canonical version in PostgreSQL.\nUnfortunately this is the case even for serializable isolation level transactions.\nIt would however be possible to check against Elastic Search version field and locking.\n\nRollback is currently not supported.\n\nTests\n-----\n\nThere are end to end tests that use docker to create a PostgreSQL and Elastic\nSearch database. These are then populated with data and tests are run against\nthem.\n\nThese require docker and docker-compose. These also require python packages\nwhich you can install with:\n\n```bash\npip install -r tests/requirements.txt\n```\n\nYou can then run the tests using `tests/run.py`.  This takes the PostgreSQL\nversion(s) to test using the `--pg` argument and the Elastic Search versions to\ntest with the `--es` argument.  The currently supported versions of PostgreSQL\nare 9.4 through to 11. The currently supported versions of Elastic Search are 5\nand 6. You can pass multiple versions to test it against all of them:\n\n```bash\n\u279c pipenv run ./tests/run.py --pg 9.4 9.5 9.6 10 11 --es 5 6 7\nTesting PostgreSQL 9.4 with Elasticsearch 5\nPostgreSQL 9.4 with Elasticsearch 5: Test read - PASS\nPostgreSQL 9.4 with Elasticsearch 5: Test query - PASS\nTesting PostgreSQL 9.4 with Elasticsearch 6\nPostgreSQL 9.4 with Elasticsearch 6: Test read - PASS\nPostgreSQL 9.4 with Elasticsearch 6: Test query - PASS\nTesting PostgreSQL 9.4 with Elasticsearch 7\nPostgreSQL 9.4 with Elasticsearch 7: Test read - PASS\nPostgreSQL 9.4 with Elasticsearch 7: Test query - PASS\nTesting PostgreSQL 9.5 with Elasticsearch 5\nPostgreSQL 9.5 with Elasticsearch 5: Test read - PASS\nPostgreSQL 9.5 with Elasticsearch 5: Test query - PASS\nTesting PostgreSQL 9.5 with Elasticsearch 6\nPostgreSQL 9.5 with Elasticsearch 6: Test read - PASS\nPostgreSQL 9.5 with Elasticsearch 6: Test query - PASS\nTesting PostgreSQL 9.5 with Elasticsearch 7\nPostgreSQL 9.5 with Elasticsearch 7: Test read - PASS\nPostgreSQL 9.5 with Elasticsearch 7: Test query - PASS\nTesting PostgreSQL 9.6 with Elasticsearch 5\nPostgreSQL 9.6 with Elasticsearch 5: Test read - PASS\nPostgreSQL 9.6 with Elasticsearch 5: Test query - PASS\nTesting PostgreSQL 9.6 with Elasticsearch 6\nPostgreSQL 9.6 with Elasticsearch 6: Test read - PASS\nPostgreSQL 9.6 with Elasticsearch 6: Test query - PASS\nTesting PostgreSQL 9.6 with Elasticsearch 7\nPostgreSQL 9.6 with Elasticsearch 7: Test read - PASS\nPostgreSQL 9.6 with Elasticsearch 7: Test query - PASS\nTesting PostgreSQL 10 with Elasticsearch 5\nPostgreSQL 10 with Elasticsearch 5: Test read - PASS\nPostgreSQL 10 with Elasticsearch 5: Test query - PASS\nTesting PostgreSQL 10 with Elasticsearch 6\nPostgreSQL 10 with Elasticsearch 6: Test read - PASS\nPostgreSQL 10 with Elasticsearch 6: Test query - PASS\nTesting PostgreSQL 10 with Elasticsearch 7\nPostgreSQL 10 with Elasticsearch 7: Test read - PASS\nPostgreSQL 10 with Elasticsearch 7: Test query - PASS\nTesting PostgreSQL 11 with Elasticsearch 5\nPostgreSQL 11 with Elasticsearch 5: Test read - PASS\nPostgreSQL 11 with Elasticsearch 5: Test query - PASS\nTesting PostgreSQL 11 with Elasticsearch 6\nPostgreSQL 11 with Elasticsearch 6: Test read - PASS\nPostgreSQL 11 with Elasticsearch 6: Test query - PASS\nTesting PostgreSQL 11 with Elasticsearch 7\nPostgreSQL 11 with Elasticsearch 7: Test read - PASS\nPostgreSQL 11 with Elasticsearch 7: Test query - PASS\nPASS\n```\n\n### Test Failure Messages\n\n```\nError starting userland proxy: listen tcp 0.0.0.0:5432: bind: address already in use\n```\nYou are already running something that listens to 5432.\nTry stopping your running postgres server:\n```\nsudo /etc/init.d/postgresql stop\n```\n\n```\nmax virtual memory areas vm.max_map_count [65530] is too low, increase to at least [262144]\n```\nYour system does not have the appropriate limits in place to run a production ready instance of elasticsearch.\nTry increasing it:\n```\nsudo sysctl -w vm.max_map_count=262144\n```\nThis setting will revert after a reboot.\n\n\n",
        "description_content_type": "text/markdown",
        "docs_url": null,
        "download_url": "https://github.com/matthewfranglen/postgres-elasticsearch-fdw/archive/0.5.1.zip",
        "downloads": {
            "last_day": -1,
            "last_month": -1,
            "last_week": -1
        },
        "home_page": "https://github.com/matthewfranglen/postgres-elasticsearch-fdw",
        "keywords": "postgres,postgresql,elastic,elastic search,fdw",
        "license": "",
        "maintainer": "",
        "maintainer_email": "",
        "name": "pg-es-fdw",
        "package_url": "https://pypi.org/project/pg-es-fdw/",
        "platform": "",
        "project_url": "https://pypi.org/project/pg-es-fdw/",
        "project_urls": {
            "Download": "https://github.com/matthewfranglen/postgres-elasticsearch-fdw/archive/0.5.1.zip",
            "Homepage": "https://github.com/matthewfranglen/postgres-elasticsearch-fdw"
        },
        "release_url": "https://pypi.org/project/pg-es-fdw/0.5.1/",
        "requires_dist": [
            "elasticsearch"
        ],
        "requires_python": "",
        "summary": "Connect PostgreSQL and Elastic Search with this Foreign Data Wrapper",
        "version": "0.5.1"
    },
    "last_serial": 5723266,
    "releases": {
        "0.3.1": [
            {
                "comment_text": "",
                "digests": {
                    "md5": "f18a1abd0817815136d3fc98b0d77d47",
                    "sha256": "42c2a2f901880f649c97155b8ab089a6d811eadc7ce90d711d1b6e4d5eff4065"
                },
                "downloads": -1,
                "filename": "pg_es_fdw-0.3.1.tar.gz",
                "has_sig": false,
                "md5_digest": "f18a1abd0817815136d3fc98b0d77d47",
                "packagetype": "sdist",
                "python_version": "source",
                "requires_python": null,
                "size": 2631,
                "upload_time": "2017-04-17T20:10:22",
                "url": "https://files.pythonhosted.org/packages/6b/5e/71c1352bc3897d1574ad853c08818859293e4c1fb10929bb39ac55b19a4c/pg_es_fdw-0.3.1.tar.gz"
            }
        ],
        "0.4.0": [
            {
                "comment_text": "",
                "digests": {
                    "md5": "2a2b25a227000f9138d2e68f58a4e66b",
                    "sha256": "c376d9f4f8f6de19d8ef0b1a16017ffce4d2f0798a69b6b7a172a1eb41b8b049"
                },
                "downloads": -1,
                "filename": "pg_es_fdw-0.4.0.tar.gz",
                "has_sig": false,
                "md5_digest": "2a2b25a227000f9138d2e68f58a4e66b",
                "packagetype": "sdist",
                "python_version": "source",
                "requires_python": null,
                "size": 2625,
                "upload_time": "2017-04-17T20:19:24",
                "url": "https://files.pythonhosted.org/packages/92/ba/0c3c32f0f4fe7a29040d1cae35e7999434a2b52119b651b08fece285a97a/pg_es_fdw-0.4.0.tar.gz"
            }
        ],
        "0.4.1": [
            {
                "comment_text": "",
                "digests": {
                    "md5": "481a48e87451c6a1b62d9f9d57642971",
                    "sha256": "d25c27301f7c86604eb59d2852ba75df8be8bfa5efc1bfcb401c669f53b3f9e5"
                },
                "downloads": -1,
                "filename": "pg_es_fdw-0.4.1.tar.gz",
                "has_sig": false,
                "md5_digest": "481a48e87451c6a1b62d9f9d57642971",
                "packagetype": "sdist",
                "python_version": "source",
                "requires_python": null,
                "size": 4251,
                "upload_time": "2017-04-19T20:10:02",
                "url": "https://files.pythonhosted.org/packages/c1/79/41f67e22b73d362ded7c7a73cfbb3bbf17f05cadb4d3ca4c553efe7cb756/pg_es_fdw-0.4.1.tar.gz"
            }
        ],
        "0.4.2": [
            {
                "comment_text": "",
                "digests": {
                    "md5": "fb60730b57a9a1ad90fe07f29498f4a6",
                    "sha256": "bf68ca056731a5fc0a44e35febda60071d2dfb29f0e3741f0427505949529b72"
                },
                "downloads": -1,
                "filename": "pg_es_fdw-0.4.2-py2.py3-none-any.whl",
                "has_sig": false,
                "md5_digest": "fb60730b57a9a1ad90fe07f29498f4a6",
                "packagetype": "bdist_wheel",
                "python_version": "py2.py3",
                "requires_python": null,
                "size": 3791,
                "upload_time": "2017-07-28T23:44:54",
                "url": "https://files.pythonhosted.org/packages/37/7d/8552582c572cb2a8316912c8c6728c974069b26631c5a1a9c6f6d3c43d50/pg_es_fdw-0.4.2-py2.py3-none-any.whl"
            }
        ],
        "0.5.0": [
            {
                "comment_text": "",
                "digests": {
                    "md5": "b396028344fe4fc7039b5861b2b83f30",
                    "sha256": "e69aa1d71a040127e7112487455174c8accb843476eca61a343f72ea58acaed7"
                },
                "downloads": -1,
                "filename": "pg_es_fdw-0.5.0-py2.py3-none-any.whl",
                "has_sig": false,
                "md5_digest": "b396028344fe4fc7039b5861b2b83f30",
                "packagetype": "bdist_wheel",
                "python_version": "py2.py3",
                "requires_python": null,
                "size": 3930,
                "upload_time": "2019-08-23T22:14:49",
                "url": "https://files.pythonhosted.org/packages/24/11/57b73d6a25f7df55483fd32707b52dd7560b70360a317dc1a49ea141917d/pg_es_fdw-0.5.0-py2.py3-none-any.whl"
            }
        ],
        "0.5.1": [
            {
                "comment_text": "",
                "digests": {
                    "md5": "e52e368bf7b106f94c2e61d49f3b2452",
                    "sha256": "27d072834d5ee349216a21886158e684466aabdfd784149a627ea49dafca905d"
                },
                "downloads": -1,
                "filename": "pg_es_fdw-0.5.1-py2.py3-none-any.whl",
                "has_sig": false,
                "md5_digest": "e52e368bf7b106f94c2e61d49f3b2452",
                "packagetype": "bdist_wheel",
                "python_version": "py2.py3",
                "requires_python": null,
                "size": 6375,
                "upload_time": "2019-08-24T00:05:05",
                "url": "https://files.pythonhosted.org/packages/02/22/ba4515a073b633c9f331c6b0173077518758007bd0c8a0a58ea18ade9b2a/pg_es_fdw-0.5.1-py2.py3-none-any.whl"
            },
            {
                "comment_text": "",
                "digests": {
                    "md5": "9feed95937543276254608b820f7c242",
                    "sha256": "3655ac95cef343b5d4442f6ea8085a3f951e536562b740058eb551a088839e95"
                },
                "downloads": -1,
                "filename": "pg_es_fdw-0.5.1.tar.gz",
                "has_sig": false,
                "md5_digest": "9feed95937543276254608b820f7c242",
                "packagetype": "sdist",
                "python_version": "source",
                "requires_python": null,
                "size": 6032,
                "upload_time": "2019-08-24T00:05:07",
                "url": "https://files.pythonhosted.org/packages/2b/d8/6777a4743cdcc59aa140be38fa7495a075d09cb4718642fd80e305bc57bf/pg_es_fdw-0.5.1.tar.gz"
            }
        ]
    },
    "urls": [
        {
            "comment_text": "",
            "digests": {
                "md5": "e52e368bf7b106f94c2e61d49f3b2452",
                "sha256": "27d072834d5ee349216a21886158e684466aabdfd784149a627ea49dafca905d"
            },
            "downloads": -1,
            "filename": "pg_es_fdw-0.5.1-py2.py3-none-any.whl",
            "has_sig": false,
            "md5_digest": "e52e368bf7b106f94c2e61d49f3b2452",
            "packagetype": "bdist_wheel",
            "python_version": "py2.py3",
            "requires_python": null,
            "size": 6375,
            "upload_time": "2019-08-24T00:05:05",
            "url": "https://files.pythonhosted.org/packages/02/22/ba4515a073b633c9f331c6b0173077518758007bd0c8a0a58ea18ade9b2a/pg_es_fdw-0.5.1-py2.py3-none-any.whl"
        },
        {
            "comment_text": "",
            "digests": {
                "md5": "9feed95937543276254608b820f7c242",
                "sha256": "3655ac95cef343b5d4442f6ea8085a3f951e536562b740058eb551a088839e95"
            },
            "downloads": -1,
            "filename": "pg_es_fdw-0.5.1.tar.gz",
            "has_sig": false,
            "md5_digest": "9feed95937543276254608b820f7c242",
            "packagetype": "sdist",
            "python_version": "source",
            "requires_python": null,
            "size": 6032,
            "upload_time": "2019-08-24T00:05:07",
            "url": "https://files.pythonhosted.org/packages/2b/d8/6777a4743cdcc59aa140be38fa7495a075d09cb4718642fd80e305bc57bf/pg_es_fdw-0.5.1.tar.gz"
        }
    ]
}