Version: 8.1All Creatio products

Deploy PostgreSQL database (Windows)

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 set up, skip to step II.

If you have already 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 documentation 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. These instructions use pg_sysadmin as a placeholder username, but you can set the username to any value.
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. These instructions use pg_user as a placeholder username, but you can set the username to any value.

To create the two PostgreSQL users:

Open the Command Prompt.
Navigate to the PostgreSQL software install folder:
```
cd /D "\path\to\PostgreSQL\folder"
```
\path\to\PostgreSQL\folder – the path to the PostgreSQL software install folder.
Navigate to the folder with the Command Line Tools component:
```
cd bin
```
Enter the DB connection password in the environment variable.
```
set PGPASSWORD=pg_password
```
pg_password – password of the postgres user for connecting to the PostgreSQL server.
Run PostgreSQL shell as postgres:
```
psql.exe --username postgres
```
Create a sysadmin user, e. g. pg_sysadmin:
```
CREATE USER pg_sysadmin;
```
pg_sysadmin – placeholder name for a sysadmin user. The sysadmin 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_syspassword – sysadmin user password for connecting to the PostgreSQL server.
Create a public user, e. g. pg_user:
```
CREATE USER pg_user;
```
pg_user – placeholder name for a public user. This user will 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
```

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 install folder.

If you plan to use a remote PostgreSQL database without installing the PostgreSQL Server on your machine, take the following steps:

Get a PostgreSQL binary package. Binary packages are available for download at postgresql.org.
Select the Command Line Tools component during installation. Selecting the other components is optional.

To restore the Creatio database from a backup file:

Open Command Prompt.
Navigate to the PostgreSQL software install folder:
```
cd /D "\path\to\PostgreSQL\folder"
```
\path\to\PostgreSQL\folder – the path to the PostgreSQL software install folder.
Navigate to the folder with executables:
```
cd bin
```
Enter the DB connection password in the environment variable:
```
set PGPASSWORD=pg_syspassword
```
pg_syspassword – sysadmin user password for connecting to the PostgreSQL server.
Create a database where the backup data will be restored.
```
psql.exe --host pg_server_ip --port pg_server_port --username=pg_sysadmin -–command "CREATE DATABASE pg_dbname_ceatio WITH OWNER = pg_user ENCODING = 'UTF8' CONNECTION LIMIT = -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 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.
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.
Restore the Creatio 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_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 to insert backup tables.
Download the CreateTypeCastsPostgreSql.sql file.
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.
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:
1. 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.
1. 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)

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

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.

Deploy PostgreSQL database (Windows)

I. Install PostgreSQL​

II. Create PostgreSQL user​

III. Restore PostgreSQL database​

IV. Configure max connections (optional)​

See also​

I. Install PostgreSQL

II. Create PostgreSQL user

III. Restore PostgreSQL database

IV. Configure max connections (optional)

See also