Docker-Compose and Postgres Extensions

Question

This is my docker-compose file. Is there any easy way to get a postgres extension installed? I'm trying to install pg_trgm.

Edit: I now have two dockerfiles and an install script. It doesn't seem to be working when I run docker-compose up build

Internal server error: pq: operator does not exist: character varying % unknown

services:
  db:
    build:
      context: .
      dockerfile: db/Dockerfile
    image: postgres
    ports:
      - "5432:5432"
    environment:
      - POSTGRES_USER=x
      - POSTGRES_PASSWORD=x
      - POSTGRES_DB=x

  api:
    build:
      context: .
      args:
        app_env: ${APP_ENV}
    volumes:
      - .:/go/src/x/y/z
    ports:
      - "8080:8080"

db/Dockerfile:

FROM postgres
COPY db/install-extensions.sql /docker-entrypoint-initdb.d

db/install-extensions.sql

CREATE EXTENSION IF NOT EXISTS pg_trgm;

There is an example for adding an extension in a Dockerfile mentioned here: Github. — char
– char, Commented Apr 18, 2019 at 8:32
You need to write your own Dockerfile that simply extends from "postgres" and installs the extension. Then you build that dockerfile in the docker-compose — Mihai
– Mihai, Commented Apr 18, 2019 at 8:44
Put the sql file at the same level as Dockerfile. Then in docker file remove "db/". Also you can remove the "context" declaration in docker-compose. — Mihai
– Mihai, Commented Apr 18, 2019 at 9:27
Indeed you have to always remove the data volume if you want to initialize the database — Mihai
– Mihai, Commented Apr 18, 2019 at 12:40

Jascha Grübel · Accepted Answer · 2020-03-01 15:22:31Z

8

Try this

FROM postgres
COPY ./install-extensions.sql /docker-entrypoint-initdb.d

and remove db from your file.

OR you can write

version: "3.1"

services:
  db:
    image: postgres:9.6
    restart: always
    environment:
      POSTGRES_PASSWORD: unit
      POSTGRES_USER: unit
      POSTGRES_DB: unit
    ports:
      - 5432:5432
    volumes:
      - ./scripts:/docker-entrypoint-initdb.d

then

create directory scripts
put your .sql or .sh file
remove created containers docker-compose rm -v
start docker docker-compose up --build
in logs you must see something like this:

created_extension

edited Mar 1, 2020 at 15:22

Jascha Grübel

1222 silver badges9 bronze badges

answered Sep 19, 2019 at 15:09

Alma Z

3042 silver badges12 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Paul Wheeler Over a year ago

I have tested this and while it does seem to run the CREATE EXTENSION command it is ineffective. The extension functions (i.e. SIMILARITY()) are still not useable when the server has started.

BillB Feb 25 at 13:53

Both of the above solutions worked beautifully, BUT I had to be sure to delete the database volumes while I was trying to get this to work. docker-compose down does not do this by itself, nor does docker-compose rm. In my case, I kept getting the message: PostgreSQL Database directory appears to contain a database; Skipping initialization . Running docker-compose down --volumes deletes the volumes that store the local database, so the next time I ran either of the above two solutions, they worked perfectly.

Michael Leli · Accepted Answer · 2023-05-12 03:43:16Z

4

You can add it via the environment variable under POSTGRES_EXTENSIONS: like this

    version: "3.1"

services:
  db:
    image: postgres:9.6
    restart: always
    environment:
      POSTGRES_PASSWORD: unit
      POSTGRES_USER: unit
      POSTGRES_DB: unit
      POSTGRES_EXTENSIONS: pg_trgm ,another_extension
    ports:
      - 5432:5432
    volumes:
      - ./scripts:/docker-entrypoint-initdb.d

make sure you separate your extensions with comma

edited May 12, 2023 at 3:43

answered May 12, 2023 at 3:40

Michael Leli

773 bronze badges

3 Comments

MikeB2019x Over a year ago

using this and the script worked perfectly.

eboraks Over a year ago

There is no need for POSTGRES_EXTENSIONS as @Alma_Z suggested works perfectly.

Luke Hutchison Over a year ago

The symbol POSTGRES_EXTENSIONS doesn't even exist in the official postgres docker source repo github.com/docker-library/postgres (and this environmental variable doesn't work for me). How are you supposed to get this working?

Paul Wheeler · Accepted Answer · 2022-08-10 01:24:42Z

2

I'm not sure precisely why but in order to get this working I had to use a shell script:

#!/usr/bin/env bash

echo "enabling pg_trgm on database $POSTGRES_DB"
psql -U $POSTGRES_USER --dbname="$POSTGRES_DB" <<-'EOSQL'
  create extension if not exists pg_trgm;
EOSQL
echo "finished with exit code $?"

possibly because I was overriding the default database and user name.

answered Aug 10, 2022 at 1:24

Paul Wheeler

20.3k3 gold badges33 silver badges47 bronze badges

1 Comment

Jonathan Mugan Over a year ago

Yep, me too. For the sql solution it didn't add it to the specific database, which was the problem for me.

tailor · Accepted Answer · 2023-03-21 08:55:57Z

The answer about docker-entrypoint-initdb.d is good, but didn't work for me since I already had a database. Actually, in this case, use your migration system to add an extension.

For django, the migration will look something like this:

from django.db import migrations

def create_third_party_extension(apps, schema_editor):
    schema_editor.execute("CREATE EXTENSION pg_trgm;")


def drop_third_party_extension(apps, schema_editor):
    schema_editor.execute("DROP EXTENSION IF EXISTS pg_trgm;")


class Migration(migrations.Migration):

    dependencies = [
        ('core', '0001_initial'),
    ]

    operations = [
        migrations.RunPython(create_third_party_extension, reverse_code=drop_third_party_extension, atomic=True)
    ]

Collectives™ on Stack Overflow

Docker-Compose and Postgres Extensions

4 Answers 4

2 Comments

3 Comments

1 Comment

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

2 Comments

3 Comments

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related