Restore one single PostgreSQL table through Docker compose

Posted by ZedTuX 0n R00t on February 17, 2017

Given you just deleted some data in one of your tables and you’re looking for a way to restore it, without re-importing the entire database, especially when it has a size of ±150GB.

You’re using Docker and docker-compose in order to manage your apps.

Mount the dump

First thing, the dump should be accessible from your database container. Update your docker-compose.yml file in order to add the a volume of the folder where is stored your dump, and mount it to let say /db_dump/.

As you did persistence, using a volume, you can delete and re-create the database container :)

docker-compose rm database
docker-compose up -d database

Now you should be able to see your dump file in the container:

docker exec legalannouncements_database_1 ls /db_dump/

DROP the table

As the dump file contains the instruction to create the table, and as you anyway lost your data, you can safely drop the table:

Import the table

Now you just need to import the table:


You will be surprised by the speed of the import. First time I was thinking “Shit… not working” while actually it worked. :)

PG Error zero-length delimited identifier

Following the table restore, and due to the table drop, you could fall in the case where your (Ruby On Rails?) application fails with:

PG::Error: ERROR: zero-length delimited identifier at or near “”“”

This error is due to the fact that the re-created table, from the dump, didn’t set the table primary key.

Here is the command you want to run in order to set the id column as the primary one:

Do not forget to restart your server :)