postgresqlpostgres-10

Can't create schema in postgres

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?

Solution

  • 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.