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

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?

Solution

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.