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

Hello Team,

Can some one help us on this please , Actually we are blocked .

Regards,

Narresh

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@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@axmdevhkdb008:/AXMDEVHKDB008/postgres/PHKGAXMD008_bck/base

     [0]postgres@axmdevhkdb008$ [PHKGAXMD008] psql

psql: FATAL:  database "postgres" does not exist

__  [RRF]postgres@axmdevhkdb008:/AXMDEVHKDB008/postgres/PHKGAXMD008_bck/base

     [2]postgres@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@axmdevhkdb008:/AXMDEVHKDB008/postgres/PHKGAXMD008_bck/base

We have the directories at Data location.

     [2]postgres@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@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@[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@[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@[local]:5433)# [PHKGAXMP003]create user axiomdevdb01 with password 'xxxxxxx';

CREATE ROLE

Time: 7.373 ms

(postgres@[local]:5433)# [PHKGAXMP003]\du

                                     List of roles

  Role name   |                         Attributes                         | Member of

--------------+------------------------------------------------------------+-----------

axiomdevdb01 |                                                            | {}

postgres     | Superuser, Create role, Create DB, Replication, Bypass RLS | {}

(postgres@[local]:5433)# [PHKGAXMP003]grant connect on database axiom to axiomdevdb01;

GRANT

Time: 5.274 ms

(postgres@[local]:5433)# [PHKGAXMP003]grant all privileges on database axiom to axiomdevdb01;

GRANT

Time: 1.677 ms

(postgres@[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@[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@[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