Version: 8.3All Creatio products

Deploy PostgreSQL database (Linux)

Deploy Creatio on PostgreSQL to enable database hosting on Debian-based Linux infrastructure, including Ubuntu Server. If you use AWS RDS, follow the instructions: Deploy PostgreSQL database (AWS RDS).

The general database deployment procedure for PostgreSQL on Linux is as follows:

Install PostgreSQL. Read more >>>
Create PostgreSQL users. Read more >>>
Create and restore the database. Read more >>>
Configure max connections (optional). Read more >>>
Change database ownership (optional). Read more >>>

1. Install PostgreSQL

Creatio requires PostgreSQL 16 or later. If PostgreSQL 16 is not available in your distribution's repositories, add the PostgreSQL Apt Repository and import its signing key to install the required version.

Use one of the following tools to deploy Creatio:

Remote DBMS (recommended)
Local PostgreSQL server

If you already have a PostgreSQL server set up, skip to step 2. Read more >>>

To install PostgreSQL on Debian-based Linux distributions:

If PostgreSQL 16 is not available in your standard repository, add the PostgreSQL repository and import its signing key:

echo -e "deb http://apt.postgresql.org/pub/repos/apt/ $(lsb_release -sc)-pgdg main" > /etc/apt/sources.list.d/pgdg.list
wget --quiet -O - https://www.postgresql.org/media/keys/ACCC4CF8.asc | apt-key add -

Update the package lists:
```
apt-get update
```
Install PostgreSQL:
```
apt-get install -y postgresql-16
```
Log out from your root session:
```
exit
```

If you want to set up PostgreSQL clustering, follow the official vendor documentation. Learn more: High Availability, Load Balancing, and Replication Table (official PostgreSQL documentation).

2. Create PostgreSQL users

If you have already set up a superuser and a regular user, skip to step 3. Read more >>>

Create the following database users for PostgreSQL deployment:

A superuser who has maximum access privileges on the database server level. This user restores the Creatio database from a backup file and assigns access permissions.
A regular user who has limited permissions. This user becomes the owner of the restored database and is used in Creatio database connection strings during runtime.

Switch to the postgres system user and run the PostgreSQL interactive terminal:

sudo su - postgres
psql

1. Set up superuser

The examples below use creatio_superuser and creatio_superuser_password. Replace them with custom values everywhere they appear in the subsequent SQL script.

To create a superuser who has maximum access privileges:

Create the superuser:
```
CREATE USER creatio_superuser;
```

Grant superuser privileges:

ALTER ROLE creatio_superuser WITH SUPERUSER;

Allow the superuser to log in:

ALTER ROLE creatio_superuser WITH LOGIN;

Set the superuser password:

ALTER ROLE creatio_superuser WITH PASSWORD 'creatio_superuser_password';

2. Set up regular user

The examples below use creatio_user and creatio_user_password. Replace them with custom values in the subsequent SQL script and in the "ConnectionStrings.config" file in the root Creatio directory.

To create a regular user who has limited permissions:

Create the regular user:
```
CREATE USER creatio_user;
```
Allow the regular user to log in:
```
ALTER ROLE creatio_user WITH LOGIN;
```

Set the regular user password:

ALTER ROLE creatio_user WITH PASSWORD 'creatio_user_password';

Exit the PostgreSQL environment:
```
\q
exit
```

3. Create and restore the database

To restore a PostgreSQL database from a backup file, you need the PostgreSQL interactive terminal and PostgreSQL restore utility. Both are part of the postgresql-client-common package.

Depending on your setup, ensure the postgresql-client-common package is available:

If you installed postgresql-16 locally using apt-get, the postgresql-client-common package is already installed as a dependency by the Advanced Package Tool (APT).
If you plan to use a remote PostgreSQL database without installing the PostgreSQL DBMS on your server, install the postgresql-client-common package manually:
```
sudo apt-get install postgresql-client-common
```

1. Create database

The example below uses creatio_superuser_password. Replace it with a custom value.

