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
In Persistence module configuration, setting Inline Resource Storage Below Size (chars) property to a low number or zero can change the way FHIR resources are stored in PostgreSQL in a significant way.
PostgreSQL can store large objects in an internal table, named pg_largeobject. Any table record referring to a large object record only needs to keep an internal id (OID) to it.
Inline Resource Storage Below Size (chars) property value is used to determine if a resource body text is stored inline in the FHIR HFJ_RES_VER table, if the number of characters of the resource body text is lower than this threshold value, or as a large object in pg_largeobject internal table.
For example, when Inline Resource Storage Below Size (chars) is set to zero, it means the body text of all resources will be kept as large objects, instead of being stored inline in the FHIR HFJ_RES_VER table.
Using pg_largeobject table extensively has two potential effects:
pg_largeobject table, large object records that are no longer referenced by any other table record, that can waste a significant amount of disk space if they are not cleared up 2. some deleted / expunged FHIR resources could be unwittingly kept in pg_largeobject table and could be accessed using a database clientTo understand why it is happening, see PostgreSQL large objects module implementation details. HAPI FHIR / Smile CDR is not using lo_manage trigger for obvious performance and maintenance reasons, as it doesn't solve orphaned entries in pg_largeobject table completely.
To make sure these entries are cleared up, you should run PostgreSQL vacuumlo command on a regular basis.
It is especially important that Inline Resource Storage Below Size (chars) property is set to a proper value and that vacuumlo command is run regularly for PostgreSQL instances hosted in the cloud, to reduce cost and security risk.
See Resource Body Storage and Inline Resource Storage Below Size (chars) for more details about this setting.
Important note: changing the Inline Resource Storage Below Size (chars) property value does not affect the location of the FHIR resources currently stored. It will only change the location of the new FHIR resources stored afterward.