PostgreSQL Overview

By using PostgreSQL, you can benefit from features in PostgreSQL infrastructure such as backup and restore.

Setting up a PostgreSQL Database in Single Node

For JFrog Artifactory to run with PostgreSQL as a single node you must create a dedicated PostgreSQL database instance and then configure Artifactory to use it as described in the following sections. Follow the instructions in the PostgreSQL website (for example https://www.postgresql.org/docs/ <Version>/tutorial-install.html) for details.

Setting up a PostgreSQL Database in High Availability

You can run an external PostgreSQL database in high availability for increased resiliency (available from Artifactory version 7.31.10 and Postgres 13). For information on setting up PostgreSQL database in high availability mode, see High Availability in PostgreSQL Database.

To avoid latency in the Artifactory database transactions, you should declare replication as asynchronous in PostgreSQL HA. Replication lag depends on several factors including setup, network latency and database load. In addition to declaring asynchronous replication, to minimize the potential for data loss in case of failover, you should also monitor the replication lag and take steps to avoid it . For specific information about how to do this, read about the replay_lag column, which approximates the delay before recent transactions become visible to queries.

Enabling PostgreSQL Connectivity from Remote Servers

The following is an example for enabling PostgreSQL connectivity from remote servers. Consult your security team for your organization's best practices.

  1. Add the following line to  <postgres_mount>/data/pg_hba.conf.

    host [artifactory_db_name] [artifactory_user] [cidr]    md5
     host  artifactory       artifactory     123.456.78.90/32 md5

    [cidr] is the single host or network segment you want to give access to.

  2. Add the following line to <postgres_mount>/data/postgresql.conf listen_addresses='*'

    You can also use a specific IP address for the PostgreSQL server to listen.

  3. Restart PostgreSQL after adding the above changes.

 

Creating the PostgreSQL Database

Artifactory supports PostgreSQL version 13.x and below (9.5 and 9.6 were EOL in 2021).

Use the commands below to create an Artifactory user and database with appropriate permissions. Modify the relevant values to match your specific environment:

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

Once you have verified that the script is correct, you need to run it to create the database and proceed with configuring the database.

We recommend providing Artifactory with full privileges on the database.


Configuring Artifactory to Use PostgreSQL

When you configure Artifactory to use PostgreSQL, all the artifact information is stored in PostgreSQL while the artifact binary data is stored in the file system (under $JFROG_HOME/artifactory/var/data/artifactory/filestore).

While it is possible to store BLOBs inside PostgreSQL we do  not recommend it. This is important because the PostgreSQL driver does not support streaming BLOBs with unknown length to the database. Therefore, Artifactory temporarily saves deployed files to the filesystem and only then saves the BLOB to the database.

Configuring Artifactory to Use PostgreSQL Single Node

  1. Stop the Artifactory service.

  2. Edit the database connection details in the system.yaml configuration file as follows.

    shared:
  database:
    type: postgresql
    driver: org.postgresql.Driver
    url: jdbc:postgresql://<your db url, for example: localhost:5432>/artifactory
    username: artifactory
    password: password


  3. Start the Artifactory service.

Configuring Artifactory HA to Use PostgreSQL Database in HA

Available from Artifactory 7.31.10.

  1. Stop the Artifactory service.

  2. Edit the system.yaml file to update the following values.

    Because Artifactory uses multiple drivers and you need to configure the connection strings for these separately.


    1. The url field under the shared database section in the following format.

      jdbc:postgresql://<PostgreSQL Database 1 URL>,..., <PostgreSQL Database N URL>/artifactory?targetServerType=primary

    2. The url field under the metadata database section in the following format.

      jdbc:postgresql://<PostgreSQL Database 1 URL>,..., <PostgreSQL Database N URL>/artifactory?target_session_attrs=read-write"

      The following sample shows an example system.yaml file configuration.

      systemYaml:
 shared:
   logging:
   ...
   database:
     type: postgresql
     url: "jdbc:postgresql://17.21.0.2:5432,17.21.0.3:5432/artifactory?targetServerType=primary"
     driver: org.postgresql.Driver
     username: "artifactory"
     password: "password"
 artifactory:
   Database:
 ...
 frontend:
 ...
 access:
 ...
 metadata:
   database:
     type: postgresql
     url: "jdbc:postgresql://17.21.0.2:5432,17.21.0.3:5432/artifactory?target_session_attrs=read-write"
     driver: org.postgresql.Driver
     username: "artifactory"
     password: "password"
...
  3. Start the Artifactory service.

Enabling TLS Encryption

To enable Transport Layer Security (TLS) encryption for PostgreSQL, set the sslmode property to verify-full in the .

For example, in the $JFROG_HOME/artifactory/var/etc/system.yaml file: 

shared:
  database:
    ...
    url:jdbc:postgresql://mypostgress.mydomain.com:5432/artifactory?ssl=true&sslfactory=org.postgresql.ssl.jdbc4.LibPQFactory&sslmode=verify-  
    full&sslrootcert=/tmp/server.crt
...

If you are using old certificates or have an AWS RDS instance that was created before July 2020, you will not have Subject Alternative Name (SAN) enabled. To resolve this issue, you will need to generate a new certificate with SAN.