Set the superuser password in the environment variable:
```
export PGPASSWORD=creatio_superuser_password
```
Connect to the PostgreSQL server and create a database that has an arbitrary name and the owner set to the creatio_user:
```
psql --host=pg_server_host --port=pg_server_port --username=creatio_superuser --dbname=pg_admin_db -c "CREATE DATABASE creatiodb WITH OWNER = creatio_user ENCODING = 'UTF8' CONNECTION LIMIT = -1"
```
Where:
- pg_server_host is the PostgreSQL server host.
- pg_server_port is the PostgreSQL server port.
- creatio_superuser is the superuser for connecting to the PostgreSQL server.
- pg_admin_db is the name of the PostgreSQL database to connect to for executing the command. If no databases exist yet or if an attempt to connect triggers the "FATAL: database "pg_admin_db" does not exist" error, use the default database template1.
- creatiodb is the name of the PostgreSQL database that hosts Creatio tables.
- creatio_user is the regular user who will be granted permission to use and update the Creatio database.

2. Restore the database

Restore the database from the "*.backup" file with the --no-owner and --no-privileges parameters.

Navigate to the database backup directory. The database backup file is supplied together with Creatio installation files and is located in the db directory of the root Creatio directory:
```
cd path_to_root_Creatio_directory/db
```
Where path_to_root_Creatio_directory is the path to the root Creatio directory.
Restore the database from the backup file:
```
pg_restore --host pg_server_host --port pg_server_port --username=creatio_superuser --dbname=creatiodb --no-owner --no-privileges --verbose path_to_db.backup
```
Where:
- pg_server_host is the PostgreSQL server host.
- pg_server_port is the PostgreSQL server port.
- creatio_superuser is the superuser for connecting to the PostgreSQL server.
- creatiodb is the name of the PostgreSQL database to restore to. Use the name you specified in the CREATE DATABASE command.
- path_to_db.backup is the path to the database backup file.

3. Create type casts

Create type casts via one of the following ways:

Download and execute the CreateTypeCastsPostgreSql.sql SQL script:

Download: CreateTypeCastsPostgreSql.sql.

psql --host=pg_server_host --port=pg_server_port --username=creatio_superuser --dbname=creatiodb --file=path_to_CreateTypeCastsPostgreSql.sql

Where:

pg_server_host is the PostgreSQL server host.
pg_server_port is the PostgreSQL server port.
creatio_superuser is the superuser for connecting to the PostgreSQL server.
creatiodb is the name of the PostgreSQL database to restore to.
path_to_CreateTypeCastsPostgreSql.sql is the path to the "CreateTypeCastsPostgreSql.sql" file.

Run the following SQL script as a single batch or individually:

DROP CAST IF EXISTS (varchar AS integer);
CREATE CAST (varchar AS integer)
    WITH
        INOUT AS IMPLICIT;

DROP CAST IF EXISTS (varchar AS uuid);
CREATE CAST (varchar AS uuid)
    WITH
        INOUT AS IMPLICIT;

DROP CAST IF EXISTS (text AS integer);
CREATE CAST (text AS integer)
    WITH
        INOUT AS IMPLICIT;

DROP CAST IF EXISTS (uuid AS text);
CREATE CAST (uuid AS text)
    WITH
        INOUT AS IMPLICIT;

DROP CAST IF EXISTS (text AS boolean);
CREATE CAST (text AS boolean)
    WITH
        INOUT AS IMPLICIT;

DROP CAST IF EXISTS (text AS numeric);
CREATE CAST (text AS numeric)
    WITH
        INOUT AS IMPLICIT;

DROP CAST IF EXISTS (text AS uuid);
CREATE CAST (text AS uuid)
    WITH
        INOUT AS IMPLICIT;

DROP FUNCTION IF EXISTS public."fn_CastTimeToDateTime" CASCADE;
CREATE FUNCTION public."fn_CastTimeToDateTime"(
    sourceValue TIME WITHOUT TIME ZONE
)
RETURNS TIMESTAMP WITHOUT TIME ZONE
AS $BODY$
BEGIN
    RETURN (MAKE_DATE(1900, 01, 01) + sourceValue)::TIMESTAMP WITHOUT TIME ZONE;
