I cannot list/show database in my postgres server (running into centos 7 VM) using psql -l command
i see this error : psql: FATAL: database « postgres » is not exist
Any idea to debug this error ? for information ; this is the pg_hba.conf
cat /var/lib/pgsql/11/data/pg_hba.conf
# TYPE DATABASE USER ADDRESS METHOD
# "local" is for Unix domain socket connections only
local all all trust
# IPv4 local connections:
host all all 127.0.0.1/32 trust
# IPv6 local connections:
host all all ::1/128 trust
# reject all remote connections
host all all 0.0.0.0/0 reject
Somebody dropped the postgres database. That is allowed, but not smart, as you see. Use template1 instead:
psql -d template1 -l
If that doesn't work either, and somebody dropped template1, you are in worse trouble. You will have to stop the PostgreSQL server and try template0 in single-user mode:
/path/to/software/bin/postgres --single -D /path/to/datadir template0
If there is no template0 either, you have a problem. You'll have to guess the name of an existing database (there must be one). look for strings in the file for pg_database:
strings /path/to/datadir/global/1262
And pick something that looks like a database name. Start the server and connect to that database.
No matter which of these ways you end up connecting, re-create all the missing databases. Figure out an appropriate template database: best is template0, next best is template1, last resort is any other database. That template database is called templ in the following:
CREATE DATABASE postgres TEMPLATE templ;
If template1 or template0 are missing, re-create them:
CREATE DATABASE template1 TEMPLATE templ;
CREATE DATABASE template0 TEMPLATE templ;
Connect to the databases you created and drop everything in them except for the empty public schema. Then, if you had to create template0, secure it:
ALTER DATABASE template0 ALLOW_CONNECTIONS FALSE;