Deploy PostgreSQL database (Windows)

Products

All Creatio products

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.

For Creatio version 7.16.3 or higher:
```
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.
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
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:

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 to insert backup tables.
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.
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.
2. 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)