Restoring specific postgres tables locally

While working on applications it is often the case that you need to restore a backup locally in order to test what you are working on. Or, perhaps, trying to investigate what is causing a bug that you just can’t replicate without some good (bad) data. In other cases, you might just need to run a simple query on a single table.

Pulling down a backup, restoring locally, and connecting to it generally does the trick. However, in the last scenario do you really need the entire backup to perform the query? Likely not.

At eXp, we have some fairly large applications and to restore an entire backup can take the better part of a working day (ouch). When just needing to perform a simple query, its incredibly beneficial to just restore the single table you are interested in — and it will save you a ton of time.

The area in which I am mostly focused on is in our Mendix application portfolio and connecting to a local postgres database is common practice. If you are unsure how to do that, Mendix has some decent documentation found here. I would suggest taking a look at that documentation if you are unfamiliar with the process.

Regardless, read below and follow the steps to restore just the table (or tables) you are interested in.

Note: There are several tools out there but in this post I am focusing on PGAdmin.

Ready a Database and PGAdmin

1. Create a new database in PGAdmin (in my example I have created a new database named “MyTestDatabase”. See the below image for reference)

New Database in PGAdmin

2. Open command prompt

3. Run the below command (Of course, you can set the password as you wish)

set "PGPASSWORD=postgres"

Create Command

Build the below command with the following steps

“C:\...\pg_restore.exe” -t [full table name] --host "localhost" --port "5432" --username "postgres" --no-password --dbname “[name of db from above]” --no-owner --no-privileges --verbose “D:\...\xyz.backup”

1. “C:\...\pg_restore.exe” Is the full path to your pg_restore.exe file. (This will be different for everyone) → It will be in the ‘pgAdmin” folder likely located in your Program Files directory. It will resemble this: C:\Program Files\pgAdmin 4\v6\runtime\pg_restore.exe
2. -t [full table name] “-t” is the flag to specify you wish to restore a specific table. In my case, I wanted to restore a ‘user’ table which is found in my ‘system’ domain, it looks like this -t system$user — If you would like to restore more than one table just add another -t flag and table name
3. --host "localhost" --port "5432" --username "postgres" --no-password These details may be different for you but generally remain the same.
4. --dbname “[name of db from above]” This tells the pg_restore.exe which database you wish to restore to. So in the example above, it would look like this: --dbname “MyTestDatabase”
5. --no-owner --no-privileges --verbose Shouldn’t really be a need to adjust these.
6. “D:\...\xyz.backup”This specifies the full path and backup file you would like to restore from.

Run Command

1. Add your command to command prompt and hit enter

Your command should look something like the following:

“C:\Program Files\pgAdmin 4\v6\runtime\pg_restore.exe” -t system$user --host "localhost" --port "5432" --username "postgres" --no-password --dbname “MyTestDatabase” --no-owner --no-privileges --verbose “D:\Users\mmcmullen\Downloads\myappbackupfile.backup”

Conclusion

That’s it. Super simple.

Go ahead and run a query on the table you just restored and pat yourself on the back for saving yourself potentially a ton of time.