pgspider sqlite mysql docker 镜像

昨天有基于官方制作了只包含core 的docker 镜像，同时我们也需要sqlite 以及mysql的
所以提取了一个base 镜像，为了减少镜像的大小，使用了多阶段构建，方便后边sqlte
以及mysql 镜像的更新，以及重新构建，对于influxdb的docker 镜像，后边也会提供

base dockerfile

主要提供pg 的源码以及构建工具

 

FROM debian:stretch-slim

RUN apt-get update && apt-get install -y build-essential git wget libreadline-dev zlib1g-dev

WORKDIR /app

RUN set -eux; 

   if [ -f /etc/dpkg/dpkg.cfg.d/docker ]; then 

   # if this file exists, we're likely in "debian:xxx-slim", and locales are thus being excluded so we need to remove that exclusion (since we need locales)

   grep -q '/usr/share/locale' /etc/dpkg/dpkg.cfg.d/docker; 

   sed -ri '//usr/share/locale/d' /etc/dpkg/dpkg.cfg.d/docker; 

   ! grep -q '/usr/share/locale' /etc/dpkg/dpkg.cfg.d/docker; 

   fi; 

   apt-get update; apt-get install -y locales; rm -rf /var/lib/apt/lists/*; 

   localedef -i en_US -c -f UTF-8 -A /usr/share/locale/locale.alias en_US.UTF-8

ENV LANG en_US.utf8

​

RUN set -eux; 

   apt-get update; 

   apt-get install -y --no-install-recommends libnss-wrapper; 

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

RUN wget https://ftp.postgresql.org/pub/source/v11.6/postgresql-11.6.tar.gz

RUN wget https://raw.githubusercontent.com/pgspider/pgspider/master/pgspider.patch

RUN tar xvf postgresql-11.6.tar.gz

RUN patch -p1 -d postgresql-11.6 < /app/pgspider.patch

RUN cd postgresql-11.6 

   && ./configure 

   && make && make install 

   && cd /app/postgresql-11.6/contrib/pgspider_core_fdw 

   && make && make install 

   && cd /app/postgresql-11.6/contrib/pgspider_fdw  

   && make && make install 

   && cd /app/postgresql-11.6/contrib  

   && make && make install

ENV PATH $PATH:/usr/local/pgspider/bin

sqlite dockerfile

参考pg 的dockerfile 进行了修改

• Dockerfile

FROM dalongrong/pgspider:base as build

WORKDIR /app

RUN apt-get update && apt-get install -y libsqlite3-dev

RUN git clone https://github.com/pgspider/sqlite_fdw.git /app/postgresql-11.6/contrib/sqlite_fdw

RUN cd /app/postgresql-11.6/contrib/sqlite_fdw && make && make install

​

FROM debian:stretch-slim

ENV GOSU_VERSION 1.11

RUN apt-get update && apt-get install -y wget libsqlite3-dev

# explicitly set user/group IDs

RUN set -eux; 

   groupadd -r postgres --gid=999; 

# https://salsa.debian.org/postgresql/postgresql-common/blob/997d842ee744687d99a2b2d95c1083a2615c79e8/debian/postgresql-common.postinst#L32-35

   useradd -r -g postgres --uid=999 --home-dir=/var/lib/postgresql --shell=/bin/bash postgres; 

# also create the postgres user's home directory with appropriate permissions

# see https://github.com/docker-library/postgres/issues/274

   mkdir -p /var/lib/postgresql; 

   chown -R postgres:postgres /var/lib/postgresql

​

RUN wget -O /usr/local/bin/gosu "https://github.com/tianon/gosu/releases/download/$GOSU_VERSION/gosu-$(dpkg --print-architecture)" 

   && chmod +x /usr/local/bin/gosu 

   && gosu nobody true

# make the "en_US.UTF-8" locale so postgres will be utf-8 enabled by default

RUN set -eux; 

   if [ -f /etc/dpkg/dpkg.cfg.d/docker ]; then 

   # if this file exists, we're likely in "debian:xxx-slim", and locales are thus being excluded so we need to remove that exclusion (since we need locales)

   grep -q '/usr/share/locale' /etc/dpkg/dpkg.cfg.d/docker; 

   sed -ri '//usr/share/locale/d' /etc/dpkg/dpkg.cfg.d/docker; 

   ! grep -q '/usr/share/locale' /etc/dpkg/dpkg.cfg.d/docker; 

   fi; 

   apt-get update; apt-get install -y locales; rm -rf /var/lib/apt/lists/*; 

   localedef -i en_US -c -f UTF-8 -A /usr/share/locale/locale.alias en_US.UTF-8

ENV LANG en_US.utf8

​

# install "nss_wrapper" in case we need to fake "/etc/passwd" and "/etc/group" (especially for OpenShift)

# https://github.com/docker-library/postgres/issues/359

# https://cwrap.org/nss_wrapper.html

RUN set -eux; 

   apt-get update; 

   apt-get install -y --no-install-recommends libnss-wrapper; 

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

​

RUN mkdir /docker-entrypoint-initdb.d

COPY --from=build /usr/local/pgspider /usr/local/pgspider

RUN sed -ri "s!^#?(listen_addresses)s*=s*S+.*!1 = '*'!" /usr/local/pgspider/share/postgresql/postgresql.conf.sample; 

   grep -F "listen_addresses = '*'" /usr/local/pgspider/share/postgresql/postgresql.conf.sample

RUN mkdir -p /var/run/postgresql && chown -R postgres:postgres /var/run/postgresql && chmod 2777 /var/run/postgresql

ENV PATH $PATH:/usr/local/pgspider/bin

ENV PGDATA /var/lib/postgresql/data

RUN mkdir -p "$PGDATA" && chown -R postgres:postgres "$PGDATA" && chmod 777 "$PGDATA"

VOLUME /var/lib/postgresql/data

COPY docker-entrypoint.sh /usr/local/bin/

RUN ln -s usr/local/bin/docker-entrypoint.sh / # backwards compat

ENTRYPOINT ["docker-entrypoint.sh"]

​

EXPOSE 5432

CMD ["postgres"]

• docker-entrypoint.sh
  sqlite 以及mysql 都是一样的

 

#!/usr/bin/env bash

set -Eeo pipefail

# TODO swap to -Eeuo pipefail above (after handling all potentially-unset variables)

​

# usage: file_env VAR [DEFAULT]

# ie: file_env 'XYZ_DB_PASSWORD' 'example'

# (will allow for "$XYZ_DB_PASSWORD_FILE" to fill in the value of

# "$XYZ_DB_PASSWORD" from a file, especially for Docker's secrets feature)

file_env() {

   local var="$1"

   local fileVar="${var}_FILE"

   local def="${2:-}"

   if [ "${!var:-}" ] && [ "${!fileVar:-}" ]; then

      echo >&2 "error: both $var and $fileVar are set (but are exclusive)"

      exit 1

   fi

   local val="$def"

   if [ "${!var:-}" ]; then

      val="${!var}"

   elif [ "${!fileVar:-}" ]; then

      val="$(< "${!fileVar}")"

   fi

   export "$var"="$val"

   unset "$fileVar"

}

​

# check to see if this file is being run or sourced from another script

_is_sourced() {

   # https://unix.stackexchange.com/a/215279

   [ "${#FUNCNAME[@]}" -ge 2 ] 

      && [ "${FUNCNAME[0]}" = '_is_sourced' ] 

      && [ "${FUNCNAME[1]}" = 'source' ]

}

​

# used to create initial postgres directories and if run as root, ensure ownership to the "postgres" user

docker_create_db_directories() {

   local user; user="$(id -u)"

​

   mkdir -p "$PGDATA"

   chmod 700 "$PGDATA"

​

   # ignore failure since it will be fine when using the image provided directory; see also https://github.com/docker-library/postgres/pull/289

   mkdir -p /var/run/postgresql || :

   chmod 775 /var/run/postgresql || :

​

   # Create the transaction log directory before initdb is run so the directory is owned by the correct user

   if [ "$POSTGRES_INITDB_WALDIR" ]; then

      mkdir -p "$POSTGRES_INITDB_WALDIR"

      if [ "$user" = '0' ]; then

         find "$POSTGRES_INITDB_WALDIR" ! -user postgres -exec chown postgres '{}' +

      fi

      chmod 700 "$POSTGRES_INITDB_WALDIR"

   fi

​

   # allow the container to be started with `--user`

   if [ "$user" = '0' ]; then

      find "$PGDATA" ! -user postgres -exec chown postgres '{}' +

      find /var/run/postgresql ! -user postgres -exec chown postgres '{}' +

   fi

}

​

# initialize empty PGDATA directory with new database via 'initdb'

# arguments to `initdb` can be passed via POSTGRES_INITDB_ARGS or as arguments to this function

# `initdb` automatically creates the "postgres", "template0", and "template1" dbnames

# this is also where the database user is created, specified by `POSTGRES_USER` env

docker_init_database_dir() {

   # "initdb" is particular about the current user existing in "/etc/passwd", so we use "nss_wrapper" to fake that if necessary

   # see https://github.com/docker-library/postgres/pull/253, https://github.com/docker-library/postgres/issues/359, https://cwrap.org/nss_wrapper.html

   if ! getent passwd "$(id -u)" &> /dev/null && [ -e /usr/lib/libnss_wrapper.so ]; then

      export LD_PRELOAD='/usr/lib/libnss_wrapper.so'

      export NSS_WRAPPER_PASSWD="$(mktemp)"

      export NSS_WRAPPER_GROUP="$(mktemp)"

      echo "postgres:x:$(id -u):$(id -g):PostgreSQL:$PGDATA:/bin/false" > "$NSS_WRAPPER_PASSWD"

      echo "postgres:x:$(id -g):" > "$NSS_WRAPPER_GROUP"

   fi

​

   if [ "$POSTGRES_INITDB_WALDIR" ]; then

      set -- --waldir "$POSTGRES_INITDB_WALDIR" "$@"

   fi

​

   eval 'initdb --username="$POSTGRES_USER" --pwfile=<(echo "$POSTGRES_PASSWORD") '"$POSTGRES_INITDB_ARGS"' "$@"'

​

   # unset/cleanup "nss_wrapper" bits

   if [ "${LD_PRELOAD:-}" = '/usr/lib/libnss_wrapper.so' ]; then

      rm -f "$NSS_WRAPPER_PASSWD" "$NSS_WRAPPER_GROUP"

      unset LD_PRELOAD NSS_WRAPPER_PASSWD NSS_WRAPPER_GROUP

   fi

}

​

# print large warning if POSTGRES_PASSWORD is empty

docker_verify_minimum_env() {

   # check password first so we can output the warning before postgres

   # messes it up

   if [ "${#POSTGRES_PASSWORD}" -ge 100 ]; then

      cat >&2 <<-'EOWARN'

​

         WARNING: The supplied POSTGRES_PASSWORD is 100+ characters.

​

           This will not work if used via PGPASSWORD with "psql".

​

           https://www.postgresql.org/message-id/flat/E1Rqxp2-0004Qt-PL%40wrigleys.postgresql.org (BUG #6412)

           https://github.com/docker-library/postgres/issues/507

​

      EOWARN

   fi

   if [ -z "$POSTGRES_PASSWORD" ]; then

      # The - option suppresses leading tabs but *not* spaces. :)

      cat >&2 <<-'EOWARN'

         ****************************************************

         WARNING: No password has been set for the database.

                  This will allow anyone with access to the

                  Postgres port to access your database. In

                  Docker's default configuration, this is

                  effectively any other container on the same

                  system.

​

                  Use "-e POSTGRES_PASSWORD=password" to set

                  it in "docker run".

         ****************************************************

      EOWARN

​

   fi

}

​

# usage: docker_process_init_files [file [file [...]]]

# ie: docker_process_init_files /always-initdb.d/*

# process initializer files, based on file extensions and permissions

docker_process_init_files() {

   # psql here for backwards compatiblilty "${psql[@]}"

   psql=( docker_process_sql )

​

   echo

   local f

   for f; do

      case "$f" in

         *.sh)

            # https://github.com/docker-library/postgres/issues/450#issuecomment-393167936

            # https://github.com/docker-library/postgres/pull/452

            if [ -x "$f" ]; then

               echo "$0: running $f"

               "$f"

            else

               echo "$0: sourcing $f"

               . "$f"

            fi

            ;;

         *.sql) echo "$0: running $f"; docker_process_sql -f "$f"; echo ;;

         *.sql.gz) echo "$0: running $f"; gunzip -c "$f" | docker_process_sql; echo ;;

         *) echo "$0: ignoring $f" ;;

      esac

      echo

   done

}

​

# Execute sql script, passed via stdin (or -f flag of pqsl)

# usage: docker_process_sql [psql-cli-args]

# ie: docker_process_sql --dbname=mydb <<<'INSERT ...'

# ie: docker_process_sql -f my-file.sql

# ie: docker_process_sql <my-file.sql

docker_process_sql() {

   local query_runner=( psql -v ON_ERROR_STOP=1 --username "$POSTGRES_USER" --no-password )

   if [ -n "$POSTGRES_DB" ]; then

      query_runner+=( --dbname "$POSTGRES_DB" )

   fi

​

   "${query_runner[@]}" "$@"

}

​

# create initial database

# uses environment variables for input: POSTGRES_DB

docker_setup_db() {

   if [ "$POSTGRES_DB" != 'postgres' ]; then

      POSTGRES_DB= docker_process_sql --dbname postgres --set db="$POSTGRES_DB" <<-'EOSQL'

         CREATE DATABASE :"db" ;

      EOSQL

      echo

   fi

}

​

# Loads various settings that are used elsewhere in the script

# This should be called before any other functions

docker_setup_env() {

   file_env 'POSTGRES_PASSWORD'

​

   file_env 'POSTGRES_USER' 'postgres'

   file_env 'POSTGRES_DB' "$POSTGRES_USER"

   file_env 'POSTGRES_INITDB_ARGS'

​

   declare -g DATABASE_ALREADY_EXISTS

   # look specifically for PG_VERSION, as it is expected in the DB dir

   if [ -s "$PGDATA/PG_VERSION" ]; then

      DATABASE_ALREADY_EXISTS='true'

   fi

}

​

# append md5 or trust auth to pg_hba.conf based on existence of POSTGRES_PASSWORD

pg_setup_hba_conf() {

   local authMethod='md5'

   if [ -z "$POSTGRES_PASSWORD" ]; then

      authMethod='trust'

   fi

​

   {

      echo

      echo "host all all all $authMethod"

   } >> "$PGDATA/pg_hba.conf"

}

​

# start socket-only postgresql server for setting up or running scripts

# all arguments will be passed along as arguments to `postgres` (via pg_ctl)

docker_temp_server_start() {

   if [ "$1" = 'postgres' ]; then

      shift

   fi

​

   # internal start of server in order to allow setup using psql client

   # does not listen on external TCP/IP and waits until start finishes

   set -- "$@" -c listen_addresses='' -p "${PGPORT:-5432}"

​

   PGUSER="${PGUSER:-$POSTGRES_USER}" 

   pg_ctl -D "$PGDATA" 

      -o "$(printf '%q ' "$@")" 

      -w start

}

​

# stop postgresql server after done setting up user and running scripts

docker_temp_server_stop() {

   PGUSER="${PGUSER:-postgres}" 

   pg_ctl -D "$PGDATA" -m fast -w stop

}

​

# check arguments for an option that would cause postgres to stop

# return true if there is one

_pg_want_help() {

   local arg

   for arg; do

      case "$arg" in

         # postgres --help | grep 'then exit'

         # leaving out -C on purpose since it always fails and is unhelpful:

         # postgres: could not access the server configuration file "/var/lib/postgresql/data/postgresql.conf": No such file or directory

         -'?'|--help|--describe-config|-V|--version)

            return 0

            ;;

      esac

   done

   return 1

}

​

_main() {

   # if first arg looks like a flag, assume we want to run postgres server

   if [ "${1:0:1}" = '-' ]; then

      set -- postgres "$@"

   fi

​

   if [ "$1" = 'postgres' ] && ! _pg_want_help "$@"; then

      docker_setup_env

      # setup data directories and permissions (when run as root)

      docker_create_db_directories

      if [ "$(id -u)" = '0' ]; then

         # then restart script as postgres user

         exec gosu postgres "$BASH_SOURCE" "$@"

      fi

​

      # only run initialization on an empty data directory

      if [ -z "$DATABASE_ALREADY_EXISTS" ]; then

         docker_verify_minimum_env

         docker_init_database_dir

         pg_setup_hba_conf

​

         # PGPASSWORD is required for psql when authentication is required for 'local' connections via pg_hba.conf and is otherwise harmless

         # e.g. when '--auth=md5' or '--auth-local=md5' is used in POSTGRES_INITDB_ARGS

         export PGPASSWORD="${PGPASSWORD:-$POSTGRES_PASSWORD}"

         docker_temp_server_start "$@"

​

         docker_setup_db

         docker_process_init_files /docker-entrypoint-initdb.d/*

​

         docker_temp_server_stop

         unset PGPASSWORD

​

         echo

         echo 'PostgreSQL init process complete; ready for start up.'

         echo

      else

         echo

         echo 'PostgreSQL Database directory appears to contain a database; Skipping initialization'

         echo

      fi

   fi

​

   exec "$@"

}

​

if ! _is_sourced; then

   _main "$@"

fi

mysql dockerfile

FROM dalongrong/pgspider:base as build

WORKDIR /app

RUN apt-get update && apt-get install -y libmysql++-dev

RUN git clone https://github.com/pgspider/mysql_fdw.git /app/postgresql-11.6/contrib/mysql_fdw

RUN cd /app/postgresql-11.6/contrib/mysql_fdw && make && make install

​

FROM debian:stretch-slim

ENV GOSU_VERSION 1.11

RUN apt-get update && apt-get install -y wget libmysql++-dev

# explicitly set user/group IDs

RUN set -eux; 

   groupadd -r postgres --gid=999; 

# https://salsa.debian.org/postgresql/postgresql-common/blob/997d842ee744687d99a2b2d95c1083a2615c79e8/debian/postgresql-common.postinst#L32-35

   useradd -r -g postgres --uid=999 --home-dir=/var/lib/postgresql --shell=/bin/bash postgres; 

# also create the postgres user's home directory with appropriate permissions

# see https://github.com/docker-library/postgres/issues/274

   mkdir -p /var/lib/postgresql; 

   chown -R postgres:postgres /var/lib/postgresql

​

RUN wget -O /usr/local/bin/gosu "https://github.com/tianon/gosu/releases/download/$GOSU_VERSION/gosu-$(dpkg --print-architecture)" 

   && chmod +x /usr/local/bin/gosu 

   && gosu nobody true

# make the "en_US.UTF-8" locale so postgres will be utf-8 enabled by default

RUN set -eux; 

   if [ -f /etc/dpkg/dpkg.cfg.d/docker ]; then 

   # if this file exists, we're likely in "debian:xxx-slim", and locales are thus being excluded so we need to remove that exclusion (since we need locales)

   grep -q '/usr/share/locale' /etc/dpkg/dpkg.cfg.d/docker; 

   sed -ri '//usr/share/locale/d' /etc/dpkg/dpkg.cfg.d/docker; 

   ! grep -q '/usr/share/locale' /etc/dpkg/dpkg.cfg.d/docker; 

   fi; 

   apt-get update; apt-get install -y locales; rm -rf /var/lib/apt/lists/*; 

   localedef -i en_US -c -f UTF-8 -A /usr/share/locale/locale.alias en_US.UTF-8

ENV LANG en_US.utf8

​

# install "nss_wrapper" in case we need to fake "/etc/passwd" and "/etc/group" (especially for OpenShift)

# https://github.com/docker-library/postgres/issues/359

# https://cwrap.org/nss_wrapper.html

RUN set -eux; 

   apt-get update; 

   apt-get install -y --no-install-recommends libnss-wrapper; 

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

​

RUN mkdir /docker-entrypoint-initdb.d

COPY --from=build /usr/local/pgspider /usr/local/pgspider

RUN sed -ri "s!^#?(listen_addresses)s*=s*S+.*!1 = '*'!" /usr/local/pgspider/share/postgresql/postgresql.conf.sample; 

   grep -F "listen_addresses = '*'" /usr/local/pgspider/share/postgresql/postgresql.conf.sample

RUN mkdir -p /var/run/postgresql && chown -R postgres:postgres /var/run/postgresql && chmod 2777 /var/run/postgresql

ENV PATH $PATH:/usr/local/pgspider/bin

ENV PGDATA /var/lib/postgresql/data

RUN mkdir -p "$PGDATA" && chown -R postgres:postgres "$PGDATA" && chmod 777 "$PGDATA"

VOLUME /var/lib/postgresql/data

COPY docker-entrypoint.sh /usr/local/bin/

RUN ln -s usr/local/bin/docker-entrypoint.sh / # backwards compat

ENTRYPOINT ["docker-entrypoint.sh"]

​

EXPOSE 5432

CMD ["postgres"]

说明

对于sqlite 以及mysql 的扩展的使用，后边会介绍

参考资料

https://github.com/pgspider/pgspider
https://github.com/pgspider/sqlite_fdw
https://github.com/pgspider/mysql_fdw
https://github.com/rongfengliang/pgspider-docker
https://github.com/docker-library/postgres/tree/0d0485cb02e526f5a240b7740b46c35404aaf13f/11