Please provide support for Postgres HADR setups for BOTH Agent and SQL Capture
I can confirm that this can be acheived by making use of the HVR_PQ_CONNECT_STRING
and setting this to something like :-
HVR_PQ_CONNECT_STRING = dbname=hvr_logicalrepl host=ivdb1320177.devin1.ms.com,ivdb1305547.devin3.ms.com port=5051,5052 connect_timeout=0 target_session_attrs=read-write
The scope of this also needs to be set to the source location.
If we run agent based capture on both nodes of the HADR cluster then we get duplicate transactions being integrated into the target locations. This is due to the capture process picking up the transactions from both sides. We need a mechanism to only capture the changes from a single side of the cluster.
We make use of HADR postgres feature for ALL production instances of Postgres. This is also used for our HVR repository databases. We currently get Repository databases to work with the environment variable above, but this is also a bit messy.