Is there a way to convert a Postgres dump file to a text file?

I'm experiencing issues getting the dump to the proper file location of I would just restore from the dump file and then export to text.

Here's what lastest.dump looks like:

enter image description here

  • How are you creating the dump? The output of pg_dump is already text. – Greg Hewgill Aug 29 '13 at 1:28
  • I'm not sure what command he used to create the dump, but it is a *.dump file. – Drew Aug 29 '13 at 1:33
  • 1
    Is it a binary or SQL dump? If it is a binary dump then the easiest thing would probably be to set up PostgreSQL, load the dump, and then transfer the data directly from PostgreSQL to SQLite. If it is an SQL dump then you might be able to feed it straight into SQLite with a little bit of editing. – mu is too short Aug 29 '13 at 1:47
  • 1
    @GregHewgill Depends on what you mean by "binary dump". If it's pg_dump -Fc output, which is likely, then it'll restore to most versions just fine. If it's a copy of the data directory as taken by pg_basebackup then you'd be right. – Craig Ringer Aug 29 '13 at 2:17
  • 1
    @Drew The output of file thefile.dump would be informative. Does it print something like thefile.dump: PostgreSQL custom database dump - v1.12-0 ? If so, you must use pg_restore to load the dump into a PostgreSQL instance or convert it to a plain SQL dump. – Craig Ringer Aug 29 '13 at 2:18

This is a dump file produced by pg_dump -Fc.

To create a text file containing SQL commands from it, use pg_restore.

The basic syntax is pg_restore file.dump > file.sql, that is, you don't specify a target database (notice the missing-d option).

  • Should the dump include the data too? – Drew Aug 30 '13 at 1:42
  • @Drew: the dump may or may not contain the data depending on the options it was made with. Run the above command and see. – Daniel Vérité Aug 30 '13 at 10:49

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.