ARTIFACTORY: How to configure an HA PostgreSQL Database with Artifactory?

Introduction:

PostgreSQL Database High Availability is a new database set-up supported by Artifactory 7.31.10 and above with PostgreSQL 13.x.

This database set-up is configured using primary and secondary nodes. 
Artifactory opens up connections to the PostgreSQL HA primary node, and the primary node replicates its data to the other PostgreSQL HA nodes.

As shown in the following diagram, the replication between the PostgreSQL nodes keeps them in sync in case the primary node fails.

 

When the primary node becomes unavailable, Artifactory will automatically connect to the next PostgreSQL node in the chain, which becomes the current “primary” node.

 

The motivation behind this database set-up is to enable automatic recovery from database failures which could prevent Artifactory system downtime.
 

Configuring the Artifactory with PostgreSQL HA connectivity:

Provided below is a generic example of setting up Artifactory with PostgreSQL HA

Prerequisites:

1. Set-up 2 or more PostgreSQL version 13.x servers
2. Create the Artifactory Databases on each of your PostgreSQL servers
3. Enable PostgreSQL Connectivity from Remote Servers

After completing the prerequisite steps, proceed with editing the $JFROG_HOME/artifactory/var/etc/system.yaml file to configure the JDBC URLs as follows:

 shared:
   database:
     type: postgresql
     url: "jdbc:postgresql://<PostgreSQL(Node1)-IP>:<PORT>,<PostgreSQL(Node2)-IP>:<PORT>,<PostgreSQL(Node3)-IP>:<PORT>/artifactory?targetServerType=primary"
     driver: org.postgresql.Driver
     username: "artifactory"
     password: "password"
 metadata:
   database:
     type: postgresql
     url: "jdbc:postgresql://<PostgreSQL(Node1)-IP>:<PORT>,<PostgreSQL(Node2)-IP>:<PORT>,<PostgreSQL(Node3)-IP>:<PORT>/artifactory?target_session_attrs=read-write"
     driver: org.postgresql.Driver
     username: "artifactory"
     password: "password"
...

In case that you are using an Artifactory HA cluster, please make sure to edit the system.yaml files of all of the Artifactory nodes to reflect the changes made.
 

Troubleshooting PostgreSQL HA setup using the Bitnami PostgreSQL HA image:

PostgreSQL HA requires some additional configuration compared to the PostgreSQL stand-alone instance, hence, it is more prone to configuration missteps.

When troubleshooting the PostgreSQL HA setup, the first thing to consider is whether the issue is with the Artifactory configuration or the PostgreSQL HA configuration, you may install the following PostgreSQL HA packaged by Bitnami pack and configure it to the Artifactory HA cluster (since it is verified to properly function with Artifactory).

After installing the above package, exec into the container and perform the following instructions (the below steps are for docker-compose):
1.  Execute the following command in order to access the container: 

docker-compose exec pg-0 bash

2.  Inside the container, run the following (password: “adminpassword”):

psql -U postgres

3.  Run the following queries on the PostgreSQL HA primary node (the first database in the URL chain)

CREATE USER artifactory WITH PASSWORD 'password';
CREATE DATABASE artifactory WITH OWNER=artifactory ENCODING='UTF8';
GRANT ALL PRIVILEGES ON DATABASE artifactory TO artifactory;

4.  Execute the below command to retrieve the locally mapped ports (as seen in the picture below):

docker-compose ps

  

5.  Create (or modify) PostgreSQL HA URL in the system.yaml file, using the ports retrieved in section (4), as shown below: 

  
 After executing the above instructions, you may start the Artifactory service.

To verify the functionality of the PostgreSQL HA setup, please execute the steps that are specified in the “Verification” section of this knowledge base article.

Conclusions:

If the above PostgreSQL HA configuration (using bitnami) is properly functioning, you can safely configure you own database.
Otherwise, check the Artifactory configuration (on the Artifactory side’s system.yaml file) or contact JFrog support.
 

Verification:

To verify that Artifactory successfully connected to your PostgreSQL HA setup and is replicating the data from the primary node to the secondary node(s), you should execute the following test:

2023-03-13T15:13:27.943Z [jfac ] [INFO ] [                ] [alConfigurationServiceBase:188] [c-default-executor-7] - Loading configuration from db finished successfully