Build postgres docker container with initial schema

Question

I'm looking to build dockerfiles that represent company databases that already exist. Similarly, I'd like create a docker file that starts by restoring a psql dump.

I have my psql_dump.sql in the . directory.

FROM postgres ADD . /init_data run "createdb" "--template=template0" "my_database" run  "psql" "-d" "my_database"  --command="create role my_admin superuser" run  "psql" "my_database" "<" "init_data/psql_dump.sql" 

I thought this would be good enough to do it. I'd like to avoid solutions that use a .sh script. Like this solution.

I use template0 since the psql documentation says you need the same users created that were in the original database, and you need to create the database with template0 before you restore.

However, it gives me an error:

createdb: could not connect to database template1: could not connect to server: No such file or directory         Is the server running locally and accepting 

I'm also using docker compose for the overall application, if solving this problem in docker-compose is better, I'd be happy to use the base psql image and use docker compose to do this.

Answer 1

According to the usage guide for the official PostreSQL Docker image, all you need is:

Dockerfile

FROM postgres ENV POSTGRES_DB my_database COPY psql_dump.sql /docker-entrypoint-initdb.d/ 

The POSTGRES_DB environment variable will instruct the container to create a my_database schema on first run.

And any .sql file found in the /docker-entrypoint-initdb.d/ of the container will be executed.

If you want to execute .sh scripts, you can also provide them in the /docker-entrypoint-initdb.d/ directory.