How to enable or allow remote access to PostgreSQL database server.

For enabling remote access to PostgreSQL database server,
There are 2 main PostgreSQL configuration files that need to be changed to allow remote connections.
1. postgresql.conf
2. pg_hba.conf

postgresql.conf


It will be located in PostgreSQL installed directory,

In Linux, You may find file at location below,
  1. /var/lib/pgsql/data/
  2. /etc/postgresql/9.1/main/
In Windows, You may find file at location below,
  1. C:/ProgramData/PostgreSQL/9.3/postgresql.conf


For exact location of "postgresql.conf" file, run the below command in psql command prompt.
SHOW config_file;
Open the file and search for "listen_addresses" and set listen_addresses parameter to,
listen_addresses = '*'

pg_hba.conf


It will be located in several directory, depending on how postgresql is installed

In Linux, You may find file at location below,
  1. /home,
  2. /var/lib/pgsql, 
  3. /var/lib/postgresql/[version]/, 
  4. /opt/postgres/  
  5. /etc/postgresql/[version]/main/
In Windows, You may find file at location below,
  1. C:/ProgramData/PostgreSQL/9.3/pg_hba.conf
For exact location of "pg_hba.conf" file, run the below command in psql command prompt.
SHOW hba_file; 

If you want PostgreSQL to accept incoming connection from any IP address then add entry like below,
 host             all             all             0.0.0.0/0             md5 

If you want PostgreSQL to accept incoming connection from particular IP address, say 192.168.82.2 then add entry in pg_hba.conf file like below,
host             all             all            192.168.82.2/24       md5 

Reload Configuration


After the changes are done, you have to reload the configuration as a superuser. run below command,
SELECT pg_reload_conf(); 
Done.

If PostgreSQL is still not accepting remote connection then try restarting the PostgreSQL service like below.

In Linux, If your PostgreSQL service name is "postgresql" then execute below command
service postgresql restart
In Windows, You can find service at
Start Menu ---> Search "Run" ---> Type "Services.msc" ---> Search "PosgreSQL"  
 ---> Right click and select Restart Service.

Done.

Enjoy !!!! 

If you find any issue in post or face any error while implementing, Please comment.

Post a Comment