SOAR - warm standby fails to reconnect to the database.

Applicability

Description

When running through the warm standby setup, there will be many errors like the ones below in the PostgreSQL log file: 

< 2024-03-22 12:47:43.076 PDT >FATAL: recovery aborted because of insufficient parameter settings
< 2024-03-22 12:47:43.076 PDT >DETAIL: max_wal_senders = 2 is a lower setting than on the primary server, where its value was 3.
< 2024-03-22 12:47:43.076 PDT >HINT: You can restart the server after making the necessary configuration changes.
< 2024-03-22 12:47:43.076 PDT >LOG: startup process (PID 6169) exited with exit code 1

Warm standby status not reflected in System Health on the UI dashboard located at:

https://<SOAR URL>/admin/system_health/health

Found when checking status on console that DB sync has stopped streaming:

phenv python /<$PHANTOM_HOME>/bin/setup_warm_standby.pyc --status

Unable to rerun the warm standby setup steps, fails trying to connect to the PostgreSQL DB. 

The issue is ibackup and warm standby each create their own postgres conf file.
ibackup creates '<PHANTOM_HOME>/data/data/db/postgresql.ibackup.conf'
warm-standby creates '<PHANTOM_HOME>/data/data/db/postgresql.warm_standby.conf'

In these conf files the value for max_wal_senders is different.

ibackup.conf: max_wal_senders = 3
warm_standby.conf: max_wal_senders = 2

Investigation

The below log files will have clues to see if it is related with steps to remediate in the Resolution section.

warm standby log:

Message:
Script execution
[pid:291078] [06/May/2024 22:13:41] setup_warm_standby.py:459 ERROR:
Exception:
Traceback (most recent call last):
File "bin/setup_warm_standby.py/setup_warm_standby.py", line 2422, in main
File "bin/setup_warm_standby.py/setup_warm_standby.py", line 2302, in _handle_configure_command
File "bin/setup_warm_standby.py/setup_warm_standby.py", line 1648, in _configure_standby_locally
File "bin/setup_warm_standby.py/setup_warm_standby.py", line 888, in _configure_standby_db
File "bin/setup_warm_standby.py/setup_warm_standby.py", line 516, in syscall
File "pycommon3/phantom_common/phproc.py/phproc.py", line 344, in run
File "pycommon3/phantom_common/phproc.py/phproc.py", line 484, in check_returncode
phantom_common.phproc.PhCalledProcessError: Command '/opt/phantom/bin/phsvc start postgresql' returned non-zero exit status 1.
Output: Starting postgresql-15: pg_ctl: could not start server
Examine the log output.
[FAILED]
Error output: None

websocket std err log:

Traceback (most recent call last):
File "/opt/phantom/usr/python39/lib/python3.9/site-packages/django/db/backends/base/base.py", line 219, in ensure_connection
self.connect()
File "/opt/phantom/usr/python39/lib/python3.9/site-packages/django/utils/asyncio.py", line 33, in inner
return func(*args, **kwargs)
File "/opt/phantom/usr/python39/lib/python3.9/site-packages/django/db/backends/base/base.py", line 200, in connect
self.connection = self.get_new_connection(conn_params)
File "www/phantom_ui/db/common/base.py/base.py", line 82, in get_new_connection
File "www/phantom_ui/db/direct/base.py/base.py", line 13, in establish_connection
File "/opt/phantom/usr/python39/lib/python3.9/site-packages/psycopg2/__init__.py", line 127, in connect
conn = _connect(dsn, connection_factory=connection_factory, **kwasync)
psycopg2.OperationalError: connection to server on socket "/tmp//.s.PGSQL.6432" failed: ERROR: pgbouncer cannot connect to server

pg_log file:

