Try JFrog
Guides
Contact SupportTry JFrog
Start typing to search…
  1. Install Individual Products
  2. Install
  3. Installing Xray

PostgreSQL for Xray

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

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

You can set up PostgreSQL for Xray in single node and HA configurations.

Set up an Xray PostgreSQL Database in Single Node

For JFrog Xray to run with PostgreSQL as a single node you must create a dedicated PostgreSQL database instance and then configure Xray 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.

Set up an Xray PostgreSQL Database in High Availability

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

📘

Best Practice

To avoid latency in the Xray 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.

Enable PostgreSQL Connectivity for Xray 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 [xray_db_name] [xray_user] [cidr]    md5

    Example

     host  xraydb       xray     123.456.78.90/32 md5
📘

Note

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

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

📘

Note

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

  1. Restart PostgreSQL after adding the above changes.

Create the Xray PostgreSQL Database

Use the following commands to create an Xray user and database with appropriate permissions. Modify the relevant values to match your specific environment:Creating an Xray User and Database

CREATE USER xray WITH PASSWORD 'password';
CREATE DATABASE xraydb WITH OWNER=xray ENCODING='UTF8' lc_collate='en_US.utf8' lc_ctype='en_US.utf8' template=template0;
GRANT ALL PRIVILEGES ON DATABASE xraydb TO xray;

Xray is tested on platforms where the database is created with the en_US.UTF-8 collation. Other collations do not have the same test coverage and may result in wrong sorting and unexpected behavior.

Run the following command to verify the collation settings.

select datname, pg_encoding_to_char(encoding) as encoding, datcollate, datctype 
from pg_database where datname='xraydb';

en_US.UTF8 settings provides the following result.

 datname | encoding | datcollate  |  datctype
---------+----------+-------------+-------------
 xraydb  | UTF8     | en_US.utf8  | en_US.utf8
(1 row)

Example result for other collations.

 datname | encoding | datcollate | datctype
---------+----------+------------+----------
 xraydb  | UTF8     | C          | C
(1 row)

If you use an RDS Amazon Aurora instance of PostgreSQL, run the following command in addition, where superUser is the RDS superuser that you use to log in to RDS.

GRANT superUser TO xray;

You can also run xray/app/third-party/postgresql/createPostgresUsers.sh in the installation folder.

Once you create the database and users, proceed with configuring the database.

Configure Xray to Use PostgreSQL Single Node

When you configure Xray to use PostgreSQL, all the information about components is stored in PostgreSQL.

  1. Stop the Xray service.

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

    shared:
  database:
    type: postgresql
    driver: org.postgresql.Driver
    url: "postgres://<DB URL>" #for example: localhost:5432>/xraydb?sslmode=disable
    username: xray
    password: password
📘

Note

Database URL for Xray is "postgres://<DB URL>", while for Artifactory it is "jdbc:postgresql://<DB URL>". Ensure that you use the correct notation.

  1. Start the Xray service.

Configure Xray HA to Use PostgreSQL Database in HA

Available from Xray 3.55.2

When you configure Xray to use PostgreSQL, all the information about components is stored in PostgreSQL.

  1. Stop the Xray service.

  2. Edit the system.yaml file to update the following values.Theurlfield under the shareddatabase section in the following format.

    "postgres://<PostgreSQL Database 1 URL>,..., <PostgreSQL Database N URL>/xraydb?sslmode=disable&target_session_attrs=read-write"

    The following sample shows an examplesystem.yamlfile configuration.

    systemYaml:
 shared:
   security
   ...
   logging:
   ...
   database:
     type: postgresql
     url: "postgres://10.90.100.110:5445,10.90.100.110:5446/xraydb?sslmode=disable&target_session_attrs=read-write"
     driver: org.postgresql.Driver
     username: xray
     password: xray
   jfrogUrl: <JFrog URL>
   security:
     joinKey: <Artifactory Join Key>
   rabbitMq:
     autoStop: true
     url: "amqp://localhost:5672/"
   ...

  3. Start the Xray service.

Support for pg_trgm Module

Starting from Xray 3.70.x versions, Xray utilizes the pg_trgm PostgreSQL module, which enhances Xray's performance. If you are using an external postgresql and this extension is not already installed, please install them manually.

This extension is not packaged until Xray version 3.84.x. If you are performing an upgrade from a previous xray version and if you are using bundled PostgreSQL, you will need to install the necessary packages manually based on the PostgreSQL version currently installed.

  1. Determine the version of bundled PostgreSQL currently installed.

  2. Visit the official PostgreSQL repository for RHEL/CentOS at the following URL: https://download.postgresql.org/pub/repos/yum/

  3. Download the appropriate PostgreSQL .contrib package version and install them.

Updated 2 days ago