> For the complete documentation index, see [llms.txt](https://docs.tonic.ai/llms.txt). Markdown versions of documentation pages are available by appending `.md` to page URLs; this page is available as [Markdown](https://docs.tonic.ai/app/setting-up-your-database/postgres/postgresql-before-create-workspace.md). # 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. {% code overflow="wrap" %} ``` --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; ``` {% endcode %} ## 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. {% code overflow="wrap" %} ``` --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; ``` {% endcode %} ## 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](/app/admin/environment-variables-setting.md) `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](/app/admin/environment-variables-setting.md) `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](/app/admin/environment-variables-setting.md) `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](/app/workspace/workspace-configuration-settings/advanced-overrides.md). 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 generation process](/app/workflows/data-generation-run-job/data-generation-process.md#data-process-customer-schema). --- # Agent Instructions This documentation is published with GitBook. GitBook is the documentation platform designed so that both humans and AI agents can read, navigate, and reason over technical content effectively. Learn more at gitbook.com. ## Querying This Documentation If you need additional information that is not directly available in this page, you can query the documentation dynamically by asking a question. Perform an HTTP GET request on the current page URL with the `ask` query parameter, and the optional `goal` query parameter: ``` GET https://docs.tonic.ai/app/setting-up-your-database/postgres/postgresql-before-create-workspace.md?ask=&goal= ``` `ask` is the immediate question: it should be specific, self-contained, and written in natural language. `goal` is optional and describes the broader end goal you are ultimately trying to accomplish on behalf of the user. GitBook uses it to tailor the answer towards what is most useful for that goal. The response will contain a direct answer to the question and relevant excerpts and sources from the documentation. Use this mechanism when the answer is not explicitly present in the current page, you need clarification or additional context, or you want to retrieve related documentation sections.