Backup and restore operations are convenient if we are working with small and medium-sized data. But, what happens when we need to restore a single table from a large database?! Most probably there will be an impact on the recovery time objective (RTO). More about RPO vs RTO. Let’s go through each step on how to restore a single table from a PostgreSQL database.
Prerequisites
- PostgreSQL
Solution
Step 1. Create a full DB backup.
pg_dump -U <username> -Fc <db_name> > <db_name>.dump
Step 2. Restore schema-only (not the data yet).
pg_restore -U <username> --schema-only -d <db_name> <dir_path_of_db_dump>.dump
Step 3. Restore a single table data.
pg_restore -U <username> --data-only -d <db_name> -t <table_name> <dir_path_of_db_dump>.dump
Conclusion
Obviously, accidents happen from time to time, whatever the issue is, data corruption, data loss, data breach or even malware attacks, however restoring a whole database or even a single table is the last thing you might want to do in a production environment.
On a side note, follow our official channel on Telegram.