Troubleshooting PostgreSQL
This page contains solutions to common problems when implementing Postgresql as a storage engine.
If an exception such as the following appears in the server logs:
org.postgresql.util.PSQLException: The hostname XXX.XXX.XXX.XXX could not be verified by hostnameverifier PgjdbcHostnameVerifier
Most recent distributions of Postgresql use TLS encryption for connection to the database by default. This is good security practice, but can cause issues if the database server is being accessed by IP address since the database client is not able to verify the identity of the server by default.
This issue is typically caused by using an IP address in the database connection URL, such as the example shown below:
jdbc:postgresql://127.168.1.15/cdr
There are several ways of solving this issue. The most secure solution to this issue is to obtain a TLS certificate for the server, and use the server's host name instead of its IP address in the JDBC connection URL.
An example JDBC URL using a host name is shown below. Note that it is not sufficient to simply use the host name in your URL; you will also need to install the appropriate certificates in both the client and the server.
jdbc:postgresql://database.example.org/cdr
If obtaining a TLS certificate for your database server hostname is impractical (e.g. the server is a development server with no fixed host name) you can disable hostname verification by adding the parameter sslmode=allow to your database connection URL. Disabling hostname verification means that the database client has no means to verify that it is actually talking to the intended server, so this should only be done within closed networks with careful consideration.
An example JDBC URL containing this parameter is shown below:
jdbc:postgresql://127.168.1.15/cdr
If you are experiencing hung connections (often due to firewalls and other network infrastructure), this can often be resolved by enabling TCP Keepalive.
Add the following to your connection URL:
These parameters are described here: https://jdbc.postgresql.org/documentation/93/connect.html