Postgresql accepts remote connections

By default, PostgreSQL just allows local connections only. To make PostgreSQL accepts remote connections, we have to do some additional steps as below:

1. Basic Steps.

Step 1. Edit the pg_hba.conf, put 1 more line.

Step 2. Edit postgresql.conf
Find the key listen_addresses=’localhost’ and change it to:

Step 3. Restart Postgresql or reload the configuration files

2. Location of configuration files and data directory.

We can find PostgreSQL configuration files on the data directory which can be resided in many different places depended on how PostgreSQL was installed.

Generally, for Windows, we can find the data directory in the:

For example, with PostgreSQL 9.4

For Linux, we can find the data directory at:

To find exactly the location of Data directory and configuration files as well, we can do as below.

Log in PostgreSQL with admin role and execute below commands:

For example:

PostgreSQL – Show data directory

• 1st command: “su – postgres” to change to postgres account.
• 2nd command: “psql” to execute command line client.
• 3rd command: show data_directory.
• The data directory is showed at: /var/lib/pgsql/9.4/data.

or

For example:

PostgreSQL – show postgresql.conf file

Or

PostgreSQL – show pg_hba file

On Windows, we can use PostgreSQL client likes PgAdmin, which is a free client tool to access PostgreSQL server.

Here is an example about locating configuration after login to postgres database:

PostgreSQL – locating data directory on Windows

3. Example of configuration files of PostgreSQL accepts remote connections.

The pg_hba.conf file.

PostgreSQL accepts remote connections – pg_hba.conf.

The postgresql.conf file.

PostgreSQL accepts remote connections – postgresql.conf.

4. Notes

After changing the configuration files, you should reload configurations file or restart PostgreSQL.
For ex: To reload configuration files on CentOS 7.