Unable to connect PostgreSQL to remote database using pgAdmin

PostgresqlPgadmin

Postgresql Problem Overview

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

I know I have to make changes in postgresql.conf and pg_hba.conf to allow the client to connect. Could you please guide me?

Postgresql Solutions

Solution 1 - Postgresql

It is actually a 3 step process to connect to a PostgreSQL server remotely through pgAdmin3.

> Note: I use Ubuntu 11.04 and PostgreSQL 8.4.

  1. You have to make PostgreSQL listening for remote incoming TCP connections because the default settings allow to listen only for connections on the loopback interface. To be able to reach the server remotely you have to add the following line into the file /etc/postgresql/8.4/main/postgresql.conf:

    listen_addresses = '*'

  2. PostgreSQL by default refuses all connections it receives from any remote address, you have to relax 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 let anybody login in from any address if he can provide a valid password (the md5 keyword). You can use needed network/mask instead of 0.0.0.0/0 .

  3. When you have applied these modifications to your configuration files you need to restart PostgreSQL server. Now it is possible to login to your server remotely, using the username and password.

Solution 2 - Postgresql

If you're using PostgreSQL 8 or above, you may need to modify the listen_addresses setting in /etc/postgresql/8.4/main/postgresql.conf.

Try adding the line:

listen_addresses = *

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

If not explicitly set, this setting defaults to localhost which means it will only accept connections from the same machine.

Solution 3 - Postgresql

In linux terminal try this:

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

Solution 4 - Postgresql

I didn't have to change my prostgresql.conf file but, i did have to do the following based on my psql via command line was connecting and pgAdmin not connecting on RDS with AWS.

I did have my RDS set to Publicly Accessible. I made sure my ACL and security groups were wide open and still problem so, I did the following: sudo find . -name *.conf then sudo nano ./data/pg_hba.conf then added to top of directives in pg_hba.conf file host all all 0.0.0.0/0 md5 and pgAdmin automatically logged me in.

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

As a side note, my RDS was in my default VPC. I had an identical RDS instance in my non-default VPC with identical security group, ACL and security group settings to my default VPC and I could not get it to work. Not sure why but, that's for another day.

Solution 5 - Postgresql

Connecting to PostgreSQL via SSH Tunneling

In the event that you don't want to open port 5432 to any traffic, or you don't want to configure PostgreSQL to listen to any remote traffic, you can use SSH Tunneling to make a remote connection to the PostgreSQL instance. Here's how:

  1. Open PuTTY. If you already have a session set up to connect to the EC2 instance, load that, but don't connect to it just yet. If you don't have such a session, see this post.
  2. Go to Connection > SSH > Tunnels
  3. Enter 5433 in the Source Port field.
  4. Enter 127.0.0.1:5432 in the Destination field.
  5. Click the "Add" button.
  6. Go back to Session, and save your session, then click "Open" to connect.
  7. This opens a terminal window. Once you're connected, you can leave that alone.
  8. Open pgAdmin and add a connection.
  9. Enter localhost in the Host field and 5433 in the Port field. Specify a Name for the connection, and the username and password. Click OK when you're done.

Solution 6 - Postgresql

For redhat linux

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

pgsql9 is the folder for the postgres version installed, might 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

Solution 7 - Postgresql

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

Categories
Recommended Postgresql Solutions
Recommended Pgadmin Solutions

Previous Solution:

Text Field using Hibernate Annotation

HibernateAnnotationsBlob

Next Solution:

Trigger a jQuery UI slider event

JqueryEventsJquery Ui-Slider

Attributions

All content for this solution is sourced from the original question on Stackoverflow.

The content on this page is licensed under the Attribution-ShareAlike 4.0 International (CC BY-SA 4.0) license.

Content TypeOriginal AuthorOriginal Content on Stackoverflow
QuestionvinayrksView Question on Stackoverflow
Solution 1 - PostgresqlSyed AslamView Answer on Stackoverflow
Solution 2 - PostgresqljwfearnView Answer on Stackoverflow
Solution 3 - Postgresqlsamen tanay singhView Answer on Stackoverflow
Solution 4 - Postgresqlmax56View Answer on Stackoverflow
Solution 5 - PostgresqlSaravanan NandhanView Answer on Stackoverflow
Solution 6 - PostgresqlRujoota ShahView Answer on Stackoverflow
Solution 7 - Postgresqluser7127267View Answer on Stackoverflow