PostgreSQL

Prerequisites

Access credentials
Database Name

Connection Properties

The following table describes the fields available when creating a new PostgreSQL Connection. Create a connection using the information below and these step-by-step instructions.

Field	Required	Description
Access Type	Required	This connection type is Read-Only, Write-Only, or Read-Write.
Connection Name	Required	Input your desired name.
Host	Required	IP or the hostname of the OracleDB Server.
Port	Optional	Host server port number.
Database name	Required	Name of the database from PostgreSQL
Requires Credentials	Optional	Check this box to create a new credential or select an existing credential.

Credential Properties

The following table describes the fields available when creating a new PostgreSQL credential.

Field	Required	Description
Credential Name	Required	The name to identify this credential with. This credential will be available as a selection for future use.
Credential Type	Required	This field will automatically populate with `PostgreSQL`.
User	Required	PostgreSQL username to connect with.
Password	Optional	PostgreSQL password to connect with.

Read Connector Properties

The following table describes the fields available when creating a new PostgreSQL Read Connector. Create a new Read Connector using the information below and these step-by-step instructions.

Field	Required	Description
Name	Required	Provide a name for your connector. We recommend using lowercase with underscores in place of spaces.
Description	Optional	Describes the connector. We recommend providing a description if you are ingesting information from the same source multiple times for different reasons.
Table Name	Required	Name of the table to ingest. The table name is case-sensitive. Enclose it in double quotes if the table name is not all upper case.
Schema Name	Optional	The name of the source schema.
Table List Strategy	Optional	If Table Name is selected, provide Table Name.
Ingest Method	Optional	Uses Spark by default to union source tables. You also have the option of using a custom SQL query.
Name of Column that Contains Source Table Name	Optional	If the source table name is listed within a specific column, provide the column name here before generating schema.
Replication Strategy	Optional	Full Resync, Filter by column range, or Incremental column name. See Database Reading Strategies for more information.
Data Version	Optional	A change to Data Version results in no longer using data previously ingested by this Connector, and a complete ingest of new data.

PostgreSQL CDC Replication Strategy

- Change Data Capture: Subscribe to your Postgres Server to capture all database table changes in real time. The user must be a superuser or has a replication role to use CDC.
  - Replication Slot Name: Replication slot ensures that the master server will retain the WAL logs that are
    needed by the replicas even when they are disconnected from the master.
    In order to create it, an external plugin is required.
    It is suggested to use wal2json. The installation instructions are available here:
    https://github.com/eulerto/wal2json and many cloud provider's Postgres have this plugin preinstalled.
    In order to quickly create a slot, run a SQL command in Postgres such as:

SELECT pg_create_logical_replication_slot('ascend_io_slot_1', 'wal2json');

Write Connector Properties

The following table describes the fields available when creating a new PostgreSQL Write Connector. Create a new Write Connector using the information below and these step-by-step instructions.

Field	Required	Description
Name	Required	Provide a name for your connector. We recommend using lowercase with underscores in place of spaces.
Description	Optional	Describes the connector. We recommend providing a description if you are ingesting information from the same source multiple times for different reasons.
Upstream	Required	The name of the previous connector to pull data from.
Table Name	Required	The name of the table to write to in Oracle
Write Strategy	Required	The strategy to copy data into AWS. The Full Load strategy will replace the entire tables contents each time that the upstream transform has changed. If the upstream data set contains multiple Ascend partitions and not all partitions are expected to change on each update, then the Incremental By Partition strategy allows for Ascend to only update/insert/delete the partitions of the Amazon Redshift table that have changed in Ascend.
A SQL Statement for Ascend to Execute Before Writing	Optional	Execute a pre-processing script before writing to final table.
A SQL Statement for Ascend to Execute After Writing	Optional	Execute a pre-processing script after writing to final table.
Schema Name	Optional	The name of the schema.
On Schema Mismatch	Optional	Select how you want Ascend to handle writing data if the schema indicated above does not match the schema of the table in Snowflake. Options are as follows: - Skip schema check - Stop and display error - Recreate table - Alter table
Ingest Method	Optional	Uses Spark by default to union source tables. You also have the option of using a custom SQL query.
Name of Column that Contains Source Table Name	Optional	If the source table name is listed within a specific column, provide the column name here to override the schema.
Data Version	Optional	A change to Data Version results in no longer using data previously ingested by this Connector, and a complete ingest of new data.