I am extending the supabase image to install apache age but when I run my migration to add the extension I get: extension "age" is not available

dbmate-1  | Applying: 20250305083433_add_age_extension.sql
dbmate-1  | Applied: 20250305083433_add_age_extension.sql in 14.471709ms
dbmate-1  | Error: pq: extension "age" is not available

but when i check the container I can see the files are not available

docker exec -it supabase-db bash
root@afe3d0e6ead4:/#  ls -l /usr/share/postgresql/15/extension/age*
-rw-r--r-- 1 postgres postgres 105081 Mar  6 08:20 /usr/share/postgresql/15/extension/age--1.5.0.sql
-rw-r--r-- 1 postgres postgres    900 Mar  6 08:20 /usr/share/postgresql/15/extension/age.control
root@afe3d0e6ead4:/# age

So it seems for some reason postgres can't access the extension files. I have tried appending age to postgres shared preload libraries but I dont think that is the correct way to solve this.

This is my dockerfile

FROM supabase/postgres:15.8.1.020 AS builder

# Add proxy configuration
RUN echo 'Acquire::http::Pipeline-Depth 0;' > /etc/apt/apt.conf.d/99fixbadproxy && \
    echo 'Acquire::http::No-Cache true;' >> /etc/apt/apt.conf.d/99fixbadproxy && \
    echo 'Acquire::BrokenProxy true;' >> /etc/apt/apt.conf.d/99fixbadproxy

# Add PostgreSQL repository
RUN apt-get update && apt-get install -y curl ca-certificates gnupg lsb-release
RUN curl -fsSL .asc | gpg --dearmor -o /usr/share/keyrings/postgresql-keyring.gpg
RUN echo "deb [signed-by=/usr/share/keyrings/postgresql-keyring.gpg]  $(lsb_release -cs)-pgdg main" > /etc/apt/sources.list.d/pgdg.list

# Install build dependencies
RUN apt-get update 

RUN apt-get install -y --no-install-recommends --no-install-suggests \
    bison \
    build-essential \
    flex \
    git \
    postgresql-server-dev-15

# Clone and build Apache AGE
RUN git clone .git /age
WORKDIR /age
RUN git checkout release/PG15/1.5.0
RUN make install

# Final stage - clean image with just the extension files
FROM supabase/postgres:15.8.1.020

# Copy only the compiled extension files
COPY --from=0 /usr/lib/postgresql/15/lib/age.so /usr/lib/postgresql/15/lib/
COPY --from=0 /usr/share/postgresql/15/extension/age* /usr/share/postgresql/15/extension/

and my docker compose file

 db:
    container_name: supabase-db
    image: localhost:5000/supabase-with-age
    restart: unless-stopped
    volumes:
      - ./volumes/db/realtime.sql:/docker-entrypoint-initdb.d/migrations/99-realtime.sql:Z
      # Must be superuser to create event trigger
      - ./volumes/db/webhooks.sql:/docker-entrypoint-initdb.d/init-scripts/98-webhooks.sql:Z
      # Must be superuser to alter reserved role
      - ./volumes/db/roles.sql:/docker-entrypoint-initdb.d/init-scripts/99-roles.sql:Z
      # Initialize the database settings with JWT_SECRET and JWT_EXP
      - ./volumes/db/jwt.sql:/docker-entrypoint-initdb.d/init-scripts/99-jwt.sql:Z
      # PGDATA directory is persisted between restarts
      - ./volumes/db/data:/var/lib/postgresql/data:Z
      # Changes required for internal supabase data such as _analytics
      - ./volumes/db/_supabase.sql:/docker-entrypoint-initdb.d/migrations/97-_supabase.sql:Z
      # Changes required for Analytics support
      - ./volumes/db/logs.sql:/docker-entrypoint-initdb.d/migrations/99-logs.sql:Z
      # Changes required for Pooler support
      - ./volumes/db/pooler.sql:/docker-entrypoint-initdb.d/migrations/99-pooler.sql:Z
      # Use named volume to persist pgsodium decryption key between restarts
      - db-config:/etc/postgresql-custom
    healthcheck:
      test:
        [
        "CMD",
        "pg_isready",
        "-U",
        "postgres",
        "-h",
        "localhost"
        ]
      interval: 5s
      timeout: 5s
      retries: 10
    depends_on:
      vector:
        condition: service_healthy
    environment:
      POSTGRES_HOST: /var/run/postgresql
      PGPORT: ${POSTGRES_PORT}
      POSTGRES_PORT: ${POSTGRES_PORT}
      PGPASSWORD: ${POSTGRES_PASSWORD}
      POSTGRES_PASSWORD: ${POSTGRES_PASSWORD}
      PGDATABASE: ${POSTGRES_DB}
      POSTGRES_DB: ${POSTGRES_DB}
      JWT_SECRET: ${JWT_SECRET}
      JWT_EXP: ${JWT_EXPIRY}
    command:
      [
        "postgres",
        "-c",
        "config_file=/etc/postgresql/postgresql.conf",
        "-c",
        "log_min_messages=fatal" # prevents Realtime polling queries from appearing in logs
      ]
  dbmate:
    image: amacneil/dbmate
    command: --wait up  # auto apply migrations
    restart: on-failure:3  # Limit restart attempts
    volumes:
      - ./db/migrations:/db/migrations:Z
    environment:
      DATABASE_URL: "postgres://postgres:postgres@db:5432/postgres?sslmode=disable"
      DBMATE_SCHEMA_MIGRATIONS_TABLE: "schema_migrations"  # Explicitly name tracking table
      DBMATE_NO_DUMP_SCHEMA: "true"  # Skip schema dumping for performance
    depends_on:
      db:
        condition: service_healthy  # Requires the healthcheck defined above
    logging:
      driver: "json-file"
      options:
        max-size: "10m"
        max-file: "3"

