Thanks Tom for the quick response and it was really helpful.
On Wed, Jun 29, 2022 at 11:31 AM Tom Lane <tgl@sss.pgh.pa.us> wrote:
Praneel Devisetty <devisettypraneel@gmail.com> writes: > pg_catalog schema has been accidentally renamed in a PG database.
"Accidentally"? Anyone who was doing that kind of fooling around in a valuable database really ought not be trusted with admin privileges.
> Renaming it back to pg_catalog is failing with error: > ERROR: unacceptable schema name "pg_catalog" > DETAIL: The prefix "pg_" is reserved for system schemas. > Is there any way to bypass this?
regression=# create database breakme; CREATE DATABASE regression=# \c breakme You are now connected to database "breakme" as user "postgres". breakme=# alter schema pg_catalog rename to broken; ALTER SCHEMA breakme=# \d ERROR: relation "pg_catalog.pg_class" does not exist LINE 5: FROM pg_catalog.pg_class c ^
... oops, it's pretty broken alright ...
breakme=# alter schema broken rename to pg_catalog; ERROR: unacceptable schema name "pg_catalog" DETAIL: The prefix "pg_" is reserved for system schemas.
... as you said. But:
breakme=# set allow_system_table_mods = 1; SET breakme=# alter schema broken rename to pg_catalog; ALTER SCHEMA breakme=# \d Did not find any relations.
Needless to say, running with allow_system_table_mods on opens the door to even more dangerous changes, some of which you will absolutely not be able to recover from. (For example, "delete from pg_class" is about as bad as "rm -rf /" ...)
Again, whoever broke this for you needs a bit of re-education before they are trusted with superuser privileges in any DB you care about.