Skip to content

Instantly share code, notes, and snippets.

Last active July 14, 2024 20:34
Show Gist options
  • Save leopoldodonnell/b0b7e06943bd389560184d948bdc2d5b to your computer and use it in GitHub Desktop.
Save leopoldodonnell/b0b7e06943bd389560184d948bdc2d5b to your computer and use it in GitHub Desktop.
Install and run Postgres with an extension using docker-compose

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.


  • Requires docker and docker-compose
  • Clone via http: git clone
  • Make executable
  • Build the image: docker-compose build


See docker-compose.yml for details.

# Project docker compose file to start up postgres.
# 1. Set the postgres variables in proj.env
# 2. Update 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"
context: .
dockerfile: pg-Dockerfile
- 5432:5432
- POSTGRES_DB=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
- 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
# 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;
FROM postgres:11.5-alpine
# Variables needed at runtime to configure postgres and run the initdb scripts
# Copy in the load-extensions script
COPY /docker-entrypoint-initdb.d/
# Add any more environment settings here
# If there are secrets in this file that shouldn't be shared, add this file to .gitignore
Copy link

kazzkiq commented Jun 3, 2020

Is it possible to load a base SQL file into this docker so it already inits with my database and tables?

Copy link

avoidik commented Jun 6, 2020

@kazzkiq add more sql statements inside the file

Copy link

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

# proj.env 

Also, remove


from pg-Dockerfile as we are passing all from env

Copy link

kazzkiq commented Jun 6, 2020

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 /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. :)

Copy link

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.

Copy link

markodvornik commented Sep 11, 2020

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


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

Copy link

Elanza-48 commented Oct 10, 2021

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

the improved shell scrpit is :

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;

Copy link

laricko commented Feb 28, 2023

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.

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment