Capture From PostgreSQL Using Direct DBMS Log Reading

Since 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.

This section describes the configuration requirements for capturing changes from PostgreSQL using DIRECT capture method. In this capture method (Capture_Method=DIRECT), Fivetran HVR reads transaction log records directly from the DBMS log file using the file I/O.

• The HVR Agent must be installed on the PostgreSQL source database server.

• PostgreSQL configuration file postgresql.conf should have the following settings:

  • wal_level = logical

    SHOW wal_level;
    
    ALTER SYSTEM SET wal_level = logical; -- server restart needed
    

  • archive_mode = on

    SHOW archive_mode;
    
    ALTER SYSTEM SET archive_mode = on; -- server restart needed
    

  • archive_command
    The value of archive_command depends on the location of the archive directory, the operating system and the way archiving is done in a PostgreSQL installation. For example:
    In Unix & Linux,

    SHOW archive_command;
    
    ALTER SYSTEM SET archive_command = 'test ! -f /var/lib/pgsql/9.5/data/archive/%f && cp %p /var/lib/pgsql/9.5/data/archive/%f';   -- server restart needed
    

    In Windows,

    SHOW archive_command;
    
    ALTER SYSTEM SET archive_command = 'copy "%p" "c:\\Program Files\\PostgreSQL\\9.5\\data\\archive\\%f"';   -- server restart needed
    

• The location property XLOG DIRECTORY (PostgreSQL_XLog) must be defined with the directory path to the PostgreSQL transaction log file directory.

  The operating system user as which HVR is running when connecting to PostgreSQL should have read permission to the files in this directory either by directly running HVR as the DBMS owner (postgres) or via a trusted executable named hvr_postgres.

• Action Environment must be defined:

  Group	Table	Action	Parameter(s)
  PostgreSQL	*	Environment	Name=HVR_LOG_RELEASE_DIR, Value=directory_path

  directory_path is the directory where the PostgreSQL transaction log files are archived (e.g. /distr/postgres/935/archive). The operating system user as which HVR is running when connecting to PostgreSQL should have read permission to the files in the directory_path either by directly running HVR as the DBMS owner (postgres) or via a trusted executable named hvr_postgres.

Creating Trusted Executable

Following are the steps to create the trusted executable hvr_postgres in the HVR_HOME/sbin directory,

1. Execute the following commands while logged in as the DBMS owner (postgres):

   $ cd /usr/hvr/hvr_home
   $ cp bin/hvr sbin/hvr_postgres
   $ chmod 4755 sbin/hvr_postgres
   

   If the user postgres does not have permission to write to the HVR installation directories, execute the following commands as user root:

   $ cd /usr/hvr/hvr_home
   $ cp /usr/hvr/hvr_home/bin/hvr /usr/hvr/hvr_home/sbin/hvr_postgres
   $ chown postgres:postgres /usr/hvr/hvr_home/sbin/hvr_postgres
   $ chmod 4755 /usr/hvr/hvr_home/sbin/hvr_postgres
   

2. Additionally, on Linux the trusted executable needs to be patched using:

   $ /usr/hvr/hvr_home/lib/patchelf --set-rpath /usr/hvr/hvr_home/lib --force-rpath /usr/hvr/hvr_home/sbin/hvr_postgres