Restore one single PostgreSQL table through Docker compose

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 :)