Version: 8.1All Creatio products

Deploy PostgreSQL database (Linux)

Use one of two database configurations to deploy Creatio:

Use a remote DBMS (recommended)
Use a local PostgreSQL server.

If you already have a PostgreSQL server running on the intended machine, skip to step II.

If you have set up sysadmin (with privileges to log in, create and modify databases) and public (unprivileged) user roles, skip to step III.

I. Install PostgreSQL

PostgreSQL is unavailable in most standard repositories. To install PostgreSQL on Linux:

Add the PostgreSQL repository:

echo -e "deb http://apt.postgresql.org/pub/repos/apt/ $(lsb_release -sc)-pgdg main" > /etc/apt/sources.list.d/pgdg.list

Import the signing key of the PostgreSQL repository:

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-12
```
Log out from your root session:
```
exit
```

note

Please refer to the PostgreSQL documenation for details on PostgreSQL clustering.

II. Create PostgreSQL user

A fresh installation of PostgreSQL is not ready for deploying Creatio immediately. If you plan to use a fresh installation of PostgreSQL, you need to create a user that can log in to the database using a password and has sufficient privileges to create and update a database. By default, no such user will be available.

We recommend to create two database users in PostgreSQL:

A user with the “sysadmin” role, who has maximum access privileges on the database server level. This user will restore the Creatio database from a backup file and assign access permissions.
A user with the “public” role, whose permissions are limited. You will need this user to set up a secure connection to the restored Creatio database using PostgreSQL authentication.

If your PostgreSQL instance already has sysadmin (privileged) and public (unprivileged) user roles, skip this step.

To create PostgreSQL users:

Log in as postgres:
```
sudo su - postgres
```
Open PostgreSQL shell:
```
psql
```
Create a sysadmin user:
```
CREATE USER pg_sysadmin;
```
pg_sysadmin – user who will be granted sysadmin privileges. This user will restore the Creatio database from a backup file and assign access permissions
Make pg_sysadmin a system administrator:
```
ALTER ROLE pg_sysadmin WITH SUPERUSER;
```
Allow pg_sysadmin to log in:
```
ALTER ROLE pg_sysadmin WITH LOGIN;
```
Set a password for pg_sysadmin:
```
ALTER ROLE pg_sysadmin WITH PASSWORD 'pg_syspassword';
```
pg_password – sysadmin user password for connecting to the PostgreSQL server.
Create a public user:
```
CREATE USER pg_user;
```
pg_user – public user for connecting to the PostgreSQL server. You will need this user to set up a connection to the restored Creatio database.
Allow pg_user to log in:
```
ALTER ROLE pg_user WITH LOGIN;
```
Set a password for pg_user:
```
ALTER ROLE pg_user WITH PASSWORD 'pg_password';
```
pg_password – public user password for connecting to the PostgreSQL server.
Exit the PostgreSQL shell:

\q

Log out from your postgres session:

exit

III. Restore PostgreSQL database

To restore a PostgreSQL database from a backup file, you will need psql and pg_restore utilities. Both are part of the postgresql-client-common package.

If you install postgresql-12 locally using apt-get, APT will install postgresql-client-common as a dependency for postgresql-12.

If you plan to use a remote PostgreSQL database without installing the PostgreSQL DBMS on your server, install the postgresql-client-common package manually by running:

sudo apt-get install postgresql-client-common

To restore the Creatio database from a backup file:

Enter DB connection password in the environment variable:
```
export PGPASSWORD=pg_syspassword
```
pg_syspassword – pg_sysadmin user password for connecting to the PostgreSQL server.
Create a database where the backup data will be restored:
```
psql --host=pg_server_address --port=pg_server_port --username=pg_sysadmin --dbname=pg_dbname -c "CREATE DATABASE pg_dbname_creatio WITH OWNER = pg_user ENCODING = 'UTF8' CONNECTION LIMIT = -1"
```
pg_server_address – PostgreSQL server address

pg_server_port – PostgreSQL server port

pg_sysadmin – sysadmin user for connecting to the PostgreSQL server

pg_dbname – name of the PostgreSQL DB where the instructions will be executed

note
If you have not created any databases yet or an attempt to connect to a database triggers the “FATAL: database "pg_dbname" does not exist” error, use the default database “template1”.

pg_dbname_creatio – name of the PostgreSQL DB which will host Creatio tables

pg_user – the "public" user who will be granted permission to use and update the Creatio database
If you are using AWS RDS:
1. Download the ChangeTypesOwner.sql script.
2. In the script, replace the “postgres” value with a valid Postgres username.
3. Run the updated ChangeTypesOwner.sql script.
Navigate to the application directory:
```
cd /path/to/application/directory/
```
/path/to/application/directory/ – the directory with Creatio setup files.
Navigate to the database directory:
```
cd db
```
Restore the database from the backup file:
```
pg_restore --host pg_server_ip --port pg_server_port --username=pg_sysadmin --dbname=pg_dbname_creatio --no-owner --no-privileges --verbose \path\to\db.backup
```
pg_server_address – PostgreSQL server address

pg_server_port – PostgreSQL server port

pg_sysadmin – sysadmin user for connecting to the PostgreSQL server

pg_dbname_creatio – name of the PostgreSQL DB to insert backup tables. Use the name you specified in the "CREATE DATABASE" command on step 2.
Download the CreateTypeCastsPostgreSql.sql file.
Execute type conversion:
```
psql --host=pg_server_address --port=pg_server_port --username=pg_sysadmin --dbname=pg_dbname_creatio --file=/path/to/CreateTypeCastsPostgreSql.sql
```
pg_server_ip – PostgreSQL server address

pg_server_port – PostgreSQL server port

pg_sysadmin – sysadmin user for connecting to the PostgreSQL server

pg_dbname_creatio – name of the PostgreSQL DB where the instructions will be executed

/path/to/CreateTypeCastsPostgreSql.sql – path to the CreateTypeCastsPostgreSql.sql file.

IV. Configure max connections (optional)

Important

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

To configure max connections, set up the following parameters:

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

Run the following command to find the path to the postgresql.conf file:

SHOW config_file

Set max_connections parameter based on the expected number of simultaneous users. Calculate the parameter value using the following formula:
```
Expected number of users * 1.5
```
If the result is less or equals 1024, use the resulting number. If the result is more or equals 1024, set the parameter to 1024.
Set shared_buffers based on the max_connections value. Calculate the parameter value using the following formula:
```
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.
Set kernel.shmmax based on the shared_buffers value. Calculate the parameter value using the following formula:
```
shared_buffers + 16MB
```
Convert the resulting value to bytes. For example, if shared_buffers is 246MB, kernel.shmmax is 274 726 912.

