Deploy PostgreSQL database (AWS RDS)

Deploy Creatio on AWS RDS PostgreSQL to enable cloud-based database hosting and eliminate the need to manage on-premises database servers.

Important

This article covers AWS RDS PostgreSQL v16.4 instances. For other PostgreSQL versions, follow the official vendor documentation. Disable auto-updating of your PostgreSQL instance in AWS to maintain compatibility.

The general database deployment procedure for AWS RDS is as follows:

1. Create AWS RDS PostgreSQL instance. Read more >>>
2. Create PostgreSQL users. Read more >>>
3. Create and restore the database. Read more >>>
4. Change database ownership. Read more >>>
5. Set up database connection string. Read more >>>

1. Create AWS RDS PostgreSQL instance

Create an AWS RDS PostgreSQL instance using the AWS Management Console or AWS CLI. Learn more about creating RDS instances: Creating a DB instance (official vendor documentation).

When creating the RDS instance, AWS creates a system user. By default, postgres.

Perform further steps using a database administration tool, for example, pgAdmin or the psql utility.

2. Create PostgreSQL users

Create the following database users for AWS RDS 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.

Execute the SQL script below as the system user.

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. Allow the superuser to log in:

   ALTER ROLE creatio_superuser WITH LOGIN;

3. Grant RDS superuser privileges:

   GRANT rds_superuser TO creatio_superuser;

   AWS RDS uses the rds_superuser role instead of the standard PostgreSQL superuser privilege. This provides the necessary permissions for database restoration and management within the AWS RDS environment.

4. Set the superuser password:

   ALTER ROLE creatio_superuser WITH PASSWORD 'creatio_superuser_password';

5. Grant database creation privileges:

   ALTER ROLE creatio_superuser CREATEDB;

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';

For subsequent steps, connect to the PostgreSQL instance under the superuser account.

3. Create and restore the database

1. Create database

1. Connect to the PostgreSQL instance as the superuser in the postgres database.

2. Create the database that has an arbitrary name and the owner set to the superuser. For example, OWNER = creatio_superuser. Execute this SQL script using psql, pgAdmin, or another database administration tool:

   CREATE DATABASE creatiodb
       WITH
           OWNER = creatio_superuser
           ENCODING = 'UTF8'
           CONNECTION LIMIT = -1;

2. Prepare for restoration

1. Connect to the PostgreSQL instance as the superuser in the context of the database created on the previous step.

2. Change the owner of the following types to the superuser to prevent issues during type conversion in restore:

   ALTER TYPE varchar   OWNER TO creatio_superuser;
   ALTER TYPE bool      OWNER TO creatio_superuser;
   ALTER TYPE text      OWNER TO creatio_superuser;
   ALTER TYPE timestamp OWNER TO creatio_superuser;

3. Change the database owner to the regular user to run Creatio under that account and avoid excessive privileges. Transfer ownership of the database objects to the regular user.

3. Restore the database

Restore the database from the "*.backup" file with the --no-owner and --no-privileges parameters via one of the following ways:

• Restore database via pgAdmin:

  1. Click Databases → right-click an arbitrary database → Restore.... This opens the Restore (Database: postgres) window.
  2. Fill out the database properties.
  3. Go to the Data Options tab → Do not save block.
  4. Enable the Owner option.
  5. Enable the Privileges option.
  6. Click Restore.

• Restore database via command-line interface:

  pg_restore \
  --host=<pg_instance_host> \
  --port=<pg_instance_port> \
  --username=<creatio_superuser> \
  --dbname=<creatiodb> \
  --no-owner \
  --no-privileges \
  --verbose \
  <*.backup> > <*.log> 2>&1

  Where:

  • pg_instance_host is the endpoint of the AWS RDS PostgreSQL instance.
  • pg_instance_port is the port of the AWS RDS PostgreSQL instance. By default, "5432."
  • creatio_superuser is the name of the superuser created earlier.
  • creatiodb is the name of the database created earlier.
  • *.backup is the path to the Creatio database backup file.
  • *.log is the path to save the restore output log. Remove > <*.log> 2>&1 from the command to restore database without logging.

4. Create type casts

Create type casts via one of the following ways:

