pg_dump: missing pg_catalog.pg_roles relation

329 views Asked by At

I had created a DB testDb1 on a postgres server. After adding some data to this DB, I created a dump of this data and restored that to a new DB testDb2. After that data was added to second DB for quite some time. Now, I want to dump data of testDb2 using following command:

pg_dump -U "postgres" --no-privileges -Fd -j 4 -f dump_20230102_db2 testDb2

But this gives me below error:

pg_dump: error: query failed: ERROR:  relation "pg_catalog.pg_roles" does not exist
LINE 1: ....tableoid, n.oid, n.nspname, (SELECT rolname FROM pg_catalog...
                                                             ^
pg_dump: error: query was: SELECT n.tableoid, n.oid, n.nspname, (SELECT rolname FROM pg_catalog.pg_roles WHERE oid = nspowner) AS rolname, (SELECT pg_catalog.array_agg(acl ORDER BY row_n) FROM (SELECT acl, row_n FROM pg_catalog.unnest(coalesce(n.nspacl,pg_catalog.acldefault('n',n.nspowner))) WITH ORDINALITY AS perm(acl,row_n) WHERE NOT EXISTS ( SELECT 1 FROM pg_catalog.unnest(coalesce(pip.initprivs,pg_catalog.acldefault('n',n.nspowner))) AS init(init_acl) WHERE acl = init_acl)) as foo) as nspacl, (SELECT pg_catalog.array_agg(acl ORDER BY row_n) FROM (SELECT acl, row_n FROM pg_catalog.unnest(coalesce(pip.initprivs,pg_catalog.acldefault('n',n.nspowner))) WITH ORDINALITY AS initp(acl,row_n) WHERE NOT EXISTS ( SELECT 1 FROM pg_catalog.unnest(coalesce(n.nspacl,pg_catalog.acldefault('n',n.nspowner))) AS permp(orig_acl) WHERE acl = orig_acl)) as foo) as rnspacl, NULL as initnspacl, NULL as initrnspacl FROM pg_namespace n LEFT JOIN pg_init_privs pip ON (n.oid = pip.objoid AND pip.classoid = 'pg_namespace'::regclass AND pip.objsubid = 0)

I checked the original DB testDb1, results are generated for above query but not for testDb2.

I also changed the owner of testDb2 to postgres but no luck there.

What can I do to restore or generate pg_catalog.pg_roles for testDb2?

Update:

Both DB are on same version 14.4

Output of \d *pg_roles* in testDb2 is:

testDb2-# \d *pg_roles*
Did not find any relation named "*pg_roles*".
1

There are 1 answers

0
Super Kai - Kazuya Ito On

You should restore a backup because your database is corrupted according to the answer to my question.

Actually, I got the same error when trying to show the definition of person table as shown below, then I asked the question, then reinstalling PostgreSQL solved the error in my case:

postgres=# \d person
ERROR:  relation "pg_catalog.pg_roles" does not exist
LINE 2: ...catalog.array_to_string(array(select rolname from pg_catalog...