PostgreSQL: Create extension not working when called from init script

Question

I'm using docker-compose to create a database capable of performing a trigram similarity search using the pg_trgm extension:

  postgres-db:
    restart: always
    image: postgres:12.2
    env_file:
      - ../../.envs/_compose_prod.env
    expose:
      - 5432
    volumes:
      - database-data:/var/lib/postgresql/data/
      - ../entrypoints/init.sql:/docker-entrypoint-initdb.d/init.sql

../entrypoints/init.sql:

create extension pg_trgm;

I used docker-compose down -v first. Here's an excerpt from the output of docker-compose up --build:

postgres-db_1   | 2020-03-19 19:36:42.352 UTC [46] LOG:  database system was shut down at 2020-03-19 19:36:42 UTC
postgres-db_1   | 2020-03-19 19:36:42.360 UTC [45] LOG:  database system is ready to accept connections
postgres-db_1   |  done
postgres-db_1   | server started
postgres-db_1   | CREATE DATABASE
postgres-db_1   |
postgres-db_1   |
postgres-db_1   | /usr/local/bin/docker-entrypoint.sh: running /docker-entrypoint-initdb.d/init.sql
postgres-db_1   | CREATE EXTENSION
postgres-db_1   |
postgres-db_1   |
postgres-db_1   | 2020-03-19 19:36:42.643 UTC [45] LOG:  received fast shutdown request
postgres-db_1   | waiting for server to shut down....2020-03-19 19:36:42.647 UTC [45] LOG:  aborting any active transactions
postgres-db_1   | 2020-03-19 19:36:42.649 UTC [45] LOG:  background worker "logical replication launcher" (PID 52) exited with exit code 1
postgres-db_1   | 2020-03-19 19:36:42.650 UTC [47] LOG:  shutting down
postgres-db_1   | 2020-03-19 19:36:42.686 UTC [45] LOG:  database system is shut down
postgres-db_1   |  done
postgres-db_1   | server stopped
postgres-db_1   |
postgres-db_1   | PostgreSQL init process complete; ready for start up.
postgres-db_1   |
postgres-db_1   | 2020-03-19 19:36:42.757 UTC [1] LOG:  starting PostgreSQL 12.2 (Debian 12.2-2.pgdg100+1) on x86_64-pc-linux-gnu, compiled by gcc (Debian 8.3.0-6) 8.3.0, 64-bit

It seems like the extension was created successfully. I have done this successfully on my own computer, so I'd expect it to work at this point, but when using queryset.annotate(similarity=TrigramSimilarity("title", value_to_search_for)) and evaluating the query set in my Django REST Framework application, I get the following:

postgres-db_1   | 2020-03-19 19:36:55.384 UTC [81] ERROR:  function similarity(text, unknown) does not exist at character 146
postgres-db_1   | 2020-03-19 19:36:55.384 UTC [81] HINT:  No function matches the given name and argument types. You might need to add explicit type casts.

What can I do?

Thanks for any help.

Does this answer your question? [How to create postgres extension inside the container?](https://stackoverflow.com/questions/40040540/how-to-create-postgres-extension-inside-the-container) — iklinac, Mar 19 '20 at 20:15

score 1 · Accepted Answer · answered Mar 19 '20 at 20:21

1

You can create a custom migration for creating an extension. That way it's not limited to docker or the environment.

from django.contrib.postgres.operations import CreateExtension
class Migration(migrations.Migration):
    ...

    operations = [
        CreateExtension(name='pg_trgm'),
        ...
    ]

answered Mar 19 '20 at 20:21

schillingt

13,493
2
32
34

That works. I still don't know why it didn't work, but I don't need to anymore. Thanks! – Audiopolis Mar 20 '20 at 11:05
@schillingt please which file I'm I meant to include the code you posted? – Toluwalemi Oct 12 '20 at 16:55
@toluwalemi You'd effectively need to create it like a data migration: https://docs.djangoproject.com/en/3.1/topics/migrations/#data-migrations – schillingt Oct 12 '20 at 17:20

PostgreSQL: Create extension not working when called from init script

1 Answers1