This article is a simple step-by-step guide on how to connect to PostgreSQL database server from the client machine.
1 2 3 4 5 6 7 |
mehmed@dataera ~ $ ping oel7u501 PING oel7u501 (192.168.56.11) 56(84) bytes of data. 64 bytes from oel7u501 (192.168.56.11): icmp_seq=1 ttl=64 time=0.477 ms 64 bytes from oel7u501 (192.168.56.11): icmp_seq=2 ttl=64 time=0.542 ms |
When we click Add New Server in PgAdmin4, we get the below error even though all the information is correct.
This error can be due to multiple causes:
Step 1:
Firewall in the PostgreSQL Linux server may be blocking the connection. By default, PostgreSQL listens on a TCP port 5432. Linux uses firewall utility called iptables. Execute the below commands to give access to the client.
Allow PostgreSQL.
1 |
root@oel7u501 ~ $ iptables -A INPUT -i eth1 -p tcp -s 0.0.0.0 --dport 5432 -j ACCEPT |
Or to control the access only from the local subnet, we can use the below command.
1 |
root@oel7u501 ~ $ iptables -A INPUT -i eth1 -p tcp -s 192.168.56.0/24 --dport 5432 -j ACCEPT |
Make sure to check all the connections are allowed as required.
1 |
root@oel7u501 ~ $ iptables -L |
Step 2:
Make sure PostgreSQL service listening correct server IP address and port. (in our case server IP is 192.168.56.11)
1 2 3 4 5 |
root@oel7u501 ~ $ netstat -tnlp |grep 5432 tcp 0 0 127.0.0.1:5432 0.0.0.0:* LISTEN 13365/postmaster tcp6 0 0 ::1:5432 :::* LISTEN 13365/postmaster |
Next make changes in PostgreSQL, so that server can listen to the server IP address.
1 2 3 |
vim /var/lib/pgsql/10/data/postgresql.conf listen_addresses = '192.168.56.11' |
Restart the PostgreSQL service to implement the above changes.
1 |
systemctl restart postgresql-10.service |
Finally check the service is listening on the correct port with the below command
1 2 3 |
root@oel7u501 ~ $ netstat -ntlp |grep 5432 tcp 0 0 192.168.56.11:5432 0.0.0.0:* LISTEN 15799/postmaster |
Step 3:
In the PostgreSQL server, we enter IP address of the client as a trusted connection.
1 2 3 |
root@oel7u501 ~ $ vim /var/lib/pgsql/10/data/pg_hba.conf host all all 192.168.56.0/24 md5 |
With the above changes in pg_hba.conf file, we have given access to all IPs in the 192.168.56.0 subnet.
With 192.168.56.1/32, we can only grant access to the IP we want.
If only one client is going to access it, it’s safer to allow access to a single IP.
Step 4:
Reloading the configuration file
1 2 3 4 5 6 7 |
postgres=# SELECT pg_reload_conf(); pg_reload_conf ---------------- t |
The connection can be established now
Connection to server via terminal.
1 2 3 4 5 6 7 8 9 10 11 12 13 |
root@oel7u502 ~ $ psql -h 192.168.56.11 -p 5432 -d cms -U postgres -W Password for user postgres: psql (9.2.23, server 10.5) WARNING: psql version 9.2, server version 10.0. Some psql features might not work. Type "help" for help. cms=# |
Note: It gives a warning when the client version and the server version are different.
1 2 3 4 5 6 7 8 9 |
[root@oel7u502 ~ $ /usr/pgsql-10/bin/psql -h 192.168.56.11 -p 5432 -d cms -U postgres -W Password for user postgres: psql (10.5) Type "help" for help. cms=# |
To see the connections and processes
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 |
root@oel7u501 ~]# ps -ef |grep postgres postgres 2374 1 0 06:45 ? 00:00:00 /usr/pgsql-10/bin/postmaster -D /var/lib/pgsql/10/data/ postgres 2376 2374 0 06:45 ? 00:00:00 postgres: logger process postgres 2378 2374 0 06:45 ? 00:00:00 postgres: checkpointer process postgres 2379 2374 0 06:45 ? 00:00:00 postgres: writer process postgres 2380 2374 0 06:45 ? 00:00:00 postgres: wal writer process postgres 2381 2374 0 06:45 ? 00:00:00 postgres: autovacuum launcher process postgres 2382 2374 0 06:45 ? 00:00:02 postgres: stats collector process postgres 2383 2374 0 06:45 ? 00:00:00 postgres: bgworker: logical replication launcher postgres 6887 2374 0 07:58 ? 00:00:29 postgres: postgres postgres 192.168.56.1(59086) idle postgres 6935 2374 0 07:59 ? 00:00:00 postgres: postgres cms 192.168.56.1(59522) idle postgres 17631 2374 0 10:51 ? 00:00:00 postgres: postgres cms 192.168.56.12(38782) idle |
User, Roles &...
12 March 2019