< 2024-05-10 08:57:45.841 PDT >LOG: starting PostgreSQL 15.6 on
x86_64-pc-linux-gnu, compiled by gcc (GCC) 10.3.1 20210422 (Red Hat
10.3.1-1), 64-bit
< 2024-05-10 08:57:45.843 PDT >LOG: listening on IPv4 address "127.0.0.1",
port 5432
< 2024-05-10 08:57:45.843 PDT >LOG: could not bind IPv6 address "::1":
Cannot assign requested address
< 2024-05-10 08:57:45.850 PDT >LOG: listening on Unix socket
"/tmp/.s.PGSQL.5432"
< 2024-05-10 08:57:45.865 PDT >LOG: database system was interrupted while
in recovery at log time 2024-05-06 15:07:37 PDT
< 2024-05-10 08:57:45.865 PDT >HINT: If this has occurred more than once
some data might be corrupted and you might need to choose an earlier
recovery target.
< 2024-05-10 08:57:46.003 PDT >LOG: entering standby mode
< 2024-05-10 08:57:46.003 PDT >LOG: restarting backup recovery with redo
LSN A8/EB01DEE8
< 2024-05-10 08:57:46.009 PDT >FATAL: recovery aborted because of
insufficient parameter settings
< 2024-05-10 08:57:46.009 PDT >DETAIL: max_wal_senders = 2 is a lower
setting than on the primary server, where its value was 3.
< 2024-05-10 08:57:46.009 PDT >HINT: You can restart the server after
making the necessary configuration changes.
< 2024-05-10 08:57:46.010 PDT >LOG: startup process (PID 3775630) exited
with exit code 1
< 2024-05-10 08:57:46.010 PDT >LOG: aborting startup due to startup
process failure
< 2024-05-10 08:57:46.012 PDT >LOG: database system is shut down

Resolution

This is product issue and it will be fixed in the SOAR 6.3 release. Currently, a workaround can be applied to successfully enable warm standby with ibackup. All commands need to be run as the user who installed SOAR, normally suggest the user 'phantom':

(optional) Use the recreate_local_db on the standby node to get a clean database. 

1. Turn off ibackup on primary node (--off option is only available after 6.2.1. For 6.2.0 version, delete the pgbackrest.conf and postgresql.ibackup.conf file from the folder <$PHANTOM_HOME>/data/db

<$PHANTOM_HOME>/bin/phenv ibackup --off

2a. On the primary node, turn off warm standby:

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

2b. On standby node, turn off warm standby:

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

 If standby.signal file is under <$PHANTOM_HOME>/data/db due to previous attempt, delete it. When the file exists, the PostgreSQL is in standby mode and not accepting client connections. After deleting the file, standby mode is exited and it switches to normal operation. 

3. Restart phantom service on primary and standby node:

<$PHANTOM_HOME>/bin/stop_phantom.sh
<$PHANTOM_HOME>/bin/start_phantom.sh

4. On primary and standby node, verify the parameter in the database. The value "10" is default for PostgreSQL version 15. As both ibackup and warm standby are off, now the DB is using the default value.

psql -c "SHOW max_wal_senders;"

Output example:

max_wal_senders
-----------------
10
(1 row)

5a. On primary node, re-run the warm standby setup:

<$PHANTOM_HOME>/bin/phenv python <$PHANTOM_HOME>/bin/setup_warm_standby.pyc --primary-mode --configure --primary-ip <primary ip> --standby-ip <standby ip>

5b. After primary node is finished, on standby node, re-run the warm standby setup

<$PHANTOM_HOME>/bin/phenv python <$PHANTOM_HOME>/bin/setup_warm_standby.pyc --standby-mode --configure --primary-ip <primary ip> --standby-ip <standby ip>

6. Stop phantom service on both the primary and secondary nodes:

<$PHANTOM_HOME>/bin/stop_phantom.sh

7. Change the setting of max_wall_senders = 2 to max_wall_senders = 3 in the <PHANTOM_HOME>/data/db/postgresql.warm_standby.conf file on both the primary and secondary nodes.

8. Restart SOAR on both the primary and secondary nodes, starting with the primary node first:

<$PHANTOM_HOME>/bin/start_phantom.sh

9. Setup ibackup on the primary node (change the version to the SOAR you currently use). Please refer to the document link Use ibackup with warm standby

10. Verify warm standby status is "currently streaming" on both the primary & secondary nodes:

<$PHANTOM_HOME>/bin/phenv python <$PHANTOM_HOME>/bin/setup_warm_standby.pyc --status