Requirements for PostgreSQL

From HVR
Jump to: navigation, search

This section describes the requirements, access privileges, and other features of HVR when using PostgreSQL/Aurora PostgreSQL for replication. For information about the capabilities supported by HVR on PostgreSQL, see Capabilities for PostgreSQL. For information about the capabilities supported by HVR on Aurora PostgreSQL, see Capabilities for Aurora PostgreSQL.

For information about compatibility and supported versions of PostgreSQL with HVR platforms, see Platform Compatibility Matrix.

PostgreSQL/Aurora
Capture Hub Integrate
Icon-Yes.png Icon-Yes.png Icon-Yes.png

Connection

HVR requires that the PostgreSQL 9 client (i.e. libpq.so.5 and its dependencies) is installed on the machine from which HVR will connect to PostgreSQL. This can be used to connect to both PostgreSQL 8 and 9 server versions.

Location Connection

This section lists and describes the connection details required for creating PostgreSQL/Aurora PostgreSQL location in HVR.

SC-Hvr-Location PostgreSQL.png
Field Description
PGLIB The optional directory path of the PostgreSQL client.
  Example: /postgres/935/lib
Node The hostname or IP-address of the machine on which the PostgreSQL server is running.
  Example: mypostgresnode
Port The port on which the PostgreSQL server is expecting connections.
  Example: 5432
Database The name of the PostgreSQL database.
  Example: mytestdb
User The username to connect HVR to PostgreSQL Database.
  Example: hvruser
Password The password of the User to connect HVR to PostgreSQL Database.


Hub

HVR allows you to create hub database in PostgreSQL/Aurora PostgreSQL. The hub database is a small database which HVR uses to control its replication activities. This database stores HVR catalog tables that hold all specifications of replication such as the names of the replicated databases, the list of replicated tables, and the replication direction.

Grants for Hub

To capture changes from source database or to integrate changes into target database, the following privileges are required::

  • The User should have permission to create and drop HVR catalog tables.

Capture

HVR supports capturing changes from PostgreSQL versions - 9.4, 9.5, 9.6, and 10.

Grants for Log-Based Capture

In order to perform log-based capture the following statement must be executed for the replicated tables:

alter table tbl replica identity full

HVR Initialize with option Supplemental Logging will run these queries. This requires the User to be either superuser or the owner of the replicated tables. Alternatively, these statements can be performed by a DBA and HVR Initialize should be run without option Supplemental Logging.

Log Read Method - DIRECT

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:

    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
    
    Windows
    show archive_command;
    alter system set archive_command = 'copy "%p" "c:\\Program Files\\PostgreSQL\\9.5\\data\\archive\\%f"';   -- server restart needed
    

HVR action Capture /XLogDirectory should be defined. Parameter /XLogDirectory should contain 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 directly, by running HVR as the DBMS owner (postgres) or via a trusted executable $HVR_HOME/sbin/hvr_postgres.

HVR action Environment /Name /Value should be defined. Parameter /Name should be set to HVR_LOG_RELEASE_DIR. Parameter /Value should contain the directory path to the directory where the PostgreSQL transaction log files are archived (for example: /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 this directory either directly, by running HVR as the DBMS owner (postgres), or via a trusted executable $HVR_HOME/sbin/hvr_postgres.

To create a hvr_postgres executable, perform the following steps while logged in as the DBMS owner (postgres):

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

When user postgres does not have permission to write to the HVR installation directories, the following steps can be performed 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

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

Log Read Method - SQL

HVR action Capture /LogReadMethod should be defined with value SQL.

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
    
  • max_replication_slots=number_of_slots
  • show max_replication_slots;
    alter system set max_replication_slots = number_of_slots;   -- server restart needed
    

    number_of_slots should be set to at least the number of channels multiplied by the number of capture locations in this PostgreSQL installation.

The User should either have replication permission or be superuser

alter user hvruser replication;

The replication plug-in test_decoding should be installed and User should have permission to use it. This plug-in is typically installed in $PG_DATA/lib. To test whether the plug-in is installed and User has the required permissions to execute the following SQL commands:

select pg_create_logical_replication_slot('hvr', 'test_decoding');
select pg_drop_replication_slot('hvr');

When capturing via the SQL interface (Capture/LogReadMethod=SQL) versions before 9.4.12 should be avoided due to a PostgreSQL bug (detected in 9.4.6) which affected this HVR feature.

Capture from Amazon RDS

Capture/LogReadMethod=SQL should be used for capturing from PostgreSQL at Amazon RDS.

To get the required settings and permissions the Parameter Group assigned to the Instance should have rds.logical_replication=1. Changing this needs to be followed by a restart of PostgreSQL.

Replication Slots

Capture/LogReadMethod=SQL uses PostgreSQL replication slots. The names for these slots have to be unique for an entire PostgreSQL cluster.

HVR uses the following naming convention for these replication slots:

'hvr' + '_' + <hub_name> + '_' + '<channel_name> + '_' + <location_name>
For example: 'hvr_hubdb_mychn_src'

This should allow multi capture in most situations. This includes multiple HVR capture jobs and also coexistence with other replication products.

PostgreSQL will not remove transaction log files for which changes exist that have not been processed by a replication slot. For this reason replication slots have to be removed when a channel is no longer needed. This can be done manually or by running hvrinit -d.

To retrieve existing replication slots:

select slot_name from pg_replication_slots;

To manually remove a specific replication slot:

select pg_drop_replication_slot('slot_name');
For example: select pg_drop_replication_slot('hvr_hubdb_mychn_src');

Limitations

Only insert, update and delete changes are captured. truncate is not captured.

Integrate and Refresh (Target)

  • The User should have permission to read and change replicated tables.
grant select, insert, update, delete on tbl to hvruser
  • The User should have permission to create and drop HVR state tables.

Compare and Refresh (Source)

  • The User should have permission to read replicated tables.
grant select on tbl to hvruser