Rillence
Login

← All bricks

PostgreSQL · Advanced

pg_dump and pg_restore

Learn the full backup and restore cycle with pg_dump and pg_restore on a real PostgreSQL stand — plain SQL dumps, custom-format archives, and selective table restores — so you know exactly what happens when you need to recover data.

Knowing how to dump a database is not the same as knowing how to restore it. This course covers both, on a real stand with 100 000 rows. We walk every format pg_dump can produce — plain SQL and custom binary — compare their sizes and contents, then restore each one and verify the row counts are right. We cover selective restore with -t, look at what pg_restore --list reveals before you pull the trigger, and finish with a clear picture of what plain SQL restore cannot do that the custom format can. You leave with a workflow you can run any time, not just a command you copy-pasted once.

What you'll build

  • Produce plain-SQL and custom-format dumps with pg_dump
  • Restore a full database from both dump formats
  • Use pg_restore --list to inspect an archive before restoring
  • Restore a single table with pg_restore -t
  • Understand what the FK constraint warning means in selective restore

Contents

  1. A database to back up
  2. Starting the stand
  3. Connect with psql
  4. The users table
  5. The orders table and its foreign key
  6. The products table
  7. Creating the tables
  8. Seeding users with a fixed random seed
  9. One hundred thousand orders
  10. A thousand products
  11. Loading the seed data
  12. Confirming what is in the database
  13. Keeping dump files out of git
  14. Plain SQL dump
  15. What is inside the plain dump
  16. Custom-format dump
  17. Inspecting the archive table of contents
  18. A query to confirm the table is gone
  19. Confirming the damage
  20. The verification query
  21. Verifying the plain restore
  22. Dropping products to test the custom restore
  23. Selective table restore with pg_restore
  24. Verifying the custom restore
  25. Preparing for the FK dependency exercise
  26. Selective restore when the referenced table is missing
  27. The data loaded despite the FK issue
  28. Logical vs physical backups
  29. When pg_dump is not enough
  30. A backup you have never restored is not a backup
  31. Choosing the right format for the job
  32. Dumping only the schema
  33. Clean restore: drop before recreate