How to Restore a PostgreSQL Database in DataSpell Using Parallel pg_restore and Verify Integrity?

Answer the core question first

In DataSpell you can restore a custom‑format pg_dump archive with parallel workers by running pg_restore -j … from the built‑in terminal or database console. After the restore you verify that every object (tables, indexes, large objects) is present and that no data is missing by comparing a fresh dump of the restored database to the original and by running simple row‑count checks.

How to invoke pg_restore with –j in DataSpell

1. Open Terminal (View → Tool Windows → Terminal) or the Database console for the target server.
2. Make sure the PostgreSQL client tools are on your PATH (e.g., which pg_restore should print a location).
3. Create or drop the target database if you want a clean restore:

   psql -U <user> -c "DROP DATABASE IF EXISTS <target_db>; CREATE DATABASE <target_db>;"
   

4. Run the parallel restore:

   pg_restore -j 4 -C -d <target_db> <dump_file>
   

   • -C tells pg_restore to create the database (omit if you already created it).
   • -j 4 starts four parallel workers; adjust the number to match your CPU cores.
   • Check the exit status: echo $? should be 0.

Verify that all objects are present

1. List the objects in the original dump:

   pg_restore --list <original_dump> > orig_list.txt
   

2. List the objects in the restored dump (create one first):

   pg_dump -Fc -d <target_db> -f restored_dump.sql
   pg_restore --list restored_dump.sql > restored_list.txt
   

3. Compare the two lists:

   diff orig_list.txt restored_list.txt
   

   Any differences indicate missing or renamed objects.

Check data integrity

1. Run a quick checksum of the dump files (only verifies the dump, not the live data):

   md5sum <original_dump> restored_dump.sql
   

2. Execute simple row‑count queries on a representative set of tables in both databases and compare the results. For example:

   psql -U <user> -d <original_db> -c "SELECT COUNT(*) FROM my_table;"
   psql -U <user> -d <target_db> -c "SELECT COUNT(*) FROM my_table;"
   

3. Optionally, compare the total size of all tables:

   psql -U <user> -d <original_db> -c "SELECT SUM(pg_total_relation_size(oid)) FROM pg_class WHERE relkind='r';"
   psql -U <user> -d <target_db> -c "SELECT SUM(pg_total_relation_size(oid)) FROM pg_class WHERE relkind='r';"
   

What DataSpell features help?

• Terminal / Database console – run any shell command, including pg_restore.
• External Tools – you can create a custom tool that runs pg_restore -j … and bind it to a shortcut.
• Database view – after the restore, the Database tool window will show all schemas, tables, and indexes. Use the Data tab to open a table and visually confirm data.

Missing diagnostic detail that could change the recommendation

Do you need to restore into an existing database with pre‑existing schemas, or would you prefer to let pg_restore create a fresh database? If you choose the former, omit the -C flag and ensure the target schemas exist beforehand.