Use jdbc_fdw with docker-compose for Postgres

284 views Asked by At

What I try to achieve is that I have a SQL Server database that has the data I want to retreive from a Postgres database. I want to be able to Read and Write the data in the SQL Server data base from the Postgres database. For this, I have created a docker-compose.yml file like so:

version: '3.8'

services:
  api:
    container_name: api
    build:
      context: .
      dockerfile: Dockerfile-api
    ports:
      - '${PORT}:${PORT}'
    volumes:
      - ./:/usr/src/app/
      - /usr/src/app/node_modules
    depends_on:
      - postgres
    restart: always

  postgres:
    container_name: postgres
    #    image: postgres-jdbc-fdw:12.3-2.0.1
    build:
      context: .
      dockerfile: Dockerfile-postgres
    ports:
      - $POSTGRES_PORT:$POSTGRES_PORT
    volumes:
      #- ./postgresdata:/var/lib/postgresql/data
      - ./src/db/docker-entrypoint-initdb.d:/docker-entrypoint-initdb.d
    environment:
      POSTGRES_USER: $POSTGRES_USER
      POSTGRES_PASSWORD: $POSTGRES_PASSWORD
      POSTGRES_DB: $POSTGRES_DB
      MSSQL_HOST: $MSSQL_HOST
      MSSQL_PORT: $MSSQL_PORT
      MSSQL_USER: $MSSQL_USER
      MSSQL_PASSWORD: $MSSQL_PASSWORD
      MSSQL_DATABASE: $MSSQL_DATABASE
    restart: unless-stopped

Here is my ./src/db/docker-entrypoint-initdb.d/1-create-extensions.sh file:

#!/bin/bash
set -e

psql -v ON_ERROR_STOP=1 --username "$POSTGRES_USER" --dbname "$POSTGRES_DB" <<-EOSQL
    CREATE EXTENSION jdbc_fdw;

    CREATE SERVER sql_server
    FOREIGN DATA WRAPPER jdbc_fdw
    OPTIONS (servername '$JDBC_FDW_CONNECTION');

    CREATE USER MAPPING FOR $POSTGRES_USER
    SERVER sql_server
    OPTIONS (username '$JDBC_FDW_USERNAME', password '$JDBC_FDW_PASSWORD'); 
EOSQL

But, before using jdbc_fdw I need to install it in my Dockerfile-postgres file. I read the documentation: pgspider/jdbc_fdw. But I have the same issue that this one: https://github.com/laurenz/oracle_fdw/issues/65

**Makefile:18: ../../src/Makefile.global: No such file or directory
Makefile:19: /contrib/contrib-global.mk: No such file or directory
make: * No rule to make target `/contrib/contrib-global.mk'. Stop.

This error comes because in the postgres code here: https://github.com/postgres/postgres/blob/master/contrib/contrib-global.mk you can see that there is a contrib/contrib-global.mk file, but it doesn't exist in the postgres docker image. Here is my code:

FROM postgres:14.3

# Install Java and MSSQL JDBC driver
RUN apt-get update && apt-get install -y \
    openjdk-11-jre-headless \
    wget \
    make \
    gcc \
    libpq-dev \
    && rm -rf /var/lib/apt/lists/*

# Set the JDBC driver path
ENV JDBC_DRIVER_PATH /usr/local/lib/sqljdbc42.jar

# Copy the JDBC driver to the image
COPY ./src/db/sqljdbc42.jar $JDBC_DRIVER_PATH

# Create /usr/lib64 directory
RUN mkdir -p /usr/lib64

# Link libjvm
RUN ln -s /usr/lib/jvm/java-11-openjdk/lib/server/libjvm.so /usr/lib64/libjvm.so

# Download and extract the jdbc_fdw archive
RUN wget https://github.com/pgspider/jdbc_fdw/archive/refs/tags/v0.3.0.tar.gz && \
  tar -xvzf v0.3.0.tar.gz && \
  cd jdbc_fdw-0.3.0 && \
  make clean && \
  make USE_PGXS=1 && \
  make install USE_PGXS=1 && \
  cd .. && \
  rm -rf v0.3.0.tar.gz jdbc_fdw-0.3.0

# Configure connection string
ENV JDBC_FDW_CONNECTION="jdbc:sqlserver://${MSSQL_HOST};databaseName=${MSSQL_DATABASE}"
ENV JDBC_FDW_DRIVER=com.microsoft.sqlserver.jdbc.SQLServerDriver
ENV JDBC_FDW_USERNAME=${MSSQL_USER}
ENV JDBC_FDW_PASSWORD=${MSSQL_PASSWORD}

# Cleanup
RUN apt purge -y \
    gcc \
    make \
    wget \
  && apt autoremove -y

The make clean returns the error above (ps: sqljdbc42.jar come from https://jdbc.postgresql.org/).

Do I have to use pgspider/jdbc_fdw in order to be able to use jdbc_fdw? If not, I can't find what I change it to. If I have to, then, how can I correct the issue above about /contrib/contrib-global.mk: No such file or directory

Edit: if I use make USE_PGXS=1 clean then the error is:

#0 1.509 jdbc_fdw.c: In function ‘jdbcReScanForeignScan’:

#0 1.509 jdbc_fdw.c:1129:55: warning: logical not is only applied to the left hand side of comparison [-Wlogical-not-parentheses]

#0 1.509 1129 | if (!fsstate->cursor_exists || !fsstate->resultSetID > 0)

#0 1.509 |
^ #0 1.509 jdbc_fdw.c:1129:33: note: add parentheses around left hand side expression to silence this warning

#0 1.509 1129 | if (!fsstate->cursor_exists || !fsstate->resultSetID > 0)

#0 1.509 |
^~~~~~~~~~~~~~~~~~~~~

#0 1.509 | ( )

0

There are 0 answers