SOAR - warm standby setup failed in the standby node

Applicability

Description

It is SOAR 6.2.2 on-prem systems. The warm standby setup was successful in the primary node however it failed in the standby node.

Investigation

In <PHANTOM_HOME>/var/log/warm_standby.log, the error message indicates that the standby node cannot connect to primary server using "replicator" user with correct password. However the password for "replicator" entered is the same as the one entered in the primary node.

[pid:1823425] [27/Aug/2024 20:07:33]    phproc.py:255  DEBUG: Shell command: bash -c $'PGPASSFILE=\'/opt/soar/.pgpass\' pg_basebackup -h <primary IP> -D /opt/soar/data/db -U replicator -v -P -w'
[pid:1823425] [27/Aug/2024 20:07:33]    phproc.py:278  DEBUG: pg_basebackup: error: connection to server at "<primary IP>", port 5432 failed: FATAL:  password authentication failed for user "replicator"
[pid:1823425] [27/Aug/2024 20:07:33]    phproc.py:278  DEBUG: password retrieved from file "/opt/soar/.pgpass"
[pid:1823425] [27/Aug/2024 20:07:33]    phproc.py:278  DEBUG: connection to server at "<primary IP>", port 5432 failed: FATAL:  no pg_hba.conf entry for replication connection from host "<standby IP>", user "replicator", no encryption
[pid:1823425] [27/Aug/2024 20:07:33] setup_warm_standby.py:460  ERROR: 
Exception:
Traceback (most recent call last):
  File "bin/setup_warm_standby.py/setup_warm_standby.py", line 517, in syscall
  File "pycommon3/phantom_common/phproc.py/phproc.py", line 326, in run
  File "pycommon3/phantom_common/phproc.py/phproc.py", line 466, in check_returncode
phantom_common.phproc.PhCalledProcessError: Command 'bash -c $'PGPASSFILE=\'/opt/soar/.pgpass\' pg_basebackup -h <primary IP> -D /opt/soar/data/db -U replicator -v -P -w'' returned non-zero exit status 1.
Output: pg_basebackup: error: connection to server at "<primary IP>", port 5432 failed: FATAL:  password authentication failed for user "replicator"
password retrieved from file "/opt/soar/.pgpass"
connection to server at "<primary IP>", port 5432 failed: FATAL:  no pg_hba.conf entry for replication connection from host "<standby IP>", user "replicator", no encryption
Error output: None

Resolution

The password used for the "replicator" user has % which is not supported by PostgreSQL ODBC connection. One of the links can be referenced as:

PostgreSQL: Re: Does psqlodbc_11_01_0000-x64 support special characters?

To resolve this issue, generating a new password for "replicator" user without % character then re-configure warm standby:

1. Disable the current warm standby on the primary node:

   phenv python /<PHANTOM_HOME>/bin/setup_warm_standby.pyc --primary-mode --off

2. On the standby node, the <PHANTOM_HOME>/data/db folder has been removed. To recover it, copy <PHANTOM_HOME>/data/tmp/db to <PHANTOM_HOME/data/db and pg_hba.conf, postgresql.conf and postgresql.auto.conf from <PHANTOM_HOME>/data folder to <PHANTOM_HOME>/data/db folder. Restarting postgresql service without any error.

   cp -pr <PHANTOM_HOME>/data/tmp/db <PHANTOM_HOME>/data/db
   cd <PHANTOM_HOME>/data
   cp -pr pg_hba.conf postgresql.conf postgresql.auto.conf db/
   <PHANTOM_HOME>/bin/phsvc restart postgresql

3. Run the warm standby setup again on both primary and standby nodes. Please refer to the document: Create a warm standby - Splunk Documentation