PostgreSQL targets

• • To deploy and run a database ingestion and replication task that includes a PostgreSQL target, the target connection must specify a database user who has the required privileges.
The user must have the CONNECT and TEMPORARY privileges for the database specified in the connection and the USAGE and CREATE privileges for the target schema specified in the target properties.
Use the following SQL statements to grant these privileges to a user role and then assign the role to a user:
CREATE ROLE dbmi_role;
GRANT CONNECT ON DATABASE database TO dbmi_role;
GRANT TEMPORARY ON DATABASE database TO dbmi_role;
GRANT CREATE ON SCHEMA schema TO dbmi_role;
GRANT USAGE ON SCHEMA schema TO dbmi_role;
CREATE USER dbmi_user with PASSWORD 'password';
GRANT dbmi_role to dbmi_user;

Note: Privileges on the target tables that are generated when the job runs are granted to the user who runs the job.
• •Database ingestion and replication incremental load jobs with a PostgreSQL target generate a LOG table based on the target table schema, with some additional metadata columns. The LOG table is created right before change data is flushed to the target. The incoming DML data is inserted to the LOG table by supplying a local CSV file to the Bulk Copy API of the PostgreSQL driver. A set of merge apply statements is generated based on the information in the LOG table, and then the DML operations are applied to the actual target table. After the DML changes are applied, the LOG table is dropped.
The LOG table might cause a temporary spike in additional space or size requirements in the customer database instance if you run multiple jobs or a job with multiple tables. The space and size required by the LOG table depends on the number of rows received as part of a flush cycle.
• •The number of columns in a source table that a database ingestion and replication incremental load job can propagate to a PostgreSQL target must not exceed 796 columns. If a source table contains more than 796 columns, the job fails while creating the LOG table.
• •PostgreSQL supports a maximum length of 63 characters for source object identifiers. If the length of any source table or column name exceeds 63 characters, the deployment of database ingestion and replication tasks with a PostgreSQL target will fail during validation.
• •PostgreSQL stores information about the INTERVAL data type in two columns:
• - data_type
• - interval_type
Database ingestion and replication does not support the interval_type column for the PostgreSQL target. For tasks that have a PostgreSQL source, the INTERVAL columns of any interval_type are replicated as INTERVAL NULL to the PostgreSQL target.
• •Database ingestion and replication incremental load jobs generate a recovery table named INFORMATICA_CDC_RECOVERY on the target to store internal service information that prevents jobs restarted after a failure from propagating previously processed data again. This recovery table is generated in the same schema as the target tables.