Report this

What is the reason for this report?

How do I connect to postgres from a client machine?

Posted on December 9, 2017

This is an extremely straightforward question. “How do I connect to Postgres DB installed on a digitalocean droplet, from an external client via command-line or via third party apps like PSequel (available for MacOS) or PGAdmin?”. I am surprised that there is no direct tutorial to explain this in plain simple words. Here is what I did so far:

  1. Created a brand new spick and span droplet running Ubuntu 16.04.
  2. Installed postgres following this tutorial - https://www.digitalocean.com/community/tutorials/how-to-install-and-use-postgresql-on-ubuntu-16-04
  3. Now I have Ubuntu user “sammy”, postgres user “sammy” and postgres database “sammy”. I am happily able to connect to DB from within the box.
  4. All that is required now is to connect to this database, from external machine, which for some reason is a herculean task.
  5. I followed https://www.digitalocean.com/community/questions/remote-connect-to-postgresql-with-pgadmin and https://www.digitalocean.com/community/questions/how-to-connect-remotely-to-the-postgres-db-on-the-server-from-the-local-client-machine and https://askubuntu.com/questions/423165/remotely-access-postgresql-database. I changed my /etc/postgresql/9.5/main/pg_hba.conf to host all all 0.0.0.0/0 md5 and in /etc/postgresql/9.5/main/postgresql.conf added listen_addresses='*'
  6. Restarted postgres on remote server via sudo service postgresql restart
  7. [On my local Mac laptop] psql -h <IPv4 address> -p 5432 -U sammy -d sammy. However I get the error psql: FATAL: password authentication failed for user "sammy"

How do I connect to postgres server? I would highly recommend digitalocean’s moderators to write a detailed but simple blog on “Connecting to Postgres from external client, along with best practises.”

The developer cloud

Scale up as you grow — whether you're running one virtual machine or ten thousand.

Start building today

From GPU-powered inference and Kubernetes to managed databases and storage, get everything you need to build, scale, and deploy intelligent applications.