Missing data for one schema in pg_dump I have a Postgres 9.6 database with several schemas, and a regular backup of this database is done with a command like

pg_dump -h localhost -d db -U postgres -Fc -f file.dump


When I restored a backup I got the unexpected result that for one of the schemas the contents were missing entirely, only the tables were there, but empty. This affects all tables in that schema. The other schemas contained all the expected data when restoring from this backup. 

To confirm that the data isn't in the backup I used the following command to create a readable text version of the binary database dump:

pg_restore mydb.dump &gt; mydb.sql


I looked for the entry that is supposed to fill in the rows and found something like this:

--
-- Data for Name: some_table; Type: TABLE DATA; Schema: schema1; Owner: postgres
--

COPY schema1.some_table (column1, column2, column3, column4) FROM stdin;
\.


There was no data inserted at all with the COPY command here, and this entry looks similar for every table in this particular schema. When I look at the other schemas, there are the individual rows to be inserted in the COPY command as I would expect.

The schemas all have the same tables, but there is one difference that is possibly connected to the source of the problem. The schema with the missing data in the backup is owned by postgres, while all other schemas are owned by other roles. Each schema is supposed to be owned by a specific role, one role per schema. That the problematic schema is owned by postgres means there was an earlier bug or issue that caused the wrong ownership, but my understanding is that as I'm making the backup as the postgres superuser, the wrong ownership alone can't explain the missing data in the backup.

I'm now looking to figure out what happened here, and why the data is missing. Right now I don't have any explanation of how just this specific data could be missing in the database dump, when the dump itself is successful. 


Is the way I'm transforming the binary dump valid, and do my observations on the plaintext dump mean that there is no data for this table in the dump?
Is there any way the ownership could affect the data contained in the dump when performing pg_dump as a role with SUPERUSER privilege?
What are the possible explanations for the missing contents of only one specific schema when dumping a whole database?
If you don't have the required permissions, pg_dump will give you an error message rather than an empty COPY statement. Moreover, permissions do not apply to superusers.

You should verify what the database user used by pg_dump sees when selecting from the original table.

The only explanations I can think of are:


Somebody deleted all rows from the table.
You have experienced data corruption that somehow rendered the table's file empty of zeroed out.


Try the following to find the file:

SELECT oid FROM pg_database
WHERE datname = 'db';

SELECT relfilenode FROM pg_class
WHERE oid = 'schema1.some_table'::regclass;


If the numbers returned are 1234 and 5678, examine the file base/1234/5678 in the data directory: How big is it, what does it contain?