Failed to connect PostgreSQL to the remote database using pgAdmin

Question

I installed PostgreSQL on my local server (Ubuntu) with IP 192.168.1.10. Now I am trying to access the database from my client machine (Ubuntu) with IP 192.168.1.11 using pgAdmin.

I know that I need to make changes to postgresql.conf and pg_hba.conf so that the client can connect. Could you guide me?

Answer 1

This is actually a three-step process for connecting remotely to a PostgreSQL server via pgAdmin3.

Note. I am using Ubuntu 11.04 and PostgreSQL 8.4.

• You must force PostgreSQL to listen for remote incoming TCP connections, since the default settings only allow listening on connections on the loopback interface. To reach the server remotely, you must add the following line to the /etc/postgresql/8.4/main/postgresql.conf: file /etc/postgresql/8.4/main/postgresql.conf:

  listen_addresses = '*'

• PostgreSQL by default rejects all connections it receives from any remote address, you need to disable these rules by adding this line to /etc/postgresql/8.4/main/pg_hba.conf:

  host all all 0.0.0.0/0 md5

  This is an access control rule that allows anyone to log in from any address if they can provide a valid password (keyword md5). You can use the required network / mask instead of 0.0.0.0/0.

• When you apply these changes to your configuration files, you need to restart the PostgreSQL server. Now you can log in to your server remotely using a username and password.

Answer 2

If you use PostgreSQL 8 or higher, you may need to change the listen_addresses parameter in /etc/postgresql/8.4/main/postgresql.conf .

Try adding a line:

 listen_addresses = * 

which will tell PostgreSQL to listen for connections on all network interfaces.

If this is not explicitly set, the default value for this parameter is localhost , which means that it will accept connections from only one computer.

Answer 3

I did not have to change the prostgresql.conf file, but I needed to do the following based on my psql via the command line, and the pgAdmin connection did not connect to RDS with AWS.

I had RDS installed for public access. I made sure that my ACLs and security groups were wide open and still had problems, so I did the following: sudo find . -name *.conf sudo find . -name *.conf then sudo nano ./data/pg_hba.conf then added to the beginning of the directives in the file pg_hba.conf host all all 0.0.0.0/0 md5 and pgAdmin automatically launched me.

This also worked in the pg_hba.conf host all all md5 file without any IP address, and it also worked with my host all all <myip>/32 md5 IP address

As a side note, my RDS was in my VPC by default. I had an identical RDS instance in my non-default VPC with the same security settings, ACLs, and security groups for my default VPC, and I could not get it to work. I don’t know why, but what the next day.

Answer 4

In a Linux terminal, try this:

• sudo service postgresql start : start server
• sudo service postgresql stop : stop the server
• sudo service postgresql status : check server status

Answer 5

For redhat linux

 sudo vi /var/lib/pgsql9/data/postgresql.conf 

pgsql9 is the folder for the installed version of postgres, which may be different for others.

changed listen_addresses = '*' from listen_addresses = 'localhost and then

 sudo /etc/init.d/postgresql stop sudo /etc/init.d/postgresql start 

Answer 6

Check your firewall. When you disconnect it, then you can connect. If you want / cannot disable the firewall, add a rule for remote connection.