I am extending the supabase image to install apache age but when I run my migration to add the extension I get: extension "age" is not available

dbmate-1  | Applying: 20250305083433_add_age_extension.sql
dbmate-1  | Applied: 20250305083433_add_age_extension.sql in 14.471709ms
dbmate-1  | Error: pq: extension "age" is not available

but when i check the container I can see the files are not available

docker exec -it supabase-db bash
root@afe3d0e6ead4:/#  ls -l /usr/share/postgresql/15/extension/age*
-rw-r--r-- 1 postgres postgres 105081 Mar  6 08:20 /usr/share/postgresql/15/extension/age--1.5.0.sql
-rw-r--r-- 1 postgres postgres    900 Mar  6 08:20 /usr/share/postgresql/15/extension/age.control
root@afe3d0e6ead4:/# age

So it seems for some reason postgres can't access the extension files. I have tried appending age to postgres shared preload libraries but I dont think that is the correct way to solve this.

This is my dockerfile

FROM supabase/postgres:15.8.1.020 AS builder

# Add proxy configuration
RUN echo 'Acquire::http::Pipeline-Depth 0;' > /etc/apt/apt.conf.d/99fixbadproxy && \
    echo 'Acquire::http::No-Cache true;' >> /etc/apt/apt.conf.d/99fixbadproxy && \
    echo 'Acquire::BrokenProxy true;' >> /etc/apt/apt.conf.d/99fixbadproxy

# Add PostgreSQL repository
RUN apt-get update && apt-get install -y curl ca-certificates gnupg lsb-release
RUN curl -fsSL https://www.postgresql./media/keys/ACCC4CF8.asc | gpg --dearmor -o /usr/share/keyrings/postgresql-keyring.gpg
RUN echo "deb [signed-by=/usr/share/keyrings/postgresql-keyring.gpg] http://apt.postgresql./pub/repos/apt $(lsb_release -cs)-pgdg main" > /etc/apt/sources.list.d/pgdg.list

# Install build dependencies
RUN apt-get update 

RUN apt-get install -y --no-install-recommends --no-install-suggests \
    bison \
    build-essential \
    flex \
    git \
    postgresql-server-dev-15

# Clone and build Apache AGE
RUN git clone https://github/apache/age.git /age
WORKDIR /age
RUN git checkout release/PG15/1.5.0
RUN make install

# Final stage - clean image with just the extension files
FROM supabase/postgres:15.8.1.020

# Copy only the compiled extension files
COPY --from=0 /usr/lib/postgresql/15/lib/age.so /usr/lib/postgresql/15/lib/
COPY --from=0 /usr/share/postgresql/15/extension/age* /usr/share/postgresql/15/extension/

