psql: FATAL: database "postgres" does not exist or ERROR: 23505: duplicate key value violates unique constraint "pg_namespace_nspname_index"

Hello Postgres Support Team,

Today we have an outage, our DB was wend down due to 100% space full at FS. We added space and could able to bring the cluster.

DB version: psql (PostgreSQL) 10.12
OS version : Red Hat Enterprise Linux Server release 7.8 (Maipo)

[0]postgres(at)axmdevhkdb008$ [PHKGAXMD008] pg_ctl start -D /AXMDEVHKDB008/postgres/PHKGAXMD008_bck
waiting for server to start....2020-07-14 08:50:42.273 CEST db:[] user:[] [] [00000] LOG: listening on IPv4 address "176.5.88.68", port 5433
2020-07-14 08:50:42.278 CEST db:[] user:[] [] [00000] LOG: listening on Unix socket "/var/run/postgresql/.s.PGSQL.5433"
2020-07-14 08:50:42.280 CEST db:[] user:[] [] [00000] LOG: listening on Unix socket "/tmp/.s.PGSQL.5433"
2020-07-14 08:50:42.546 CEST db:[] user:[] [] [00000] LOG: redirecting log output to logging collector process
2020-07-14 08:50:42.546 CEST db:[] user:[] [] [00000] HINT: Future log output will appear in directory "log".
done
server started
__ [RRF]postgres(at)axmdevhkdb008:/AXMDEVHKDB008/postgres/PHKGAXMD008_bck/base
[0]postgres(at)axmdevhkdb008$ [PHKGAXMD008] psql
psql: FATAL: database "postgres" does not exist
__ [RRF]postgres(at)axmdevhkdb008:/AXMDEVHKDB008/postgres/PHKGAXMD008_bck/base
[2]postgres(at)axmdevhkdb008$ [PHKGAXMD008] ps -ef|grep postgres
root 16567 16513 0 14:12 pts/1 00:00:00 sudo su - postgres
root 16569 16567 0 14:12 pts/1 00:00:00 su - postgres
postgres 16571 16569 0 14:12 pts/1 00:00:00 -ksh
postgres 23888 1 0 14:50 pts/1 00:00:00 /opt/rh/rh-postgresql10/root/usr/bin/postgres -D /AXMDEVHKDB008/postgres/PHKGAXMD008_bck
postgres 23890 23888 0 14:50 ? 00:00:00 postgres: PHKGAXMD008: logger process
postgres 23892 23888 0 14:50 ? 00:00:00 postgres: PHKGAXMD008: checkpointer process
postgres 23893 23888 0 14:50 ? 00:00:00 postgres: PHKGAXMD008: writer process
postgres 23894 23888 0 14:50 ? 00:00:00 postgres: PHKGAXMD008: wal writer process
postgres 23895 23888 0 14:50 ? 00:00:00 postgres: PHKGAXMD008: autovacuum launcher process
postgres 23896 23888 0 14:50 ? 00:00:00 postgres: PHKGAXMD008: stats collector process
postgres 23897 23888 0 14:50 ? 00:00:00 postgres: PHKGAXMD008: bgworker: logical replication launcher
postgres 24689 16571 0 14:55 pts/1 00:00:00 ps -ef
postgres 24690 16571 0 14:55 pts/1 00:00:00 grep --color=auto postgres
__ [RRF]postgres(at)axmdevhkdb008:/AXMDEVHKDB008/postgres/PHKGAXMD008_bck/base

We have the directories at Data location.

[2]postgres(at)axmdevhkdb008$ [PHKGAXMD008] ls -lrt
total 180
drwx------ 2 postgres postgres 4096 Jan 2 2020 1
drwx------ 2 postgres postgres 4096 Jan 2 2020 13211
drwx------ 2 postgres postgres 4096 Jul 13 09:51 pgsql_tmp
drwx------ 2 postgres postgres 139264 Jul 13 21:02 16389
drwx------ 2 postgres postgres 12288 Jul 13 21:02 13212
drwx------ 2 postgres postgres 4096 Jul 14 11:08 13213
drwx------ 2 postgres postgres 12288 Jul 14 11:08 13214
__ [RRF]postgres(at)axmdevhkdb008:/AXMDEVHKDB008/postgres/PHKGAXMD008_bck/base

How to overcome this ?

Note: we don't have any wal files, it is ok for us if we could bring it up with our recover.

2 nd one:

Since we are not able to connect to DB , we did a new initialization and copy the directories from base directory to new base directory(in new data location)

And update pg_catalog.pg_database table with the DB name

(postgres(at)[local]:5433)# [PHKGAXMP003]INSERT INTO
-- # pg_catalog.pg_database(
-- # datname, datdba, encoding, datcollate, datctype, datistemplate, datallowconn,
-- # datconnlimit, datlastsysoid, datfrozenxid, datminmxid, dattablespace, datacl)
-- # VALUES(
-- # -- Write Your collation
-- # 'axiom', 10, 0, 'C', 'C',
-- # False, True, -1, 16389, '536', '1', 1663, Null);
INSERT 16384 1
Time: 70.239 ms
(postgres(at)[local]:5433)# [PHKGAXMP003]select oid from pg_database a where a.datname = 'axiom';
oid
-------
16384
(1 row)

