Easy way to view postgresql dump files?

Question

I have a ton of postgresql dump files I need to peruse through for data. Do I have to install Postgresql and "recover" each one of them into new databases one by one? Or I'm hoping there's a postgresql client that can simply open them up and I can peek at the data, maybe even run a simple SQL query?

The dump files are all from a Postgresql v9.1.9 server.

Or maybe there's a tool that can easily make a database "connection" to the dump files?

UPDATE: These are not text files. They are binary. They come from Heroku's backup mechanism, this is what Heroku says about how they create their backups:

PG Backups uses the native pg_dump PostgreSQL tool to create its backup files, making it trivial to export to other PostgreSQL installations.

Megathere

how were the dumps made? i.e. ... do you have the exact command used to create them? – Womanize

Dumps were made from Heroku's backup mechanism – Megathere

Answer 1

Try opening the files with text editor - the default dump format is plain text.

If the dump is not plain text - try using pg_restore -l your_db_dump.file command. It will list all objects in the database dump (like tables, indexes ...).

Another possible way (may not work, haven't tried it) is to grep through the output of pg_restore your_db_dump.file command. If I understood correctly the manual - the output of pg_restore is just a sequence of SQL queries, that will rebuild the db.

Shiism

is pg_restore a command that's installed when installing the Postgresql server? Or can I easily grab that command from somewhere? – Megathere

pg_restore -l only gives you the tables and other metadata, not the actual data. Your last suggestion worked perfect for me, however. – Megathere

Answer 2

This was what I was looking for:

pg_restore db.bin > db.sql

Thanks @andrewtweber

Bamberger

The dump file might be large. If that is the case, I suggest you to try pg_restore db.bin | less, if you only want to take a look or execute pg_restore db.bin | psql dbname > log 2> error if you want to restore it. – Bird

Now what? pg_restore: [archiver] unsupported version (1.13) in file header – Selfinterest

@Selfinterest that means that your pg_restore version is too low for the backup you're trying to restore. – Sulfonation

With Postgres 13, I had to do pg_restore db.bin -f db.sql instead, see also @AlexDev's answer – Addend

Answer 3

In newer versions you need to specify the -f flag with a filename or '-' for stdout

pg_restore -f - dump_file.bin

Cerberus

Thank you! I was having trouble with the above solutions. – Eulogize

So in other words in order to generate a file with raw sql pg_restore dump_file.bin -f dump_file.sql Thank you for this – Insoluble

the syntax is incorrect, it should be: pg_restore -f extracted.sql dump_file.bin options have to go before specifying dump file – Koonce

@Mihailo, we need your comment higher up. I hope the moderators edit this answer. – Soy

@Koonce I updated the answer according to the comments, although I didn't test it – Cerberus

@Koonce In my version of pg_restore the options can also come after. – Triennium

Answer 4

I had this same problem and I ended up doing this:

1. Install Postgresql and PGAdmin3.
2. Open PGAdmin3 and create a database.
3. Right click the db and click restore.
4. Ignore file type.
5. Select the database dump file from Heroku.
6. Click Restore.

Leesaleese

Answer 5

pg_restore -f - db.bin > db.sql

Crank

-f - ... > foo is, more simply, -f foo .... See AlexDev's answer. – Caron

Answer 6

If you use pgAdmin on Windows, can just backup the file as plain text, there is one option when you do backup instead of pg_dump in command line prompt.

Causey

Answer 7

Dump files are usually text file, if Not compressed, and you can open them with a text editor. Inside you will find all the queries that allow the reconstruction of the database ...

Perales

These dumps are not text files, they're binary – Megathere