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 postgres=# create database games; with the postgres user connected to the server. I know the database was created, because if I run postgres=# \l+ while connected to the server, I get a list of databases, including my 'games'.

In the second picture is my screenshot of the pgAdmin 9.5 application with all the property values I use to connect to my server. So I manage to connect to the server, but I am unable to see the database 'games'.

I also went a step further and granted all permissions to the postgres user by executing postgres=# grant all privileges on database games to postgres;. You can see in my third screenshot, that the access privileges changed. But this change did not do help me to solve the problem. I am still not able to see 'games' in pgAdmin. I saw someone had a similar problem and run the right click on the server and clicked 'New database'.

This seemed to created a new database, because as you can see from the pgAdmin, the application was able 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 games=# select * from score; results in ERROR: relation "score" does not exist LINE 1: select * from score;.

Any other ideas where I went wrong. Would appreciate any help I can get.

I can log in to my database. When I run psql games postgres, this will log me into my games database with the postgres user. And this is also how I logged in and executed the select statement:

games=# select * from score;

ERROR: relation "score" does not exist LINE 1: select * from score;

I went a step further I printed out the tables we have in 'games':

games=# \dt

No relations found.

Answer 1

Can you log into your games database using psql?

What I can see is that the first image has you connecting to localhost:5432, the second (the psql connection), you're connecting to jkalancic@vps73162 - on what appears to be a telnet session - are you sure that you're looking at the same database on the same machine?

[Following OP's answer]

A tip for next time - you could install from source and not be messing with /etc/postgresql - you can never be absolutely sure that you're not mixing clients, servers and ports unless you do this. I do this all the time for installs - on Linux and I see that you're on Ubuntu, so that should be a piece of cake.

PostgreSQL is really excellent for this kind of thing. Works first time straight - and you can tailor your prompt to point to your various installs - be sure to have a shutdown (in your startup script) if you want to put more than one server on port 5432.

Answer 2

I managed to find the problems.

One was I had an installed PostgreSQL server on my machine that I was unaware of. It seems I installed it with my pgAdmin install. So every time I connected to my server, I would establish a connection to my localhost server and not my remote server. 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 ran:

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 two restart the configuration by executing:

sudo /etc/init.d/postgresql reload

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