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.
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
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" />
