Some PostgreSQL databases are not displayed via clients like Navicat or pgAdmin
When I login to see my remote PostgreSQL databases using pgAdmin or Navicat, I can connect without any problem and the first three databases in the list below show up well. But for some reason I cannot get the last two databases ("template0" and "template1") to be displayed along with the others. Also, when I ssh into the server database, I can run "SELECT * FROM a_table_in_template1_database;" and it shows all the contents of the table without issue, so I know everything is ok with the database and the tables inside it. How can I get the database "template1" to appear in this list and work? Here is PostgreSQL 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)
SEE that there might be something like "Access Privileges" that is causing this. However, I did things like "GRANT ALL ON DATABASE template1 TO postgres" but that doesn't seem to help.
Some other useful information: when I log in with Navicat and three databases appear, I can click the "Open Database" option, then type "template1" and it appears in the left pane along with the other three databases, but I don't can click on it or do something with it.
I just want to show that all 5 of my databases show up in Navicat or pgAdmin and have access to all of them :) Any help would be greatly appreciated!
source to share
These tools tend to hide databases where pg_database.datistemplate
- true
, as they are considered "internal" databases and not regular user databases.
The client application may offer a parameter to display these databases, or you can open them directly by name.
Note that this is template0
usually a read-only database that you cannot modify, so there is very little point in accessing it in the management interface. template1
can be connected and modified, but whatever you do with template1
will be copied when you create a new database (no explicit option TEMPLATE
), so you usually don't want to modify it.
I highly recommend that you ignore template0
and template1
. Pretend they don't exist.
source to share