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 sysadmin (with privileges to log in, create and modify databases) and public (unprivileged) user roles, 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
Note
High-availability PostgreSQL configurations have not been tested with Creatio. Please refer to the PostgreSQL documenation for details on PostgreSQL clustering.
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 “superuser” 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 regular user 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 superuser (privileged) and unprivileged user roles, skip this step.
To create PostgreSQL users:
1.Log in as postgres:
sudo su - postgres
2.Open PostgreSQL shell:
psql
3.Create a pg_sysadmin user who will be granted either superuser (administrator) privileges or a set of the most essential privileges only:
CREATE USER pg_sysadmin;
•pg_sysadmin – replace with the name of the privileged user. This user will restore the Creatio database from a backup file and assign access permissions
4.Make pg_sysadmin a system administrator:
ALTER ROLE pg_sysadmin WITH SUPERUSER;
Alternatively, grant the pg_sysadmin user essential privileges only instead of superuser privileges:
GRANT CREATE DATABASE TO pg_sysadmin;
GRANT ALTER DATABASE TO pg_sysadmin;
Attention
Using the pg_sysadmin user without superuser privileges may result in permission-based errors during the restoration of the Creatio database.
5.Allow pg_sysadmin to log in:
ALTER ROLE pg_sysadmin WITH LOGIN;
6.Set a password for pg_sysadmin:
ALTER ROLE pg_sysadmin WITH PASSWORD 'pg_syspassword';
•pg_password – replace with the password of the pg_sysadmin user.
7.Create a regular user:
CREATE USER pg_user;
•pg_user – replace with the name of user whose credentials the Creatio application will use to connect to the Creatio database.
8.Allow pg_user to log in:
ALTER ROLE pg_user WITH LOGIN;
9.Set a password for pg_user:
ALTER ROLE pg_user WITH PASSWORD 'pg_password';
•pg_password – replace with the password of the unprivileged pg_user.
10.Exit the PostgreSQL shell:
\q
11.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_syspassword
•pg_syspassword – replace with the password of the user with either superuser (administrator) privileges or “CREATE DATABASE” privileges.
2.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 -–command "CREATE DATABASE pg_dbname_ceatio WITH OWNER = pg_user ENCODING = 'UTF8' CONNECTION LIMIT = -1"
•pg_server_address – replace with the PostgreSQL server address.
•pg_server_port – replace with the PostgreSQL server port.
•pg_sysadmin – replace with the name of the user for connecting to the PostgreSQL server. The user must have either superuser (administrator) privileges or “CREATE DATABASE” privileges.
•pg_user – replace with the name of the user whose credentials the Creatio application will use to connect to the database. You can specify any user when creating the database. To change the user data, follow step IV of this instruction.
•pg_dbname – replace with the name of the PostgreSQL DB to connect to for running the command. The database must exist when you run this command.
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 – replace with the name of the database that the Creatio application will use. This database will be created after the command runs successfully.
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/ – replace with the directory with Creatio setup files.
5.Navigate to the database directory:
cd db
6.Restore the database from the backup file:
For Creatio version 7.16.3 or higher:
pg_restore --host pg_server_address --port pg_server_port --username=pg_sysadmin --dbname=pg_dbname_creatio --no-owner --no-privileges --verbose /path/to/db.backup
•pg_server_address – replace with the PostgreSQL server address.
•pg_server_port – replace with the PostgreSQL server port.
•pg_sysadmin – replace with the name of the user for connecting to the PostgreSQL server. The user must have either superuser (administrator) privileges or have sufficient permissions on the pg_dbname_creatio database.
•pg_dbname_creatio – replace with the name of the PostgreSQL DB to insert backup tables. Use the name you specified in the "CREATE DATABASE" command on step 2.
For Creatio version 7.16.0 – 7.16.2:
pg_restore --host pg_server_address --port pg_server_port --username=pg_sysadmin --dbname=pg_dbname_creatio --verbose /path/to/db.backup
•pg_server_address – replace with the PostgreSQL server address.
•pg_server_port – replace with the PostgreSQL server port.
•pg_sysadmin – replace with the name of the user for connecting to the PostgreSQL server. The user must have superuser (administrator) privileges.
•pg_dbname_creatio – replace with the name of the PostgreSQL DB to insert backup tables. Use the name you specified in the "CREATE DATABASE" command on step 2.
7.Download the CreateTypeCastsPostgreSql.sql file.
8.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 – replace with the PostgreSQL server address
•pg_server_port – replace with the PostgreSQL server port
•pg_sysadmin – replace with the name of the user for connecting to the PostgreSQL server. The user must either have superuser (administrator) privileges or be the owner of the Creatio database,
•pg_dbname_creatio – replace with the name of the PostgreSQL DB where the instructions will be executed
•/path/to/CreateTypeCastsPostgreSql.sql – replace with the path to the CreateTypeCastsPostgreSql.sql file.
As a result, the Creatio database will be deployed.
IV. Change the owner of the Creatio database (optional)
Creatio version 7.16.3 supports changing the owner of the database and database objects to a non-administrator user (i.e. not a superuser). To do this, use the ChangeDbObjectsOwner script. Download file.
To restore the database from a backup as a regular user:
1.Change the owner of the database:
psql --host pg_server_ip --port pg_server_port --username=pg_sysadmin --dbname=pg_dbname –-command "ALTER DATABASE pg_dbname_creatio OWNER TO pg_user"
•pg_server_ip – replace with the PostgreSQL server address.
•pg_server_port – replace with the PostgreSQL server port.
•pg_sysadmin – replace with the name of the user for connecting to the PostgreSQL server. The user must have either administrator (superuser) privileges or “ALTER DATABASE” privileges.
•pg_user – replace with the name of the new owner. The Creatio application will use this user’s credentials to connect to the database.
•pg_dbname_creatio – replace with the name of the database whose owner is changed.
2.Change the owner of the database objects:
psql --host pg_server_ip --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_ip – replace with the PostgreSQL server address.
•pg_server_port – replace with the PostgreSQL server port.
•pg_sysadmin – replace with the name of the user for connecting to the PostgreSQL server. The user must either have superuser (administrator) privileges or be the owner of the Creatio database.
•pg_user – replace with the name of the new owner. The Creatio application will use this user’s credentials to connect to the database.
•pg_dbname_creatio – replace with the name of the database whose owner is changed
•/path/to/ChangeDbObjectsOwner.sql – replace with the path to the downloaded ChangeDbObjectsOwner.sql file.
Skip this step to leave the default owner of the database and database objects, which is the user who runs the pg_restore utility (usually “postgres”).
Previous step
Next step
