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.12 instances. For other PostgreSQL versions, follow the Amazon RDS for PostgreSQL updates (official Amazon RDS 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 (optional). 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 an Amazon RDS DB instance (official Amazon RDS 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.

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 set up a superuser who has maximum access privileges, execute the SQL script below as the postgres user:

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;

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 set up 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. Connect to the PostgreSQL instance and create a database that has an arbitrary name and the owner set to the 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.

note

During the restore, there might be 8 errors in the log about creating casts. They are expected if the backup was created not from an AWS RDS PostgreSQL instance and can be ignored.

pg_restore: processing item 8589 CAST CAST (text AS boolean)
pg_restore: creating CAST "CAST (text AS boolean)"
pg_restore: while PROCESSING TOC:
pg_restore: from TOC entry 8589; 2605 14629379 CAST CAST (text AS boolean) (no owner)
pg_restore: error: could not execute query: ERROR: must be owner of type text or type boolean
Command was: CREATE CAST (text AS boolean) WITH INOUT AS IMPLICIT;

pg_restore: processing item 8592 CAST CAST (text AS integer)
pg_restore: creating CAST "CAST (text AS integer)"
pg_restore: from TOC entry 8592; 2605 14629377 CAST CAST (text AS integer) (no owner)
pg_restore: error: could not execute query: ERROR: must be owner of type text or type integer
Command was: CREATE CAST (text AS integer) WITH INOUT AS IMPLICIT;

pg_restore: processing item 8596 CAST CAST (text AS numeric)
pg_restore: creating CAST "CAST (text AS numeric)"
pg_restore: from TOC entry 8596; 2605 14629380 CAST CAST (text AS numeric) (no owner)
pg_restore: error: could not execute query: ERROR: must be owner of type text or type numeric
Command was: CREATE CAST (text AS numeric) WITH INOUT AS IMPLICIT;

pg_restore: processing item 8598 CAST CAST (text AS uuid)
pg_restore: creating CAST "CAST (text AS uuid)"
pg_restore: from TOC entry 8598; 2605 14629381 CAST CAST (text AS uuid) (no owner)
pg_restore: error: could not execute query: ERROR: must be owner of type text or type uuid
Command was: CREATE CAST (text AS uuid) WITH INOUT AS IMPLICIT;

pg_restore: processing item 1742 FUNCTION fn_CastTimeToDateTime(time without time zone)
pg_restore: creating FUNCTION "public.fn_CastTimeToDateTime(time without time zone)"

pg_restore: processing item 8670 CAST CAST (time without time zone AS timestamp without time zone)
pg_restore: creating CAST "CAST (time without time zone AS timestamp without time zone)"
pg_restore: from TOC entry 8670; 2605 14629383 CAST CAST (time without time zone AS timestamp without time zone) (no owner)
pg_restore: error: could not execute query: ERROR: must be owner of type time without time zone or type timestamp without time zone
Command was: CREATE CAST (time without time zone AS timestamp without time zone) WITH FUNCTION public."fn_CastTimeToDateTime"(time without time zone) AS IMPLICIT;

pg_restore: processing item 8717 CAST CAST (uuid AS text)
pg_restore: creating CAST "CAST (uuid AS text)"
pg_restore: from TOC entry 8717; 2605 14629378 CAST CAST (uuid AS text) (no owner)
pg_restore: error: could not execute query: ERROR: must be owner of type uuid or type text
Command was: CREATE CAST (uuid AS text) WITH INOUT AS IMPLICIT;

pg_restore: processing item 8660 CAST CAST (character varying AS integer)
pg_restore: creating CAST "CAST (character varying AS integer)"
pg_restore: from TOC entry 8660; 2605 14629375 CAST CAST (character varying AS integer) (no owner)
pg_restore: error: could not execute query: ERROR: must be owner of type character varying or type integer
Command was: CREATE CAST (character varying AS integer) WITH INOUT AS IMPLICIT;

pg_restore: processing item 8666 CAST CAST (character varying AS uuid)
pg_restore: creating CAST "CAST (character varying AS uuid)"
pg_restore: from TOC entry 8666; 2605 14629376 CAST CAST (character varying AS uuid) (no owner)
pg_restore: error: could not execute query: ERROR: must be owner of type character varying or type uuid
Command was: CREATE CAST (character varying AS uuid) WITH INOUT AS IMPLICIT;

4. Create rds_casts extension and type casts

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

CREATE EXTENSION IF NOT EXISTS rds_casts;

CREATE CAST (varchar AS integer)
    WITH FUNCTION rds_casts.rds_varchar_to_int4_inout
    AS IMPLICIT;

CREATE CAST (varchar AS uuid)
    WITH FUNCTION rds_casts.rds_varchar_to_uuid_inout
    AS IMPLICIT;

CREATE CAST (text AS integer)
    WITH FUNCTION rds_casts.rds_text_to_int4_inout
    AS IMPLICIT;

CREATE CAST (uuid AS text)
    WITH FUNCTION rds_casts.rds_uuid_to_text_inout
    AS IMPLICIT;

CREATE CAST (text AS boolean)
    WITH FUNCTION rds_casts.rds_text_to_bool_inout
    AS IMPLICIT;

CREATE CAST (text AS numeric)
    WITH FUNCTION rds_casts.rds_text_to_numeric_inout
    AS IMPLICIT;

CREATE CAST (text AS uuid)
    WITH FUNCTION rds_casts.rds_text_to_uuid_inout
    AS IMPLICIT;

4. Change database ownership (optional)

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 := 'creatio_user';
       rec RECORD;
       -- Add any other RDS schemas you encounter here
       excluded_schemas TEXT[] := ARRAY['pg_catalog', 'information_schema', 'rds_casts', 'rds_tools', 'extensions'];
   BEGIN
       -- 1. Schemas
       FOR rec IN
           SELECT nspname FROM pg_namespace
           WHERE nspname NOT LIKE 'pg_toast%'
             AND nspname NOT LIKE 'pg_temp%'
             AND NOT (nspname = ANY(excluded_schemas))
       LOOP
           EXECUTE 'ALTER SCHEMA ' || quote_ident(rec.nspname) || ' OWNER TO ' || newOwner;
       END LOOP;
   
       -- 2. Tables
       FOR rec IN
           SELECT schemaname, tablename FROM pg_tables
           WHERE NOT (schemaname = ANY(excluded_schemas))
       LOOP
           EXECUTE 'ALTER TABLE ' || quote_ident(rec.schemaname) || '.' || quote_ident(rec.tablename) || ' OWNER TO ' || newOwner;
       END LOOP;
   
       -- 3. Sequences
       FOR rec IN
           SELECT sequence_schema, sequence_name FROM information_schema.sequences
           WHERE NOT (sequence_schema = ANY(excluded_schemas))
       LOOP
           EXECUTE 'ALTER SEQUENCE ' || quote_ident(rec.sequence_schema) || '.' || quote_ident(rec.sequence_name) || ' OWNER TO ' || newOwner;
       END LOOP;
   
       -- 4. Views
       FOR rec IN
           SELECT table_schema, table_name FROM information_schema.views
           WHERE NOT (table_schema = ANY(excluded_schemas))
       LOOP
           EXECUTE 'ALTER VIEW ' || quote_ident(rec.table_schema) || '.' || quote_ident(rec.table_name) || ' OWNER TO ' || newOwner;
       END LOOP;
   
       -- 5. Materialized views
       FOR rec IN
           SELECT n.nspname, c.relname
           FROM pg_class c
           JOIN pg_namespace n ON n.oid = c.relnamespace
           WHERE c.relkind = 'm'
             AND NOT (n.nspname = ANY(excluded_schemas))
       LOOP
           EXECUTE 'ALTER MATERIALIZED VIEW ' || quote_ident(rec.nspname) || '.' || quote_ident(rec.relname) || ' OWNER TO ' || newOwner;
       END LOOP;
   
       -- 6. Functions
       FOR rec IN
           SELECT n.nspname, p.proname, pg_catalog.pg_get_function_identity_arguments(p.oid) as args
           FROM pg_catalog.pg_proc p
           JOIN pg_catalog.pg_namespace n ON n.oid = p.pronamespace
           WHERE NOT (n.nspname = ANY(excluded_schemas))
       LOOP
           EXECUTE 'ALTER FUNCTION ' || quote_ident(rec.nspname) || '.' || quote_ident(rec.proname) || '(' || rec.args || ') OWNER TO ' || newOwner;
       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

Amazon RDS for PostgreSQL updates (official Amazon RDS documentation)

Creating an Amazon RDS DB instance (official Amazon RDS documentation)