How do I connect to postgres from a client machine?
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:
- Created a brand new spick and span droplet running Ubuntu 16.04.
- Installed postgres following this tutorial - https://www.digitalocean.com/community/tutorials/how-to-install-and-use-postgresql-on-ubuntu-16-04
- 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.
- All that is required now is to connect to this database, from external machine, which for some reason is a herculean task.
- 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
host all all 0.0.0.0/0 md5and in
- Restarted postgres on remote server via
sudo service postgresql restart
- [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.”