PostgreSQL database deployment
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 setup files are available for download at postgresql.org.
Note
High-availability PostgreSQL configurations have not been tested with Creatio. Please refer to the PostgreSQL documenation for details on PostgreSQL clustering.
II. Create PostgreSQL user
A fresh installation of PostgreSQL Server is not ready for deploying Creatio immediately. If you plan to use a fresh installation of PostgreSQL Server, 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.
To create the two PostgreSQL users:
1.Open Command Prompt.
2.Navigate to the PostgreSQL software setup folder:
cd /D "\\path\to\PostgreSQL\folder"
•\\path\to\PostgreSQL\folder – the path to the PostgreSQL software setup folder.
3.Navigate to the folder with Command Line Tools:
cd bin
4.Enter DB connection password in the environment variable.
set PGPASSWORD=pg_password
•pg_password – password of the postgres user for connecting to the PostgreSQL server.
5.Run PostgreSQL shell as postgres:
psql.exe --username postgres
6.Create a sysadmin user:
CREATE USER pg_sysadmin;
•pg_sysadmin – sysadmin user for connecting to the PostgreSQL server. This user will restore the Creatio database from a backup file and assign access permissions.
7.Make pg_sysadmin a system administrator:
ALTER ROLE pg_sysadmin WITH SUPERUSER;
8.Allow pg_sysadmin to log in:
ALTER ROLE pg_sysadmin WITH LOGIN;
9.Set a password for pg_sysadmin:
ALTER ROLE pg_sysadmin WITH PASSWORD 'pg_syspassword';
•pg_syspassword – sysadmin user password for connecting to the PostgreSQL server.
10.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.
11.Allow pg_user to log in:
ALTER ROLE pg_user WITH LOGIN;
12.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.
13.Exit the PostgreSQL shell:
\q
III. Restore PostgreSQL database
To restore a PostgreSQL database from a backup file, you will need psql.exe and pg_restore.exe utilities. Both are part of the Command Line Tools PostgreSQL component that comes with the PostgreSQL Server. They are located in the PostgreSQL software setup folder.
If you plan to use a remote PostgreSQL database without installing the PostgreSQL Server on your machine, take the following steps:
1.Get a PostgreSQL binary package. Binary packages are available for download at postgresql.org.
2.Select the Command Line Tools component during installation. Selecting the other components is optional.
To restore the Creatio database from a backup file:
1.Open Command Prompt.
2.Navigate to the PostgreSQL software setup folder:
cd /D "\\path\to\PostgreSQL\folder"
•\\path\to\PostgreSQL\folder – the path to the PostgreSQL software setup folder.
3.Navigate to the folder with executables:
cd bin
4.Enter DB connection password in the environment variable:
set PGPASSWORD=pg_syspassword
•pg_syspassword – pg_sysadmin user password for connecting to the PostgreSQL server.
5.Create a database where the backup data will be restored.
For Creatio version 7.16.3 or higher:
psql.exe --host pg_server_address --port pg_server_port --username=pg_sysadmin -–command "CREATE DATABASE pg_dbname_ceatio WITH OWNER = pg_user ENCODING = 'UTF8' CONNECTION LIMIT = -1"
•pg_server_address – PostgreSQL server address.
•pg_server_port – PostgreSQL server port.
•pg_sysadmin – user for connecting to the PostgreSQL server. The user must have either superuser (administrator) privileges or “CREATE DATABASE” privileges.
•pg_user – the application will use this user’s credentials to connect to the database. You can specify any user when creating the database. To change the user data, follow step 10 of this instruction.
For Creatio version 7.16.0 – 7.16.2:
psql.exe --host pg_server_address --port pg_server_port --username=pg_sysadmin -–command "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 – user for connecting to the PostgreSQL server. The user must have either superuser (administrator) privileges or “CREATE DATABASE” privileges.
•pg_user – the "public" user who will be granted permission to use and update the Creatio database
6.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.
7.Restore the Creatio database from a backup file:
For Creatio version 7.16.3 or higher:
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_ip – PostgreSQL server address.
•pg_server_port – PostgreSQL server port.
•pg_sysadmin – user for connecting to the PostgreSQL server. The user must have either superuser (administrator) privileges or sufficient access permissions to run the pg_restore utility.
•pg_dbname_creatio – name of the PostgreSQL DB where the instructions will be executed.
For Creatio version 7.16.0 – 7.16.2:
pg_restore.exe --host pg_server_address --port pg_server_port --username=pg_sysadmin --dbname=pg_dbname_creatio --verbose \\path\to\db.backup
•pg_server_address – PostgreSQL server address
•pg_server_port – PostgreSQL server port
•pg_sysadmin – user for connecting to the PostgreSQL server. The user must have either superuser (administrator) privileges or sufficient access permissions to run the pg_restore utility.
•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.
8.Download the CreateTypeCastsPostgreSql.sql file.
9.Execute type conversion:
psql.exe --host pg_server_ip --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 – user with administrator privileges 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 downloaded CreateTypeCastsPostgreSql.sql file.
10.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 the script.
To restore the database from a backup as a regular user:
a.Change the owner of the database:
psql.exe --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 – PostgreSQL server address.
•pg_server_port – PostgreSQL server port.
•pg_sysadmin – user for connecting to the PostgreSQL server. The user must have either administrator (superuser) privileges or “CREATE DATABASE” privileges.
•pg_user – new database owner.
•pg_dbname_creatio – the name of the database whose owner is changed.
b.Change the owner of the database objects:
psql.exe --host pg_server_ip --port pg_server_port --username=pg_sysadmin --dbname=pg_dbname_creatio --file=\\path\to\ChangeDbObjectsOwner.sql --variable owner=pg_user --variable ON_ERROR_STOP=1
•pg_server_ip – PostgreSQL server address.
•pg_server_port – PostgreSQL server port.
•pg_sysadmin – user for connecting to the PostgreSQL server. The user must have either administrator (superuser) privileges or “CREATE DATABASE” privileges.
•pg_user – new database owner.
•pg_dbname_creatio – the name of the database whose owner is changed
•\\path\to\ChangeDbObjectsOwner.sql – 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”).
See also
•Set up Creatio caching server
