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 ?

  • 5
    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"
    – user1822
    Sep 12, 2014 at 14:29

1 Answer 1


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.

  • 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, 2016 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. Nov 13, 2016 at 8:47
  • @CraigRinger is there any speed difference using psql vs using pg_dump for restore? Mar 14, 2017 at 14:45
  • 2
    @NikunjSardhara Not usually, but pg_restore supports parallel restore, and if you use that it'll be a LOT faster. Mar 15, 2017 at 0:18

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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