EDIT - since Jul 23, 2015
The official postgres docker image will run .sql scripts found in the /docker-entrypoint-initdb.d/ folder.
So all you need is to create the following sql script:
init.sql
CREATE USER docker;
CREATE DATABASE docker;
GRANT ALL PRIVILEGES ON DATABASE docker TO docker;
and add it in your Dockerfile:
Dockerfile
FROM library/postgres
COPY init.sql /docker-entrypoint-initdb.d/
But since July 8th, 2015, if all you need is to create a user and database, it is easier to just make use to the POSTGRES_USER, POSTGRES_PASSWORD and POSTGRES_DB environment variables:
docker run -e POSTGRES_USER=docker -e POSTGRES_PASSWORD=docker -e POSTGRES_DB=docker library/postgres
or with a Dockerfile:
FROM library/postgres
ENV POSTGRES_USER docker
ENV POSTGRES_PASSWORD docker
ENV POSTGRES_DB docker
for images older than Jul 23, 2015
From the documentation of the postgres Docker image, it is said that
[...] it will source any *.sh script found in that directory [
/docker-entrypoint-initdb.d] to do further initialization before starting the service
What's important here is "before starting the service". This means your script make_db.sh will be executed before the postgres service would be started, hence the error message "could not connect to database postgres".
After that there is another useful piece of information:
If you need to execute SQL commands as part of your initialization, the use of Postgres single user mode is highly recommended.
Agreed this can be a bit mysterious at the first look. What it says is that your initialization script should start the postgres service in single mode before doing its actions. So you could change your make_db.ksh script as follows and it should get you closer to what you want:
NOTE, this has changed recently in the following commit. This will work with the latest change:
export PGUSER=postgres
psql <<- EOSQL
CREATE USER docker;
CREATE DATABASE docker;
GRANT ALL PRIVILEGES ON DATABASE docker TO docker;
EOSQL
Previously, the use of --single mode was required:
gosu postgres postgres --single <<- EOSQL
CREATE USER docker;
CREATE DATABASE docker;
GRANT ALL PRIVILEGES ON DATABASE docker TO docker;
EOSQL
Specify an initialization script for Postgres, using docker run
How to run a script PostgreSQL with Docker Compose in the initialization ?
Docker entrypoint-initdb.d postgres
Postgres init script
Videos
EDIT - since Jul 23, 2015
The official postgres docker image will run .sql scripts found in the /docker-entrypoint-initdb.d/ folder.
So all you need is to create the following sql script:
init.sql
CREATE USER docker;
CREATE DATABASE docker;
GRANT ALL PRIVILEGES ON DATABASE docker TO docker;
and add it in your Dockerfile:
Dockerfile
FROM library/postgres
COPY init.sql /docker-entrypoint-initdb.d/
But since July 8th, 2015, if all you need is to create a user and database, it is easier to just make use to the POSTGRES_USER, POSTGRES_PASSWORD and POSTGRES_DB environment variables:
docker run -e POSTGRES_USER=docker -e POSTGRES_PASSWORD=docker -e POSTGRES_DB=docker library/postgres
or with a Dockerfile:
FROM library/postgres
ENV POSTGRES_USER docker
ENV POSTGRES_PASSWORD docker
ENV POSTGRES_DB docker
for images older than Jul 23, 2015
From the documentation of the postgres Docker image, it is said that
[...] it will source any *.sh script found in that directory [
/docker-entrypoint-initdb.d] to do further initialization before starting the service
What's important here is "before starting the service". This means your script make_db.sh will be executed before the postgres service would be started, hence the error message "could not connect to database postgres".
After that there is another useful piece of information:
If you need to execute SQL commands as part of your initialization, the use of Postgres single user mode is highly recommended.
Agreed this can be a bit mysterious at the first look. What it says is that your initialization script should start the postgres service in single mode before doing its actions. So you could change your make_db.ksh script as follows and it should get you closer to what you want:
NOTE, this has changed recently in the following commit. This will work with the latest change:
export PGUSER=postgres
psql <<- EOSQL
CREATE USER docker;
CREATE DATABASE docker;
GRANT ALL PRIVILEGES ON DATABASE docker TO docker;
EOSQL
Previously, the use of --single mode was required:
gosu postgres postgres --single <<- EOSQL
CREATE USER docker;
CREATE DATABASE docker;
GRANT ALL PRIVILEGES ON DATABASE docker TO docker;
EOSQL
By using docker-compose:
Assuming that you have following directory layout:
$MYAPP_ROOT/docker-compose.yml
/Docker/init.sql
/Docker/db.Dockerfile
File: docker-compose.yml
version: "3.3"
services:
db:
build:
context: ./Docker
dockerfile: db.Dockerfile
volumes:
- ./var/pgdata:/var/lib/postgresql/data
ports:
- "5432:5432"
File: Docker/init.sql
CREATE USER myUser;
CREATE DATABASE myApp_dev;
GRANT ALL PRIVILEGES ON DATABASE myApp_dev TO myUser;
CREATE DATABASE myApp_test;
GRANT ALL PRIVILEGES ON DATABASE myApp_test TO myUser;
File: Docker/db.Dockerfile
FROM postgres:11.5-alpine
COPY init.sql /docker-entrypoint-initdb.d/
Composing and starting services:
docker-compose -f docker-compose.yml up --no-start
docker-compose -f docker-compose.yml start
I there a way to specify an initialization script for Postgres, using docker run?
If I use Docker Compose, this line will copy the script and run it:
volumes: - ./init.sql:/docker-entrypoint-initdb.d/init.sql
However, this syntax doesn't work in the CLI:
$ docker volume create postgres-data $ docker run --detach \ --rm \ --name postgres \ --publish 5432:5432 \ --env-file .env \ --volume postgres-data:/var/lib/postgresql/data \ --volume ./init.sql:/docker-entrypoint-initdb.d/init.sql \ postgres:9.6.22 docker: Error response from daemon: create ./init.sql: "./init.sql" includes invalid characters for a local volume name, only "[a-zA-Z0-9][a-zA-Z0-9_.-]" are allowed. If you intended to pass a host directory, use absolute path.
Is this possible?
I have this:
services:
db:
image: postgres:16.3-alpine3.20
restart: always
environment:
POSTGRES_USER: postgres
POSTGRES_PASSWORD:
POSTGRES_DB: pento
volumes:
- ./init-db.sql:/docker-entrypoint-initdb.d/init-db.sql
- pg_data:/var/lib/postgresql/data
ports:
- "5432:5432"
volumes:
pg_data:And I would like to CREATE other DATABASE when container is starting using a sql script.
For example:
/init-db.sql CREATE DATABASE pento_dev;
How can I do that ? Is it possible ?
person ancient cough retire squash paint books ruthless jeans provide
This post was mass deleted and anonymized with Redact
I have a standard postgres container running, with the pg_data volume mapped to a directory on the host machine.
I want to be able to run an init script everytime I build or re-build the container, to run migrations and other such things. However, any script or '.sql' file placed in /docker-entrypoint-initdb.d/ only gets executed if the pg_data volume is empty.
What is the easiest solution to this – at the moment I could make a pg_dump pf the pg_data directory, then remove it’s content, and restore from the pg_dump, but it seems pointlessly convoluted and open to errors with potential data loss.