Accidentally dropped information_schema postgres

Question

I accidentally ran and committed drop schema information_schema cascade on postgres 15.4 as a DBA.

How to recreate the said schema?

• user7070613: 1) Do you need it? 2) Do you have another database that still has it? 3) If the answer to 2) is yes you could try pg_dump -d otherdb_name -n information_schema -U postgres -f info_sch.sql and then psql -d db_name -U postgres -f info_sch.sql. You want to make sure the other db is the same major version as the one you want to restore to as the information_schema contains views over the Postgres system catalogs and they can change between major versions.
• Frank Heikens: Before anything else, make a backup of your database!
• puio: @AdrianKlaver 1. yes it's required in some major ways. 2. sorry no backup
• user7070613: You don't need a backup just another database in the cluster. If one does not exist then create a 'dummy' data e.g. CREATE DATABASE dummy_db and then use the pg_dump command I provided previously against that database. Per Frank Heikens , make sure you do a backup of your current database before making any changes.
• puio: @AdrianKlaver Is that the same as github.com/postgres/postgres/blob/REL_15_4/src/backend/catalog/… ?

Answer 1

Create a new database, take a pg_dump of the database where you dropped information_schema and restore it to the new database.

Then you can drop the broken database and rename the new copy, and you have got the information_schema back.