75

I do want to search for some data inside a database dump but these dumps are using the binary-compressed format (PGDMP header).

How can I convert these to SQL without restoring them?

sorin
  • 161,544
  • 178
  • 535
  • 806

3 Answers3

126

pg_restore, when run without a database name, outputs a text dump to stdout; you can send that elsewhere with -f or with I/O redirection.

pg_restore -f mydatabase.sql mydatabase.dump

Note that you must ensure there's no PGDATABASE environment variable set, or it'll try to connect to that database.

Craig Ringer
  • 307,061
  • 76
  • 688
  • 778
  • 1
    When I try to do this, I get `pg_restore: [archiver] input file does not appear to be a valid archive`. I can assure you that the dumps were made using the same version of postgresql 8.4.17. – sorin Jan 29 '14 at 14:41
  • 1
    Sorry, I just discovered that the guy that made the backups, gzipped them after using -Fc ?! ... and mc view was showing me the uncompressed content on file-view... – sorin Jan 29 '14 at 14:46
  • This was very helpful! – Stian Håklev Oct 14 '16 at 09:33
  • @sorin Do you solved the issue? I have the same issue now. – kripindas Jun 25 '20 at 15:19
  • 1
    @kripindas They posted in 2014, wouldn't bet on them remembering or seeing this. Post a new question. But first use `file the-archive-file-name` to find out what kind of file it is. – Craig Ringer Jun 26 '20 at 02:14
  • This worked for me. My file was a .dump file – Promise Preston May 21 '22 at 08:02
26

The fastest method that I've used was:

pg_restore -f mybinaryfile.backup > mysqlfile.sql

No special flags, since pg_restore just spits it out to stdout.

knownasilya
  • 5,998
  • 4
  • 36
  • 59
  • 7
    I had to add `-f` like this: `pg_restore -f mybinaryfile.backup > mysqlfile.sql` – Alex Mar 23 '20 at 20:07
  • I had to add `-f`, and i only wanted to restore one table, so i ended up with this: `pg_restore -f - --table=users my-backup.dump > users.sql` – psmith Mar 25 '22 at 04:52
2

Note that if you run multiple clusters, the restore command may not like the default version...

pg_restore: [archiver] unsupported version (1.12) in file header

In that case you have to specify the version, host and port as in:

pg_restore --cluster 9.1/localhost:5433 -f db.sql db.pgsql

(note that the host:port info is ignored with the -f option.)

The port (5433) can be determined using the pgsql command as in:

pgsql --port 5433 template1

When pgsql connects, it writes a comment such as:

psql (9.3.6, server 9.1.13)

This means you are running pgsql 9.3.6 and that port 5433 references server 9.1.13.

If you are not sure which ports are currently used, you may use the netstat command as in:

sudo netstat -a64np | grep LISTEN | grep postgres

The sudo is required for the -p option which prints the process name. That gives you a list of ports (usually TCP and UDP ports).

Finally, on a Debian/Ubuntu system, you can get a list of installed clusters with the dpkg -l command as in:

dpkg -l '*postgres*'

The list of entries that start with 'ii' (left most column) are currently installed. You, of course, have similar commands for other Unices to help you determine installed versions.

Alexis Wilke
  • 19,179
  • 10
  • 84
  • 156