Remote PostgreSQL connection with pgAdmin
First of all test if you can connect to the database via psql:
psql -h ip_address -d name_of_the_database -U username
If you get connection refused error you had to set up something wrong and check the What should I check if remote connect to PostgreSQL not working?
psql: could not connect to server: Connection refused Is the server running on host ip_address
What should I check if remote connect to PostgreSQL not working?
Check the authentication configuration in
pg_hba.conf
Usually located on linux -
/etc/postgresql/version/main/pg_hba.conf
.You should allow authentication for client for specific IP all from all IP addresses:# Database administrative login by Unix domain socketlocal all postgres peer# TYPE DATABASE USER ADDRESS METHOD# "local" is for Unix domain socket connections onlylocal all all peer# IPv4 local connections:host all all 0.0.0.0/0 md5# IPv6 local connections:host all all ::0/0 md5#all ipshost all all all md5
More information how to set up
pg_hba.conf
you can find in documentation.Then you should set up listening on specific port.
You have to find the
postgresql.conf
. Usually located/etc/postgresql/9.1/main/postgresql.conf
) file and change the line with listen_address from:#listen_address = ''
to (don't forget remove # which means comment):
listen_address = '*'
After every step you should restart Postgresql service:
sudo service postgresql restart
After step 2 you should see port 5432 (or 5433) in listening address after netstat command:
netstat -ntlp
After that you have to open port for PostgreSQL in firewall:
sudo ufw allow 5432
You can check firewall settings with (you should see 5432 in the list):
sudo ufw status
If any of the previous step doesn't work you should check if PostgreSQL is not running on different port (usually 5433) and repeat the previous steps.
This happens very often when you have more running versions of PostgreSQL or you upgrade database and forgot stop the previous version of PostgreSQL.
If you have problems to find configuration files you can check this thread Where are my postgres *.conf files?.
In case you are using GCP remember to set the firewall rule inside GCP to allow that port, it might save you some hours of debugging.