Switching XLog source from archive to streaming when primary available

SATYANARAYANA NARLAPURAM <satyanarlapuram@gmail.com>

From: SATYANARAYANA NARLAPURAM <satyanarlapuram@gmail.com>

To: PostgreSQL-development <pgsql-hackers@postgresql.org>

Date: 2021-11-28T20:00:34Z

Lists: pgsql-hackers

Commits

Same data as JSON: GET /api/v1/messages/:b64id/commits the thread's linked commits as JSON, with link sources. API reference →

Add basic TAP tests for the low-level backup method, take two
- 071e3ad59d6f 17.0 cited
Add a failover option to subscriptions.
- 776621a5e479 17.0 cited
Make all Perl warnings fatal
- c5385929593d 17.0 cited
Add PostgreSQL::Test::Cluster::advance_wal
- c161ab74f76a 17.0 cited
Rename wal_keep_segments to wal_keep_size.
- c3fe108c025e 14.0 cited

Hi Hackers,

When the standby couldn't connect to the primary it switches the XLog
source from streaming to archive and continues in that state until it can
get the WAL from the archive location. On a server with high WAL activity,
typically getting the WAL from the archive is slower than streaming it from
the primary and couldn't exit from that state. This not only increases the
lag on the standby but also adversely impacts the primary as the WAL gets
accumulated, and vacuum is not able to collect the dead tuples. DBAs as a
mitigation can however remove/advance the slot or remove the
restore_command on the standby but this is a manual work I am trying to
avoid. I would like to propose the following, please let me know your
thoughts.

   - Automatically attempt to switch the source from Archive to streaming
   when the primary_conninfo is set after replaying 'N' wal segment governed
   by the GUC retry_primary_conn_after_wal_segments
   - when  retry_primary_conn_after_wal_segments is set to -1 then the
   feature is disabled
   - When the retry attempt fails, then switch back to the archive

Thanks,
Satya