PostgreSQL

Learn the required and optional properties of creating a PostgreSQL Connection, Credential, Read Connector, and Write Connector.

postgresql logo

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.

FieldRequiredDescription
Access TypeRequiredThis connection type is Read-Only, Write-Only, or Read-Write.
Connection NameRequiredInput your desired name.
HostRequiredIP or the hostname of the OracleDB Server.
PortOptionalHost server port number.
Database nameRequiredName of the database from PostgreSQL
Requires CredentialsOptionalCheck 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.

FieldRequiredDescription
Credential NameRequiredThe name to identify this credential with. This credential will be available as a selection for future use.
Credential TypeRequiredThis field will automatically populate with PostgreSQL.
UserRequiredPostgreSQL username to connect with.
PasswordOptionalPostgreSQL 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.

FieldRequiredDescription
NameRequiredProvide a name for your connector. We recommend using lowercase with underscores in place of spaces.
DescriptionOptionalDescribes the connector. We recommend providing a description if you are ingesting information from the same source multiple times for different reasons.
Table NameRequiredName 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 NameOptionalThe name of the source schema.
Table List StrategyOptionalIf Table Name is selected, provide Table Name.
Ingest MethodOptionalUses 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 NameOptionalIf the source table name is listed within a specific column, provide the column name here before generating schema.
Replication StrategyOptionalFull Resync, Filter by column range, or Incremental column name. See Database Reading Strategies for more information.
Data VersionOptionalA 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.

FieldRequiredDescription
NameRequiredProvide a name for your connector. We recommend using lowercase with underscores in place of spaces.
DescriptionOptionalDescribes the connector. We recommend providing a description if you are ingesting information from the same source multiple times for different reasons.
UpstreamRequiredThe name of the previous connector to pull data from.
Table NameRequiredThe name of the table to write to in Oracle
Write StrategyRequiredThe 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 WritingOptionalExecute a pre-processing script before writing to final table.
A SQL Statement for Ascend to Execute After WritingOptionalExecute a pre-processing script after writing to final table.
Schema NameOptionalThe name of the schema.
On Schema MismatchOptionalSelect 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 MethodOptionalUses 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 NameOptionalIf the source table name is listed within a specific column, provide the column name here to override the schema.
Data VersionOptionalA change to Data Version results in no longer using data previously ingested by this Connector, and a complete ingest of new data.