Can't create schema in postgres

Question

I'm trying to create schema with query:

CREATE SCHEMA IF NOT EXISTS hdb_catalog

but following error occurred:

2019-09-10 13:47:37.025 UTC [129] ERROR:  duplicate key value violates unique constraint "pg_namespace_nspname_index"
2019-09-10 13:47:37.025 UTC [129] DETAIL:  Key (nspname)=(hdb_catalog) already exists.
2019-09-10 13:47:37.025 UTC [129] STATEMENT:  
       CREATE SCHEMA IF NOT EXISTS hdb_catalog

How it is possible with IF NOT EXISTS?

score 0 · Accepted Answer · answered Sep 10 '19 at 14:00

That looks like you have catalog corruption.

With some luck, only the index is affected. You can try to repair it using

REINDEX pg_catalog.pg_namespace;

Like in all cases of corruption, it is commendable to create a new cluster with initdb and use pg_dump/pg_restore to copy the database there. There might be more problems.

Also, try to find out what caused the corruption. Often it is bad hardware.

Can't create schema in postgres

1 Answers1