PostgreSQL as Source
Capture
Fivetran HVR supports capturing changes from PostgreSQL location. HVR uses PostgreSQL native client library "libpq" to connect to the PostgreSQL server. For the list of supported PostgreSQL versions from which HVR can capture changes, see Capture changes from location in Capabilities.
Table Types
- HVR supports capture from regular tables.
- HVR does not support capture from inheritance tables.
Capture Methods
HVR supports the following methods for capturing changes from PostgreSQL:
Logical Replication (SQL Fetch from Replication Slot): Capture changes using PostgreSQL's logical decoding infrastructure and streaming replication protocol. This is the default and recommended capture method for any PostgreSQL installation.
Direct DBMS Log Reading: Capture changes directly from PostgreSQL's binary logs and archive.
Since version 6.1.5/5, capture from PostgreSQL using Direct DBMS Log Reading method is deprecated. The support for this capture method will be removed in the future HVR 6 releases. We encourage you to consider migrating to Logical Replication capture method.
Grants for Log-Based Capture
This section lists the grants required for capturing changes from PostgreSQL database.
For Logical Replication capture method, the HVR database user must have the
REPLICATION
privilege.For Direct DBMS Log Reading capture method, the HVR database user must have the
SUPERUSER
privilege or ownership of the tables for the Activate Replication function with Supplemental Logging option to change theREPLICA IDENTITY
of the replicated tables.
Configuring REPLICA IDENTITY
In PostgreSQL databases, the REPLICA IDENTITY
setting is crucial for replication and data capture, especially in cases involving row-level changes. It specifies how PostgreSQL identifies rows in replicated tables, using either a PRIMARY KEY
or a UNIQUE INDEX
. This is essential for understanding how data changes, such as updates or deletes, are tracked and replicated to another system.
There are several options for REPLICA IDENTITY
:
DEFAULT
: This is the default option. It uses the table'sPRIMARY KEY
as the replication key.Without a
PRIMARY KEY
, HVR cannot replicate tables ifREPLICA IDENTITY
is set toDEFAULT
. Consider adding aPRIMARY KEY
in such cases.USING INDEX
: This option allows the use of aUNIQUE INDEX
, other than thePRIMARY KEY
, as the replication key.If the chosen
UNIQUE INDEX
is dropped, HVR will not replicate the tables. Consider changingREPLICA IDENTITY
toDEFAULT
or using a differentUNIQUE INDEX
.FULL
: This option uses all table columns as the replication key. If available, HVR uses thePRIMARY KEY
or aUNIQUE INDEX
as the replication key.Until version 6.1.5/4, or when using the Direct DBMS Log Reading capture method, it was required to perform Activate Replication with Supplemental Logging (equivalent to hvractivate with option -ol in the command line) to set
REPLICA IDENTITY
toFULL
for all replicated tables. Since version 6.1.5/4, settingREPLICA IDENTITY
toFULL
is no longer necessary.NOTHING
: This option explicitly disables theREPLICA IDENTITY
mechanism, making it impossible to captureUPDATE
andDELETE
operations.This option is generally not recommended outside of testing environments.
The
REPLICA IDENTITY
setting in PostgreSQL can be configured usingALTER TABLE
statement.For example:
ALTER TABLE table_name REPLICA IDENTITY FULL;
Both capture methods depend on the REPLICA IDENTITY
setting of the replicated tables:
- Direct DBMS Log Reading is supported only when
REPLICA IDENTITY
is set toFULL
. - Logical Replication supports all options since version 6.1.5/4.
Capture Limitations
HVR captures only the key columns based on the selected
REPLICA IDENTITY
forDELETE
operations and the before-change version ofUPDATE
operations. This has the following effects:- Non-key columns will be
NULL
forDELETE
operations when action ColumnProperties with parameter TimeKey is used. - Only the key columns will be available to the CaptureExpression when CaptureExpressionType is set to SQL_PER_ROW in ColumnProperties action.
- Non-key columns will be
Until version 6.1.5/3, only
INSERT
,UPDATE
, andDELETE
operations are captured,TRUNCATE
is not captured. Since version 6.1.5/3,TRUNCATE
is also captured by Logical Replication when PostgreSQL version is 11 or higher.TRUNCATE
of table partitions is not captured.Until version 6.1.5/6, AdaptDDL was not supported for PostgreSQL. Since version 6.1.5/6, AdaptDDL is supported when using the Logical Replication capture method. However, this support is limited to DDL commands that modify the columns of a table in the channel, such as
ALTER TABLE ... ADD COLUMN
,ALTER TABLE ... DROP COLUMN
, etc. Commands likeCREATE TABLE
orDROP TABLE
are not supported.DDL commands modifying the columns of a table are not immediately captured. HVR detects these changes in the following
INSERT
orUPDATE
statement.Only some of the AdaptDDL action parameters are supported for PostgreSQL. The supported parameters are OnEnrollBreak, RefreshOptions, and KeepExistingStructure. Option -m of RefreshOptions is not supported.
For bi-directional replication, the state tables for collision detection must be created before replication begins. You can create state tables by activating the channel with the State Tables option selected or using the CLI command hvractivate with the option -os. If the state table is created while capture is running, capture will re-capture changes coming from integrate and/or Refresh.
Compare and Refresh from Source
HVR allows you to perform only Compare and Refresh from PostgreSQL database (without using Capture). This section describes the configuration requirements for performing Compare and Refresh from PostgreSQL location.
Grants for Compare and Refresh from PostgreSQL
This section lists the grants required for performing only Compare and Refresh from PostgreSQL database.
The HVR database User must be granted the SELECT
privilege:
shell GRANT SELECT ON tablename TO username;