Creatio administration
This documentation is valid for Creatio version 7.15.0. We recommend using the newest version of Creatio documentation.

Deploy Creatio database server via PostgreSQL on 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 a user role with privileges to log in, create and modify databases, skip to step III.

I. Install PostgreSQL

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

1.Log in as root:

sudo su

2.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

3.Import the signing key of the PostgreSQL repository:

wget --quiet -O - https://www.postgresql.org/media/keys/ACCC4CF8.asc | apt-key add -

4.Update the package lists:

apt-get update

5.Install PostgreSQL:

apt-get install -y postgresql-12

6.Log out from your root session:

exit

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.

To create a PostgreSQL user:

1.Log in as postgres:

sudo su - postgres

2.Open PostgreSQL shell:

psql

3.Create a user:

CREATE USER pg_user;

pg_user – user for connecting to the PostgreSQL server

4.Set permissions for pg_user. For example, make pg_user a superuser:

ALTER ROLE pg_user WITH SUPERUSER;

5.Allow pg_user to log in:

ALTER ROLE pg_user WITH LOGIN;

6.Set a password for pg_user:

ALTER ROLE pg_user WITH PASSWORD 'pg_password';

pg_password – user password for connecting to the PostgreSQL server.

7.Exit the PostgreSQL shell:

\q

8.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:

1.Enter DB connection password in the environment variable:

export PGPASSWORD=pg_password

pg_password – user password for connecting to the PostgreSQL server.

2.Create a database where the backup data will be restored:

psql --host=pg_server_address --port=pg_server_port --username=pg_user --dbname=pg_dbname -c "CREATE DATABASE pg_dbname_new WITH OWNER = pg_user ENCODING = 'UTF8' CONNECTION LIMIT = -1"

pg_server_address – PostgreSQL server address

pg_server_port – PostgreSQL server port

pg_user – 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_new – name of the PostgreSQL DB which will host Creatio tables

3.If you are using AWS RDS:

Download the ChangeTypesOwner.sql script.

In the script, replace the “postgres” value with a valid Postgres username.

Run the updated ChangeTypesOwner.sql script.

4.Navigate to the application directory:

cd /path/to/application/directory/

/path/to/application/directory/ – the directory with Creatio setup files.

5.Navigate to the database directory:

cd db

6.Restore the database from the backup file:

pg_restore --host=pg_server_address --port=pg_server_port --username=pg_user --dbname=pg_dbname --verbose \\path\to\db.backup

pg_server_address – PostgreSQL server address

pg_server_port – PostgreSQL server port

pg_user – user for connecting to the PostgreSQL server

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

Note

Use the name you specified in pg_dbname_new on step 2.

7.Download the CreateTypeCastsPostgreSql.sql file.

8.Execute type conversion:

psql --host=pg_server_address --port=pg_server_port --username=pg_user --dbname=pg_dbname --file=CreateTypeCastsPostgreSql.sql

pg_server_address – PostgreSQL server address

pg_server_port – PostgreSQL server port

pg_user – user for connecting to the PostgreSQL server

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

file – path to the CreateTypeCastsPostgreSql.sql file.

Set up the ConnectionStrings.config file

Use the following parameters in the ConnectionStrings.config file:

Db – the element that ensures connection with the database. In this element, you configure the path to the database you need to establish connection with and the method of authorization on the database server.

<add name="db" connectionString="Data Source=[The database server name];Initial Catalog=[The database name];Persist Security Info=True; MultipleActiveResultSets=True;[Authorization method on the database server]; Pooling = true; Max Pool Size = 100; Async = true" />

Redis – the element that ensures interaction with the Redis server.

<add name="redis" connectionString="host=[Servername];db=[Redis DB number];port=6379;maxReadPoolSize=10;maxWritePoolSize=500" />