Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Easy way to view postgresql dump files?

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.

like image 501
at. Avatar asked Apr 25 '13 20:04

at.


People also ask

How do I access PostgreSQL data?

The default username for postgres is postgres. (If you are using Advanced Server it is enterprisedb.) On a Mac or Windows, you are able to connect to the default instance by simply hitting enter at the shell or command prompt when trying to run psql and keying in the password.

What is PostgreSQL dump file?

pg_dump is a backup tool for PostgreSQL databases. Even if the database is in use at the same time, it creates consistent backups. Other users are not prevented from accessing the database via pg_dump (readers or writers). pg dump only dumps one database at a time.


2 Answers

This was what I was looking for:

pg_restore db.bin > db.sql 

Thanks @andrewtweber

like image 170
jcuenod Avatar answered Sep 26 '22 17:09

jcuenod


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.

like image 28
Ihor Romanchenko Avatar answered Sep 25 '22 17:09

Ihor Romanchenko