Docker Tip #49: Importing a Database SQL File with Docker Compose
When dealing with databases such as PostgreSQL, MariaDB or MySQL, you've likely wanted to import a raw SQL file. Here's how.
I’m going to write this in the context of using PostgreSQL and psql but these
commands work just the same with MariaDB / MySQL and mysql. I’m also not
setting any usernames or passwords in the commands to keep it readable.
Importing a SQL file without Docker (works):
psql exampledatabase < exampledata.sql
That’s easy enough. You just let psql know which database you want to import
the data to and then redirect the file in through STDIN.
Importing a SQL file with Docker Compose (doesn’t work):
You would think it would be easy enough right? Just do:
docker-compose exec postgres psql exampledatabase < exampledata.sql
In theory that should work, but it won’t (at least not with Docker Compose 1.20).
You’ll end up with an error of ValueError: file descriptor cannot be a negative integer (-1).
For some reason Docker Compose doesn’t like doing this. Now, you could try
adding -T to exec to disable TTY, in which case it will import
without errors but there won’t be any data. That doesn’t really help us.
Importing a SQL file with Docker (works):
Since we can’t do it with Compose until that bug is fixed, we need to do it
with the raw docker command instead. Here’s how:
docker container exec -i $(docker-compose ps -q postgres) psql exampledatabase < exampledata.sql
You could simplify that by replacing $(docker-compose ps -q postgres) with
the name of the container if you know it beforehand, otherwise it will dynamically
grab it from whatever you have defined as the service name in your
docker-compose.yml.
Well done. Now you know how to restore a database from a SQL file!