# Configuring PostgreSQL workspace data connections

During workspace creation, under **Connection Type**, select **PostgreSQL**.

## Connecting to the source database <a href="#postgresql-connect-source-data" id="postgresql-connect-source-data"></a>

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

### Providing the connection details <a href="#postgresql-connection-source-details" id="postgresql-connection-source-details"></a>

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

#### **Populating the connection fields** <a href="#postgresql-connection-source-details-fields" id="postgresql-connection-source-details-fields"></a>

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](https://docs.tonic.ai/app/workspace/managing-workspaces/workspace-configuration-settings/secrets-manager/selecting-a-secrets-manager-secret).
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** <a href="#postgresql-connection-source-details-connection-string" id="postgresql-connection-source-details-connection-string"></a>

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:

{% code overflow="wrap" %}

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

{% endcode %}

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](https://docs.tonic.ai/app/workspace/managing-workspaces/workspace-configuration-settings/secrets-manager/selecting-a-secrets-manager-secret).\
\
The secrets manager option only displays if at least one secrets manager is configured. For information about configuring the available secrets managers, go to [secrets-manager](https://docs.tonic.ai/app/workspace/managing-workspaces/workspace-configuration-settings/secrets-manager "mention").

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.

### Ensuring encryption of source database authentication <a href="#postgresql-source-auth-encrypt" id="postgresql-source-auth-encrypt"></a>

The **Enable SSL/TLS** setting indicates whether to encrypt source database authentication.

By default, the toggle is in the on position. We strongly recommend that you do not turn off this setting.

### Trusting the server certificate <a href="#postgresql-source-trust-cert" id="postgresql-source-trust-cert"></a>

To indicate that Structural should trust the server certificate, toggle **Trust Server Certificate** to the on position.

### Connecting through an SSH bastion <a href="#postgresql-source-ssh-bastion" id="postgresql-source-ssh-bastion"></a>

For additional security, to connect to an SSH bastion :

1. Toggle **Enable SSH Tunnel** to the on position.
2. In the **SSH Host** field, provide the host for the SSH bastion.
3. In the **SSH Port** field, provide the port for the SSH bastion.
4. In the **SSH User** field, provide the name of the user to use to connect to the SSH bastion.
5. If you do not use a private key, then in the **SSH Passphrase** field, provide the passphrase to use for authentication.\
   \
   If secrets managers are available, you can instead [select a secret name from a secrets manager](https://docs.tonic.ai/app/workspace/managing-workspaces/workspace-configuration-settings/secrets-manager/selecting-a-secrets-manager-secret).
6. If you do use a private key, then in the **SSH Private Key** field, provide the private key.\
   \
   If secrets managers are available, you can instead [select a secret name from a secrets manager](https://docs.tonic.ai/app/workspace/managing-workspaces/workspace-configuration-settings/secrets-manager/selecting-a-secrets-manager-secret).\
   \
   If the private key uses a passphrase, then in the **SSH Passphrase** field, provide the passphrase for the private key.

### Providing your own client certificate <a href="#postgresql-source-client-cert" id="postgresql-source-client-cert"></a>

To specify your own client certificate for authentication:

1. Click the expand icon for **Client certificate settings**.
2. For **Client Cert**, choose the client certificate file.
3. For **Client Key**, choose the key file for the client certificate.
4. For **Root Cert**, choose the root certificate file.

For all of these settings, if secrets managers are available, you can instead [select a secret name from a secrets manager](https://docs.tonic.ai/app/workspace/managing-workspaces/workspace-configuration-settings/secrets-manager/selecting-a-secrets-manager-secret).

### Limiting the included schemas <a href="#postgresql-connection-source-limit-schemas" id="postgresql-connection-source-limit-schemas"></a>

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 <a href="#postgresql-data-connection-intermediate" id="postgresql-data-connection-intermediate"></a>

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](https://docs.tonic.ai/app/workspace/managing-workspaces/workspace-configuration-settings/workspace-config-upsert) 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 <a href="#postgresql-data-connection-intermediate-copy-source" id="postgresql-data-connection-intermediate-copy-source"></a>

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](https://docs.tonic.ai/app/workspace/managing-workspaces/workspace-configuration-settings/secrets-manager/selecting-a-secrets-manager-secret).
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 <a href="#postgresql-data-connection-intermediate-connection-details" id="postgresql-data-connection-intermediate-connection-details"></a>

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** <a href="#postgresql-data-connection-intermediate-connection-fields" id="postgresql-data-connection-intermediate-connection-fields"></a>

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](https://docs.tonic.ai/app/workspace/managing-workspaces/workspace-configuration-settings/secrets-manager/selecting-a-secrets-manager-secret).
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** <a href="#postgresql-data-connection-intermediate-connection-string" id="postgresql-data-connection-intermediate-connection-string"></a>

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:

{% code overflow="wrap" %}

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

{% endcode %}

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](https://docs.tonic.ai/app/workspace/managing-workspaces/workspace-configuration-settings/secrets-manager/selecting-a-secrets-manager-secret).\
\
The secrets manager option only displays if at least one secrets manager is configured. For information about configuring the available secrets managers, go to [secrets-manager](https://docs.tonic.ai/app/workspace/managing-workspaces/workspace-configuration-settings/secrets-manager "mention").

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.

### Ensuring encryption of intermediate database authentication <a href="#postgresql-intermediate-auth-encrypt" id="postgresql-intermediate-auth-encrypt"></a>

The **Enable SSL/TLS** setting indicates whether to encrypt intermediate database authentication.

By default, the toggle is in the on position. We strongly recommend that you do not turn off this setting.

### Trusting the server certificate <a href="#postgresql-intermediate-trust-cert" id="postgresql-intermediate-trust-cert"></a>

To indicate that Structural should trust the server certificate, toggle **Trust Server Certificate** to the on position.

### Connecting through an SSH bastion <a href="#postgresql-intermediate-ssh-bastion" id="postgresql-intermediate-ssh-bastion"></a>

To connect to an SSH bastion for additional security:

1. Toggle **Enable SSH Tunnel** to the on position.
2. In the **SSH Host** field, provide the host for the SSH bastion.
3. In the **SSH Port** field, provide the port for the SSH bastion.
4. In the **SSH User** field, provide the name of the user to use to connect to the SSH bastion.
5. If you do not use a private key, then in the **SSH Passphrase** field, provide the passphrase to use for authentication.\
   \
   If secrets managers are available, you can instead [select a secret name from a secrets manager](https://docs.tonic.ai/app/workspace/managing-workspaces/workspace-configuration-settings/secrets-manager/selecting-a-secrets-manager-secret).
6. If you do use a private key, then in the **SSH Private Key** field, provide the private key.\
   \
   If secrets managers are available, you can instead [select a secret name from a secrets manager](https://docs.tonic.ai/app/workspace/managing-workspaces/workspace-configuration-settings/secrets-manager/selecting-a-secrets-manager-secret).\
   \
   If the private key uses a passphrase, then in the **SSH Passphrase** field, provide the passphrase for the private key.

### Providing your own client certificate <a href="#posgresql-intermediate-client-cert" id="posgresql-intermediate-client-cert"></a>

To specify your own client certificate for authentication:

1. Click the expand icon for **Client certificate settings**.
2. For **Client Cert**, choose the client certificate file.
3. For **Client Key**, choose the key file for the client certificate.
4. For **Root Cert**, choose the root certificate file.

For all of these settings, if secrets managers are available, you can instead [select a secret name from a secrets manager](https://docs.tonic.ai/app/workspace/managing-workspaces/workspace-configuration-settings/secrets-manager/selecting-a-secrets-manager-secret).

## Connecting to the destination database <a href="#postgresql-connect-destination-data" id="postgresql-connect-destination-data"></a>

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

* A destination database server.
* A container repository. For more information, go to [workspace-config-write-to-container-artifacts](https://docs.tonic.ai/app/workspace/managing-workspaces/workspace-configuration-settings/workspace-config-write-to-container-artifacts "mention").

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 <a href="#postgresql-connection-destination-copy-from-source" id="postgresql-connection-destination-copy-from-source"></a>

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](https://docs.tonic.ai/app/workspace/managing-workspaces/workspace-configuration-settings/secrets-manager/selecting-a-secrets-manager-secret).
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 <a href="#postgresql-connection-dest-details" id="postgresql-connection-dest-details"></a>

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** <a href="#postgresql-connection-dest-details-fields" id="postgresql-connection-dest-details-fields"></a>

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](https://docs.tonic.ai/app/workspace/managing-workspaces/workspace-configuration-settings/secrets-manager/selecting-a-secrets-manager-secret).
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** <a href="#postgresql-connection-dest-details-connection-string" id="postgresql-connection-dest-details-connection-string"></a>

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.

### Ensuring the encryption of destination database authentication <a href="#postgresql-destination-auth-encrypt" id="postgresql-destination-auth-encrypt"></a>

The **Enable SSL/TLS** setting indicates whether to encrypt destination database authentication.

By default, the toggle is in the on position. We strongly recommend that you do not turn off this setting.

### Trusting the server certificate <a href="#postgresql-destination-trust-cert" id="postgresql-destination-trust-cert"></a>

To indicate that Tonic should trust the server certificate, toggle **Trust Server Certificate** to the on position.

### Connecting through an SSH bastion <a href="#postgresql-destination-ssh-bastion" id="postgresql-destination-ssh-bastion"></a>

For additional security, to connect through an SSH bastion :

1. Toggle **Enable SSH Tunnel** to the on position.
2. In the **SSH Host** field, provide the host for the SSH bastion.
3. In the **SSH Port** field, provide the port for the SSH bastion.
4. In the **SSH User** field, provide the name of the user to use to connect to the SSH bastion.
5. If you do not use a private key, then in the **SSH Passphrase** field, provide the passphrase to use for authentication.\
   \
   If secrets managers are available, you can instead [select a secret name from a secrets manager](https://docs.tonic.ai/app/workspace/managing-workspaces/workspace-configuration-settings/secrets-manager/selecting-a-secrets-manager-secret).
6. If you do use a private key, then in the **SSH Private Key** field, provide the private key.\
   \
   If secrets managers are available, you can instead [select a secret name from a secrets manager](https://docs.tonic.ai/app/workspace/managing-workspaces/workspace-configuration-settings/secrets-manager/selecting-a-secrets-manager-secret).\
   \
   If the private key uses a passphrase, then in the **SSH Passphrase** field, provide the passphrase for the private key.

### Providing your own client certificate <a href="#postgresql-destination-client-cert" id="postgresql-destination-client-cert"></a>

To specify your own client certificate for authentication:

1. Click the expand icon for **Client certificate settings**.
2. For **Client Cert**, choose the client certificate file.
3. For **Client Key**, choose the key file for the client certificate.
4. For **Root Cert**, choose the root certificate file.

For all of these settings, if secrets managers are available, you can instead [select a secret name from a secrets manager](https://docs.tonic.ai/app/workspace/managing-workspaces/workspace-configuration-settings/secrets-manager/selecting-a-secrets-manager-secret).