How to create User/Database in script for Docker Postgres

Question

I have been trying to set up a container for a development postgres instance by creating a custom user & database. I am using the official postgres docker image. In the documentation it instructs you to insert a bash script inside of the /docker-entrypoint-initdb.d/ folder to set up the database with any custom parameters.

My bash script: make_db.sh

su postgres -c "createuser -w -d -r -s docker"
su postgres -c "createdb -O docker docker"

Dockerfile

FROM library/postgres

RUN ["mkdir", "/docker-entrypoint-initdb.d"]
ADD make_db.sh /docker-entrypoint-initdb.d/

The error I get from the docker logs -f db (db is my container name) is:

createuser: could not connect to database postgres: could not connect to server: No such file or directory

It seems that the commands inside of the /docker-entrypoint-initdb.d/ folder are being executed before postgres is started. My question is, how do I set up a user/database programmatically using the official postgres container? Is there any way to do this with a script?

Dan Nissenbaum · Accepted Answer · 2018-06-26 14:22:30Z

654

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

edited Jun 26, 2018 at 14:22

Dan Nissenbaum

14k22 gold badges111 silver badges188 bronze badges

answered Oct 28, 2014 at 0:58

Thomasleveil

105k18 gold badges123 silver badges119 bronze badges

Sign up to request clarification or add additional context in comments.

26 Comments

Thomasleveil Over a year ago

you can with: gosu postgres postgres --single < /tmp/somefile.sql

DarVar Over a year ago

How can I run psql -U myDb -d myDb -f myDb.sql

brianz Over a year ago

Note, --single is no longer supported in any of the postgres Dockerfiles.

Stefan Arentz Over a year ago

Although this is marked as the correct answer, there is a nuance here nobody talked about: POSTGRES_USER/PASSWORD will set the super user in your database. That may not always be a good idea to use for your main database user. Instead consider creating a separate application user & database.

LucasBr Over a year ago

As per September 2021, this can be seen on PostgreSQL official page on docker: Warning: scripts in /docker-entrypoint-initdb.d are only run if you start the container with a data directory that is empty.

|

Vlad · Accepted Answer · 2019-10-11 17:48:26Z

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

danigb · Accepted Answer · 2020-05-11 11:54:55Z

51

With docker compose there's a simple alternative (no need to create a Dockerfile). Just create a init-database.sh:

#!/bin/bash
set -e

psql -v ON_ERROR_STOP=1 --username "$POSTGRES_USER" --dbname "$POSTGRES_DB" <<-EOSQL
    CREATE USER docker;
    CREATE DATABASE my_project_development;
    GRANT ALL PRIVILEGES ON DATABASE my_project_development TO docker;
    CREATE DATABASE my_project_test;
    GRANT ALL PRIVILEGES ON DATABASE my_project_test TO docker;
EOSQL

And reference it in the volumes section:

version: '3.4'

services:
  postgres:
    image: postgres
    restart: unless-stopped
    volumes:
      - postgres:/var/lib/postgresql/data
      - ./init-database.sh:/docker-entrypoint-initdb.d/init-database.sh
    environment:
      - POSTGRES_USER=postgres
      - POSTGRES_PASSWORD=postgres
    ports:
      - 5432:5432

volumes:
  postgres:

answered May 11, 2020 at 11:54

danigb

8817 silver badges14 bronze badges

5 Comments

Rich Apodaca Over a year ago

This didn't work for me. It appears the volumes directive creates a directory, not a file, in docker-entrypoint-initdb.d. The errors is that the file is a directory.

Ron Over a year ago

Confirmed it works perfect on my local machine. Just one thing we need to make local ./init-database.sh executable by running chmod 755 ./init-datbase.sh.

A. Rabus Over a year ago

docker creates an empty directory if the source file is not found... for me that means usually i messed up the paths...

ron_g Over a year ago

Sorry, bit of a noob question - in docker-compose you specify POSTGRES_PASSWORD for the container environment. Why is this not used in init-database.sh (whilst POSTGRES_USER is)?

PirateApp Over a year ago

