Re: archive command doesnt work

Hello Laurenz,

Thank you for your answer.

I have checked the pg_is_in_recovery() and here are the results from
primary and hot stand by server.

Primary (old standby ):
select pg_is_in_recovery();
pg_is_in_recovery
-------------------
f
(1 row)

hot standby(old primary):
select pg_is_in_recovery();
pg_is_in_recovery
-------------------
t
(1 row)
and there is also standby.signal file in standby server.
So it seems that there is nothing wrong.

recovery_min_apply_delay = '2d' are set in warm standby servers. Before
the switchover we had the same settings but we did not have this problem.
How can I fix it?

Best regards,
Alpaslan

On Wed, Feb 7, 2024 at 9:06 PM Laurenz Albe <laurenz(dot)albe(at)cybertec(dot)at>
wrote:

> On Wed, 2024-02-07 at 12:07 +0100, Alpaslan AKDAĞ wrote:
> > We have a primary, a hot standby and 2 warm standby servers with 2 days
> delay.
> >
> > After switchover since 01.02.2024 hot standby server does not archive
> wal files. I couldn't find the problem.
> >
> > related postgresql.conf lines:
> > archive_mode = on
> > archive_command = 'cp %p /archive/archived_wal/%f && cp %p
> /opt/postgres/backup/archived_wal/%f'
> >
> > and in log files there are lines like below but no error or failure
> lines:
> >
> > postgresql-2024-02-05_000000.log:2024-02-05 06:25:50.272 CET [979081]
> LOG: restartpoint complete: wrote 477 buffers (0.0%); 0 WAL file(s) added,
> 0
> > removed, 4 recycled; write=47.946 s, sync=0.005 s, total=47.964 s; sync
> files=85, longest=0.001 s, average=0.001 s; distance=65484 kB,
> estimate=413488
> > kB
> > postgresql-2024-02-05_000000.log-2024-02-05 06:25:50.272 CET [979081]
> LOG: recovery restart point at 25C/74083E58
> > postgresql-2024-02-05_000000.log-2024-02-05 06:25:50.272 CET [979081]
> DETAIL: Last completed transaction was at log time 2024-02-05
> 06:25:50.223799
> > +01.
>
> That looks like the standby wasn't promoted and is still in recovery,
> so it won't generate WAL.
>
> Restartpoints are only written on servers in recovery mode. Moreover,
> a promote request would generate a log entry.
>
> Another option would be that the promote request is further up in the log,
> and the server takes a long time to replay all the changes
> (if the "2 days delay" you mention are set in "recovery_min_apply_delay").
>
> What do you get for
>
> SELECT pg_is_in_recovery();
>
> Yours,
> Laurenz Albe
>