Is it possible to use CrateDB with a PostgreSQL database by using Foreign Data Wrapper?

CrateDB
1

Is it possible to use CrateDB from within a PostgreSQL database by using Foreign Data Wrapper? Normally PostgreSQL allows to connect to some other databases by creating foreign data wrappers, as described in:

(Assuming this is possible) Do you have any suggestions/recommendations if I want to query CrateDB tables from a PostgreSQL database?

2

Currently this is unfortunately not supported.

We have tested connecting from PG to CrateDB using FDW, however it seems like for using postgresql_fdw we miss Cursor support, which might be integrated with 4.8 (but still with a big question mark) also see Add DECLARE CURSOR support · Issue #10212 · crate/crate · GitHub

With using the more generic odbc_fdw there are currently problems with passing down query parameters resulting in PG trying to load complete tables from CrateDB

—

We are also investigating the other direction (i.e. allowing querying a pg instance from within CrateDB, but no concrete plans for that yet.

1 Like
3

Hi,
Just a quick update to mention that since CrateDB added support for DECLARE CURSOR this is now working but there are currently some limitations depending on the queries and the version of PostgreSQL, see CrateDB/postgres_fdw/LIMIT parameter for details.