一些PostgreSQL数据库通过诸如Navicat或pgAdmin之类的客户端不可见 [英] Some PostgreSQL databases not visible through clients like Navicat or pgAdmin

问题描述

当我使用pgAdmin或Navicat登录查看我的远程PostgreSQL数据库时，我可以连接没有任何问题，下面列表中的前三个数据库显示得很好。但由于某些原因，我不能得到最后两个数据库（template0和template1）显示与其他人。另外，当我ssh进入服务器的数据库，我可以运行SELECT * FROM a_table_in_template1_database;并显示表的所有内容没有问题，所以我知道一切都是好的数据库和其中的表。如何获取template1数据库显示在此列表中并工作？这是PostgreSQL的输出从终端，当我运行\list：

When I log in to see my remote PostgreSQL databases with pgAdmin or Navicat, I can connect without any problems, and the first three databases in the list below show up fine. But for some reason I can't get the last two databases ("template0" and "template1") to show up with the others. Also, when I ssh into the server's database, I can run a "SELECT * FROM a_table_in_template1_database;" and it shows all of the contents of the table with no issues, so I know everything is okay with the database and the tables within it. How can I get the "template1" database to show up in this list and work? Here is PostgreSQL's the output from the terminal when I run "\list":

template1=# \list
                                List of databases
     Name      |  Owner   | Encoding | Collation | Ctype |   Access privileges   
---------------+----------+----------+-----------+-------+-----------------------
 mygigline     | jball037 | LATIN1   | en_US     | en_US | 
 mygiglinemain | postgres | LATIN1   | en_US     | en_US | 
 postgres      | postgres | LATIN1   | en_US     | en_US | 
 template0     | postgres | LATIN1   | en_US     | en_US | =c/postgres
 template1     | postgres | LATIN1   | en_US     | en_US | =CTc/postgres
                                                         : postgres=CTc/postgres
(5 rows)

它看起来像可能有些东西与访问权限是导致这一点。但是，我做了像GRANT ALL ON DATABASE template1 TO postgres这样的事情，但这似乎并不帮助。

It LOOKS like there might be something with "Access Privileges" that is causing this. However, I have done things like "GRANT ALL ON DATABASE template1 TO postgres" but that doesn't seem to help.

另一个有用的信息：当我登录与Navicat和三个数据库显示，我可以单击选项打开数据库，然后我键入模板1，它将显示在左面板与其他三个数据库，但我不能点击

Another useful piece of info: When I log in with Navicat and the three databases show up, I can click the option to "Open Database", then I type in "template1", and it will show up on the left panel along with the other three databases, but I can't click on it or do anything with it.

我只想显示所有5个数据库显示在Navicat或pgAdmin，并且能够访问所有的

I just want to be able to show all 5 of my databases show up in either Navicat or pgAdmin and be able to access all of them :) Any help would be much appreciated!

推荐答案

这些工具往往隐藏数据库，其中 pg_database。因为它们被认为是内部数据库，而不是普通用户数据库。

These tools tend to hide databases where pg_database.datistemplate is true, as they're considered "internal" databases rather than ordinary user databases.

客户端应用程序可能会提供一个设置来取消隐藏这些数据库，或者您可以直接通过名称打开它们。

The client application(s) may offer a setting to unhide these DBs, or you may be able to open them directly by name.

请注意， template0 通常是一个只读数据库，您无法修改，因此在管理UI中访问它几乎没有意义。 template1 可以连接到和修改，但是在创建新数据库时将复制 template1 没有明确的 TEMPLATE 选项），因此您通常不想更改它。

Note that template0 is typically a read-only database that you cannot modify, so there's very little point to accessing it in a management UI. template1 can be connected to and modified, but whatever you do to template1 will be copied when you create a new database (without an explicit TEMPLATE option) so you usually don't want to change it.

我强烈建议您忽略 template0 和 template1 。假装它们不存在。

I strongly recommend that you ignore template0 and template1. Pretend that they do not exist.

这篇关于一些PostgreSQL数据库通过诸如Navicat或pgAdmin之类的客户端不可见的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！