and my docker compose file

 db:
    container_name: supabase-db
    image: localhost:5000/supabase-with-age
    restart: unless-stopped
    volumes:
      - ./volumes/db/realtime.sql:/docker-entrypoint-initdb.d/migrations/99-realtime.sql:Z
      # Must be superuser to create event trigger
      - ./volumes/db/webhooks.sql:/docker-entrypoint-initdb.d/init-scripts/98-webhooks.sql:Z
      # Must be superuser to alter reserved role
      - ./volumes/db/roles.sql:/docker-entrypoint-initdb.d/init-scripts/99-roles.sql:Z
      # Initialize the database settings with JWT_SECRET and JWT_EXP
      - ./volumes/db/jwt.sql:/docker-entrypoint-initdb.d/init-scripts/99-jwt.sql:Z
      # PGDATA directory is persisted between restarts
      - ./volumes/db/data:/var/lib/postgresql/data:Z
      # Changes required for internal supabase data such as _analytics
      - ./volumes/db/_supabase.sql:/docker-entrypoint-initdb.d/migrations/97-_supabase.sql:Z
      # Changes required for Analytics support
      - ./volumes/db/logs.sql:/docker-entrypoint-initdb.d/migrations/99-logs.sql:Z
      # Changes required for Pooler support
      - ./volumes/db/pooler.sql:/docker-entrypoint-initdb.d/migrations/99-pooler.sql:Z
      # Use named volume to persist pgsodium decryption key between restarts
      - db-config:/etc/postgresql-custom
    healthcheck:
      test:
        [
        "CMD",
        "pg_isready",
        "-U",
        "postgres",
        "-h",
        "localhost"
        ]
      interval: 5s
      timeout: 5s
      retries: 10
    depends_on:
      vector:
        condition: service_healthy
    environment:
      POSTGRES_HOST: /var/run/postgresql
      PGPORT: ${POSTGRES_PORT}
      POSTGRES_PORT: ${POSTGRES_PORT}
      PGPASSWORD: ${POSTGRES_PASSWORD}
      POSTGRES_PASSWORD: ${POSTGRES_PASSWORD}
      PGDATABASE: ${POSTGRES_DB}
      POSTGRES_DB: ${POSTGRES_DB}
      JWT_SECRET: ${JWT_SECRET}
      JWT_EXP: ${JWT_EXPIRY}
    command:
      [
        "postgres",
        "-c",
        "config_file=/etc/postgresql/postgresql.conf",
        "-c",
        "log_min_messages=fatal" # prevents Realtime polling queries from appearing in logs
      ]
  dbmate:
    image: amacneil/dbmate
    command: --wait up  # auto apply migrations
    restart: on-failure:3  # Limit restart attempts
    volumes:
      - ./db/migrations:/db/migrations:Z
    environment:
      DATABASE_URL: "postgres://postgres:postgres@db:5432/postgres?sslmode=disable"
      DBMATE_SCHEMA_MIGRATIONS_TABLE: "schema_migrations"  # Explicitly name tracking table
      DBMATE_NO_DUMP_SCHEMA: "true"  # Skip schema dumping for performance
    depends_on:
      db:
        condition: service_healthy  # Requires the healthcheck defined above
    logging:
      driver: "json-file"
      options:
        max-size: "10m"
        max-file: "3"

• database
• postgresql
• docker
• docker-compose
• supabase

1 Answer 1

TDLR: use supabase_admin to create extension and edit the control file of the extension you are installing to point directly to the .so files you are installing.

Supabase for security reasons has removed the superuser access on the postgres user

https://github/s/supabase/discussions/2495 https://github/s/supabase/discussions/9314

So when installing the extensions it has to be using the supabase_admin user ie in the above example for dbmate service - DATABASE_URL: "postgresql://supabase_admin:${POSTGRES_PASSWORD}@db:${POSTGRES_PORT}/postgres?sslmode=disable"

The next issue was that the extension wasn't available to postgres to install.

First find where PostgreSQL looks for extensions in your container: While in the container pg_config --sharedir which returned /root/.nix-profile/share/postgresql

I checked the file structure in those directories using:

ls -la /root/.nix-profile/lib/postgresql/

ls -la /root/.nix-profile/share/postgresql/extension/

So I could link the files in my dockerfile to the correct location.

This helped in that the files showed up as available extensions in postgres but when I went to install I was getting an error: could not access file "$libdir/age": No such file or directory.

Using pg_config --pkglibdir shows where $libdir is pointing to but when I linked my files here as well and save them in age dir it had no effect. Possibly because the directory is immutable but I am not sure.

The solution that worked for me was editing the control file to point to the the directory where I am storing the files ie

# Modify the control file to use the full path to the library
&& sed -i "s|module_pathname = '\\\$libdir/age'|module_pathname = '/usr/local/pgsql/lib/age.so'|" /usr/local/pgsql/share/extension/age.control \