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 14).
- Log-based capture (capture from DBMS logging system) (PostgreSQL from version 9.4 to version 14).
- Log-based capture of tables without a primary key (PostgreSQL from version 9.4 to version 14).
- Direct access to logs on a file system (PostgreSQL from version 9.4 to version 14, 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 14).
- Log-based capture of tables with LOB column (PostgreSQL from version 9.4 to version 14).
- Rewind log-based capture to specific time (hvractivate option -i) (PostgreSQL from version 9.4 to version 14).
- Rewind log-based capture to the beginning of currently active oldest transaction (PostgreSQL from version 9.4 to version 14).
- Multiple log-based capture jobs can capture from same database (PostgreSQL from version 9.4 to version 14).
- Capture from tables with compression pglz (PostgreSQL from version 9.4 to version 14).
- Capture from tables with compression lz4 (PostgreSQL version 14).
- Log-based capture checkpointing using location property Capture_Checkpoint_Frequency (PostgreSQL from version 9.4 to version 14).
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 14).
- Burst integration (Integrate with parameter Method=BURST) (PostgreSQL from version 9.4 to version 14).
- Integrate with parameter BurstCommitFrequency (PostgreSQL from version 9.4 to version 14).
- Continuous integration (Integrate with parameter Method=CONTINUOUS) (PostgreSQL from version 9.4 to version 14).
- Tables without a key and without TableProperties with NoDuplicateRows for continuous integration (PostgreSQL from version 9.4 to version 14).
- Continuous integrate with parameter OnErrorSaveFailed (PostgreSQL from version 9.4 to version 14).
- ColumnProperties with parameter SoftDelete (PostgreSQL from version 9.4 to version 14).
- Creation and update of HVR state tables (PostgreSQL from version 9.4 to version 14).
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 14, state table needs to be created before replication starts).
- CollisionDetect with parameter TimestampColumn (PostgreSQL from version 9.4 to version 14).
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 14).
- hvrrefresh into target location (PostgreSQL from version 9.4 to version 14).
- Row-wise hvrrefresh into target location (option -g) (PostgreSQL from version 9.4 to version 14).
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 14).
- International table and column names where DBMS is not configured with UTF-8 encoding (PostgreSQL from version 9.4 to version 14).
- 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 14).
- Always treat DBMS schema names case sensitive (PostgreSQL from version 9.4 to version 14).
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'.