How to install `jdbc_fdw` with PostgreSQL 15 on Amazon Linux 2023

These instructions were written and tested on 2024-02-17, using Amazon Linux 2023 AMI 2023.3.20240205.2 arm64 HVM kernel-6.1 (ami-0bbebc09f0a12d4d9) on a t4g.medium (2 vCPU, 4 GiB RAM) instance.

Instructions

Step 1. Install prerequisite packages

$ sudo yum update -y
$ sudo yum install -y postgresql15-server postgresql15-server-devel make

Step 2. Setup PostgreSQL

$ sudo postgresql-setup --initdb
$ sudo systemctl enable --now postgresql

Step 3. Install Corretto, Amazon's "no-cost, multiplatform, production-ready distribution of OpenJDK"

$ sudo yum install -y java-17-amazon-corretto-devel

Step 4. Install `jdbc_fdw`

$ cd /tmp
$ curl -sL https://github.com/pgspider/jdbc_fdw/archive/refs/tags/v0.4.0.tar.gz | tar xz
$ cd jdbc_fdw-0.4.0
$ make all USE_PGXS=1 LIBDIR="/usr/lib/jvm/java-17-amazon-corretto.aarch64/lib/server \
    -Wl,-rpath,/usr/lib/jvm/java-17-amazon-corretto.aarch64/lib/server"
$ sudo make install USE_PGXS=1

Step 5. Install the JDBC drivers of the foreign datasources you want to connect to

For example, you can use pgJDBC if you want to connect to Postgres datasources.

$ sudo mkdir /opt/jdbc_fdw
$ sudo chown postgres:postgres /opt/jdbc_fdw
$ sudo -u postgres sh -c 'cd /opt/jdbc_fdw && \
    curl -sLO https://jdbc.postgresql.org/download/postgresql-42.7.1.jar'

If you need to connect to a different foreign datasource, you will need to obtain and install the JDBC client. I recommend installing them into /opt/jdbc_fdw to make it clear what they're being used for.

Step 6. Connect to PostgreSQL and set up `jdbc_fdw`

For the purposes of this document, I chose to use RNAcentral's public Postgres database to verify that this all works. You will need to adapt these instructions to suit your actual datasources, as appropriate.

Once connected to your database using psql, issue the following statements:

CREATE EXTENSION jdbc_fdw;

CREATE SERVER rnacentral
FOREIGN DATA WRAPPER jdbc_fdw
OPTIONS (
		drivername 'org.postgresql.Driver',
		url 'jdbc:postgresql://hh-pgsql-public.ebi.ac.uk:5432/pfmegrnargs',
		querytimeout '60',
		jarfile '/opt/jdbc_fdw/postgresql-42.7.1.jar',
		maxheapsize '512'
);

CREATE USER MAPPING
FOR PUBLIC
SERVER rnacentral
OPTIONS (
    username 'reader',
    password 'NWDMCE5xdipIjRrp'
);

CREATE FOREIGN TABLE rnacen_xref (
    dbid smallint NOT NULL,
    created integer NOT NULL,
    last integer NOT NULL,
    upi character varying(26) NOT NULL,
    version_i integer NOT NULL,
    deleted character(1) NOT NULL,
    "timestamp" timestamp without time zone NOT NULL,
    userstamp character varying(20) NOT NULL,
    ac character varying(300) NOT NULL,
    version integer,
    taxid bigint,
    id bigint
)
SERVER rnacentral
OPTIONS (
    schema_name 'rnacen',
    table_name 'xref'
);

You can test querying the remote table with:

SELECT *
FROM rnacen_xref
LIMIT 10;

Step 7. Celebrate

If you were able to query the RNAcentral database at the end of the previous step, then everything is working as expected.

Time to celebrate! 🍾🥂🎆