could not receive data from WAL stream: ERROR: requested WAL segment XXX has already been removed

Question

Heads up - I have never dealt with postgres replication before; our DBA is on leave in Europe ATM.

It appears that something broke our replication a week ago; the master has 121 current wal files, there are 256 week old ones on the slave.

The slave just keeps repeating since 01 Sept:

2022-09-04 06:00:19 UTC LOG:  started streaming WAL from primary at 4C9/7B000000 on timeline 1
2022-09-04 06:00:19 UTC FATAL:  could not receive data from WAL stream: ERROR:  requested WAL segment 00000001000004C90000007B has already been removed

Having read similar questions (and answers) here, varied resources from google searches and some documentation I'm starting to wonder whether I need to blow the slave away and repopulate it from scratch?

Answer 1

The authoritative answer is: yes, I need to start from scratch.

On Ubuntu 20.04 the solution to getting a fully functioning secondary back was as follows:

systemctl stop postgresql
rm -rf /var/lib/postgresql/11
sudo -u postgres pg_basebackup -h postgres-pri.example.com -D /var/lib/postgresql/11/main -U standby_replica -v -P -Xs -c fast -l "$(date)" -R
systemctl start postgresql

Answer 2

I just had the same issue with Postgres (TimescaleDB) in our Kubernetes cluster. I resolved it by logging in to the pod and reinitialised the replica according to https://github.com/zalando/patroni/issues/689#issuecomment-393812629 with:

patronictl reinit