Connections for INFACore > Connections to source and target endpoints > PostgreSQL

PostgreSQL

Create a PostgreSQL connection to read from or write to PostgreSQL and Amazon Aurora PostgreSQL databases.

Feature snapshot

Operation	Support
Read	Yes
Write	Yes

Before you begin

Before you configure the connection properties, you'll need to get information from your PostgreSQL Cloud account.

The following video shows you how to get information from your PostgreSQL Cloud account:

Connection properties

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.
Host Name	Host name of the PostgreSQL server to which you want to connect.
Port	Port number for the PostgreSQL server to which you want to connect. Default is 5432.
Schema	The schema name. If you don't specify the schema name, all the schemas available in the database are listed
Database	The PostgreSQL database name.
User Name	User name to access the PostgreSQL database.
Password	Password for the PostgreSQL database user name.
Encryption Method	Determines whether the data exchanged between the agent and the PostgreSQL database server is encrypted: Select one of the following encryption methods: - noEncryption. Establishes a connection without using SSL. Data is not encrypted. - SSL. Establishes a connection using SSL. Data is encrypted using SSL. If the PostgreSQL database server can't configure SSL, the connection fails. - requestSSL. Attempts to establish a connection using SSL. If the PostgreSQL database server can't configure SSL, the Secure Agent establishes an unencrypted connection. Default is noEncryption.
Validate Server Certificate	Applicable if you select SSL or requestSSL as the encryption method. Select the Validate Server Certificate option so that the Secure Agent validates the server certificate that is sent by the PostgreSQL database server. If you specify the Host Name In Certificate property, the Secure Agent also validates the host name in the certificate.
TrustStore	Applicable if you select SSL or requestSSL as the encryption method and the Validate Server Certificate option. The path and name of the truststore file, which contains the list of the Certificate Authorities (CAs) that the PostgreSQL client trusts.
TrustStore Password	Applicable if you select SSL or requestSSL as the encryption method and the Validate Server Certificate option. The password to access the truststore file that contains the SSL certificate.
Host Name In Certificate	Optional when you select SSL or requestSSL as the encryption method and the Validate Server Certificate option. A host name for providing additional security. The Secure Agent validates the host name included in the connection with the host name in the SSL certificate.
KeyStore	Applicable if you select SSL as the encryption method and when client authentication is enabled on the PostgreSQL database server. The path and the file name of the key store. The keystore file contains the certificates that the PostgreSQL client sends to the PostgreSQL server in response to the server's certificate request.
KeyStore Password	Applicable if you select SSL as the encryption method and when client authentication is enabled on the PostgreSQL database server. The password for the keystore file required for secure communication.
Key Password	Applicable if you select SSL as the encryption method and when client authentication is enabled on the PostgreSQL database server. Required when individual keys in the keystore file have a different password than the keystore file.
Additional Connection Properties	Additional connection parameters that you want to use. Provide the connection parameters as semicolon-separated key-value pairs.
Crypto Protocol Versions	Required if you select SSL or requestSSL as the encryption method. A cryptographic protocol or a list of cryptographic protocols to use with an encrypted connection. You can select one of the following protocols: - SSLv3 - TLSv1_2 Default is TLSv1_2.

Read properties

The following table describes the advanced source properties that you can configure in the Python code to read from PostgreSQL:

Property	Description
Pre-SQL	The pre-SQL commands to run a query before you read data from PostgreSQL. You can partially parameterize pre-SQL with values specified in a parameter file.
Post-SQL	The post-SQL commands to run a query after you write data to a target. You can partially parameterize post-SQL with values specified in a parameter file.
Fetch Size	Determines the number of rows to read in one resultant set from PostgreSQL. Specifying a number limits the number of rows to fetch with each trip to the database and avoids unnecessary memory consumption. You can specify a maximum fetch size of 2147483647. Default is 100000.
Schema Name	Overrides the schema name of the source object.
Source Table Name	Overrides the default PostgreSQL source table name.
SQL Override	The SQL statement to override the default query generated from the specified source type to read data from the PostgreSQL source. You can partially parameterize SQL override with values specified in a parameter file. Ensure that the list of selected columns, data types, and the order of the columns that appear in the query matches the columns, data types, and order in which they appear in the source object. Note: SQL override is not applicable when you enable partitioning. If you specify an SQL override and configure partitioning, the mapping fails.

Write properties

The following table describes the advanced target properties that you can configure in the Python code to write to PostgreSQL:

Property	Description
Update Mode	Specifies the mode to write data to PostgreSQL target. You can specify the following modes: - Update As Update. Updates all rows flagged for update if the entries exist. - Update Else Insert. Updates all rows flagged for update if the entries exist in the target. If the entries do not exist, the Secure Agent inserts the entries.
Override Target Query	An SQL statement to override the default update query that the Secure Agent generates for the update operation.
Schema Name	Overrides the schema name of the target object.
Target Table Name	Overrides the default PostgreSQL target table name.
Pre-SQL	The pre-SQL commands to run a query before you read data from a source. You can partially parameterize pre-SQL with values specified in a parameter file.
Post-SQL	The post-SQL commands to run a query after you write data to PostgreSQL. You can partially parameterize post-SQL with values specified in a parameter file.
Truncate Target	The Secure Agent truncates the target before writing the data.
Enable target bulk load	Performs bulk upload when you configure an insert operation to write to PostgreSQL. Select this option to improve the performance of inserting data in bulk to PostgreSQL. Default is unselected. Note: When you enable the target bulk mode to insert data to PostgreSQL, error files are not generated for rejected records.
Batch size	The number of rows that the Secure Agent writes in a single batch to PostgreSQL. Specify a batch size value that is greater than zero. Applicable if you select the Enable target bulk load option.
Reject File Directory	The directory that stores the rejected files. Specify the directory where you want to store the rejected files.
Reject File Name	Name of the rejected file that is stored in the reject file directory.