Skip to content

Instantly share code, notes, and snippets.

@leopoldodonnell
Last active October 10, 2024 09:40
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.

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.

# 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
#!/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
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/
# 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
@kazzkiq
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?

@avoidik
Copy link

avoidik commented Jun 6, 2020

@kazzkiq add more sql statements inside the load-extension.sh file

@abhijithvijayan
Copy link

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

@kazzkiq
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 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. :)

@leopoldodonnell
Copy link
Author

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.

@markodvornik
Copy link

markodvornik commented Sep 11, 2020

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;

@Elanza-48
Copy link

Elanza-48 commented Oct 10, 2021

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

@laricko
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