Skip to content

Instantly share code, notes, and snippets.

@leopoldodonnell
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.

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