Build postgres docker container with initial schema
Solution 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.
Solution 2
As said in the comments, @Thomasleveil answer is great and simple if your schema recreation is fast. But in my case it's slow, and I wanted to use docker volumes, so here is what I did
- First use docker image as in @Thomasleveil answer to create a container with postgres with all the schema initialization
Dockerfile:
FROM postgres
WORKDIR /docker-entrypoint-initdb.d
ADD psql_dump.sql /docker-entrypoint-initdb.d
EXPOSE 5432
then run it and create new local dir which contains the postgres data after its populated from the “psql_dump.sql” file:
docker cp mypg:/var/lib/postgresql/data ./postgres-data
Copy the data to a temp data folder, and start a new postgres docker-compose container whose volume is at the new temp data folder:
startPostgres.sh:
rm -r ./temp-postgres-data/data
mkdir -p ./temp-postgres-data/data
cp -r ./postgres-data/data ./temp-postgres-data/
docker-compose -p mini-postgres-project up
and the docker-compose.yml file is:
version: '3'
services:
postgres:
container_name: mini-postgres
image: postgres:9.5
ports:
- "5432:5432"
volumes:
- ./temp-postgres-data/data:/var/lib/postgresql/data
Now you can run steps #1 and #2 on a new machine or if your psql_dump.sql changes. And each time you want a new clean (but already initialized) db, you can only run startPostgres.sh from step #3. And it still uses docker volumes.
Related videos on Youtube
Jono
Owner of Philia Software Consulting and co-founder of Socialveil.io - simplified social media management for normal people. Need remote help with rapid (greenfield) product development or micro-services strategy reach out.
Updated on July 09, 2022Comments
-
Jono almost 2 years
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.
-
damoiser over 7 yearsPossible duplicate of Starting and populating a Postgres container in Docker
-
-
Jono over 8 yearsAwesome, thanks! I also wasn't realizing that I needed to enter psql with
psql --username=my_user --dbname=my_database
that made it work the way I expected. -
Nick W. over 6 yearsThe subject asks for "Build postgres docker container with initial schema". But what you suggest creates the schema on startup, rather than building a container that already has that schema. If the schema takes 5 minutes to create, that's a 5 minute penalty each time it has to create a volume, rather than having it inside the actual container.
-
Thomasleveil over 6 yearsHaving your data inside the image (so you can have it at container creation) is a bad idea since the docker image union file system is slow. Your data should be on a volume, thus out of the container filesystem / image filesystem
-
yishaiz over 6 years@Thomasleveil you can do it in 3 steps: first create a container as you did, then copy its data to a local host directory, and then each time you want a fresh PostreSQL, start a PostreSQL container which it's volume is working with a copy of this local host directory.
-
Lee Benson over 5 years
ADD psql_dump.sql /docker-entrypoint-initdb.d
can be shortened toADD psql_dump.sql .
, since you're already in that WORKDIR -
Juan Carrey over 4 yearsWhy not just using a volume to add the sql to docker-entrypoint-initdb.d ?