where do you put chmod 755 inside the docker-compose file?

Gabriel · Accepted Answer · 2019-04-23 18:52:52Z

26

You can use this commands:

docker exec -it yournamecontainer psql -U postgres -c "CREATE DATABASE mydatabase ENCODING 'LATIN1' TEMPLATE template0 LC_COLLATE 'C' LC_CTYPE 'C';"

docker exec -it yournamecontainer psql -U postgres -c "GRANT ALL PRIVILEGES ON DATABASE postgres TO postgres;"

answered Apr 23, 2019 at 18:52

Gabriel

5336 silver badges9 bronze badges

1 Comment

Zyigh Over a year ago

ENCODING 'LATIN1' is very weird... You must have very particular needs to avoid using utf8

m0meni · Accepted Answer · 2017-04-29 16:42:44Z

17

You can now put .sql files inside the init directory:

From the docs

If you would like to do additional initialization in an image derived from this one, add one or more *.sql 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 and source any *.sh scripts found in that directory to do further initialization before starting the service.

So copying your .sql file in will work.

edited Apr 29, 2017 at 16:42

answered Sep 2, 2015 at 0:15

m0meni

16.5k18 gold badges88 silver badges149 bronze badges

6 Comments

Jeremiah Adams Over a year ago

environmental variables for user/password and db creation still works. (9.5.3)

MadPhysicist Over a year ago

I have a project that works locally. However, when I move it to AWS EC2, it says that DB is not found. I copy the .sql file in the proper directory, but it still returns that error. Any idea where I could look? I am new to Docker.

Violet Red Over a year ago

This is only true if there is a single db instance bound to a single volume, i.e. it won't run any scripts if anything is already in the volume folder. This gives me a headache, because I want to have a separate db initializers for each of my stack services.

sh87 Over a year ago

From the docs

Warning: scripts in /docker-entrypoint-initdb.d are only run if you start the container with a data directory that is empty; any pre-existing database will be left untouched on container startup. One common problem is that if one of your /docker-entrypoint-initdb.d scripts fails (which will cause the entrypoint script to exit) and your orchestrator restarts the container with the already initialized data directory, it will not continue on with your scripts.

Elte156 Over a year ago

@mrpandey It's by alpha order. So name your files like 1-create_tables.sql and 2-insert_data.sql.

|

firelynx · Accepted Answer · 2023-05-31 09:22:28Z

14

This is my docker-compose.yml

   postgres:
    image: postgres:latest
    ports:
      - 5432:5432
    volumes:
      - postgres_data:/var/lib/postgresql/data
      - ${PROJECTDIR}/init.sql:/docker-entrypoint-initdb.d/1-schema.sql
    environment:
      - POSTGRES_USER=postgres
      - POSTGRES_DB=postgres
      - POSTGRES_PASSWORD=postgres

Here are two things you need to consider:

docker-entrypoint-initdb.d will only work when your data dictionary is empty. So if you have volumes, be sure to delete them docker-compose down --volumes.
Create your user and database in the init.sql instead of docke-compose.yml. In docker-compose.yml, you should specify the postgres user .

edited May 31, 2023 at 9:22

firelynx

32.5k10 gold badges94 silver badges104 bronze badges

answered Aug 15, 2022 at 3:56

haojie

7831 gold badge14 silver badges33 bronze badges

1 Comment

Folusho Over a year ago

I prefer this option of using a *.sql file instead of a *.sh file as it prevents the need for setting permissions on the *.sh file.

Abhishek Chatterjee · Accepted Answer · 2021-05-16 16:08:19Z

10

With Postgres latest image (ID: b262c8b2fb54) and Docker version 20.10.6 the docker-compose will look like,

version: '2'

services:
  app:
    # the detail of app
  db:
    image: 'postgres'
    container_name: book-shop-db-postgres
    environment:
      - POSTGRES_USER=postgres
      - POSTGRES_PASSWORD=postgres
      - POSTGRES_DB=bookshop-db

This will create the user, databases as mentioned while starting

answered May 16, 2021 at 16:08

Abhishek Chatterjee

