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
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
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 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
Well done. Now you know how to restore a database from a SQL file!