Creating Postgres Table in Docker

Question

This is in relation to Error While Attempting to docker-compose a Postgres Image.

In addition to the files described in the question above, init.sh looks like this:

#for line in $(cat .env); do
#  export $line;
#done

#service postgresql-9.3 initdb


psql -d users -U even_financial -f schemas.sql

where schemas.sql looks like this:

CREATE TABLE accounts (
    user_id serial PRIMARY KEY,
    username VARCHAR ( 50 ) UNIQUE NOT NULL,
    password VARCHAR ( 50 ) NOT NULL,
    email VARCHAR ( 255 ) UNIQUE NOT NULL,
    created_on TIMESTAMP NOT NULL,
    last_login TIMESTAMP
);

If I execute init.sh from inside the docker container, postgres populates properly.

How do I do the same thing from my Dockerfile/docker-compose?

My docker-compose looks like this:

version: "3.9"

services:
  postgres:
    build:
      docker/postgres
    ports:
      - "5432:5432"
    env_file:
      docker/postgres/.env
#    command:
#      - /bin/chmod +x /app/init.sh
#      - . /app/init.sh

My docker/postgres/Dockerfile looks like this:

FROM postgres:14.5-alpine

WORKDIR /app

COPY schemas.sql .
COPY init.sh .
COPY .env .

#RUN . init.sh

Attempting to run init.sh from commands in docker-compose (as above) results in:

/usr/local/bin/docker-entrypoint.sh: line 341: /bin/chmod +x /app/init.sh: No such file or directory

Attempting to run init.sh from the Dockerfile itself via RUN results in:

 => ERROR [6/6] RUN . init.sh                                                                                      0.2s
------
 > [6/6] RUN . init.sh:
#0 0.144 psql: error: connection to server on socket "/var/run/postgresql/.s.PGSQL.5432" failed: No such file or directory
#0 0.144        Is the server running locally and accepting connections on that socket?
------
failed to solve: executor failed running [/bin/sh -c . init.sh]: exit code: 2
`docker-compose` process finished with exit code 17

First, you should not be setting command. You should be placing your scripts into /docker-entrypoint-initdb.d as described in the "How to extend this image" section of the official image README. Second, you probably don't even need init.sh; you could just drop the .sql file into that directory. — larsks
– larsks, Commented Oct 14, 2022 at 15:44
@larsks yes, that does the trick :-). Post it as an answer so I can accept it — Joe Shmo
– Joe Shmo, Commented Oct 14, 2022 at 15:47

larsks · Accepted Answer · 2022-10-14 17:03:56Z

The official Postgres image can be extended by placing shell and/or sql scripts into /docker-entrypoint-initdb.d:

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

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.

You can build these scripts into a custom image, or you can mount them at runtime via bind mounts. For example, if you have your schemas.sql script in a local directory name docker/postgres/init, you could use the following docker-compose.yaml:

version: "3.9"

services:
  postgres:
    image: docker.io/postgres:14
    ports:
      - "5432:5432"
    env_file:
      docker/postgres/.env
    volumes:
      - ./docker/postgres/init:/docker-entrypoint-initdb.d

Collectives™ on Stack Overflow

Creating Postgres Table in Docker

1 Answer 1

Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Linked

Related