END;
$BODY$
LANGUAGE PLPGSQL;

DROP CAST IF EXISTS (TIME WITHOUT TIME ZONE AS TIMESTAMP WITHOUT TIME ZONE);
CREATE CAST (TIME WITHOUT TIME ZONE AS TIMESTAMP WITHOUT TIME ZONE)
    WITH
        FUNCTION public."fn_CastTimeToDateTime"(
            TIME WITHOUT TIME ZONE
        ) AS IMPLICIT;

When the SQL script runs, PostgreSQL may display warnings for casts that already exist. These warnings are expected and can be ignored.

As a result, the PostgreSQL database will be restored and ready for Creatio deployment.

4. Configure max connections (optional)

Important

Take these steps only if your Creatio deployment is going to serve more than 100 simultaneous users.

Calculate max_connections parameter value based on the expected number of simultaneous users:
```
Expected number of users * 1.5
```
If the result is less than 1024, use the resulting number. If the result is 1024 or greater, set the parameter to 1024.
Calculate shared_buffers parameter value based on the max_connections value:
```
max_connections * 0.24MB
```
Set the parameter value to MB and round the resulting value up. For example, if max_connections is 1024, shared_buffers is 245.76MB rounded up, i.e., 246MB.
Calculate kernel.shmmax parameter value based on the shared_buffers value:
```
shared_buffers + 16MB
```
Convert the resulting value to bytes. For example, if shared_buffers is 246MB, kernel.shmmax is 274 726 912.

Set the calculated values in the following parameters of the configuration files:

max_connections of the "postgresql.conf" file
shared_buffers of the "postgresql.conf" file
kernel.shmmax of the "/etc/sysctl.conf" file

To locate the "postgresql.conf" file, run the following command in the PostgreSQL interactive terminal:

SHOW config_file;

5. Change database ownership (optional)

By default, the user who runs the pg_restore command (typically "postgres") remains the owner of the database and its objects. If you want a regular user to own the database instead of the superuser, follow the steps below.

To change the database ownership:

Change the database owner to the regular user:
```
psql --host pg_server_host --port pg_server_port --username=creatio_superuser --dbname=pg_admin_db --command "ALTER DATABASE creatiodb OWNER TO creatio_user"
```
Where:
- pg_server_host is the PostgreSQL server host.
- pg_server_port is the PostgreSQL server port.
- creatio_superuser is the superuser for connecting to the PostgreSQL server. This user must be an administrator (superuser) or have the ALTER DATABASE privileges.
- creatio_user is the new database owner. This user is used to set up a connection to the Creatio database.
- creatiodb is the name of the database whose owner is being changed.
Download and run the SQL script to transfer ownership of all database objects to the regular user:

To change the database ownership, use the database ownership transfer SQL script.
- For Postgres version 11 and later: Download the script.
- For Postgres version 10 and earlier: Download the script.
```
psql --host pg_server_host --port pg_server_port --username=creatio_superuser --dbname=creatiodb --file=path_to_database_ownership_transfer_script.sql --variable owner=creatio_user --variable ON_ERROR_STOP=1
```
Where:
- pg_server_host is the PostgreSQL server host.
- pg_server_port is the PostgreSQL server port.
- creatio_superuser is the superuser for connecting to the PostgreSQL server. This user must be an administrator (superuser) or the Creatio database owner.
- creatio_user is the new database owner. This user is used to set up a connection to the Creatio database.
- creatiodb is the name of the database whose owner is being changed.
- path_to_database_ownership_transfer_script.sql is the path to the previously saved database ownership transfer script.

1. Install PostgreSQL​

2. Create PostgreSQL users​

1. Set up superuser​

2. Set up regular user​

3. Create and restore the database​

1. Create database​

2. Restore the database​

3. Create type casts​

4. Configure max connections (optional)​

5. Change database ownership (optional)​

See also​