Is it possible to rebuild pg_depend?

Question

I have a PostgreSQL 9.4 database affected by the following bug in BDR:

https://github.com/2ndQuadrant/bdr/issues/309

In a nutshell, that bug in BDR resulted in missing dependencies in the pg_depend system catalog. Now when I use pg_dump, objects are dumped out of order and the dump can't be used without manual editing.

Is there a way to make PostgreSQL rebuild the dependencies in pg_depend without rebuilding the database from scratch?

Answer 1

No, because that information is not redundant (that would be a problem).

Manually messing with pg_depend is likely to make things worse.

Your best bet is to create a schema-only dump (pg_dump --section=pre-data) and massage that manually until you can load it into a new database (some dependencies will still be lost, e.g. columns owning a sequence).

Once you have succeeded with that, use ALTER statements to adjust further dependencies you notice are missing.

Once you are happy with the result, dump the rest of the database (pg_dump --section=data and pg_dump --section=post-data) and restore them into the database in this order.

I'm afraid you cannot do better than that.