Install and run Postgres with an extension using docker-compose

Readme.md

Local Postgres

This gist is an example of how you can simply install and run and extended Postgres using docker-compose. It assumes that you have docker and docker-compose installed and running on your workstation.

Install

• Requires docker and docker-compose
• Clone via http: git clone https://gist.github.com/b0b7e06943bd389560184d948bdc2d5b.git
• Make load-extensions.sh executable
• Build the image: docker-compose build

Use

See docker-compose.yml for details.

docker-compose.yml

# Project docker compose file to start up postgres.
#
# 1. Set the postgres variables in proj.env
# 2. Update load-extensions.sh to create the extensions you want loaded
# 3. Upon running for the first time, the container will be created and the database initialized
# 4. Subsequent times you run, the database will already be initialized
# 5. Deleting the container removes the content
#
#
#  To run in the foreground (easiest)
#  > docker-compose up
#
#  To run in the background and tail the logs
#  > docker-compose up -d
#  > docker-compose logs -f
#
#  Subsequent builds (if you change pg-Dockerfile)
#  > docker-compose build
#
version: "3.7"
services:
  postgres:
    build:
      context: .
      dockerfile: pg-Dockerfile
    ports:
      - 5432:5432
    environment:
      - POSTGRES_DB=dspace
      - POSTGRES_USER=dspace
    # You can put all of your env vars in env_file, but you may also only
    # want to put secrets in it and put the file in .gitignore
    env_file:
      - proj.env
    # Uncomment the following if you really want to keep your postgres data around
    # on your disk. This will write to a local directory called 'db-data'
    # volumes:
    #   - ./db-data:/var/lib/postgresql/data

load-extensions.sh

#!/bin/sh

# You could probably do this fancier and have an array of extensions
# to create, but this is mostly an illustration of what can be done

psql -v ON_ERROR_STOP=1 --username "$POSTGRES_USER" <<EOF
create extension pg_trgm;
select * FROM pg_extension;
EOF

pg-Dockerfile

FROM postgres:11.5-alpine

# Variables needed at runtime to configure postgres and run the initdb scripts
ENV POSTGRES_DB ''
ENV POSTGRES_USER ''
ENV POSTGRES_PASSWORD ''

# Copy in the load-extensions script
COPY load-extensions.sh /docker-entrypoint-initdb.d/

proj.env

# Add any more environment settings here
# If there are secrets in this file that shouldn't be shared, add this file to .gitignore
POSTGRES_PASSWORD=dspace

in pg-Dockerfile
RUN chmod 755 /docker-entrypoint-initdb.d/load-extensions.sh
must be added afer
COPY load-extensions.sh /docker-entrypoint-initdb.d/
or else the script execution permission will be denied.

the improved shell scrpit is :

#!/bin/sh
set -e

psql -v ON_ERROR_STOP=1 --username "$POSTGRES_USER" --dbname "$POSTGRES_DB" <<-EOSQL
# must quote extension names or else symbolic error will be thrown.
  create extension if not exists "pg_trgm";
  create extension if not exists "uuid-ossp";
  select * FROM pg_extension;
EOSQL

I have postgres:14 image
I have this error permission denied for language c
When I tried to fix that I got that permission denied for table pg_language

Then I realized that I need to use superuser role.