After this we could able to see the DB axiom.

Name | Owner | Encoding | Collate | Ctype | Access privileges | Size | Tablespace | Description
-----------+----------+-----------+---------+-------+-----------------------+---------+------------+--------------------------------------------
axiom | postgres | SQL_ASCII | C | C | | 16 GB | pg_default |
postgres | postgres | SQL_ASCII | C | C | | 7647 kB | pg_default | default administrative connection database
template0 | postgres | SQL_ASCII | C | C | =c/postgres +| 7513 kB | pg_default | unmodifiable empty database
| | | | | postgres=CTc/postgres | | |
template1 | postgres | SQL_ASCII | C | C | =c/postgres +| 7513 kB | pg_default | default template for new databases
| | | | | postgres=CTc/postgres | | |
(4 rows)

But no schema and username

Some how we could manage to create the user.

(postgres(at)[local]:5433)# [PHKGAXMP003]create user axiomdevdb01 with password 'xxxxxxx';
CREATE ROLE
Time: 7.373 ms
(postgres(at)[local]:5433)# [PHKGAXMP003]\du
List of roles
Role name | Attributes | Member of
--------------+------------------------------------------------------------+-----------
axiomdevdb01 | | {}
postgres | Superuser, Create role, Create DB, Replication, Bypass RLS | {}

(postgres(at)[local]:5433)# [PHKGAXMP003]grant connect on database axiom to axiomdevdb01;
GRANT
Time: 5.274 ms
(postgres(at)[local]:5433)# [PHKGAXMP003]grant all privileges on database axiom to axiomdevdb01;
GRANT
Time: 1.677 ms
(postgres(at)[local]:5433)# [PHKGAXMP003]\l+
List of databases
Name | Owner | Encoding | Collate | Ctype | Access privileges | Size | Tablespace | Description
-----------+----------+-----------+---------+-------+---------------------------+---------+------------+--------------------------------------------
axiom | postgres | SQL_ASCII | C | C | =Tc/postgres +| 16 GB | pg_default |
| | | | | postgres=CTc/postgres +| | |
| | | | | axiomdevdb01=CTc/postgres | | |
postgres | postgres | SQL_ASCII | C | C | | 7647 kB | pg_default | default administrative connection database
template0 | postgres | SQL_ASCII | C | C | =c/postgres +| 7513 kB | pg_default | unmodifiable empty database
| | | | | postgres=CTc/postgres | | |
template1 | postgres | SQL_ASCII | C | C | =c/postgres +| 7513 kB | pg_default | default template for new databases
| | | | | postgres=CTc/postgres | | |
(4 rows)

But not able to create the schema:

(postgres(at)[local]:5433)# [PHKGAXMP003]create schema ax_system;
ERROR: 23505: duplicate key value violates unique constraint "pg_namespace_nspname_index"
DETAIL: Key (nspname)=(ax_system) already exists.
SCHEMA NAME: pg_catalog
TABLE NAME: pg_namespace
CONSTRAINT NAME: pg_namespace_nspname_index
LOCATION: _bt_check_unique, nbtinsert.c:434
Time: 17.752 ms

But we could able to see the tables with schema name "NULL"

(postgres(at)[local]:5433)# [PHKGAXMP003]select * from pg_tables;
schemaname | tablename | tableowner | tablespace | hasindexes | hasrules | hastriggers | rowsecurity
--------------------+------------------------------+--------------+------------+------------+----------+-------------+-------------
[null] | axiom_object_dependencies | axiomdevdb01 | [null] | t | f | f | f
[null] | axiom_archive_instances | axiomdevdb01 | [null] | t | f | f | f
[null] | axiom_report_history | axiomdevdb01 | [null] | t | f | f | f
[null] | axiom_task_history | axiomdevdb01 | [null] | t | f | f | f
[null] | event_number | axiomdevdb01 | [null] | t | f | f | f
pg_catalog | pg_statistic | postgres | [null] | t | f | f | f
pg_catalog | pg_type | postgres | [null] | t | f | f | f
[null] | axo_hb | axiomdevdb01 | [null] | t | f | f | f
[null] | axiom_object_search_index | axiomdevdb01 | [null] | t | f | f | f
[null] | axod_rce | axiomdevdb01 | [null] | t | f | f | f
[null] | axos_rce_mgmt_1_0 | axiomdevdb01 | [null] | t | f | f | f

SO Please help us how to proceed with this?

Regards,
Narresh

=========================================================

Ce message et toutes les pieces jointes (ci-apres le "message")
sont confidentiels et susceptibles de contenir des informations
couvertes par le secret professionnel. Ce message est etabli
a l'intention exclusive de ses destinataires. Toute utilisation
ou diffusion non autorisee interdite.
Tout message electronique est susceptible d'alteration. La SOCIETE GENERALE
et ses filiales declinent toute responsabilite au titre de ce message
s'il a ete altere, deforme falsifie.

=========================================================

This message and any attachments (the "message") are confidential,
intended solely for the addresses, and may contain legally privileged
information. Any unauthorized use or dissemination is prohibited.
E-mails are susceptible to alteration. Neither SOCIETE GENERALE nor any
of its subsidiaries or affiliates shall be liable for the message
if altered, changed or falsified.

=========================================================