Problem scenario
You have a Windows desktop computer with HeidiSQL installed on it. You have a Linux server with Postgres installed on it. How do you get your SQL front end (e.g., Hiedi SQL), to work with the Postgres database?
Solution
Part 1 / From the back end:
1. Modify this file /etc/postgresql/9.5/main/postgresql.conf so this stanza is not commented out:listen_addresses = 'localhost'
# Make sure in the quotes there is a space and then the external IP address of the Postgres server. To find the external IP address, you may use this link.
# The "9.5" in the path should be changed to the version of Postgresql that you installed.
2. Obtain database credentials. One way would be to go to the Linux server and create a user. Once you have access to the command prompt of the Linux server with postgres, see this article for running commands. If you know how to get to the Postgres command prompt from the Linux terminal, run these commands to create a user:
CREATE USER charles WITH PASSWORD 'veryNeatpw';
3. Ensure that port 5432 is not blocked on the Linux server. Confirm there is no process with ufw, firewalld, or iptables. If there is a process, ensure that port 5432 is not blocked.
4. Ensure that the Postgres service is running. See this link for more information.
Part 2 / From the front-end
5. Open HeidiSQL. When configuring a connection for the first time, the "Network Type" should be PostgreSQL (Experimental).
6. The specific database that you want to connect to should be specified when you set up the connection in the connection manager. You can save these settings for each database if you have many different databases.
Here is what the connection manager will look like:
