jdbc_fdw

Overview

missing el.aarch64

Version

Install

You can install jdbc_fdw directly. First, make sure the PGDG repository is added and enabled:

pig repo add pgdg -u          # Add PGDG repo and update cache

Install the extension using pig or apt/yum/dnf:

• Install
• pig
• dnf

pig install jdbc_fdw;          # Install for current active PG version

pig ext install -y jdbc_fdw -v 16  # PG 16
pig ext install -y jdbc_fdw -v 15  # PG 15
pig ext install -y jdbc_fdw -v 14  # PG 14

dnf install -y jdbc_fdw_16       # PG 16
dnf install -y jdbc_fdw_15       # PG 15
dnf install -y jdbc_fdw_14       # PG 14

Create Extension:

CREATE EXTENSION jdbc_fdw;

Usage

jdbc_fdw: Foreign data wrapper for remote servers available over JDBC

Create Server

CREATE EXTENSION jdbc_fdw;

CREATE SERVER jdbc_server FOREIGN DATA WRAPPER jdbc_fdw
  OPTIONS (
    drivername 'org.postgresql.Driver',
    url 'jdbc:postgresql://remotehost:5432/mydb',
    jarfile '/usr/share/java/postgresql.jar',
    maxheapsize '256'
  );

Server Options: drivername (required, JDBC driver class), url (required, JDBC connection URL), jarfile (required, absolute path to JDBC driver JAR), querytimeout (query timeout in seconds), maxheapsize (JVM heap size in MB, minimum 1).

Create User Mapping

CREATE USER MAPPING FOR CURRENT_USER SERVER jdbc_server
  OPTIONS (username 'dbuser', password 'dbpass');

Create Foreign Table

CREATE FOREIGN TABLE remote_table (
  id integer OPTIONS (key 'true'),
  name text,
  value numeric
)
SERVER jdbc_server
OPTIONS (table_name 'schema.tablename');

Set key 'true' on primary key columns to enable UPDATE and DELETE operations.

Query Remote Data

SELECT * FROM remote_table WHERE id > 100;

Execute Arbitrary SQL with jdbc_exec

The jdbc_exec function executes SQL against the remote database and returns result sets:

SELECT * FROM jdbc_exec('jdbc_server', 'SELECT id, name FROM remote_schema.remote_table WHERE status = 1')
  AS t(id integer, name text);

This is useful for executing queries that go beyond the foreign table definition, including DDL or complex queries on the remote server.