Subject: Re: Logical replication type- WAL recovery fails and changes the size of wal segment in archivedir

From: Laurenz Albe <[email-masked]>
Date: Wed, 05 Jun 2024 12:26:03 +0200

On Wed, 2024-06-05 at 06:36 +0000, Meera Nair wrote:
> 2024-06-05 11:41:32.369 IST [54369] LOG:  restored log file "00000005000000010000006A" from archive
> 2024-06-05 11:41:33.112 IST [54369] LOG:  restored log file "00000005000000010000006B" from archive
> cp: cannot stat ‘/home/pgsql/wmaster/00000005000000010000006C’: No such file or directory
> 2024-06-05 11:41:33.167 IST [54369] LOG:  redo done at 1/6B000100   
> 2024-06-05 11:41:33.172 IST [54369] FATAL:  archive file "00000005000000010000006B" has wrong size: 0 instead of 16777216
> 2024-06-05 11:41:33.173 IST [54367] LOG:  startup process (PID 54369) exited with exit code 1
> 2024-06-05 11:41:33.173 IST [54367] LOG:  terminating any other active server processes
> 2024-06-05 11:41:33.174 IST [54375] FATAL:  archive command was terminated by signal 3: Quit
> 2024-06-05 11:41:33.174 IST [54375] DETAIL:  The failed archive command was: cp pg_wal/00000005000000010000006B /home/pgsql/wmaster/00000005000000010000006B
> 2024-06-05 11:41:33.175 IST [54367] LOG:  archiver process (PID 54375) exited with exit code 1
> 2024-06-05 11:41:33.177 IST [54367] LOG:  database system is shut down
>  
> Here ‘/home/pgsql/wmaster’ is my archivedir (the folder where WAL segments are restored from)
>  
> Before attempting start, size of 
> 00000005000000010000006B file was 16 MB.
> After failing to detect 00000005000000010000006C, there is a FATAL error saying wrong size for 00000005000000010000006B
> Now the size of 00000005000000010000006B is observed as 2 MB. Size of all other WAL segments remain 16 MB.
>  
> -rw------- 1 postgres postgres  2359296 Jun  5 11:34 00000005000000010000006B

That looks like you have "archive_mode = always", and "archive_command" writes
back to the archive.  Don't do that.

Yours,
Laurenz Albe

---

From: Meera Nair <[email-masked]>
Date: Thu, 6 Jun 2024 01:52:07 +0000

Hi Laurenz,

Thanks a lot! 

Regards,
Meera

-----Original Message-----
From: Laurenz Albe <[email-masked]> 
Sent: Wednesday, June 5, 2024 3:56 PM
To: Meera Nair <[email-masked]>; [email-masked]
Cc: Punit Pranesh Koujalgi <[email-masked]>; B Ganesh Kishan <[email-masked]>
Subject: Re: Logical replication type- WAL recovery fails and changes the size of wal segment in archivedir

[You don't often get email from [email-masked]. Learn why this is important at https://aka.ms/LearnAboutSenderIdentification ]

External email. Inspect before opening.

On Wed, 2024-06-05 at 06:36 +0000, Meera Nair wrote:
> 2024-06-05 11:41:33.172 IST [54369] FATAL:  archive file 
> "00000005000000010000006B" has wrong size: 0 instead of 16777216
> 2024-06-05 11:41:33.174 IST [54375] DETAIL:  The failed archive 
> command was: cp pg_wal/00000005000000010000006B 
> /home/pgsql/wmaster/00000005000000010000006B
> After failing to detect 00000005000000010000006C, there is a FATAL 
> error saying wrong size for 00000005000000010000006B Now the size of 00000005000000010000006B is observed as 2 MB. Size of all other WAL segments remain 16 MB.
>

That looks like you have "archive_mode = always", and "archive_command" writes back to the archive.  Don't do that.

Yours,
Laurenz Albe

---

From: Ron Johnson <[email-masked]>
Date: Thu, 6 Jun 2024 12:25:38 -0400

On Wed, Jun 5, 2024 at 6:26 AM Laurenz Albe <[email-masked]>
wrote:

> > 2024-06-05 11:41:33.174 IST [54375] DETAIL:  The failed archive command
> was: cp pg_wal/00000005000000010000006B
> > After failing to detect 00000005000000010000006C, there is a FATAL error
> saying wrong size for 00000005000000010000006B

In fact, don't write your own PITR backup process.  Use something like
PgBackRest or BarMan.

---

From: Meera Nair <[email-masked]>
Date: Fri, 7 Jun 2024 01:45:39 +0000

Hi Ron,
We do have our own solution. We work for Commvault, a data protection company.
We support backup of PostgreSQL in different ways - streaming, snapshot, block level, backup from standby server.
PostgreSQL (commvault.com)<https://documentation.commvault.com/2024/expert/postgresql.html>

Regards,
Meera

From: Ron Johnson <[email-masked]>
Sent: Thursday, June 6, 2024 9:56 PM
To: Laurenz Albe <[email-masked]>
Cc: Meera Nair <[email-masked]>; [email-masked]; Punit Pranesh Koujalgi <[email-masked]>; B Ganesh Kishan <[email-masked]>
Subject: Re: Logical replication type- WAL recovery fails and changes the size of wal segment in archivedir

You don't often get email from [email-masked]<mailto:[email-masked]>. Learn why this is important<https://aka.ms/LearnAboutSenderIdentification>
External email. Inspect before opening.

On Wed, Jun 5, 2024 at 6:26 AM Laurenz Albe <[email-masked]<mailto:[email-masked]>> wrote:

In fact, don't write your own PITR backup process.  Use something like PgBackRest or BarMan.