无法在Postgres Docker镜像中创建PostGIS扩展问题排查求助

Hey there! Let's walk through the most common reasons why your PostGIS extension isn't being created in your Postgres Docker container— I’ve tackled this exact issue a few times, so here’s what to check step by step:

1. Make sure PostGIS dependencies are installed in your Docker image #

This is the most basic (and easy-to-miss) step. The official Postgres Docker image doesn’t include PostGIS out of the box, so you need to install the required packages in your Dockerfile. For example, if you’re using Postgres 15:

FROM postgres:15
# Install PostGIS and related scripts
RUN apt-get update && apt-get install -y --no-install-recommends \
    postgresql-15-postgis-3 \
    postgresql-15-postgis-3-scripts \
    && rm -rf /var/lib/apt/lists/*

Without these packages, the CREATE EXTENSION postgis; command will fail because the extension files don’t exist.

2. Verify your initialization script’s location and permissions #

Postgres Docker automatically runs scripts in the /docker-entrypoint-initdb.d/ directory only on the first container startup (when the data volume is empty). Double-check:

• Did you copy your postgis.sh into this directory in your Dockerfile?

  COPY postgis.sh /docker-entrypoint-initdb.d/
  

• Does the script have executable permissions? Add this line to your Dockerfile to be safe:

  RUN chmod +x /docker-entrypoint-initdb.d/postgis.sh
  

• Is the script targeting the correct database? If you’re using a custom database (not the default postgres), explicitly specify it in the script:

  #!/bin/bash
  set -e
  
  # Replace with your actual database name from file.yaml
  TARGET_DB="your_database_name"
  
  psql -v ON_ERROR_STOP=1 --username "$POSTGRES_USER" --dbname "$TARGET_DB" <<-EOSQL
      CREATE EXTENSION IF NOT EXISTS postgis;
      CREATE EXTENSION IF NOT EXISTS postgis_topology;
      # Add any other PostGIS extensions you need
  EOSQL
  

If you skip specifying the database, the script will run against the default postgres database instead of your target one.

3. Check if the initialization script actually ran #

Postgres only executes the /docker-entrypoint-initdb.d/ scripts once— when the data volume is brand new. If you’ve started the container before (and kept the mounted volume), the script won’t re-run. To fix this:

• Delete the local volume data (e.g., if you mounted ./postgres-data:/var/lib/postgresql/data, delete the postgres-data folder) and restart the container.
• Or manually run the extension creation command inside the container:

  docker exec -it your_postgres_container_name psql -U your_user -d your_db -c "CREATE EXTENSION IF NOT EXISTS postgis;"
  

4. Confirm environment variables are correctly passed #

Your file.yaml configures user/password, but ensure the critical Postgres env vars are set and available to the container:

services:
  postgres:
    image: your-custom-postgres-image
    environment:
      POSTGRES_USER: your_db_user
      POSTGRES_PASSWORD: your_db_password
      POSTGRES_DB: your_target_db  # This tells Postgres to create this DB on first startup
    volumes:
      - ./postgres-data:/var/lib/postgresql/data

If POSTGRES_DB isn’t set, Postgres creates a DB with the same name as POSTGRES_USER— make sure your script references this correct name.

5. Check container logs for specific errors #

Logs are your best friend here. Run this command to see what’s happening during initialization:

docker logs your_postgres_container_name

Look for lines like:

• extension "postgis" does not exist: Means you forgot to install PostGIS dependencies.
• database "xxx" does not exist: Means your script is targeting a DB that wasn’t created.
• permission denied: Means the user running the command doesn’t have superuser access (default POSTGRES_USER should have this, but double-check if you’re using a custom user).

6. Ensure the extension creation syntax is correct #

Always use CREATE EXTENSION IF NOT EXISTS postgis; to avoid errors if the extension is already created. Also, make sure the user executing this command has superuser privileges (the default POSTGRES_USER does, but custom users need explicit grants).

内容的提问来源于stack exchange，提问作者Slim