pg_last_xact_replay_timestamp is null in replica snapshot rebuild pipelines of Data-Server Rebuild Ansible project

Snehansh Chaturvedi
2026-04-02T12:35:15Z

## General Information

- Related issue for context (if applicable): [+ link +]
- Service this relates to (if applicable): [+ service_label +]

https://gitlab.com/gitlab-data/analytics/-/work_items/26799

https://gitlab.com/gitlab-com/gl-infra/data-server-rebuild-ansible

## Details

[+ Please provide as much detail and context as possible including any time constraints and customer impact]

`pg_last_xact_replay_timestamp()` is intermittently returning NULL during the `ansible-deploy` stage of the Data-Server Rebuild pipeline. This NULL value gets persisted into last_replication_timestamp, which the Data Platform team relies on to validate replica snapshot liveness. When NULL, downstream processes fail.

## Root Cause

When `ansible-deploy` retries after a failed run, the rebuilt replica instance may still be mid-bootstrap, Patroni is up, but WAL streaming replication has not yet been established. In this state, `pg_last_xact_replay_timestamp()` returns NULL (not an error). The pipeline then truncates and reinserts this NULL into `last_replication_timestamp`, overwriting any previously valid value.

## Impact

Downstream processes that depend on last_replication_timestamp for replica liveness validation fail intermittently.
Manual intervention is currently required to unblock affected pipelines.

## Requested Actions

1. Guard checks before timestamp write (immediate fix). Before truncating and reinserting into `last_replication_timestamp`, add the following validations:
- `pg_is_in_recovery()` must return true (confirms the instance is actually a standby)
- `pg_last_xact_replay_timestamp()` must return a non-NULL value
- If either condition fails, the insert should be skipped or the pipeline should wait/retry until the replica is actively streaming.

Link to process code: https://gitlab.com/gitlab-com/gl-infra/data-server-rebuild-ansible/-/blob/main/rebuild.yml?ref_type=heads#L171

2. Investigate WAL replay delay on freshly rebuilt instances
- Determine whether the bootstrap or Chef convergence process is causing WAL replay to be consistently delayed after instance rebuilds, and address if possible.

### Evidence

Example of successful timestamp: https://gitlab.com/gitlab-com/gl-infra/data-server-rebuild-ansible/-/jobs/13753437393#L129

Example of NULL timestamp: https://gitlab.com/gitlab-com/gl-infra/data-server-rebuild-ansible/-/jobs/13748335968#L129

Example pipeline with multiple ansible-deploy runs: https://gitlab.com/gitlab-com/gl-infra/data-server-rebuild-ansible/-/pipelines/2423830368