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](https://docs.docker.com/samples/library/postgres/#additional-extensions): [Github](https://github.com/appropriate/docker-postgis/blob/f6d28e4a1871b1f72e1c893ff103f10b6d7cb6e1/10-2.4/Dockerfile). — char, Apr 18 '19 at 08: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, Apr 18 '19 at 08: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, Apr 18 '19 at 09:27
@Mihai they are at the same level– for whatever reason I get a 'file not found' if I don't include the db/ — aroooo, Apr 18 '19 at 09:35
Did you remove the context declaration? You can try and build directly the dockerfile for a test — Mihai, Apr 18 '19 at 09:39
@Mihai tried removing the context and got this: `Service db has neither an image nor a build context specified. At least one must be provided.` — aroooo, Apr 18 '19 at 09:41
Looks like if data exists the script is ignored. That is why it wasn't working: https://stackoverflow.com/a/52715521/5509839 — aroooo, Apr 18 '19 at 09:45
Indeed you have to always remove the data volume if you want to initialize the database — Mihai, Apr 18 '19 at 12:40
my problem with the sql file is that it didn't add the extension to the particular database. The solution by @paul-wheeler below worked for me. — Jonathan Mugan, Aug 29 '22 at 03:50

score 7 · Answer 1 · edited Mar 01 '20 at 15:22

7

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 01 '20 at 15:22

Jascha Grübel

122
2
9

answered Sep 19 '19 at 15:09

Alma Z

244
2
10

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. – Paul Wheeler Aug 10 '22 at 00:41

Michael Leli · Answer 2 · 2023-05-12T03:43:16.763

3

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 '23 at 03:43

answered May 12 '23 at 03:40

Michael Leli

31
3

using this and the script worked perfectly. – MikeB2019x May 18 '23 at 15:34
There is no need for POSTGRES_EXTENSIONS as @Alma_Z suggested works perfectly. – eboraks Aug 18 '23 at 12:33

score 1 · Answer 3 · answered Aug 10 '22 at 01:24

1

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 '22 at 01:24

Paul Wheeler

18,988
3
28
41

Yep, me too. For the sql solution it didn't add it to the specific database, which was the problem for me. – Jonathan Mugan Aug 29 '22 at 03:46

score 0 · Answer 4 · answered Mar 21 '23 at 08:55

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)
    ]

Docker-Compose and Postgres Extensions

4 Answers4

Linked