Configuring PostgreSQL workspace data connections

During workspace creation, under Connection Type, select PostgreSQL.

Connecting to the source database

In the Source Settings section, provide the details about connecting to the source database.

Providing the connection details

To provide the connection details, you can either populate the connection fields or use a connection string.

Populating the connection fields

By default, Use connection string is off, and you provide the connection values in the individual fields:

1. In the Server field, provide the server where the database is located.

2. In the Database field, provide the name of the database.

3. In the Port field, provide the port to use to connect to the database.

4. In the Username field, provide the username for the account to use to connect to the database.

5. For Password, you can either specify the password manually or, if secrets managers are available, you can select a secret name from a secrets manager.

6. To test the connection to the source database, click Test Source Connection. If the connection test is successful, then Structural also tests and reports on the connection speed.

Using a connection string

To use a connection string to connect to the source database:

1. Toggle Use connection string to the on position.

2. In the Connection String field, provide the connection string.

The connection string uses the following format:

Server=serverIP;Database=databaseName;Port=portNumber;User Id=userName;Password=<password>

To provide the user password to replace <password>, you can either specify the password manually, or you can select a secret name from a secrets manager. The secrets manager option only displays if at least one secrets manager is configured. For information about configuring the available secrets managers, go to Using secrets managers for authentication.

To test the connection to the source database, click Test Source Connection. If the connection test is successful, then Structural also tests and reports on the connection speed.

Connection security settings

For the source connection, you can also:

• Use SSL/TLS to encrypt authentication.

• Trust the server certificate.

• Use a connection tunneling option.

• Provide your own client certificate, client cert key, and root key.

Limiting the included schemas

By default, the source database includes all of the schemas. To specify a list of specific schemas to either include or exclude:

1. Toggle Limit Schemas to the on position.

2. From the filter option dropdown list, select whether to include or exclude the listed schemas.

3. In the field, provide the list of schemas to either include or exclude. Use commas or semicolons to separate the schemas.

Do not exclude schemas that are referred to by included schemas, unless you create those schemas manually outside of Tonic Structural.

Connecting to the intermediate database for upsert

PostgreSQL supports the upsert process. When you enable upsert for the workspace, the data generation process initially writes the transformed data to an intermediate database.

After the initial data generation is complete, the upsert job writes new records to the destination database, and updates existing records in the destination database. It does not touch any other records that are in the destination database.

In the Upsert section, when you enable upsert, you are prompted to configure the upsert processing and to provide the connection information for the intermediate database.

If the intermediate database is in the same location as the source database, then you can copy the connection and authentication details from the source database.

Copying the connection and authentication details from the source database

To copy the connection and authentication details from the source database:

1. Click Copy Settings from Source.

2. For Password, you can either specify the password manually or, if secrets managers are available, you can select a secret name from a secrets manager.

3. To test the connection to the intermediate database, click Test Intermediate Connection. If the connection test is successful, then Structural also tests and reports on the connection speed.

Providing the connection details

If you do not copy the details from the source database, then you can either populate the connection fields or use a connection string.

Populating the connection fields

By default, Use connection string is off, and you provide the connection values in the individual fields:

1. In the Server field, provide the server where the database is located.

2. In the Database field, provide the name of the database.

3. In the Port field, provide the port to use to connect to the database.

4. In the Username field, provide the username for the account to use to connect to the database.

5. For Password, you can either specify the password manually or, if secrets managers are available, you can select a secret name from a secrets manager.

6. To test the connection to the intermediate database, click Test Intermediate Connection. If the connection test is successful, then Structural also tests and reports on the connection speed.

Using a connection string

To use a connection string to connect to the intermediate database:

1. Toggle Use connection string to the on position.

2. In the Connection String field, provide the connection string.

The connection string uses the following format:

Server=serverIP;Database=databaseName;Port=portNumber;User Id=userName;Password=<password>

To provide the user password to replace <password>, you can either specify the password manually, or you can select a secret name from a secrets manager. The secrets manager option only displays if at least one secrets manager is configured. For information about configuring the available secrets managers, go to Using secrets managers for authentication.

To test the connection to the intermediate database, click Test Intermediate Connection. If the connection test is successful, then Structural also tests and reports on the connection speed.

Connection security settings

For the intermediate database connection, you can also:

• Use SSL/TLS to encrypt authentication.

• Trust the server certificate.

• Use a connection tunneling option.

• Provide your own client certificate, client cert key, and root key.

Connecting to the destination database

For a PostgreSQL workspace, you can write the destination data to either:

• A destination database server.

• A container repository. For more information, go to Writing output to a container repository.

Under Destination Settings, to write the transformed data to a destination database on a database server, click Database Server. Structural installs any generally available extensions that are used on the source database. However, you must manually install any custom extensions and plugins.

Copying the connection and authentication details from the source database

If the destination database is in the same location as the source database, then you can copy the connection and authentication details from the source database.

To copy the connection and authentication details from the source database:

1. Click Copy Settings from Source.

2. For Password, you can either specify the password manually or, if secrets managers are available, you can select a secret name from a secrets manager.

3. To test the connection to the destination database, click Test Destination Connection. If the connection test is successful, then Structural also tests and reports on the connection speed.

Providing the connection details

If you do not copy the details from the source database, then you can either populate the connection fields or use a connection string.

Populating the connection fields

By default, Use connection string is off, and you provide the connection values in the individual fields:

1. In the Server field, provide the server where the database is located.

2. In the Database field, provide the name of the database.

3. In the Port field, provide the port to use to connect to the database.

4. In the Username field, provide the username for the account to use to connect to the database.

5. For Password, you can either specify the password manually or, if secrets managers are available, you can select a secret name from a secrets manager.

6. To test the connection to the destination database, click Test Destination Connection. If the connection test is successful, then Structural also tests and reports on the connection speed.

Using a connection string

To use a connection string to connect to the source database:

1. Toggle Use connection string to the on position.

2. In the Connection String field, provide the connection string.

The connection string uses the following format:

Server=serverIP;Database=databaseName;User Id=userName;Password=<password>

For Password, set the value to <password>. In the Password field, type the actual password.

To test the connection to the destination database, click Test Destination Connection. If the connection test is successful, then Structural also tests and reports on the connection speed.

Connection security settings

For the destination connection, you can also:

• Use SSL/TLS to encrypt authentication.

• Trust the server certificate.

• Use a connection tunneling option.

• Provide your own client certificate, client cert key, and root key.