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*".
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: