Calling pgxn from Docker Image

[Eric Powell]

I have been Googling for a while and can't seem to find an answer to this:

I am trying to build a Docker image based on the Postgresql10:10:stretch-slim image that contains several extensions: PostGIS, pgRouting (both can be pulled from APT),

sqlite3_fdw and neo4j-fdw (both pulled via pgxn). 

My problem is that, even though the base image points to Postgres10.10, pgxn want to build the extensions for Postgresql-12:

---> Running in ccd8afa3b8cd

INFO: best version: sqlite_fdw 1.2.0

INFO: saving /tmp/tmpMkbHEP/sqlite_fdw-1.2.0.zip

INFO: unpacking: /tmp/tmpMkbHEP/sqlite_fdw-1.2.0.zip

INFO: building extension

Makefile:35: /usr/lib/postgresql/12/lib/pgxs/src/makefiles/pgxs.mk: No such file or directory

Makefile:40: *** PostgreSQL  9.6, 10, 11 or 12 is required to compile this extension.  Stop.

ERROR: command returned 2: make PG_CONFIG=/usr/bin/pg_config all

The command '/bin/sh -c pgxn install sqlite_fdw' returned a non-zero code: 1

(same result for Neo).

How do I get pgxn to use the proper version in my Docker file?

I tried updating the path to ensure that pg_config from the /usr/lib/postgresql10/bin folder was first based on this post: 

https://dba.stackexchange.com/questions/203087/use-pgxn-to-install-pg-repack-on-postgres-9-6-on-top-of-ubuntu-14-04

And I tried setting an ENV variable pointing to the proper version of Postgres, but always get stuck at the above error....

Here is the applicable parts of the Docker file:

# vim:set ft=dockerfile:

# We base our deployment on Postgres10.10

FROM postgres:10.10

LABEL maintiner="epo...@xxxx.com";

#ENV dirpath /opt/GIT_REPO/PostgresConfiguration/

#Update the path to use the proper Postgres directory

#ENV PATH=/usr/lib/postgresql/10:$PATH

# Populate the PGDATA variable from the base container

# ENV PGDATA /var/lib/pgsql/10/data

#Install dependencies: postgis25_10, pgrouting_10, multicorn (basis for FDWs)

RUN apt-get update && apt-get install -y \

    build-essential\

    pgxnclient\

postgresql-10-postgis-2.5 \

postgresql-10-pgrouting \

    s3cmd \

    libpq-dev \

    build-essential \

    postgresql-10-python-multicorn \

    python-pip \

    python3-pip \

    libsqlite3-dev \

    git \

&& rm -rf /var/lib/apt/lists/*

RUN pip install psycopg2-binary

RUN pip3 install psycopg2-binary

## Install the SQLite FDW

# Update the local git repo for the sqlite FDW extension

# RUN git clone https://github.com/pgspider/sqlite_fdw.git ./sqlite_fdw

# RUN cd ./sqlite_fdw

# RUN ["make", "USE_PGXS=1"]

# RUN ["make install","USE_PGXS=1"]

#Remove the source tree

# RUN cd ..

# RUN ["rm", "-R", "./sqlite_fdw"]

ENV PG_CONFIG=/usr/lib/postgresql/10/bin/pg_config

ENV USE_PGXS=1

RUN pgxn install sqlite_fdw

## Install the Neo4J FDW packages

## We have multicorn, install from source

RUN pgxn install neo4j-fdw

I would appreciate any help on this....seems like it is probably a fairly simple solution....

Thanks,

Eric

[Daniele Varrazzo]

On Sat, Oct 26, 2019 at 7:21 PM Eric Powell <ebpowe...@gmail.com> wrote:

> I have been Googling for a while and can't seem to find an answer to this:

> RUN pgxn install sqlite_fdw

> I would appreciate any help on this....seems like it is probably a fairly simple solution....

Yes, it's documented.

https://pgxn.github.io/pgxnclient/usage.html#pgxn-install

pgxn client doesn't use the PG_CONFIG env var, never done. use
"--pg-config=usr/lib/postgresql/10/bin/pg_config" instead.

Are there problem with these docs so that you had to google etc?

-- Daniele

[Eric Powell]

Figured it out. 

Because the Docker file was pulling in libpq-dev, that was setting the version of PG to 12 (and /usr/bin/postgresql/10/bin from the repo did not contain a version of pg_config) updating the path did not work.

Because I couldn't find a version 10 specific package for libpq-dev in the Stretch repos, I replaced it with postgresql-server-dev-10 to meet the dependency and that resolved the problem.