Skip to content
ElasticSearch to PostgreSQL loader
Shell Smarty Dockerfile
Branch: master
Clone or download
Fetching latest commit…
Cannot retrieve the latest commit at this time.
Permalink
Type Name Latest commit message Commit time
Failed to load latest commit information.
helm-chart ENH: Store ES Dump files in /data in K8S Feb 10, 2020
Dockerfile FIX: Link pypy to python Feb 13, 2020
LICENSE Initial commit Feb 10, 2020
README.md DOC: Be explicit about Helm version Feb 11, 2020
es2postgres.sh ENH: Add time to load/store commands Feb 13, 2020

README.md

es2postgres

docker build docker pulls license

ElasticSearch to PostgreSQL Loader.

Uses es2csv, xsv, GNU sed and psql client to periodically Extract, Transform and Load data from ES to PSQL.

  • Extract: ElasticSearch is queried from the last ingestion point for new data (delta-load). The result is stored in a CSV file.
  • Transform: Currently limited to column name changes via GNU sed expressions.
  • Load: The transformed data is loaded to PostgreSQL Database using the provided schema.

Usage

Docker

docker run \
  -e ES_URL="http://my_elasticsearch_host:9200" \
  -e ES_INDEX_PATTERN='logstash-*' \
  -e CSV_TMPFILE_PATH=/tmp/esdump.csv \
  -e CSV_SED_CMD="1s/prefix_to_remove\.//g ; 1s/@timestamp/timestamp/ ; 1s/kubernetes\.labels\.release/my_instance/ " \
  -e PG_HOST=my_database_host \
  -e PG_USER=my_database_user \
  -e PG_DATABASE=my_database_name \
  -e PG_PASSWORD=my_database_pass \
  -e PG_DDL_FILE=/tmp/table.sql \
  -e PG_SCHEMA_NAME=public \
  -e PG_TABLE_NAME=my_table \
  -e PG_TIME_FIELD=my_time_field \
  -e REFRESH_INTERVAL=3600 \
  -v $(pwd)/table.sql:/tmp/table.sql \
  tibkiss/es2postgres

Kubernetes / Helm-2.x

git clone https://github.com/tibkiss/es2postgres
cd es2postgres
cp /path/to/my/ddl.sql helm-chart/ddl.sql

helm install \
  --name es2postgres \
  --set es.url=http://elasticsearch-master:9200 \
  --set es.indexPattern="logstash-*" \
  --set pg.host="postgresql" \
  --set pg.password=superSeKretPaSS \
  --set pg.database=my_database \
  --set pg.tableName=my_table \
  --set pg.timeField=time \
  --set pg.ddl=ddl.sql \
  --set csvSedCmd="1s/@timestamp/timestamp/ ; 1s/kubernetes\.labels\.release/my_instance/ " \
  ./helm-chart

License

Licensed under the Apache License, Version 2.0 (the "License"); you may not use this file except in compliance with the License. You may obtain a copy of the License at

http://www.apache.org/licenses/LICENSE-2.0

Unless required by applicable law or agreed to in writing, software distributed under the License is distributed on an "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. See the License for the specific language governing permissions and limitations under the License.

You can’t perform that action at this time.