Обсуждение: Renamed pg_catalog schema
Hi ,
pg_catalog schema has been accidentally renamed in a PG database.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.
DETAIL: The prefix "pg_" is reserved for system schemas.
Is there any way to bypass this?
pg_dump/restore are failing as pg_catalog could not be found.Any other way to recover data?
Best regards
Praneel
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. regards, tom lane
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.
regards, tom lane