How to install jdbc_fdw
into 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.
$ sudo yum update -y
$ sudo yum install -y postgresql15-server postgresql15-server-devel make
$ 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
$ 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
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.
$ sudo -u postgres psql
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;
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! 🍾🥂🎆