gpt4 book ai didi

postgresql - Postgres 将列出我的数据库,但当我尝试连接到它时它不存在

转载 作者:行者123 更新时间:2023-12-03 03:38:36 25 4
gpt4 key购买 nike

从终端,我

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)

但是当尝试连接到它时:

\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)

转储:

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

创建脚本:

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

以 nwnx 用户身份连接

$: 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

@laurenz-albe 的工作解决方案:

显示所有数据库

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)

检查 nwnx 是否被省略(必须使用数据名的别名)

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)

我按照解决方案中的说明进行操作,效果非常好!非常感谢!

Postgres 版本是 11.5

关于我做错了什么或发生了什么有任何提示吗?

最佳答案

这看起来很像数据库损坏,特别是索引 pg_database_datname_index(在 GetDatabaseTuple() 中使用)已损坏。

为了确保万无一失,请尝试以下操作:

-- 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;

如果这证实了我的怀疑,请执行以下操作:

  • 停止数据库

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

    并对数据库目录进行冷备份。

  • 启动数据库

    pg_ctl start -o -P -D /path/to/data/directory
  • 连接到数据库postgres并运行

    REINDEX TABLE pg_database;
  • 停止并重新启动 PostgreSQL。

现在从集群中获取 pg_dumpall 并将其恢复到您使用 initdb 创建的新集群。

关于postgresql - Postgres 将列出我的数据库,但当我尝试连接到它时它不存在,我们在Stack Overflow上找到一个类似的问题: https://stackoverflow.com/questions/59094999/

25 4 0
Copyright 2021 - 2024 cfsdn All Rights Reserved 蜀ICP备2022000587号
广告合作:1813099741@qq.com 6ren.com