V. Change the database owner (optional)

Creatio lets you replace the owner of the database and its objects to a non-administrator user (not a superuser) during the restoration. Use the ChangeDbObjectsOwner script for that. For Postgres version 10 and earlier: Download the script. For Postgres version 11 and later: Download the script.

To restore the database on behalf of a non-administrator user:

Replace the database owner:
```
psql --host pg_server_address --port pg_server_port --username=pg_sysadmin --dbname=pg_dbname –-command "ALTER DATABASE pg_dbname_creatio OWNER TO pg_user"
```
pg_server_address – PostgreSQL server address

pg_server_port – PostgreSQL server port

pg_sysadmin – sysadmin user for connecting to the PostgreSQL server This user must be an administrator (superuser) or have the “ALTER DATABASE” privileges.

pg_user – the placeholder to replace with the actual username of the new database owner. You will need this user to set up a connection to the Creatio database.

pg_dbname_creatio – the name of the database whose owner is being changed.
Replace the owner of the database objects:
```
psql --host pg_server_address --port pg_server_port --username=pg_sysadmin --dbname=pg_dbname_creatio --file=/path/toChangeDbObjectsOwner.sql --variable owner=pg_user --variable ON_ERROR_STOP=1
```
pg_server_address – PostgreSQL server address

pg_server_port – PostgreSQL server port

pg_sysadmin – sysadmin user for connecting to the PostgreSQL server This user must be an administrator (superuser) or the Creatio database owner

pg_user – the placeholder to replace with the actual username of the new database owner. You will need this user to set up a connection to the Creatio database.

pg_dbname_creatio – the name of the database whose owner is being changed.

/path/toChangeDbObjectsOwner.sql – the path to the previously saved toChangeDbObjectsOwner.sql file.

You can ignore this step. In that case, the user who ran the pg_restore command will remain the owner of the database and its objects. Normally, this is the postgres user.

Deploy PostgreSQL database (Linux)

I. Install PostgreSQL​

II. Create PostgreSQL user​

III. Restore PostgreSQL database​

IV. Configure max connections (optional)​

V. Change the database owner (optional)​

See also​

I. Install PostgreSQL

II. Create PostgreSQL user

III. Restore PostgreSQL database

IV. Configure max connections (optional)

V. Change the database owner (optional)

See also