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 ?

Generally, I've done this by writing a bash script that does a docker-compose up -d, then a docker-compose exec db_service_name with the command to execute the SQL dump. — Catherine Cai
– Catherine Cai, Commented Apr 21, 2016 at 23:16

jood · Accepted Answer · 2016-04-22 06:14:40Z

80

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!

edited Apr 22, 2016 at 6:14

answered Apr 22, 2016 at 0:41

jood

2,3973 gold badges23 silver badges32 bronze badges

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

4 Comments

pmsoltani Over a year ago

The documentation indicates can that *.sh scripts can also be used, which provides much wider flexibility. I've used this to pg_restore a .dump file.

JPG Over a year ago

I am trying this approach and I'm getting the following: postgres | /usr/local/bin/docker-entrypoint.sh: running /docker-entrypoint-initdb.d/dump-202105231637-1.sql postgres | The input is a PostgreSQL custom-format dump. postgres | Use the pg_restore command-line client to restore this dump to a database. any ideas ?

David Wilton Over a year ago

I also got "The input is a PostgreSQL custom-format dump. Use the pg_restore command-line client to restore this dump to a database." so not working for me

Rafs Over a year ago

@pmsoltani that is exactly what I am after; custom binary dumps are more efficient and flexible and I am keen on using them, so an .sh script to deal with them is much better than falling back to plain dumps, plus the restore can be done in parallel.

emilaz · Accepted Answer · 2022-01-21 15:07:33Z

33

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.

edited Jan 21, 2022 at 15:07

answered Dec 20, 2021 at 12:13

emilaz

2,1823 gold badges24 silver badges47 bronze badges

3 Comments

Pexers Over a year ago

The problem that I'm experiencing with this solution is that if the 'dump.sql' does't exist on the host machine, Docker will create an empty 'dump.sql' FOLDER inside the container for some reason.. causing the postgres container to fail.

Shashan Sooriyahetti Over a year ago

Life saver +1 love ya

Ojasv singh Feb 14 at 12:00

The host path is wrt location of docker file. If the file does not exist at host, it will create empty folder in the container

Valentin Kantor · Accepted Answer · 2018-06-03 04:24:01Z

18

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

edited Jun 3, 2018 at 4:24

answered Jun 3, 2018 at 3:50

Valentin Kantor

1,8742 gold badges24 silver badges31 bronze badges

4 Comments

Robert Columbia Over a year ago

While this code may answer the question, it is better to explain how to solve the problem and provide the code as an example or reference. Code-only answers can be confusing and lack context.

Valentin Kantor Over a year ago

it solves importing dump without tweaking dockerfile like in accepted answer.. also what do i need to explain in this piece: psql -U postgres my_db_name < dump.sql ? if you having troubles in this level, may be it's better to read the basics

Phitherek_ Over a year ago

This worked for me, but needs -i after exec so it is sudo docker exec -i postgres psql -U postgres my_db_name < dump.sql

Ezekiel Over a year ago

Also, exec requires (I believe?) that the Docker system is already booted, which may not be compatible with importing a full database dump.

GabLeRoux · Accepted Answer · 2021-07-02 18:57:05Z

5

After the docker-compose up, do docker ps it will give you a list of active docker containers. From that, you can get the container ID.

Then,

docker exec -i ${CONTAINER_ID} psql -U ${USER} < ${SQL_FILE}

edited Jul 2, 2021 at 18:57

GabLeRoux

18.2k17 gold badges67 silver badges83 bronze badges

answered Dec 13, 2019 at 7:14

Prashantkumar K B

2013 silver badges13 bronze badges

Comments

GabLeRoux · Accepted Answer · 2021-07-02 18:59:39Z

5

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}

edited Jul 2, 2021 at 18:59

GabLeRoux

18.2k17 gold badges67 silver badges83 bronze badges

answered Apr 7, 2020 at 13:27

porto

5955 silver badges7 bronze badges

Comments

David Wilton · Accepted Answer · 2022-12-05 00:14:43Z

4

In order to restore from I dump I use an sh to restore the database.

If you use a dump with docker-entrypoint-initdb.d it gives the error "The input is a PostgreSQL custom-format dump. Use the pg_restore command-line client to restore this dump to a database."

docker-compose.yaml

version: "3.9"

services:
  db:
    container_name: postgis_my_db_name
    image: postgis/postgis:14-3.3
    ports:
      - "5430:5432"
    # restart: always
    volumes:
    - ./my_db_name.sql:/my_db_name.sql
    - ./restore.sh:/docker-entrypoint-initdb.d/restore.sh
    environment:
      POSTGRES_USER: postgres
      POSTGRES_PASSWORD: my_password
      POSTGRES_DB: my_db_name

restore.sh

 pg_restore -d my_db_name my_db_name.sql

edited Dec 5, 2022 at 0:14

answered Dec 4, 2022 at 23:50

David Wilton

3942 silver badges11 bronze badges

1 Comment

Stavros Koureas Over a year ago

I am getting pg_restore: error: connection to server on socket "/var/run/postgresql/.s.PGSQL.5432" failed: FATAL: database "<my_database>" does not exist custompostgres exited with code 1 dependency failed to start: container for service "dataspace_postgres" exited (1)

Theophile · Accepted Answer · 2023-01-31 18:49:11Z

3

You can also do it without a dockerfile :

# start first container
docker compose start $db_container

# dump intial database
docker compose exec db pg_dump -U $user -Fc $database > $dump_file

# start container db
docker compose start $db_container

# get container id 
docker ps

# copy to container
docker cp $dump_file $container_id:/var

# delete database container / Can't use
docker compose exec $db_container dropdb -U $user $database

# user pg_restore
docker compose exec $db_container pg_restore -U $user -C -d postgres /var/$dump_file

answered Jan 31, 2023 at 18:49

Theophile

3301 gold badge3 silver badges10 bronze badges

Comments

GabLeRoux · Accepted Answer · 2021-07-02 19:00:43Z

1

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}"

edited Jul 2, 2021 at 19:00

GabLeRoux

18.2k17 gold badges67 silver badges83 bronze badges

answered Oct 25, 2018 at 12:59

Mcmil

91711 silver badges26 bronze badges

Collectives™ on Stack Overflow

Load Postgres dump after docker-compose up

8 Answers 8

4 Comments

3 Comments

4 Comments

Comments

Comments

1 Comment

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

8 Answers 8

4 Comments

3 Comments

4 Comments

Comments

Comments

1 Comment

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related