Postgres will list my database, but it doesn't exist when I try connecting to it

Question

From terminal, I

sudo su postgres

psql

\l:

postgres=# \l
                                  List of databases
   Name    |  Owner   | Encoding |   Collate   |    Ctype    |   Access privileges   
-----------+----------+----------+-------------+-------------+-----------------------
 nwnx      | nwnx     | UTF8     | en_US.UTF-8 | en_US.UTF-8 | 
 postgres  | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | 
 template0 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres          +
           |          |          |             |             | postgres=CTc/postgres
 template1 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres          +
           |          |          |             |             | postgres=CTc/postgres
(4 rows)

But when trying to connect to it:

\c nwnx:

FATAL:  database "nwnx" does not exist
Previous connection kept

quote_ident:

postgres=# select quote_ident(datname) from pg_database;
 quote_ident 
-------------
 postgres
 template1
 template0
 nwnx
(4 rows)

dumpall:

pg_dumpall --schema-only | grep '\connect'
\connect template1
pg_dump: [archiver (db)] connection to database "nwnx" failed: FATAL:  database "nwnx" does not exist
pg_dumpall: pg_dump failed on database "nwnx", exiting

Creation script:

pg_dumpall --schema-only | grep -i database
-- PostgreSQL database cluster dump
-- PostgreSQL database dump
-- Dumped from database version 11.5
-- PostgreSQL database dump complete
pg_dump: [archiver (db)] connection to database "nwnx" failed: FATAL:  database "nwnx" does not exist
pg_dumpall: pg_dump failed on database "nwnx", exiting

Connecting as nwnx user

$: psql postgres -U nwnx
psql (11.5)
Type "help" for help.

postgres=> \conninfo
You are connected to database "postgres" as user "nwnx" via socket in "/run/postgresql" at port "5432".
postgres=> \l
                                  List of databases
   Name    |  Owner   | Encoding |   Collate   |    Ctype    |   Access privileges   
-----------+----------+----------+-------------+-------------+-----------------------
 nwnx      | nwnx     | UTF8     | en_US.UTF-8 | en_US.UTF-8 | 
 postgres  | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | 
 template0 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres          +
           |          |          |             |             | postgres=CTc/postgres
 template1 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres          +
           |          |          |             |             | postgres=CTc/postgres
(4 rows)

postgres=> \c nwnx
FATAL:  database "nwnx" does not exist
Previous connection kept

WORKING SOLUTION BY @laurenz-albe:

Showing all dbs

postgres=# select oid, datname, datname::bytea FROM pg_database;
  oid  |  datname  |       datname        
-------+-----------+----------------------
 13121 | postgres  | \x706f737467726573
     1 | template1 | \x74656d706c61746531
 13120 | template0 | \x74656d706c61746530
 59515 | nwnx      | \x6e776e78
(4 rows)

Checking if nwnx is omitted (had to use alias for datname)

postgres=# SELECT oid, datname dn, datname::bytea FROM pg_database ORDER BY dn;
  oid  |    dn     |       datname        
-------+-----------+----------------------
 13121 | postgres  | \x706f737467726573
 13120 | template0 | \x74656d706c61746530
     1 | template1 | \x74656d706c61746531
(3 rows)

I followed the instructions from the solution and it worked perfectly! Thank you very much!

Postgres version is 11.5

Any hints on what I'm doing wrong or whats going on?

Answer 1

That looks a lot like database corruption, in particular like the index pg_database_datname_index (which is used in GetDatabaseTuple()) got corrupted.

To be sure, try the following:

-- should show all databases
SELECT oid, datname, datname::bytea FROM pg_database;
SET enable_seqscan = off;
-- should omit database "nwnx"
SELECT oid, datname, datname::bytea FROM pg_database ORDER BY datname;

If that confirms my suspicion, do the following:

• Stop the database with

  pg_ctl stop -m immediate -D /path/to/data/directory
  

  and take a cold backup of the database directory.

• Start the database with

  pg_ctl start -o -P -D /path/to/data/directory
  

• Connect to the database postgres and run

  REINDEX TABLE pg_database;
  

• Stop and restart PostgreSQL.

Now take a pg_dumpall from the cluster and restore it to a new cluster you create with initdb.