Connectors and Connections > Data Ingestion and Replication connection properties > PostgreSQL connection properties
  

PostgreSQL connection properties

Create a PostgreSQL connection to securely read data from or write data to PostgreSQL.

Prepare for authentication

You can configure database or Kerberos authentication method to connect to a PostgreSQL database.
Note: Data Ingestion and Replication does not support Kerberos authentication.
Before you configure the connection properties, you need to keep the authentication details handy based on the authentication type that you want to use.
To configure database authentication, you need the user name, password, host name, port, and database name from your PostgreSQL account.
To configure Kerberos authentication, you need the service principal name, host name, port, and database name from your PostgreSQL account. You'll also need to perform certain prerequisite tasks.

Connect to PostgreSQL

Let's configure the PostgreSQL connection properties to connect to PostgreSQL.

Before you begin

Before you get started, get the required information from your PostgreSQL account based on the authentication method that you want to use.
Check out Prepare for authentication to learn more about the authentication prerequisites.

Connection details

The following table describes the PostgreSQL connection properties:
Property
Description
Connection Name
Name of the connection.
Each connection name must be unique within the organization. Connection names can contain alphanumeric characters, spaces, and the following special characters: _ . + -,
Maximum length is 255 characters.
Description
Description of the connection. Maximum length is 4000 characters.
Type
PostgreSQL
Runtime Environment
The name of the runtime environment where you want to run tasks.
For Data Ingestion and Replication, you can use either a local Secure Agent installation or serverless runtime environment. You can use the serverless runtime environment for cloud source types for initial loads only. You cannot run application ingestion and replication or database ingestion and replication tasks on a Hosted Agent.

Authentication types

You can configure database or Kerberos authentication methods to connect to PostgreSQL databases.
Note: For Data Ingestion and Replication, you must use database authentication.
Select the required authentication type and then configure the authentication-specific parameters.

Advanced settings

The following table describes the advanced connection properties:
Property
Description
Schema Name
The schema name.
If you don't specify the schema name, all the schemas available in the database are listed when you import the source object in Data Integration.
Connection Environment SQL
The SQL statement to set up the database environment when you connect to the database. The database environment applies for the entire session that uses this connection. For example, you can enter this statement to set the time zone: SET timezone to 'America/New_York';
Additional Connection Properties
Additional connection parameters that you want to use.
Provide the connection parameters as semicolon-separated key-value pairs.

Encryption types

The encryption method determines if the Secure Agent and the PostgreSQL database server exchange encrypted data. If you do not want to establish a connection using SSL, select noEncryption. PostgreSQL establishes a connection without using SSL. Data is not encrypted. Default is noEncryption.
To use SSL, select the required encryption method and then configure the encryption-specific parameters.

Configure SSL with serverless runtime environment

You can use the serverless runtime environment with PostgreSQL Connector to connect to an SSL-enabled PostgreSQL database.
Before you configure a secure PostgreSQL connection using the serverless runtime environment, complete the following prerequisite tasks to add the SSL certificates to the serverless runtime location:
  1. 1Create the following structure for the serverless agent configuration in AWS or Azure: <Supplementary file location>/serverless_agent_config
    2. Note: Data Ingestion and Replication does not support serverless runtime environments hosted on AWS.
  2. 2Add the truststore and keystore certificates in the Amazon S3 bucket or Azure container in the following location in your AWS or Azure account: <Supplementary file location>/serverless_agent_config/SSL
  3. 3Copy the following code snippet to a text editor:
    4. version: 1
    agent:
    agentAutoApply:
    general:
    sslStore:
    - fileCopy:
    sourcePath: SSL/<TrustStore_filename>
    - fileCopy:
    sourcePath: SSL/<KeyStore_filename>
    where the sourcePath is the directory path of the certificate files in AWS or Azure.
  4. 4Ensure that the syntax and indentations are valid, and then save the file as serverlessUserAgentConfig.yml in the following AWS or Azure location: <Supplementary file location>/serverless_agent_config
    5. When the .yml file runs, the SSL certificates are copied from the AWS or Azure location to the serverless agent directory.
  5. 5In the PostgreSQL connection properties, specify the following certificate path in the serverless agent directory in the Trust Store and Key Store fields: /home/cldagnt/SystemAgent/serverless/configurations/ssl_store/<cert_filename>