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 > 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?