Foreign data wrappers¶

Access PostgreSQL database tables on remote servers as if they were stored within CrateDB and perform read-only queries on the data.

Prerequisites¶

This guide walks you through setting up and querying foreign data wrappers. Before configuring a foreign data wrapper (FDW), you should have CrateDB and PostgreSQL instances up and running, or other services that speak the PostgreSQL wire protocol. Please note that FDW in CrateDB is available with version 5.7 and above.

Set up¶

Set firewall rules¶

Ensure outbound firewall rules allow CrateDB → remote DB traffic before proceeding with the following steps. The default PostgreSQL port is 5432.

Create a server in CrateDB¶

CREATE SERVER my_postgresql FOREIGN DATA WRAPPER jdbc
OPTIONS (url 'jdbc:postgresql://example.com:5432/');

Note

By default only the crate user can use server definitions that connect to localhost. Other users are not allowed to connect to instances running on the same host as CrateDB. This is a security measure to prevent users from bypassing Host-Based Authentication (HBA) restrictions. See fdw.allow_local.

Create a user mapping¶

Use a DDL statement to map a CrateDB user to another user on a foreign server. If not set, your session details will be used instead.

CREATE USER MAPPING
FOR mylocaluser
SERVER my_postgresql
OPTIONS ("user" 'myremoteuser', password '*****');

Create foreign table¶

Establish a view onto data in the foreign system:

CREATE FOREIGN TABLE remote_readings (
  ts      timestamp,
  device  text,
  value   double
) SERVER my_postgresql
  OPTIONS (
    schema_name 'public',  -- remote schema
    table_name  'readings'
  );

Usage¶

Query and debug¶

You can query these foreign tables like regular user tables:

SELECT ts, value
FROM   remote_readings
WHERE  device = 'sensor-42';

Drop server¶

DROP SERVER my_postgresql;

You can drop the server once it is no longer used. The clauses available are:

IF EXISTS – the statement won’t raise an error if any servers listed don’t exist.
RESTRICT – raises an error if any foreign table or user mappings for the given servers exist. This is the default.
CASCADE – causes DROP SERVER to also delete all foreign tables and mapped users using the given servers.

Example¶

Integrating ROAPI data sources with CrateDB.

Demonstrates how to mount ROAPI data sources as tables in CrateDB using the PostgreSQL foreign data wrapper.

https://github.com/crate/cratedb-examples/tree/main/application/roapi