Load Postgres dump after docker-compose up

Question

I have a dump.sql file that I would like to load with docker-compose.

docker-compose.yml:

services:
  postgres:
    environment:
      POSTGRES_DB: my_db_name
      POSTGRES_USER: my_name
      POSTGRES_PASSWORD: my_password
    build:
      context: .
      dockerfile: ./devops/db/Dockerfile.db

My Dockerfile.db is really simple at the moment:

FROM postgres
MAINTAINER me <[email protected]>

COPY ./devops/db ./devops/db
WORKDIR ./devops/db

I would like to run a command like psql my_db_name < dump.sql at some point. If I run a script like this from the Dockerfile.db, the issue is that the script is run after build but before docker-compose up, and the database is not running yet.

Any idea how to do this ?

Solution 1

Reading https://hub.docker.com/_/postgres/, the section 'Extend this image' explains that any .sql in /docker-entrypoint-initdb.d will be executed after build.

I just needed to change my Dockerfile.db to:

FROM postgres

ADD ./devops/db/dummy_dump.sql /docker-entrypoint-initdb.d

And it works!

Solution 2

sudo docker exec postgres psql -U postgres my_db_name < dump.sql

Solution 3

Another option that doesn't require a Dockerfile would be to mount your sql file into the docker-entrypoint-initdb.d folder using the volumes attribute of docker-compose. The official postgres image https://hub.docker.com/_/postgres/ will import and execute all SQL files placed in that folder. So something like

services:
  postgres:
    environment:
      POSTGRES_DB: my_db_name
      POSTGRES_USER: my_name
      POSTGRES_PASSWORD: my_password
  volumes:
    - ./devops/db/dummy_dump.sql:/docker-entrypoint-initdb.d/dummy_dump.sql

This will automatically populate the specified POSTGRES_DB for you.

Solution 4

You can use pg_restore inside the container:

cat ${BACKUP_SQL_File} | docker exec -i ${CONTAINER_NAME} pg_restore \
    --verbose \
    --clean \
    --no-acl \
    --no-owner \
    -U ${USER} \
    -d ${DATABASE}

Solution 5

CONTAINER_NAME="postgres"
DB_USER=postgres
LOCAL_DUMP_PATH="..."
docker run --name "${CONTAINER_NAME}" postgres
docker exec -i "${CONTAINER_NAME}" psql -U "${DB_USER}" < "${LOCAL_DUMP_PATH}"

View more solutions