Deploy PostgreSQL database (Windows)

Deploy Creatio on PostgreSQL to enable database hosting on Windows-based infrastructure. Learn more about AWS RDS PostgreSQL deployment: Deploy PostgreSQL database (AWS RDS).

The general database deployment procedure for PostgreSQL on Windows is as follows:

1. Install PostgreSQL. Read more >>>
2. Create PostgreSQL users. Read more >>>
3. Create and restore the database. Read more >>>
4. Configure max connections (optional). Read more >>>
5. Change database ownership (optional). Read more >>>

Use one of the following tools to deploy Creatio:

• Remote DBMS (recommended)
• Local PostgreSQL server

1. Install PostgreSQL

If a PostgreSQL server is already set up, skip to step 2. Read more >>>

Before deploying PostgreSQL database, make sure you have PostgreSQL 16 or later installed. Otherwise, download PostgreSQL setup files (official PostgreSQL documentation).

If you want to set up PostgreSQL clustering, follow the official vendor documentation. Learn more: High Availability, Load Balancing, and Replication Table (official PostgreSQL documentation).

2. Create PostgreSQL users

If a superuser and a regular user are already set up, skip to step 3. Read more >>>

Create the following database users for PostgreSQL deployment:

• A superuser who has maximum access privileges on the database server level. This user restores the Creatio database from a backup file and assigns access permissions.
• A regular user who has limited permissions. This user becomes the owner of the restored database and is used in Creatio database connection strings during runtime.

Switch to the postgres system user and run the PostgreSQL interactive terminal:

1. Open the command-line interface.

2. Navigate to the PostgreSQL software installation directory:

   cd /D "path_to_PostgreSQL_directory"

   path_to_PostgreSQL_directory is the PostgreSQL installation directory.

3. Navigate to the directory with executables:

   cd bin

4. Set the postgres user password in the environment variable:

   set PGPASSWORD=postgres_password

   postgres_password is the postgres user password.

5. Run the PostgreSQL interactive terminal as postgres:

   psql.exe --username postgres

1. Set up superuser

The examples below use creatio_superuser and creatio_superuser_password. Replace them with custom values everywhere they appear in the subsequent SQL script.

To create a superuser who has maximum access privileges:

1. Create the superuser:

   CREATE USER creatio_superuser;

2. Grant superuser privileges:

   ALTER ROLE creatio_superuser WITH SUPERUSER;

3. Allow the superuser to log in:

   ALTER ROLE creatio_superuser WITH LOGIN;

4. Set the superuser password:

   ALTER ROLE creatio_superuser WITH PASSWORD 'creatio_superuser_password';

2. Set up regular user

The examples below use creatio_user and creatio_user_password. Replace them with custom values in the subsequent SQL script and in the "ConnectionStrings.config" file in the root Creatio directory.

To create a regular user who has limited permissions:

1. Create the regular user:

   CREATE USER creatio_user;

2. Allow the regular user to log in:

   ALTER ROLE creatio_user WITH LOGIN;

3. Set the regular user password:

   ALTER ROLE creatio_user WITH PASSWORD 'creatio_user_password';

4. Exit the PostgreSQL environment:

   \q

3. Create and restore the database

To restore a PostgreSQL database from a backup file, the psql.exe and pg_restore.exe utilities are required. Both are part of the Command Line Tools PostgreSQL component that comes with the PostgreSQL Server and are located in the PostgreSQL software installation directory.

Install PostgreSQL components:

1. Download a PostgreSQL interactive installer on the official vendor website.
2. Select the Command Line Tools component during installation. Selecting the other components is optional.

1. Create database

The example below uses "creatio_superuser_password." Replace it with a custom value.

1. Open Command Prompt.

2. Navigate to the PostgreSQL software installation directory:

   cd /D "path_to_PostgreSQL_directory"

3. Navigate to the directory with executables:

   cd bin

4. Set the superuser password in the environment variable:

   set PGPASSWORD=creatio_superuser_password

5. Connect to the PostgreSQL server and create a database that has an arbitrary name and the owner set to the creatio_user:

   psql.exe --host=pg_server_host --port=pg_server_port --username=creatio_superuser --dbname=pg_admin_db -c "CREATE DATABASE creatiodb WITH OWNER = creatio_user ENCODING = 'UTF8' CONNECTION LIMIT = -1"

   Where:

   • pg_server_host is the PostgreSQL server host.
   • pg_server_port is the PostgreSQL server port.
   • creatio_superuser is the superuser for connecting to the PostgreSQL server.
   • pg_admin_db is the name of the PostgreSQL database to connect to for executing the command. If no databases exist yet or if an attempt to connect triggers the 'FATAL: database "pg_admin_db" does not exist' error, use the default database template1.
   • creatiodb is the name of the PostgreSQL database that hosts Creatio tables.
   • creatio_user is the regular user who is granted permission to use and update the Creatio database.

2. Restore the database

Restore the database from the "*.backup" file with the --no-owner and --no-privileges parameters:

pg_restore --host=pg_server_host --port=pg_server_port --username=creatio_superuser --dbname=creatiodb --no-owner --no-privileges --verbose path_to_db.backup

Where:

• pg_server_host is the PostgreSQL server host.
• pg_server_port is the PostgreSQL server port.
• creatio_superuser is the superuser for connecting to the PostgreSQL server.
• creatiodb is the name of the PostgreSQL database to restore to. Use the name specified in the CREATE DATABASE command.
• path_to_db.backup is the path to the database backup file. The backup file is supplied together with Creatio installation files and is located in the db directory of the root Creatio directory.

3. Create type casts

Create type casts via one of the following ways:

Download and execute the CreateTypeCastsPostgreSql.sql SQL script:

Download: CreateTypeCastsPostgreSql.sql.

