Automated User and Database Creation in Docker PostgreSQL Containers

Problem Background and Error Analysis

When deploying PostgreSQL development environments using Docker, many developers encounter failures in user and database creation. Typical error messages like createuser: could not connect to database postgres: could not connect to server: No such file or directory indicate that initialization scripts execute before the PostgreSQL service starts.

Environment Variable Configuration Method

Since July 8, 2015, the official PostgreSQL Docker image supports automatic user and database creation through environment variables. This is the simplest and recommended approach:

Using Docker run command:

docker run -e POSTGRES_USER=docker -e POSTGRES_PASSWORD=docker -e POSTGRES_DB=docker library/postgres

Or configuring in Dockerfile:

FROM library/postgres
ENV POSTGRES_USER docker
ENV POSTGRES_PASSWORD docker
ENV POSTGRES_DB docker

This method automatically creates the specified user, password, and database during container startup without requiring additional scripts.

SQL Script Initialization Method

For scenarios requiring more complex initialization logic, SQL scripts can be used. The official image automatically executes .sql files in the /docker-entrypoint-initdb.d/ directory:

Create init.sql file:

CREATE USER docker;
CREATE DATABASE docker;
GRANT ALL PRIVILEGES ON DATABASE docker TO docker;

Add to Dockerfile:

FROM library/postgres
COPY init.sql /docker-entrypoint-initdb.d/

This approach is suitable for scenarios requiring complex SQL statements or data initialization.

Shell Script Advanced Configuration

For scenarios requiring dynamic logic or complex processing, shell scripts can be used. Pay attention to script execution timing and connection methods:

Recommended for latest versions:

export PGUSER=postgres
psql <<- EOSQL
    CREATE USER docker;
    CREATE DATABASE docker;
    GRANT ALL PRIVILEGES ON DATABASE docker TO docker;
EOSQL

Older versions require single-user mode:

gosu postgres postgres --single <<- EOSQL
    CREATE USER docker;
    CREATE DATABASE docker;
    GRANT ALL PRIVILEGES ON DATABASE docker TO docker;
EOSQL

User Creation Syntax Details

When manually creating users, pay attention to PostgreSQL username conventions:

Usernames containing special characters require double quotes:

CREATE USER "test.user" SUPERUSER LOGIN WITH ENCRYPTED PASSWORD 'password';

Each SQL command must end with a semicolon:

CREATE USER docker SUPERUSER;

In PostgreSQL command line, =# prompt indicates readiness for new commands, while -# prompt indicates incomplete commands.

Best Practices and Version Compatibility

When selecting initialization methods, consider: environment variables offer simplicity and version compatibility; SQL scripts suit data initialization; shell scripts handle complex logic. Prioritize environment variables or SQL scripts unless special requirements exist.

Different PostgreSQL versions exhibit variations in initialization mechanisms. Using the latest official image is recommended for optimal compatibility and feature support.