Before you create a PostgreSQL workspace

The source and destination databases:

• Must exist before you connect to them.
• Should use the same version of PostgreSQL.
• Must have the same extensions installed.

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

Creating a source database account

Use these instructions to create a source database account that has the minimum required permissions that Tonic Structural needs to function.

For a source database account, the following example:

• Creates a new user, called tonic.
• Grants tonic the necessary permissions on the public schema.

The example refers to a single schema. However, if your source data includes multiple schemas, then you must run the same commands for all of the schemas.

--Create a user. It's better to use a user with limited permissions, not an rds_superuser
CREATE USER tonic WITH PASSWORD 'tonic_password';

--Grant the user the following access to the tables, sequences, and types in all of the applicable schemas.
--As new schemas are added, make sure to add new entries.
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;
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT USAGE ON TYPES TO tonic;

--Add USAGE GRANTs on all of the applicable schemas.
--As new schemas are added, make sure to add new entries. 
GRANT USAGE ON SCHEMA public TO tonic; 

--Add SELECT and GRANTs ON ALL TABLES in all of the applicable schemas.
--As new schemas are added, make sure to add new entries. 
GRANT SELECT ON ALL TABLES IN SCHEMA public TO tonic; 

--Add USAGE GRANTs on all sequences in all of the applicable schemas.
--As new schemas are added, make sure to add new entries. 
GRANT USAGE ON ALL SEQUENCES IN SCHEMA public TO tonic; 

--Add SELECT GRANTs on all sequences in all of the applicable schemas.
--As new schemas are added, make sure to add new entries.
GRANT SELECT ON ALL SEQUENCES IN SCHEMA public TO tonic; 

Creating a destination database account

Use these instructions to create a destination database account that has the minimum required permissions that Tonic Structural needs to function.

The following example:

• Create a destination database user called tonic
• Grants the user ownership of the public schema.
• Makes tonic a superuser.

Structural requires the superuser access so that it can manage extensions to support special column types.

This example refers to a single schema. However, if your destination data includes multiple schemas, then you must grant the user ownership of all of the schemas.

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

--Make this user the schema owner of all of the applicable schemas. 
--As new schemas are added, make sure to add new entries.
ALTER SCHEMA public OWNER TO tonic; 

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

--Give the user the SUPERUSER role (or azure_pg_admin in Azure PostgreSQL).
--The exact command depends on whether you use Amazon RDS.

--On Azure PostgreSQL
GRANT tonic TO azure_pg_admin;

--On PostgreSQL RDS 
GRANT RDS_SUPERUSER TO tonic;

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

--On PostgreSQL (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.

Configuring how Structural reads PostgreSQL source data

The first step in data generation is to read the source data. Structural only needs to read source data from tables that use the De-Identify or Incremental table modes.

For Incremental mode, Structural always reads each table straight through.

For De-identify mode, how Structural reads the data varies based on the size of the table and the Structural configuration.

Using a simple read

In the following cases, Structural uses a simple read through the table:

• The table is small.
• The table is partitioned or inherited.
• Subsetting is enabled.

Using a parallel (page range or index range) read

When a table is very large, then Structural splits the table to allow multiple readers to work in parallel.

By default, Structural splits the table into ranges of physical pages.

If that isn't possible, it uses an indexed column to split the table into ranges.

Configuring whether to allow page range reads

The environment setting TONIC_POSTGRESQL_ENABLE_TID_SCAN determines whether Structural performs page range reads. You can configure this setting from the Environment Settings tab on Structural Settings.

By default, the setting is true, and Structural can use page range reads.

If false, then Structural cannot use page range reads, and must use index range reads for larger tables.

Configuring whether Structural creates the destination database schema

By default, during each data generation job, Structural creates the database schema for the destination database tables, then populates the database tables based on the workspace configuration.

If you prefer to manage the destination database schema yourself, then set the environment setting TONIC_POSTGRES_SKIP_CREATE_DB to true. You can add this setting manually to the Environment Settings list on Structural Settings.

You can override this setting manually in individual workspaces. For more information, go to Advanced workspace overrides.

When TONIC_POSTGRES_SKIP_CREATE_DB is true, or the workspace overrides the setting to true, then Structural does not create the destination database schema. Before you run data generation, you must create the destination database with the full schema.

During data generation, Structural deletes the data from the destination database tables, except in the following cases:

• Tables that use Preserve Destination mode.
• Upsert data generation.

It then populates the tables with the new destination data.

For a diagram of the data generation process when you manage the destination schema, go to Data process - User-managed destination schema.