0

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?
  • Did you run the command as root? Do you (or does your account) have sufficient permissions? – John aka hot2use Jan 20 at 12:57
  • @Johnakahot2use yes, it was run as root. And the DB user was the default postgres user with SUPERUSER privilege. – John Jan 20 at 13:02
0

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?

  • Thanks. Unfortunately the database does not exist anymore, that is why I was looking at the backup. And while the data was not frequently accessed, there was an application that used the data around the time the backups were made, so the rows must have existed somewhere. There are also all tables empty for this one schema, not just one, so it's unlikely to be data corruption. – John Jan 20 at 14:34
  • If the dump had been taken by a non-superuser, it might have been row level security that excluded the data. With a superuser I have no explanation other that the ones I gave in my answer. – Laurenz Albe Jan 20 at 14:39
0

Necesito hacerle una consulta, verifico o sabe aproximadamente cuanto pesaba esa tabla/base a respaldar? ya que debemos tener ese dato . Muchas veces cometemos el error de automatizar este procedimiento sin limpiar los espacios y como consecuencia .. el script comienza ( crea estructura), pero se cae a mitad de camino por no tener el espacio suficiente para realizar el volcado de los datos.

Otro punto importante es verificar si el usuario (linux) con el que estamos trabajando permite que nuestra base cree/escriba en la carpeta de destino del dump.

Son datos importantes que nos pueden ayudar a dilucidar lo que está viendo ahora.

Saludos

Erik Serrano

  • This is an English-language site, would you be able to translate your post? Especially given that the original question was in English. – mustaccio Jan 20 at 18:37

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.