Import SQL file into Docker Compose Container

Tags: docker postgresql docker-compose

Get in there!

It took me a while to figure out how to properly import a .sql dump file into a docker container I had started with docker-compose. The recommended way was to import the data on creation of the container by adding the dump file to theĀ /docker-entrypoint-initdb.d directory. I wanted to avoid this if possible because the dump file is a backup of a production database but the docker-compose file is shared in a public repository. I just didn't like the idea of referencing the database file in a public repository; I've made the mistake of pushing sensitive info public repositories before and it is such a pain to clean up those mistakes afterwards. It seemed safer and more logical to do the import after the container had been initialized, especially since the data is purely for testing, and isn't essential to the function of the application.

Unfortunately, this wasn't as easy as it seemed like it should be.

Initially I tried something like this:

docker-compose exec -u postgres service_name psql db_name < /path/to/dump/backup_file.sql

But I would run into the following error:

gTraceback (most recent call last):
  File "bin/docker-compose", line 6, in 
  File "compose/cli/main.py", line 71, in main
  File "compose/cli/main.py", line 124, in perform_command
  File "compose/cli/main.py", line 467, in exec_command
  File "site-packages/dockerpty/pty.py", line 338, in start
  File "site-packages/dockerpty/io.py", line 32, in set_blocking
ValueError: file descriptor cannot be a negative integer (-1)
Failed to execute script docker-compose

This error is well discussed, and provides several solutions depending on what you're trying to do. Several users seem only to need to pass the -T parameter to their docker-compose exec command. For me, the command seems to hang. By checking the logs, I can see that it does actually try to execute, but fails with many errors complaining how relations don't exist and whatnot.

The key to salvation is somewhat buried the comments of the issue discussion. It relies on docker exec, rather than docker-compose exec and keeps the stdin open with the -i flag. Or something like that. The important thing is that it worked.

docker exec -u postgres -i $(docker-compose ps -q service_name) psql db_name < /path/to/dump/backup_file.sql