-1

I have three tables 'xdb.resources', 'xdb.resources_ref', 'xdb.resources_tmax'. All three belong to the same user and same schema. I try do export them using the following command

pg_dump -U xdb -t 'xdb.resources*'

Unfortunately, only two of them are exported ('xdb.resources_ref', 'xdb.resources_tmax'). The application does not see 'xdb.resources'

I do not see anything that can prevent 'xdb.resources' from export.

Please advise.

GMichael
  • 2,726
  • 1
  • 20
  • 30
  • Do you see the three tables in `SELECT '"' || relname || '"' FROM pg_class WHERE relnamespace = 'xdb'::regnamespace AND relname LIKE 'resources%';`? – Laurenz Albe Jan 29 '19 at 12:53
  • Yes. I see all of them. – GMichael Jan 29 '19 at 12:58
  • Then the `pg_dump` should work. I see that you didn't specify a database name, so that the database will default to `xdb` (unless you have `PGDATABASE` set); is that intentional? Does the user `xdb` have the required permissions? – Laurenz Albe Jan 29 '19 at 13:40
  • I did not specify the DB name here, in the question. I connect providing all the parameters: host, port, DB name, user name. The user is the owner of the tables. It has all the permissions. The application works. Just not for all the tables. It does not work for the only one that I need. – GMichael Jan 29 '19 at 14:51
  • Unless you are committing a trivial error, like connecting to the wrong database, this would mean data corruption. Does the behavior or the result to my query above change if you `REINDEX TABLE pg_class;` as superuser? – Laurenz Albe Jan 29 '19 at 14:57
  • I cannot connect as a superuser. I am a software developer. Not a DBA. I connect to the DB with psql and with pg_dump using the same connection string. The table is full with data. I doubt there is data corruption. – GMichael Jan 29 '19 at 15:02
  • The `REINDEX` would not hurt; perhaps you can ask your DBA to do it. If you are sure that there is no data corruption, it must be a trivial pilot error. – Laurenz Albe Jan 29 '19 at 15:48

1 Answers1

0

It was not a dump problem. The table is partitioned. Since the table itself does not contain data, it is not dumped. The real data is located inside the partition called xdb.resources_tmax which is exported.

The only problem is that this behavior is not documented and it took a long time to understand what happened.

GMichael
  • 2,726
  • 1
  • 20
  • 30