When you install PostgreSQL, by default connection to the database using TCP/IP is not allowed.
When you try to connect from a client to a remote PostgreSQL database using psql command, you might get “psql: could not connect to server: Connection refused” error message
For example:
# psql -U postgres -h 62.181.1.126
psql: could not connect to server: Connection refused
Is the server running on host "62.181.1.126" and accepting
TCP/IP connections on port 5432?
To enable TCP/IP connection for PostgreSQL database, you need to follow the two steps mentioned below:
1. Modify pg_hba.conf to add Client Authentication Record:
On the PostgreSQL database server, by default, you’ll notice the following records towards the end of the /var/lib/pgsql/data/pg_hba.conf or /var/lib/pgsql/9.3/data/pg_hba.conf
As indicated below, it accepts connections only from the localhost.
# IPv4 local connections:
host all all 127.0.0.1/32 trust
Add the following line to the pg_hba.conf server.If you want to allow connection from multiple client machines on a specific network, specify the network address here in the CIDR-address format.
# vi /var/lib/pgsql/9.3/data/pg_hba.conf
host all all 62.181.1.126/24 trust
2. Change the Listen Address in postgresql.conf
On the PostgreSQL database server, by default, the listen address will be localhost in the postgresql.conf file as shown below.
grep listen /var/lib/pgsql/9.3/data/postgresql.conf
#listen_addresses = ‘localhost’ # what IP address(es) to listen on;
Modify this line and give *. If you have multiple interfaces on the server, you can also specify a specific interface to be listened.
grep listen /var/lib/pgsql/9.3/data/postgresql.conf
listen_addresses = ‘*’ # what IP address(es) to listen on;
3. Test the Remote Connection
$ psql -U postgres -h 62.181.1.126
psql (9.3.5)
Type "help" for help.
postgres=#
Also, if you don’t want to specify the hostname in the command line parameter every time, you can setup the remote PostgreSQL database ip-address in PGHOST environment variable name as shown below.
$ export PGHOST=62.181.1.126
$ psql -U postgres
psql (9.3.5)
Type "help" for help.
postgres=#
Like this:
Like Loading...