Install Extensions in Postgres Docker container

Recently I wanted use the Fluent migrator for seeding my postgres database, which was running as a docker container. I basically had a table User, which had a primary key of type UUID that needs to be auto-generated.

My migration code looked as follows.

Create.Table(User.TABLE_NAME)
    .WithColumn("id").AsGuid().NotNullable().WithDefaultValue(SystemMethods.NewGuid).PrimaryKey()
    .WithColumn("username").AsString().NotNullable()
    .WithColumn("password").AsString().NotNullable();

The above code would essentially turn out as the following postgres sql.

CREATE TABLE "public"."users" ("id" uuid NOT NULL DEFAULT uuid_generate_v4(), "username" text NOT NULL, "password" text NOT NULL, CONSTRAINT "PK_users" PRIMARY KEY ("id"));

Notice the default function uuid_generate_v4() used in the query. This would generate an error if you use the default Postgres docker image for building your container.

The error was 42883: function uuid_generate_v4() does not exist

This is because the function uuid_generate_v4() is defined in an extension uuid-ossp which has to be installed first in the container before the command could be executed. Since we are using docker compose for handling our containers, we will use the same to install the extension.

We will define our required scripts to install the extension in a separate file and include it in our docker compose.

\c ntuserauth;
CREATE EXTENSION IF NOT EXISTS "uuid-ossp";

In the above code, we have used the ntuserauth database (which is the database in which we need to install the extension) and then instructed to install the extension uuid-ossp if it doesn’t already exists. We will the scripts in a file install-extensions.sql and place in our scripts directory.

We will then use the scripts defined above to be copied over to docker-entrypoint-initdb.d and used to initialize the database.

From the official Docker image page for Postgres

If you would like to do additional initialization in an image derived from this one, add one or more *.sql, *.sql.gz, or *.sh scripts under /docker-entrypoint-initdb.d (creating the directory if necessary). After the entrypoint calls initdb to create the default postgres user and database, it will run any *.sql files, run any executable *.sh scripts, and source any non-executable *.sh scripts found in that directory to do further initialization before starting the service

Let us go ahead change our docker compose to include the scripts.

  nt.authservice.db:
    image: postgres:14.1-alpine
    container_name: nt.authservice.db
    hostname: "nt.authservice.db"
    restart: always
    environment:
      - POSTGRES_USER=postgres
      - POSTGRES_PASSWORD=mypassword
      - POSTGRES_DB=ntuserauth
    ports:
      - '5432:5432'
    volumes:
      - nt.authservice.db.volume:/var/lib/postgresql/data
      - ./services/db/scripts:/docker-entrypoint-initdb.d

You can verify that you have the extension installed correctly by running the \dx command against the database in the container.

                            List of installed extensions
   Name    | Version |   Schema   |                   Description
-----------+---------+------------+-------------------------------------------------
 plpgsql   | 1.0     | pg_catalog | PL/pgSQL procedural language
 uuid-ossp | 1.1     | public     | generate universally unique identifiers (UUIDs)
(2 rows)

That’s all we need to ensure our required extension uuid-ossp is installed in the postgres container and we are able to initialize the database using the migrations from fluent migrations.