This PostgreSQL extension is a Foreign Data Wrapper (FDW) for InfluxDB (version 1.x series).
The current version can work with PostgreSQL 10, 11, 12, 13 and 14.
Go version should be 1.10.4 or later.
Install InfluxDB Go client library
go get github.com/influxdata/influxdb1-client/v2
Add a directory of pg_config to PATH and build and install influxdb_fdw.
make USE_PGXS=1 with_llvm=no make install USE_PGXS=1 with_llvm=no
with_llvm=no is necessary to disable llvm bit code generation when PostgreSQL is configured with --with-llvm because influxdb_fdw use go code and cannot be compiled to llvm bit code.
If you want to build influxdb_fdw in a source tree of PostgreSQL instead, use
make with_llvm=no make install with_llvm=no
CREATE EXTENSION influxdb_fdw;
CREATE SERVER influxdb_server FOREIGN DATA WRAPPER influxdb_fdw OPTIONS (dbname 'mydb', host 'http://localhost', port '8086') ;
CREATE USER MAPPING FOR CURRENT_USER SERVER influxdb_server OPTIONS(user 'user', password 'pass');
You need to declare a column named "time" to access InfluxDB time column.
CREATE FOREIGN TABLE t1(time timestamp with time zone , tag1 text, field1 integer) SERVER influxdb_server OPTIONS (table 'measurement1');
You can use "tags" option to specify tag keys of a foreign table.
CREATE FOREIGN TABLE t2(tag1 text, field1 integer, tag2 text, field2 integer) SERVER influxdb_server OPTIONS (tags 'tag1, tag2');
IMPORT FOREIGN SCHEMA public FROM SERVER influxdb_server INTO public;
SELECT * FROM t1;
Support GROUP BY times() fill() syntax for influxdb. The fill() is supported by two stub function:
- influx_fill_numeric(): use with numeric parameter for example: 100, 100.1111
- influx_fill_option(): use with specified option such as: none, null, linear, previous.
The influx_fill_numeric() and influx_fill_option() is embeded as last parameter of time() function. The table below illustrates the usage:
PostgreSQL syntax | Influxdb Syntax |
---|---|
influx_time(time, interval '2h') | time(2h) |
influx_time(time, interval '2h', interval '1h') | time(2h, 1h) |
influx_time(time, interval '2h', influx_fill_numeric(100)) | time(2h) fill(100) |
influx_time(time, interval '2h', influx_fill_option('linear')) | time(2h) fill(linear) |
influx_time(time, interval '2h', interval '1h', influx_fill_numeric(100)) | time(2h, 1h) fill(100) |
influx_time(time, interval '2h', interval '1h', influx_fill_option('linear')) | time(2h,1h) fill(linear) |
- InfluxDB FDW supports pushed down some aggregate functions: count, stddev, sum, max, min.
- InfluxDB FDW supports INSERT, DELETE statements.
time
andtime_text
column can used for INSERT, DELETE statements.time
column can express timestamp with precision down to microseconds.time_text
column can express timestamp with precision down to nanoseconds.
- InfluxDB FDW supports bulk INSERT by using batch_size option from PostgreSQL version 14 or later.
- WHERE clauses including timestamp, interval and
now()
functions are pushed down. - LIMIT...OFFSET clauses are pushed down when there is LIMIT clause only or both LIMIT and OFFSET.
- UPDATE is not supported.
- WITH CHECK OPTION constraints is not supported. Following limitations originate from data model and query language of InfluxDB.
- Result sets have different number of rows depending on specified target list.
For example,
SELECT field1 FROM t1
andSELECT field2 FROM t1
returns different number of rows if the number of points with field1 and field2 are different in InfluxDB database. - Timestamp precision may be lost because timestamp resolution of PostgreSQL is microseconds while that of InfluxDB is nanoseconds.
- Conditions like
WHERE time + interval '1 day' < now()
do not work. Please useWHERE time < now() - interval '1 day'
.
When a query to foreign tables fails, you can find why it fails by seeing a query executed in InfluxDB with EXPLAIN VERBOSE
.
Opening issues and pull requests on GitHub are welcome.
Portions Copyright (c) 2018-2021, TOSHIBA CORPORATION Portions Copyright (c) 2011-2016, EnterpriseDB Corporation
Permission to use, copy, modify, and distribute this software and its documentation for any purpose, without fee, and without a written agreement is hereby granted, provided that the above copyright notice and this paragraph and the following two paragraphs appear in all copies.
See the LICENSE
file for full details.