PostgreSQL as Target

Data Source Solutions DSS supports integrating changes into PostgreSQL, including managed PostgreSQL services. This section describes the configuration requirements for integrating changes (using Integrate and Refresh) into PostgreSQL location. For the list of supported PostgreSQL versions, into which DSS can integrate changes, see Integrate changes into location in Capabilities.

DSS uses the following interfaces to write data to PostgreSQL during Integrate and Refresh:

• PostgreSQL native client library "libpq" is used during Continuous Integrate and Row-wise Refresh.

• PostgreSQL <b>COPY FROM STDIN</b> command via "libpq" is used for Burst Integrate and Bulk Refresh.

Table and Column Names Forced Case

<b>Since</b> v6.1.0/34

This option allows you to manage the case sensitivity of object names created in the target PostgreSQL tables. This applies to Activating Replication, Refresh, and Compare.

Available options are:

• No default: Create table and column names in the same case as received from the source, which can be either lowercase or uppercase.
• Upper: Create table and column names in uppercase.
• Lower: Create table and column names in lowercase.

Grants for Integrate and Refresh

This section lists the grants required for integrating changes into PostgreSQL location.

1. The DSS database User must be granted the following privileges to read and change the replicated tables:

   GRANT SELECT, INSERT, UPDATE, DELETE ON <em>tablename</em> TO <em>username</em>;
   

2. The DSS database User must be granted the permission to create and drop DSS state tables.

Disabling Triggers and Constraints

You can prevent DSS from firing triggers and performing constraint checks in the database during replication. This can be achieved by setting the PostgreSQL session-level parameter session_replication_role. For more information about this parameter, refer to PostgreSQL documentation.

For DSS to automatically configure this parameter, define action Environment with parameters Name=DSS_SQL_INIT and Value="SET session_replication_role = replica".

Intermediate Directory

{% partial file="dss6/requirements/source-and-target-requirements/intermediate-directory.template.md" /%}

Intermediate Directory is Local

{% partial file="dss6/requirements/source-and-target-requirements/intermediate-directory-local.template.md" /%}