Capabilities for PostgreSQL

This section lists the Capabilities of Fivetran HVR when using 'PostgreSQL'. For more information about the pre-requisites, access privileges, and other configuration requirements, see PostgreSQL Requirements.

Capture

HVR supports the following capabilities on PostgreSQL:

• Capture changes from location (PostgreSQL from version 9.4 to version 16).
• Log-based capture (capture from DBMS logging system) (PostgreSQL from version 9.4 to version 16).
• Log-based capture of tables without a primary key (PostgreSQL from version 9.4 to version 16).
• Direct access to logs on a file system (PostgreSQL from version 9.4 to version 16, except on Amazon RDS for PostgreSQL, Google Cloud SQL for PostgreSQL, and Microsoft Azure Database for PostgreSQL).
• Access to logs using SQL interface (PostgreSQL from version 9.4 to version 16).
• Log-based capture of tables with LOB column (PostgreSQL from version 9.4 to version 16).
• Rewind log-based capture to specific time (hvractivate option -i) (PostgreSQL from version 9.4 to version 16).
• Rewind log-based capture to the beginning of currently active oldest transaction (PostgreSQL from version 9.4 to version 16).
• Multiple log-based capture jobs can capture from same database (PostgreSQL from version 9.4 to version 16).
• Capture from tables with compression pglz (PostgreSQL from version 9.4 to version 16).
• Capture from tables with compression lz4 (PostgreSQL from version 14 to version 16).
• Log-based capture checkpointing using location property Capture_Checkpoint_Frequency (PostgreSQL from version 9.4 to version 16).

HVR does not support the following capabilities on PostgreSQL:

• Capture from Archive log files only.
• Log-based capture of DDL statements using action AdaptDDL.
• Log-based capture from hidden rowid/RRN column (ColumnProperties with parameter CaptureFromRowId).
• Rewind log-based capture to specific time (hvractivate option -i) with Capture Method SQL.
• Rewind log-based capture to the beginning of currently active oldest transaction for a specific list of tables.
• Online refresh using accurate LSN/SCN.
• Populates column hvr_cap_user for use in ColumnProperties {hvr_cap_user} substitutions.
• Log-based capture of truncate table statements.
• Read archives from an alternative directory (location property Archive_Log_Path on the capture location).
• Trigger-based capture (location property Capture_Method=DB_TRIGGER on the capture location).

Repository Database

HVR supports Repository database on PostgreSQL.

Integrate

HVR supports the following capabilities on PostgreSQL:

• Integrate changes into location (PostgreSQL from version 9.4 to version 16).
• Burst integration (Integrate with parameter Method=BURST) (PostgreSQL from version 9.4 to version 16).
• Integrate with parameter BurstCommitFrequency (PostgreSQL from version 9.4 to version 16).
• Continuous integration (Integrate with parameter Method=CONTINUOUS) (PostgreSQL from version 9.4 to version 16).
• Tables without a key and without TableProperties with NoDuplicateRows for continuous integration (PostgreSQL from version 9.4 to version 16).
• Continuous integrate with parameter OnErrorSaveFailed (PostgreSQL from version 9.4 to version 16).
• ColumnProperties with parameter SoftDelete (PostgreSQL from version 9.4 to version 16).
• Creation and update of HVR state tables (PostgreSQL from version 9.4 to version 16).

HVR does not support the following capabilities on PostgreSQL:

• Disable/enable database triggers during integrate (with parameter NoTriggerFiring).
• Integrate with parameter DbProc.

Bi-directional Replication

HVR supports the following capabilities on PostgreSQL:

• Detection of changes made by HVR in a bidirectional channel to prevent loop-back (PostgreSQL from version 9.4 to version 16, state table needs to be created before replication starts).
• CollisionDetect with parameter TimestampColumn (PostgreSQL from version 9.4 to version 16).

HVR does not support the following capabilities on PostgreSQL:

• CollisionDetect with Log-based Capture (without parameter TimestampColumn).

Refresh and Compare

HVR supports the following capabilities on PostgreSQL:

• hvrrefresh or hvrcompare from source location (PostgreSQL from version 9.4 to version 16).
• hvrrefresh into target location (PostgreSQL from version 9.4 to version 16).
• Row-wise hvrrefresh into target location (option -g) (PostgreSQL from version 9.4 to version 16).

Other Capabilities

HVR supports the following capabilities on PostgreSQL:

• Call database procedure dbproc during replication jobs (AgentPlugin with parameter DbProc) (PostgreSQL from version 11 to version 16).
• International table and column names where DBMS is not configured with UTF-8 encoding (PostgreSQL from version 9.4 to version 16).
• Always treat DBMS table names and column names as case sensitive, even if not configured (equivalent to location property Case_Sensitive_Names always set to TRUE) (PostgreSQL from version 9.4 to version 16).
• Always treat DBMS schema names case sensitive (PostgreSQL from version 9.4 to version 16).

HVR does not support the following capabilities on PostgreSQL:

• Use distribution key for parallelizing changes within a table (ColumnProperties with parameter DistributionKey).
• Lossless binary float datatypes. No data loss when transporting float values (because base 2 fractions are never converted to base 10). The values for these datatypes, when selected back from the database, might be slightly different from the ones that were inserted. This leads to Compare differences..
• Distinguish and support capture from 'materialized views'.