pgAdmin 9.5 not showing all databases

Question

I have a problem with my database. I installed postgreSQL 9.5 on my Ubuntu server. I changed the postgresql.conf file to allow binding the postgreSQL server to the localhost. This allows me to run pgAdmin and connect to my database by forwarding also the port 5432, where I run my postgreSQL.

The problem I am experiencing is that I only see the default table 'postgres', but not my newly created one 'games' (I created this table by running create database games with the postgres user connected to the server).

And here is my screen shot of the pgAdmin application with all the property value I use to connect to my server.

As you can see from the first picture I use the same permissions as for the postgres database - it is blank, which should grant access to everyone. I know I have to change that later and limit it to the postgres user I have, but for now I will let it that way. Once I manage to see my 'games' database, then I will start to tighten the security more.

UPDATE I granted all access to the database 'games', which is visible right on the third screen shot down. The access privilege is different. This did not help me, I would still not see the database, when connecting to the server with pgAdmin. A saw someone had a similar problem and run the right click on the server and clicked 'New database'. This seems created a new database, because as you can see from the pgAdmin, the application manage to find the score table I create inside pgAdmin. The reason I believe this is the case is, because running the same SQL connected to the server postgres=# select * from score; results in ERROR: relation "score" does not exist LINE 1: select * from score;.

Answer 1

I manage to find the problem. One of my problems was that I had (unaware of that) installed a postgreSQL server on my machine. Seems I installed it with my pgAdmin install. So everytime I would connect to my server, I would establish a connection to my localhost server and not my remote server. So I just uninstalled the server and installed only the pgAdmin client.

The second problem I had was that the file /etc/postgresql/9.5/main/pg_hba.conf had to be changed. So I run:

sudo vi /etc/postgresql/9.5/main/pg_hba.conf

and changed the line

# Database administrative login by Unix domain socket local all postgres peer

to

# Database administrative login by Unix domain socket local all postgres md5

Once that was changed, I had to restart the configuration by executing:

sudo /etc/init.d/postgresql reload

I would also point out that it is important to have postgres user as a unix and DB user with same passwords. I found all this information here.

Answer 2

Try granting access privileges explicitly for your new table.

I believe a blank access privileges column means the table has DEFAULT access privileges. The default could be no public access for tables, columns, schemas, and tablespaces. For more info: http://www.postgresql.org/docs/9.4/static/sql-grant.html

Answer 3

If only the 'postgres' database is visible and your newly created database is not showing up, here is the solution: Right-click on 'postgresql' and open the query tool as shown in

. Then, in

click on 'New Connection,' select your database, click on 'Save,' and confirm. In the query screen, write this query:

UPDATE pg_database SET datistemplate='false' WHERE datname='database_name';

replace 'database_name' with your actual database name, and execute the query. After that, refresh your databases, and it should appear.