pgsql-hackers-bug-report-final.md

# BUG: Cascading standby fails to reconnect after falling back to archive recovery

## Summary

PostgreSQL has a bug where cascading standbys fail to reconnect to their upstream after falling back to archive recovery. The standby requests streaming from an incorrect WAL position (next segment boundary instead of current position), causing connection failures when the upstream uses archive-only recovery.

## Affected Versions

Confirmed on PostgreSQL 17.x and 18.x. The relevant code exists in earlier versions as well.

## Reproduction

The bug occurs in cascading replication setups where:
1. Primary server with archiving enabled
2. Standby server using archive-only recovery (no streaming from primary)
3. Cascading standby streaming from the archive-only standby

### Steps to Reproduce

Setup topology:
```
Primary (15432) → Archive
                    ↓
            Standby (15433) [archive-only]
                    ↓
            Cascade (15434) [streaming]
```

The bug triggers when:
1. The cascading standby loses its streaming connection
2. Falls back to archive recovery
3. Attempts to reconnect for streaming

### Error Message

```
FATAL: could not receive data from WAL stream:
ERROR: requested starting point 0/A000000 is ahead of the WAL flush position of this server 0/9000000
```

The cascade requests position 0/A000000 (next 16MB boundary) while the upstream is at 0/9000000 (current segment).

## Reproducer Scripts

Two shell scripts are provided that demonstrate the issue:

1. **reproducer_restart_upstream_portable.sh** - Triggers bug by restarting the upstream standby
2. **reproducer_cascade_restart_portable.sh** - Triggers bug by restarting the cascade itself

Both scripts:
- Create a three-node setup (primary, archive-only standby, cascading standby)
- Use ports 15432-15434 and /tmp for unix sockets
- Require PostgreSQL binaries in PATH
- Reliably reproduce the error

## Technical Details

When a cascading standby falls back to archive recovery and then attempts to reconnect:

1. Reads WAL from archive up to position (e.g., 0/9XXXXXX)
2. Calls RequestXLogStreaming() to reconnect
3. Function rounds position to segment boundary
4. Uses wrong position variable, rounds to next segment (0/A000000)
5. Archive-only upstream cannot provide future segment
6. Connection fails, retry loop continues

The issue appears to be in `src/backend/access/transam/xlogrecovery.c` around line 3880, where the position passed to RequestXLogStreaming() determines which segment boundary is requested.

## Impact

Affects cascading replication topologies where:
- Intermediate standbys use archive-only recovery
- Disaster recovery setups relay through archive storage
- Replicas cascade through non-streaming standbys

Once triggered, the cascading standby cannot reconnect without manual intervention. The cascade restart reproducer shows that even restarting the cascade itself triggers the bug, affecting routine maintenance operations.

## Example Log Output

From cascading standby after connection loss:

```
2025-01-27 10:15:23.456 CASCADE: starting WAL streaming at 0/9000000 (timeline 1)
2025-01-27 10:15:23.457 CASCADE: could not receive data from WAL stream: ERROR: requested starting point 0/A000000 is ahead of the WAL flush position of this server 0/9000148
2025-01-27 10:15:28.462 CASCADE: starting WAL streaming at 0/9000000 (timeline 1)
2025-01-27 10:15:28.463 CASCADE: could not receive data from WAL stream: ERROR: requested starting point 0/A000000 is ahead of the WAL flush position of this server 0/9000148
```

The pattern repeats indefinitely with the cascade requesting 0/A000000 while the upstream remains at 0/9XXXXXX.