16

I am dumping my Postgres database using pg_dump (in plain-text format) and then restore it simply by using psql (with the -f option).

Which begs the question: am I missing anything by not using pg_restore which seems like a specialized restore tool (compared to the generic psql)?

I can control options like disabling of triggers and such by using pg_dump parameters. What, then, is pg_restore used for? non-plain-text dump formats ?

|improve this question
  • 4
    From the manual: "pg_restore is a utility for restoring a PostgreSQL database from an archive created by pg_dump in one of the non-plain-text formats" – a_horse_with_no_name Sep 12 '14 at 14:29
19

If you created an SQL-format dump, all you can use is psql.

If you created a custom-format (pg_dump -Fc) or directory-format (pg_dump -Fd) dump, you can and must use pg_restore.

Custom and directory format dumps offer a lot of advantages over plain SQL script dumps, and I use them exclusively. You can selectively restore only some tables/schema, can choose whether to include only schema, only data, or both at restore time, etc. Lots of the options you have to specify at pg_dump time with SQL-format dumps can be chosen at restore-time if you use a custom-format dump and pg_restore.

If it weren't for backward compatibility I'm sure the default for pg_dump would be -Fc (custom) format.

You can't convert an SQL-format dump into custom-format or directory-format without restoring it to a PostgreSQL database and then dumping the restored DB.

|improve this answer
  • Is it also possible to batch rename functions and do data manipulation like you could in a sql file with a good regexp? – velop Nov 12 '16 at 17:10
  • 1
    No, but you can pg_restore without a dbname argument to turn it into a SQL-format dump if you need one. You cannot do the reverse. – Craig Ringer Nov 13 '16 at 8:47
  • @CraigRinger is there any speed difference using psql vs using pg_dump for restore? – Nikunj Sardhara Mar 14 '17 at 14:45
  • 2
    @NikunjSardhara Not usually, but pg_restore supports parallel restore, and if you use that it'll be a LOT faster. – Craig Ringer Mar 15 '17 at 0:18

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.