FROM postgres:11
ENV POSTGISV 2.5
# add addressing dictionary
RUN mkdir -p /opt/apps
RUN apt-get update \
&& apt-get install -y --no-install-recommends \
postgresql-$PG_MAJOR-postgis-$POSTGISV \
postgresql-$PG_MAJOR-postgis-$POSTGISV-scripts \
make
# add init script
RUN mkdir -p /docker-entrypoint-initdb.d
COPY ./initdb-postgis.sh /docker-entrypoint-initdb.d/postgis.sh
initdb-postgis.sh
#!/bin/sh
set -e
# Perform all actions as $POSTGRES_USER
export PGUSER="postgres"
# https://pgtune.leopard.in.ua/
# DB Version: 11
# OS Type: linux
# DB Type: web
# Total Memory (RAM): 4 GB
# CPUs num: 2
# Connections num: 150
# Data Storage: ssd
psql -c "ALTER SYSTEM SET max_connections = '150';"
psql -c "ALTER SYSTEM SET shared_buffers = '1GB';"
psql -c "ALTER SYSTEM SET effective_cache_size = '3GB';"
psql -c "ALTER SYSTEM SET maintenance_work_mem = '256MB';"
psql -c "ALTER SYSTEM SET checkpoint_completion_target = '0.7';"
psql -c "ALTER SYSTEM SET wal_buffers = '16MB';"
psql -c "ALTER SYSTEM SET default_statistics_target = '100';"
psql -c "ALTER SYSTEM SET random_page_cost = '1.1';"
psql -c "ALTER SYSTEM SET effective_io_concurrency = '200';"
psql -c "ALTER SYSTEM SET work_mem = '6990kB';"
psql -c "ALTER SYSTEM SET min_wal_size = '1GB';"
psql -c "ALTER SYSTEM SET max_wal_size = '2GB';"
psql -c "ALTER SYSTEM SET max_worker_processes = '2';"
psql -c "ALTER SYSTEM SET max_parallel_workers_per_gather = '1';"
psql -c "ALTER SYSTEM SET max_parallel_workers = '2';"
docker-entrypoint-initdb.d/create_databases.sh
#!/bin/bash
set -e
set -u
function create_user_and_database() {
local database=$1
local username=$2
echo " Creating user and database '$database'"
psql -v ON_ERROR_STOP=1 --username "$POSTGRES_USER" <<-EOSQL
CREATE DATABASE "$database";
GRANT ALL PRIVILEGES ON DATABASE "$database" TO "$username";
CREATE EXTENSION IF NOT EXISTS postgis;
EOSQL
}
if [ -n "$POSTGRES_MULTIPLE_DATABASES" ]; then
echo "Multiple database creation requested: $POSTGRES_MULTIPLE_DATABASES"
for db in $(echo $POSTGRES_MULTIPLE_DATABASES | tr ',' ' '); do
create_user_and_database $db $POSTGRES_USER
done
echo "Multiple databases created"
fi
Facing an issue while docker compose up
/usr/local/bin/docker-entrypoint.sh: running /docker-entrypoint-initdb.d/create_databases.sh
gym-server-vendors-db-1 | Multiple database creation requested: vendorsdb,vendorsdb-test
gym-server-vendors-db-1 | Creating user and database 'vendorsdb'
gym-server-vendors-db-1 | CREATE DATABASE
gym-server-vendors-db-1 | GRANT
gym-server-vendors-db-1 | ERROR: could not open extension control file "/usr/local/share/postgresql/extension/postgis.control": No such file or directory
gym-server-vendors-db-1 | 2023-01-25 12:36:54.345 UTC [47] ERROR: could not open extension control file "/usr/local/share/postgresql/extension/postgis.control": No such file or directory
gym-server-vendors-db-1 | 2023-01-25 12:36:54.345 UTC [47] STATEMENT: CREATE EXTENSION IF NOT EXISTS postgis;
docker-compose.yml
Dockerfile.postgis
initdb-postgis.sh
docker-entrypoint-initdb.d/create_databases.sh
Facing an issue while docker compose up
/usr/local/bin/docker-entrypoint.sh: running /docker-entrypoint-initdb.d/create_databases.sh gym-server-vendors-db-1 | Multiple database creation requested: vendorsdb,vendorsdb-test gym-server-vendors-db-1 | Creating user and database 'vendorsdb' gym-server-vendors-db-1 | CREATE DATABASE gym-server-vendors-db-1 | GRANT gym-server-vendors-db-1 | ERROR: could not open extension control file "/usr/local/share/postgresql/extension/postgis.control": No such file or directory gym-server-vendors-db-1 | 2023-01-25 12:36:54.345 UTC [47] ERROR: could not open extension control file "/usr/local/share/postgresql/extension/postgis.control": No such file or directory gym-server-vendors-db-1 | 2023-01-25 12:36:54.345 UTC [47] STATEMENT: CREATE EXTENSION IF NOT EXISTS postgis;