问题

I cannot list/show database in my postgres server (running into centos 7 VM) using psql -l command.

我无法使用 psql -l 命令在我的CentOS 7虚拟机上运行的PostgreSQL服务器中列出/显示数据库。

I see this error: psql: FATAL: database "postgres" is not exist.

我看到这个错误：psql: FATAL: 数据库 "postgres" 不存在。

Any idea to debug this error? For information; this is the pg_hba.conf.

有没有任何想法来调试这个错误？提供信息：这是 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

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

答案1

得分: 2

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;

psql -d template1 -l

/path/to/software/bin/postgres --single -D /path/to/datadir template0

strings /path/to/datadir/global/1262

CREATE DATABASE postgres TEMPLATE templ;

CREATE DATABASE template1 TEMPLATE templ;
CREATE DATABASE template0 TEMPLATE templ;

ALTER DATABASE template0 ALLOW_CONNECTIONS FALSE;