6.3 Configure PostgreSQL as Resource Manager

To use PostgreSQL as resource manager for XA transactions, you must enable prepared transactions and session affinity.

Skip this section if you don't want to use PostgreSQL as a resource manager.

By default, the value of max_prepared_transactions is set to 0 and prepared transactions are disabled. If you do not enable prepared transactions for PostgreSQL, you will receive the following error message when you start an XA transaction.

Exception: org.postgresql.util.PSQLException: ERROR: prepared transactions are disabled

1. Add the following dependency to your application's pom.xml file.

   <dependency>
   	<groupId>org.postgresql</groupId>
   	<artifactId>postgresql</artifactId>
   	<version>RELEASE</version>
   </dependency>

2. Connect to the database using pgAdmin 4 or any another PostgreSQL tool, and then run the following SQL statements. Set the value of max_prepared_transactions to a positive number, such as 100, to enable prepared transactions for PostgreSQL.

   SHOW max_prepared_transactions; 
   ALTER SYSTEM SET max_prepared_transactions = 100;

3. Restart the PostgreSQL service.

   services restart postgresql

4. Enable session affinity or sticky sessions for the transaction participant service that uses PostgreSQL as resource manager. When you enable session affinity, all the requests for a unique transaction or session are routed to the same endpoint or replica of the participant service that served the first request. See Enable Session Affinity.