{"id":4163,"date":"2021-09-09T13:13:02","date_gmt":"2021-09-09T10:13:02","guid":{"rendered":"https:\/\/sysdba.org\/?p=3787"},"modified":"2021-09-09T13:13:02","modified_gmt":"2021-09-09T10:13:02","slug":"how-to-connect-to-postgresql-database","status":"publish","type":"post","link":"https:\/\/sysdba.org\/en\/how-to-connect-to-postgresql-database\/","title":{"rendered":"How to connect to PostgreSQL Database"},"content":{"rendered":"

This article is a simple step-by-step guide on how to connect to PostgreSQL database server from the client machine.<\/p>\n

 <\/p>\n

\"PostgreSQL<\/p>\n

[crayon]<\/p>\n

mehmed@dataera ~ $ ping oel7u501<\/p>\n

PING oel7u501 (192.168.56.11) 56(84) bytes of data.<\/p>\n

64 bytes from oel7u501 (192.168.56.11): icmp_seq=1 ttl=64 time=0.477 ms<\/p>\n

64 bytes from oel7u501 (192.168.56.11): icmp_seq=2 ttl=64 time=0.542 ms<\/p>\n

[\/crayon]<\/p>\n

When we click Add New Server<\/strong> in PgAdmin4<\/strong>, we get the below error even though all the information is correct.<\/p>\n

\"\"<\/p>\n

 <\/p>\n

 <\/p>\n

\"\"<\/p>\n

 <\/p>\n

This error can be due to multiple causes:<\/p>\n

Step 1:<\/strong><\/p>\n

Firewall in the PostgreSQL Linux server may be blocking the connection. By default, PostgreSQL listens on a TCP port 5432. \u00a0Linux uses firewall utility called iptables. Execute the below commands to give access to the client.<\/p>\n

Allow PostgreSQL.<\/p>\n

[crayon]<\/p>\n

root@oel7u501 ~ $ iptables -A INPUT -i eth1 -p tcp -s 0.0.0.0\u00a0 –dport 5432 -j ACCEPT<\/p>\n

[\/crayon]<\/p>\n

Or to control the access only from the local subnet, we can use the below command.<\/p>\n

[crayon]<\/p>\n

root@oel7u501 ~ $ iptables -A INPUT -i eth1 -p tcp -s 192.168.56.0\/24\u00a0 –dport 5432 -j ACCEPT<\/p>\n

[\/crayon]<\/p>\n

Make sure to check all the connections are allowed as required.<\/p>\n

[crayon]<\/p>\n

root@oel7u501 ~ $ iptables -L<\/p>\n

[\/crayon]<\/p>\n

 <\/p>\n

\u00a0<\/strong><\/p>\n

Step 2: <\/strong><\/p>\n

Make sure PostgreSQL service listening correct server IP address and port. (in our case server IP is 192.168.56.11)<\/p>\n

[crayon]<\/p>\n

root@oel7u501 ~ $ netstat -tnlp |grep 5432<\/p>\n

tcp\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 0\u00a0\u00a0\u00a0\u00a0\u00a0 0 127.0.0.1:5432\u00a0 0.0.0.0:*\u00a0\u00a0 LISTEN\u00a0\u00a0\u00a0\u00a0\u00a0 13365\/postmaster<\/p>\n

tcp6\u00a0\u00a0\u00a0\u00a0\u00a0 0\u00a0\u00a0\u00a0\u00a0\u00a0 0 ::1:5432\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 :::*\u00a0\u00a0 \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 LISTEN\u00a0\u00a0\u00a0\u00a0\u00a0 13365\/postmaster<\/p>\n

[\/crayon]<\/p>\n

Next make changes in PostgreSQL, so that server can listen to the server IP address.<\/p>\n

[crayon]<\/p>\n

vim \/var\/lib\/pgsql\/10\/data\/postgresql.conf<\/p>\n

listen_addresses = ‘192.168.56.11’<\/p>\n

[\/crayon]<\/p>\n

Restart the PostgreSQL service to implement the above changes.<\/p>\n

[crayon]<\/p>\n

systemctl restart postgresql-10.service<\/p>\n

[\/crayon]<\/p>\n

Finally check the service is listening on the correct port with the below command<\/p>\n

[crayon]<\/p>\n

root@oel7u501 ~ $ netstat -ntlp |grep 5432<\/p>\n

tcp\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 0\u00a0\u00a0\u00a0\u00a0\u00a0 0 192.168.56.11:5432\u00a0\u00a0\u00a0\u00a0\u00a0 0.0.0.0:*\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 LISTEN\u00a0\u00a0\u00a0\u00a0\u00a0 15799\/postmaster<\/p>\n

[\/crayon]<\/p>\n

Step 3:<\/strong><\/p>\n

In the PostgreSQL server, we enter IP address of the client as a trusted connection.<\/p>\n

[crayon]<\/p>\n

root@oel7u501 ~ $ vim \/var\/lib\/pgsql\/10\/data\/pg_hba.conf<\/p>\n

host\u00a0\u00a0\u00a0 all\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 all\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 192.168.56.0\/24\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 md5<\/p>\n

[\/crayon]<\/p>\n

With the above changes in pg_hba.conf file, we have given access to all IPs in the 192.168.56.0 subnet.<\/p>\n

With 192.168.56.1\/32, we can only grant access to the IP we want.<\/p>\n

If only one client is going to access it, it’s safer to allow access to a single IP.<\/p>\n

 <\/p>\n

Step 4: <\/strong><\/p>\n

Reloading the configuration file<\/p>\n

[crayon]<\/p>\n

postgres=# SELECT pg_reload_conf();<\/p>\n

pg_reload_conf<\/p>\n

—————-<\/p>\n

t<\/p>\n

[\/crayon]<\/p>\n

 <\/p>\n

The connection can be established now<\/p>\n

\"\"<\/p>\n

Connection to server via terminal.<\/p>\n

[crayon]<\/p>\n

root@oel7u502 ~ $ psql -h 192.168.56.11 -p 5432 -d cms -U postgres -W<\/p>\n

Password for user postgres:<\/p>\n

psql (9.2.23, server 10.5)<\/p>\n

WARNING: psql version 9.2, server version 10.0.<\/p>\n

Some psql features might not work.<\/p>\n

Type “help” for help.<\/p>\n

cms=#<\/p>\n

[\/crayon]<\/p>\n

Note: It gives a warning when the client version and the server version are different.<\/p>\n

[crayon]<\/p>\n

[root@oel7u502 ~ $ \/usr\/pgsql-10\/bin\/psql -h 192.168.56.11 -p 5432 -d cms -U postgres -W<\/p>\n

Password for user postgres:<\/p>\n

psql (10.5)<\/p>\n

Type “help” for help.<\/p>\n

cms=#<\/p>\n

[\/crayon]<\/p>\n

To see the connections and processes<\/p>\n

[crayon]<\/p>\n

root@oel7u501 ~]# ps -ef |grep postgres<\/p>\n

postgres\u00a0 2374\u00a0\u00a0\u00a0\u00a0 1\u00a0 0 06:45 ?\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 00:00:00 \/usr\/pgsql-10\/bin\/postmaster -D \/var\/lib\/pgsql\/10\/data\/<\/p>\n

postgres\u00a0 2376\u00a0 2374\u00a0 0 06:45 ?\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 00:00:00 postgres: logger process<\/p>\n

postgres\u00a0 2378\u00a0 2374\u00a0 0 06:45 ?\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 00:00:00 postgres: checkpointer process<\/p>\n

postgres\u00a0 2379\u00a0 2374\u00a0 0 06:45 ?\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 00:00:00 postgres: writer process<\/p>\n

postgres\u00a0 2380\u00a0 2374\u00a0 0 06:45 ?\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 00:00:00 postgres: wal writer process<\/p>\n

postgres\u00a0 2381\u00a0 2374\u00a0 0 06:45 ?\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 00:00:00 postgres: autovacuum launcher process<\/p>\n

postgres\u00a0 2382\u00a0 2374\u00a0 0 06:45 ?\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 00:00:02 postgres: stats collector process<\/p>\n

postgres\u00a0 2383\u00a0 2374\u00a0 0 06:45 ?\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 00:00:00 postgres: bgworker: logical replication launcher<\/p>\n

postgres\u00a0 6887\u00a0 2374\u00a0 0 07:58 ?\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 00:00:29 postgres: postgres postgres 192.168.56.1(59086) idle<\/p>\n

postgres\u00a0 6935\u00a0 2374\u00a0 0 07:59 ?\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 00:00:00 postgres: postgres cms 192.168.56.1(59522) idle<\/p>\n

postgres 17631\u00a0 2374\u00a0 0 10:51 ?\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 00:00:00 postgres: postgres cms 192.168.56.12(38782) idle<\/p>\n

[\/crayon]<\/p>\n","protected":false},"excerpt":{"rendered":"

This article is a simple step-by-step guide on how to connect to PostgreSQL database server from the client machine.   [crayon] 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 [\/crayon] When we click … How to connect to PostgreSQL Database<\/span><\/a><\/p>\n","protected":false},"author":1,"featured_media":3769,"comment_status":"closed","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[355],"tags":[],"class_list":["post-4163","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-postgre-sql"],"_links":{"self":[{"href":"https:\/\/sysdba.org\/en\/wp-json\/wp\/v2\/posts\/4163","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/sysdba.org\/en\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/sysdba.org\/en\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/sysdba.org\/en\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/sysdba.org\/en\/wp-json\/wp\/v2\/comments?post=4163"}],"version-history":[{"count":0,"href":"https:\/\/sysdba.org\/en\/wp-json\/wp\/v2\/posts\/4163\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/sysdba.org\/en\/wp-json\/"}],"wp:attachment":[{"href":"https:\/\/sysdba.org\/en\/wp-json\/wp\/v2\/media?parent=4163"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/sysdba.org\/en\/wp-json\/wp\/v2\/categories?post=4163"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/sysdba.org\/en\/wp-json\/wp\/v2\/tags?post=4163"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}