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

You can remove environment field in dockerfile if you are passing all the variables for db using single env file

# proj.env 
POSTGRES_USER=username
POSTGRES_PASSWORD=user_password
POSTGRES_DB=dev_db_name

Also, remove

ENV POSTGRES_DB ''
ENV POSTGRES_USER ''
ENV POSTGRES_PASSWORD ''

from pg-Dockerfile as we are passing all from env

I just noticed you can also add SQL files to the /docker-entrypoint-initdb.d/ directory and they'll be executed directly, so no need to create .sh files for that.

In my case I just created a startup.sql with the SQL needed to create my DB and tables, and then copied it on pg-Dockerfile:

# ...

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

Now every time I run docker-compose up, I have a PostgreSQL with my database created on it. :)

Thanks for the feedback @kazziq, @avoidik, @abhijithvijayan - its been a while since I've looked at this. I'll try to fit some time in to tidy this up some time this week.

How would you install an extension that has system wide dependancies that need compilnig?

e.g.

ora_migrator is a plugin for db_migrator that uses oracle_fdw

The whole process consists of downloading the source to the extension folder inside the docker Postgres installation (container shell), compiling the extension (container shell), and installing it (sql).

---

The extension files must be placed in the extension subdirectory of the PostgreSQL shared files directory, which can be found with

pg_config --sharedir

If the extension building infrastructure PGXS is installed, you can do that simply with

make install

The extension is installed in the database to which you want to migrate the data with the SQL command
CREATE EXTENSION db_migrator;

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.