• Download and execute the CreateTypeCastsPostgreSql.sql SQL script.

• 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.

4. Change database ownership

1. Change the database owner to the regular user:

   ALTER DATABASE creatiodb OWNER TO creatio_user;

2. Transfer ownership of all database objects to the regular user:

   DO
   $$
   DECLARE
       newOwner TEXT;
       rec record;
   BEGIN
       newOwner := 'creatio_user';
       EXECUTE 'ALTER TYPE varchar OWNER TO '||newOwner||';';
       EXECUTE 'ALTER TYPE bool OWNER TO '||newOwner||';';
       EXECUTE 'ALTER TYPE text OWNER TO '||newOwner||';';
       EXECUTE 'ALTER TYPE timestamp OWNER TO '||newOwner||';';
       /* Schemas. */
       FOR rec IN
           (SELECT 'ALTER SCHEMA '||
                   nspname||
                   ' OWNER TO '||newOwner||';' AS query
            FROM pg_namespace
            WHERE nspname NOT LIKE 'pg_toast%'
              AND nspname NOT LIKE 'pg_temp%'
              AND nspname <> 'pg_catalog'
              AND nspname <> 'information_schema')
       LOOP
           EXECUTE rec.query;
       END LOOP;
       /* Tables. */
       FOR rec IN
           (SELECT 'ALTER TABLE '||
                   schemaname||'."'||tablename||'" OWNER TO '||
                   newOwner||';' AS query
            FROM pg_tables
            WHERE NOT schemaname IN ('pg_catalog', 'information_schema'))
       LOOP
           EXECUTE rec.query;
       END LOOP;
       /* Sequences. */
       FOR rec IN
           (SELECT 'ALTER SEQUENCE '||
                   sequence_schema||'."'||sequence_name||'" OWNER TO '||
                   newOwner||';' AS query
            FROM information_schema.sequences
            WHERE NOT sequence_schema IN ('pg_catalog', 'information_schema'))
       LOOP
           EXECUTE rec.query;
       END LOOP;
       /* Views. */
       FOR rec IN
           (SELECT 'ALTER VIEW '||
                   table_schema||'."'||table_name||'" OWNER TO '||
                   newOwner||';' AS query
            FROM information_schema.views
            WHERE NOT table_schema IN ('pg_catalog', 'information_schema'))
       LOOP
           EXECUTE rec.query;
       END LOOP;
       /* Materialized views. */
       FOR rec IN
           (SELECT 'ALTER TABLE '||
                   oid::regclass::text||' OWNER TO '||newOwner||';' AS query
            FROM pg_class
            WHERE relkind = 'm')
       LOOP
           EXECUTE rec.query;
       END LOOP;
       /* Functions. */
       FOR rec IN
           (SELECT 'ALTER FUNCTION '||
                   n.nspname||'."'||p.proname||'"('||
                   pg_catalog.pg_get_function_identity_arguments(p.oid)||
                   ') OWNER TO '||newOwner||';' AS query
           FROM pg_catalog.pg_proc p
           JOIN pg_catalog.pg_namespace n ON n.oid = p.pronamespace
           WHERE n.nspname NOT IN ('pg_catalog', 'information_schema'))
       LOOP
           EXECUTE rec.query;
       END LOOP;
   END$$;

5. Set up database connection string

1. Go to the root Creatio directory.
2. Open the "ConnectionStrings.config" file.
3. Specify the regular user credentials. To do this, set the regular user name and password in the User ID and password parameters of the connectionString attribute. For example, creatio_user and creatio_user_password. Instructions: Modify ConnectionStrings.config for PostgreSQL.
4. Specify the AWS RDS endpoint and port. To do this, set the AWS RDS PostgreSQL instance endpoint in the Server parameter and the port in the Port parameter. By default, the port number is "5432" for PostgreSQL connections.

As a result, AWS RDS PostgreSQL will be configured for Creatio deployment.

---

See also

Deploy PostgreSQL database (Linux)

Deploy PostgreSQL database (Windows)

Set up Creatio application server on IIS

Creatio setup FAQ

System requirements calculator for Creatio on-site

Official AWS RDS PostgreSQL Release Notes

Official AWS RDS User Guide