Data missing in PostgreSQL dump

Question

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.

• Luan Huynh: If your dump file is small, think you can restore and check directly in database.
• bma: 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.
• fin1te: Is there a command to use to list the contents as well? Thanks for the tip on the -d
• bma: 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
• jjanes: 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

Answer 1

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.

Cheers.

Answer 2

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.

• András Váczi: 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?
• Lohit Gupta: 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.
• András Váczi: 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.