Before you create a PostgreSQL workspace

The source and destination databases must exist before you connect to them. They should use the same version of PostgreSQL and must have the same extensions installed.

Use the following instructions to create database accounts that have the minimum required permissions that are needed for Tonic Structural to function.

Creating a source database account

The following is an example of how to create a new user, called tonic, and grant the necessary permissions on the public schema.

For the source database, we recommend that you use a backup instead of connecting directly to your production environment.

If your database contains additional schemas that are included, then you must run the same commands for those schemas.

--create a user. It's better to use a user with limited permissions as opposed to an rds_superuser
CREATE USER tonic WITH PASSWORD 'tonic_password';

--ensure that the tonic user gets the correct access to new tables as they are added
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON TABLES TO tonic;
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT USAGE ON SEQUENCES TO tonic;
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON SEQUENCES TO tonic;

--add USAGE GRANTs on all schemas in the DB 
GRANT USAGE ON SCHEMA public TO tonic; 

--add SELECT and GRANTs ON ALL TABLES in each schema in the DB. 
GRANT SELECT ON ALL TABLES IN SCHEMA public TO tonic; 

--add USAGE GRANTs on all sequences in the DB 
GRANT USAGE ON ALL SEQUENCES IN SCHEMA public TO tonic; 

--add SELECT GRANTs on all sequences in each schema 
GRANT SELECT ON ALL SEQUENCES IN SCHEMA public TO tonic; 

Creating a destination database account

The following is an example of how to create a new user, called tonic, grant the user ownership of the public schema, and make the user a superuser.

If your database contains additional schemas that are included, then you must run the same commands for those schemas.

--create a new user. 
CREATE USER tonic WITH PASSWORD 'tonic_password'; 

--make this user the schema owner of whichever schemas are in the database. 
ALTER SCHEMA public OWNER TO tonic; 

--allow user to create new schemas. 
GRANT CREATE ON DATABASE test_data_output TO tonic; 

--This user must be given the SUPERUSER role (or azure_pg_admin in Azure Postgres).
--The exact command depends on if you use Amazon RDS.

--On Azure Postgres
GRANT tonic TO azure_pg_admin;

--On Postgres RDS 
GRANT RDS_SUPERUSER TO tonic;

--On Google Cloud SQL Postgres
GRANT cloudsqlsuperuser to tonic;

--On Postgres (non-RDS) 
ALTER USER tonic WITH SUPERUSER;

Configuring handling of infinity date and time values

Default behavior

By default, when Structural:

• Displays data on the application

• Processes data during data generation

It converts -infinity and infinity values in date or time columns to the closest representable date or time.

When Structural writes data to the destination:

• It writes date or time values that are the maximum representable value, such as 9999-12-31 in a date column, as infinity.

• It writes date or time values that are the minimum representable value as -infinity.

Disabling the default behavior

To opt out of the default behavior, set the environment setting TONIC_POSTGRES_DISABLE_DATETIME_INFINITY_CONVERSIONS to true.

You configure the setting in the Structural web server and worker.

When the setting is true, then during data processing, Structural drops any source database rows that contain -infinity or infinity values.

Example

The source database contains a date column with the values infinity and 9999-12-31.

By default, on the application and during data processing, both values appear as 9999-12-31.

If no generator is applied, then in the destination database, both values are written as infinity.

If the default behavior is disabled:

• The row containing infinity is dropped.

• 9999-12-31 is processed and written to the destination as 9999-12-31.