psql.exe --host=pg_server_host --port=pg_server_port --username=creatio_superuser --dbname=creatiodb --file=path_to_CreateTypeCastsPostgreSql.sql

Where:

• pg_server_host is the PostgreSQL server host.
• pg_server_port is the PostgreSQL server port.
• creatio_superuser is the superuser for connecting to the PostgreSQL server.
• creatiodb is the name of the PostgreSQL database to restore to.
• path_to_CreateTypeCastsPostgreSql.sql is the path to the "CreateTypeCastsPostgreSql.sql" file.

Run the following SQL script as a single batch or individually:

DROP CAST IF EXISTS (varchar AS integer);
CREATE CAST (varchar AS integer)
    WITH
        INOUT AS IMPLICIT;

DROP CAST IF EXISTS (varchar AS uuid);
CREATE CAST (varchar AS uuid)
    WITH
        INOUT AS IMPLICIT;

DROP CAST IF EXISTS (text AS integer);
CREATE CAST (text AS integer)
    WITH
        INOUT AS IMPLICIT;

DROP CAST IF EXISTS (uuid AS text);
CREATE CAST (uuid AS text)
    WITH
        INOUT AS IMPLICIT;

DROP CAST IF EXISTS (text AS boolean);
CREATE CAST (text AS boolean)
    WITH
        INOUT AS IMPLICIT;

DROP CAST IF EXISTS (text AS numeric);
CREATE CAST (text AS numeric)
    WITH
        INOUT AS IMPLICIT;

DROP CAST IF EXISTS (text AS uuid);
CREATE CAST (text AS uuid)
    WITH
        INOUT AS IMPLICIT;

DROP FUNCTION IF EXISTS public."fn_CastTimeToDateTime" CASCADE;
CREATE FUNCTION public."fn_CastTimeToDateTime"(
    sourceValue TIME WITHOUT TIME ZONE
)
RETURNS TIMESTAMP WITHOUT TIME ZONE
AS $BODY$
BEGIN
    RETURN (MAKE_DATE(1900, 01, 01) + sourceValue)::TIMESTAMP WITHOUT TIME ZONE;
END;
$BODY$
LANGUAGE PLPGSQL;

DROP CAST IF EXISTS (TIME WITHOUT TIME ZONE AS TIMESTAMP WITHOUT TIME ZONE);
CREATE CAST (TIME WITHOUT TIME ZONE AS TIMESTAMP WITHOUT TIME ZONE)
    WITH
        FUNCTION public."fn_CastTimeToDateTime"(
            TIME WITHOUT TIME ZONE
        ) AS IMPLICIT;

When the SQL script runs, PostgreSQL may display warnings for casts that already exist. These warnings are expected and can be ignored.

As a result, the PostgreSQL database will be restored and ready for Creatio deployment.

4. Configure max connections (optional)

Take these steps only if your Creatio deployment is going to serve more than 100 simultaneous users.

1. Calculate max_connections parameter value based on the expected number of simultaneous users:

   Expected number of users * 1.5

   If the result is less than 1024, use the resulting number. If the result is 1024 or greater, set the parameter to 1024.

2. Calculate shared_buffers parameter value based on the max_connections value:

   max_connections * 0.24MB

   Set the parameter value in MB and round the resulting value up. For example, if max_connections is 1024, shared_buffers is 245.76MB, rounded up to 246MB.

Set the calculated values in the following parameters of the "postgresql.conf" file:

• max_connections
• shared_buffers

To locate the "postgresql.conf" file, run the following command in the PostgreSQL interactive terminal:

SHOW config_file;

5. Change database ownership (optional)

By default, the user who runs the pg_restore command (typically "postgres") remains the owner of the database and its objects. If a regular user should own the database instead of the superuser, follow the steps below.

To change the database ownership:

1. Change the database owner to the regular user:

   psql.exe --host=pg_server_host --port=pg_server_port --username=creatio_superuser --dbname=pg_admin_db --command "ALTER DATABASE creatiodb OWNER TO creatio_user"

   Where:

   • pg_server_host is the PostgreSQL server host.
   • pg_server_port is the PostgreSQL server port.
   • creatio_superuser is the superuser for connecting to the PostgreSQL server. This user must be an administrator (superuser) or have the ALTER DATABASE privileges.
   • pg_admin_db is the name of the PostgreSQL database to connect to for executing the command. If no databases exist yet or if an attempt to connect triggers the 'FATAL: database "pg_admin_db" does not exist' error, use the default database template1.
   • creatio_user is the new database owner. This user is used to set up a connection to the Creatio database.
   • creatiodb is the name of the database whose owner is being changed.

2. Download and run the SQL script to transfer ownership of all database objects to the regular user:

   To change the database ownership, use the database ownership transfer SQL script.

   • For Postgres version 11 and later: Download the script.
   • For Postgres version 10 and earlier: Download the script.

   psql.exe --host=pg_server_host --port=pg_server_port --username=creatio_superuser --dbname=creatiodb --file=path_to_database_ownership_transfer_script.sql --variable owner=creatio_user --variable ON_ERROR_STOP=1

   Where:

   • pg_server_host is the PostgreSQL server host.
   • pg_server_port is the PostgreSQL server port.
   • creatio_superuser is the superuser for connecting to the PostgreSQL server. This user must be an administrator (superuser) or the Creatio database owner.
   • creatio_user is the new database owner. This user is used to set up a connection to the Creatio database.
   • creatiodb is the name of the database whose owner is being changed.
   • path_to_database_ownership_transfer_script.sql is the path to the previously saved database ownership transfer script.

---

See also

General setup procedure for data caching server (Redis)

Set up Creatio application server on IIS

Creatio setup FAQ

System requirements calculator for Creatio on-site

Official PostgreSQL documentation