I upgraded postgresql from 8.4.2 to 12.3. I created a dump of the db from the old version, and restored into the new version.
The port for the new version is 5532. The old one uses 5432. After I restore the dump, I check that the service is running for the new version, and the server is listening on port 5532 for all interfaces.
I even copy the pg_hba.conf entries from the old version to the new version. But instead of "password" I change all the entries to "md5" because this is a newer version. After changes, I reload pg_hba.conf.
However, when we test access to the new db now, it does not work. It fails with error :
Your login attempt was not successful, try again. Reason : Could not
get JDBC Connection; nested exception is
org.postgresql.util.PSQLException: FATAL: no pg_hba.conf entry for
host "192.168.130.209", user "app_support", database "bosupportdb"
Postgres ports :
[root@reportv2 data]# ss -tulpn | grep postgres
tcp LISTEN 0 128 *:5432 *:* users:(("postgres",pid=3454,fd=3))
tcp LISTEN 0 128 *:5532 *:* users:(("postgres",pid=4763,fd=3))
tcp LISTEN 0 128 :::5432 :::* users:(("postgres",pid=3454,fd=4))
tcp LISTEN 0 128 :::5532 :::* users:(("postgres",pid=4763,fd=4))
[root@reportv2 data]#
These are my pg_hba.conf entries :
# TYPE DATABASE USER CIDR-ADDRESS METHOD
# "local" is for Unix domain socket connections only
local all all md5
# IPv4 local connections:
host all all 127.0.0.1/32 md5
host all all 192.168.130.0/24 md5
#host all all 192.168.8.0/24 md5
host all all 192.168.90.8/32 md5
host all all 192.168.8.175/32 md5
host all all 192.168.112.0/23 md5
Please help me resolve this. I am clueless.
Best Answer
Make sure that the HBA rules that PostgreSQL is actually using are the ones you expect:
Check the error column for anything amiss with the file syntax.