postgresql,

How to restore an individual table in PostgreSQL

Feb 05, 2022 · 1 min read · Post a comment
How to restore an individual table in PostgreSQL

Backup and restore operations are convinient 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 wanna do in a production environment.
On a side note, follow our official channel on Telegram.