42

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 ?

Marcus Junius Brutus
  • 3,309
  • 7
  • 29
  • 44
  • 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**" –  Sep 12 '14 at 14:29

1 Answers1

46

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.

Craig Ringer
  • 56,343
  • 5
  • 158
  • 190
  • 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 08: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 00:18