WAL File Recovery on Standby Server Stops Before End of WAL Files

["Ryan, Les" <Les.Ryan@xxxxxxx>]

Hello,

 

I’m hoping to get some suggestions on what to do here.  I am running PostgreSQL version 13.2 and am shipping the WAL files to a standby server.  Once a day I restart the standby server and it recovers the new WAL files that have been shipped to it.  Everything was working great until yesterday.  My problem is that the WAL recovery is now stopping before it recovers all of the available WAL files.  This happened once before and the only way I could get the WAL recovery to go past that file was to create a fresh back and restore that.  I’m hoping to avoid that as it takes about a week to create the backup.

 

Here are the specifics:

• PostgreSQL version 13.2
• The primary server creates the WAL files and a scheduled process copies them to a folder on the standby server.
• Once a day, the standby server is restared using the following command:
• "C:\Program Files\PostgreSQL\13\bin\pg_ctl" restart -D .\
• The log contains the following:

2021-10-27 10:26:30.508 MDT [6204] LOG:  starting PostgreSQL 13.2, compiled by Visual C++ build 1914, 64-bit

2021-10-27 10:26:30.509 MDT [6204] LOG:  listening on IPv6 address "::", port 5432

2021-10-27 10:26:30.510 MDT [6204] LOG:  listening on IPv4 address "0.0.0.0", port 5432

2021-10-27 10:26:30.585 MDT [2012] LOG:  database system was shut down in recovery at 2021-10-27 10:26:29 MDT

2021-10-27 10:26:30.701 MDT [2012] LOG:  entering standby mode

2021-10-27 10:26:30.821 MDT [2012] LOG:  restored log file "000000010000041900000056" from archive

2021-10-27 10:26:31.158 MDT [2012] LOG:  restored log file "000000010000041900000052" from archive

2021-10-27 10:26:31.467 MDT [2012] LOG:  redo starts at 419/5229A858

2021-10-27 10:26:31.561 MDT [2012] LOG:  restored log file "000000010000041900000053" from archive

2021-10-27 10:26:32.108 MDT [2012] LOG:  restored log file "000000010000041900000054" from archive

2021-10-27 10:26:32.849 MDT [2012] LOG:  restored log file "000000010000041900000055" from archive

2021-10-27 10:26:33.612 MDT [2012] LOG:  restored log file "000000010000041900000056" from archive

2021-10-27 10:26:34.342 MDT [2012] LOG:  restored log file "000000010000041900000057" from archive

2021-10-27 10:26:35.146 MDT [2012] LOG:  restored log file "000000010000041900000058" from archive

2021-10-27 10:26:35.718 MDT [2012] LOG:  restored log file "000000010000041900000059" from archive

2021-10-27 10:26:36.188 MDT [2012] LOG:  restored log file "00000001000004190000005A" from archive

2021-10-27 10:26:36.750 MDT [2012] LOG:  consistent recovery state reached at 419/5ABFFFF8

2021-10-27 10:26:36.752 MDT [6204] LOG:  database system is ready to accept read only connections

2021-10-27 10:26:36.823 MDT [6040] LOG:  started streaming WAL from primary at 419/5A000000 on timeline 1

• There are many more WAL files available starting with 00000001000004190000005B but the restore process always stops at 00000001000004190000005A.

 

I have two questions:

• Why does the WAL file recovery process now stop after it reads 00000001000004190000005A?
• What do I need to do to get PostgreSQL to recover the rest of the available WAL files.

 

Thanks in advance for any suggestions.

 

Sincerely,

-Les

 

 

Les Ryan, P.Eng | WSP

 

SCADA Engineer

Energy, Resources & Industry

 

T +1 403-813-6327

E les.ryan@xxxxxxx

O 405 18 St SE. Calgary, Alberta T2E 6J5

 

---

NOTICE: This communication and any attachments ("this message") may contain information which is privileged, confidential, proprietary or otherwise subject to restricted disclosure under applicable law. This message is for the sole use of the intended recipient(s). Any unauthorized use, disclosure, viewing, copying, alteration, dissemination or distribution of, or reliance on, this message is strictly prohibited. If you have received this message in error, or you are not an authorized or intended recipient, please notify the sender immediately by replying to this message, delete this message and all copies from your e-mail system and destroy any printed copies. You are receiving this communication because you are listed as a current WSP contact. Should you have any questions regarding WSP's electronic communications policy, please consult our Anti-Spam Commitment at www.wsp.com/casl. For any concern or if you believe you should not be receiving this message, please forward this message to caslcompliance@xxxxxxx so that we can promptly address your request. Note that not all messages sent by WSP qualify as commercial electronic messages.

AVIS : Ce message, incluant tout fichier l'accompagnant (« le message »), peut contenir des renseignements ou de l'information privilégiés, confidentiels, propriétaires ou à divulgation restreinte en vertu de la loi. Ce message est destiné à l'usage exclusif du/des destinataire(s) voulu(s). Toute utilisation non permise, divulgation, lecture, reproduction, modification, diffusion ou distribution est interdite. Si vous avez reçu ce message par erreur, ou que vous n'êtes pas un destinataire autorisé ou voulu, veuillez en aviser l'expéditeur immédiatement et détruire le message et toute copie électronique ou imprimée. Vous recevez cette communication car vous faites partie des contacts de WSP. Si vous avez des questions concernant la politique de communications électroniques de WSP, veuillez consulter notre Engagement anti-pourriel au www.wsp.com/lcap. Pour toute question ou si vous croyez que vous ne devriez pas recevoir ce message, prière de le transférer au conformitelcap@xxxxxxx afin que nous puissions rapidement traiter votre demande. Notez que ce ne sont pas tous les messages transmis par WSP qui constituent des messages electroniques commerciaux.



-LAEmHhHzdJzBlTWfa4Hgs7pbKl