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 requires the database  to be created with the en_US.UTF-8 collation. The use of  C collation 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';

Correct settings provides the following result.

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

Incorrect settings provides the following result.

 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.