I'm trying to verify that I am actually getting data in my pg_dump from a PostgreSQL 9.3 server. I'm specifically running the following to get a dump of my Foreman database:

pg_dump -Fc foreman -U foreman -h foreman-db -p 5432 > foreman.sql

I am reading the dump using pg_restore -l foreman.sql but when I search for known fqdns of hosts that should be in the hosts table, I can not find them.

I'm no DBA, but it seems I'm doing something very wrong or I don't know how to search well enough yet to find data within pg_dump's.

Any assistance is much appreciated.

  • If your dump file is small, think you can restore and check directly in database. – Luan Huynh Aug 2 '17 at 15:57
  • 1
    pg_restore -l only lists the DDL, not the contents. Also, you might want to prefix that initial "foreman" db name with -d, to denote that it's the db. – bma Aug 2 '17 at 17:22
  • Is there a command to use to list the contents as well? Thanks for the tip on the -d – fin1te Aug 2 '17 at 21:24
  • As far as I know you can easily see the contents of the tables if you use -Fc. If you export to plaintext (the default, or explicitly via -Fp) you'll be able to view the contents with your text editor of choice. If you are by chance looking only for a subset of the data, a sample in effect, have a look at github.com/mla/pg_sample – bma Aug 2 '17 at 21:48
  • 2
    Seeing the data is the default for pg_restore when not replaying into a database. Just remove the -l, no need to add anything. BTW, it is not customary to use the .sql suffix for a custom dump formatted file (the output format of pg_dump -Fc). The usual suffix for a custom dump file is .dmp – jjanes Aug 2 '17 at 21:57

This turned out to be my own fault and not making sure that the Foreman host had a postgresql93 client installed so that the rake task that imports the database could actually do the pg_restore on the valid dump.

I was able to use pg_dump -Fp to get the plain text and validate that my data was in there. Thank you everyone for your help. Chalk this one up to a newbie trying to learn Postgres.



Use this command to create the dump:

pg_dump -Fp -U foreman -h foreman-db -p 5432 foreman > foreman.sql

It will create the dump in plain text format. You can then directly use psql to execute the sql file to restore the dump. No need to use pg_restore.

Also as you are taking a full database dump, i would suggest to first create a database structure dump using pg_dumpall and restore it first, before restoring the data.

  • Why would the latter make sense? Also, pg_dumpall takes a dump of every DB that there is (plus the global objects) - how do you know they want this? – dezso Aug 3 '17 at 7:32
  • I am assuming the problem here is because of pg_restore. So to avoid pg_restore, take a text dump and restore directly. Also you have an option to take structure dump of a single database using pg_dumpall. – Lohit Gupta Aug 3 '17 at 9:12
  • postgresql.org/docs/9.6/static/app-pg-dumpall.html you cannot dump a single DB using pg_dumpall. I think you mean pg_dump instead. – dezso Aug 3 '17 at 9:14

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.