PostgreSQL

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 thePostgreSQL website(例如https://www.postgresql.org/docs//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, seeHigh Availability in PostgreSQL Database.

Best Practice

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 alsomonitor the replication lag and takesteps to avoid it. For specific information about how to do this, read about thereplay_lagcolumn, 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.

Add the following line to/data/pg_hba.conf.For example, if you use PostgreSQL 12, the default location of the file is/etc/postgresql/12/main/pg_hba.conf.
```
host [artifactory_db_name] [artifactory_user] [cidr] md5
```
Example
```
host artifactory artifactory 123.456.78.90/32 md5
```
[cidr] is the single host or network segment you want to give access to.
Add the following line to/data/postgresql.conf listen_addresses='*'

You can also use a specific IP address for the PostgreSQL server to listen.
Restart PostgreSQL after adding the above changes.

You can check the PostgreSQL logs to see if the configuration is applied. The default location of the log is/var/log/postgresql/postgresql--main.log.

Page Contents

Creating the PostgreSQL Database

Supported PostgreSQL Versions

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:

创建一个Artifactory用户和数据库

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.

Artifactory Privileges

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

Stop the Artifactory service.

Edit the database connection details in thesystem.yaml configuration fileas follows.

shared: database: type: postgresql driver: org.postgresql.Driver url: jdbc:postgresql:///artifactory username: artifactory password: password

Start the Artifactory service.

Configuring Artifactory HA to Use PostgreSQL Database in HA

Available from Artifactory 7.31.10.

Stop the Artifactory service.

Edit thesystem.yamlfile to update the following values.

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

Theurlfield under theshareddatabase section in the following format.
```
jdbc:postgresql://,..., /artifactory?targetServerType=primary
```

Theurlfield under the元datadatabase section in the following format.

jdbc:postgresql://,..., /artifactory?target_session_attrs=read-write"

The following sample shows an examplesystem.yamlfile 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" ...

Start the Artifactory service.

Enabling TLS Encryption

To enable Transport Layer Security (TLS) encryption for PostgreSQL, set thesslmodeproperty toverify-fullin theJDBC connector URL.

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

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 anew certificate with SAN.

Content

Space Tools

JFrog Help Center

JFrog Help Center - A new knowledge experience is coming your way soon!