2,1373 gold badges26 silver badges36 bronze badges

Comments

Rondo · Accepted Answer · 2014-10-28 03:13:55Z

8

I add custom commands to a environment evoked in a CMD after starting services... I haven't done it with postgres, but with Oracle:

#set up var with noop command
RUN export POST_START_CMDS=":"
RUN mkdir /scripts
ADD script.sql /scripts
CMD service oracle-xe start; $POST_START_CMDS; tail -f /var/log/dmesg

and start with

docker run -d ... -e POST_START_CMDS="su - oracle -c 'sqlplus @/scripts/script' " <image>

.

answered Oct 28, 2014 at 3:13

Rondo

3,74131 silver badges28 bronze badges

Comments

seanmcl · Accepted Answer · 2014-10-28 00:39:41Z

4

You need to have the database running before you create the users. For this you need multiple processes. You can either start postgres in a subshell (&) in the shell script, or use a tool like supervisord to run postgres and then run any initialization scripts.

A guide to supervisord and docker https://docs.docker.com/articles/using_supervisord/

answered Oct 28, 2014 at 0:39

seanmcl

9,9793 gold badges42 silver badges47 bronze badges

1 Comment

mrm Over a year ago

Or just

RUN service postgresql start ; su - postgres -c "psql -c \"CREATE USER test WITH PASSWORD 'test' CREATEDB\"" ; service postgresql stop

usersina · Accepted Answer · 2023-11-15 16:54:12Z

Building up on the previous answers, I had a dump from a database named ecomm-db.sql where ecomm-db is the actual database name that I wanted to import to a local docker container.

The ecomm-db.sql file already had a user named admin that it was referencing, with statements such as ALTER TABLE public."Products" OWNER TO admin;

The way I could get it all to work is:

Create the user admin and database ecomm-db using (using POSTGRES_USER and POSTGRES_DB respectively in docker compose)
Create a script role.sh that grants all privileges on the newly created ecomm-db to the user admin
Initialize the container with the role.sh script and the ecomm-db.sql file.

There is no need to create a new Dockerfile

docker-compose.yml

services:
  postgres:
    image: postgres
    ports:
      - 5432:5432
    networks:
      - postgres-net
    environment:
      - POSTGRES_USER=admin
      - POSTGRES_PASSWORD=password
      - POSTGRES_DB=ecomm-db
    volumes:
      - postgres-vol:/var/lib/postgresql/data
      - ./init/role.sh:/docker-entrypoint-initdb.d/role.sh # order matters
      - ./init/ecomm-db.sql:/docker-entrypoint-initdb.d/data.sql # name in container is arbitrary
    healthcheck:
      test: [ "CMD-SHELL", "pg_isready" ]
      interval: 10s
      timeout: 5s
      retries: 5
    logging:
      options:
        max-size: 10m
        max-file: "3"

init/role.sh

#!/bin/bash
set -e

psql -v ON_ERROR_STOP=1 --username "$POSTGRES_USER" --dbname "$POSTGRES_DB" <<-EOSQL
    GRANT ALL PRIVILEGES ON DATABASE ecomm-db TO admin;
EOSQL

init/ecomm-db.sh (snippet)

--
-- PostgreSQL database dump
--

-- Dumped from database version 13.7 (Ubuntu 13.7-1.pgdg20.04+1)
-- Dumped by pg_dump version 13.7 (Ubuntu 13.7-1.pgdg20.04+1)

...

ALTER TABLE public."Products" OWNER TO admin;
...

Collectives™ on Stack Overflow

How to create User/Database in script for Docker Postgres

My bash script: make_db.sh

Dockerfile

10 Answers 10

EDIT - since Jul 23, 2015

for images older than Jul 23, 2015

26 Comments

Comments

5 Comments

1 Comment

6 Comments

1 Comment

Comments

Comments

1 Comment

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

My bash script: make_db.sh

Dockerfile

10 Answers 10

EDIT - since Jul 23, 2015

for images older than Jul 23, 2015

26 Comments

Comments

5 Comments

1 Comment

6 Comments

1 Comment

Comments

Comments

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related