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 :)
1
2
docker-compose rm database
docker-compose up -d database
Now you should be able to see your dump file in the container:
1
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:
Done.
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:
1
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 :)
