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 | ( )