Обсуждение: archive command doesnt work

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

On Thu, 2024-02-08 at 09:23 +0100, Alpaslan AKDAĞ wrote:
> 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.

It is becoming increasingly obvious that you never actually performed
a switchover: it seems that you didn't promote the standby.

Either use

  pg_ctl promote -D /path/to/datadir

on the command line or

  SELECT pg_promote();

in SQL.

Yours,
Laurenz Albe

czw., 8 lut 2024 o 21:10 Laurenz Albe <laurenz.albe@cybertec.at> napisał(a):

On Thu, 2024-02-08 at 09:23 +0100, Alpaslan AKDAĞ wrote:
> 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.

It is becoming increasingly obvious that you never actually performed
a switchover: it seems that you didn't promote the standby.

Either use

  pg_ctl promote -D /path/to/datadir

on the command line or

  SELECT pg_promote();

in SQL.

Yours,
Laurenz Albe

From what you sent:
 

"After switchover since 01.02.2024 hot standby server does not archive wal files. I couldn't find the problem."

Hot standby normally does not archive WAL files. Only the primary server does that unless you have archive_mode set to "always" but your configuration shows that you have it "on" only.

"related postgresql.conf lines:

archive_mode = on "

Cheers,
Mateusz

Hello Mateusz

Thank you for your answer.

If it is like that, there is not any problem. I tried to find an explanation that hot standby archive wal files or not but I couldn't.

Is this information  from the Documentation?

Best Regards,

Alpaslan

On Thu, Feb 8, 2024 at 9:24 PM Mateusz Henicz <mateuszhenicz@gmail.com> wrote:

czw., 8 lut 2024 o 21:10 Laurenz Albe <laurenz.albe@cybertec.at> napisał(a):

On Thu, 2024-02-08 at 09:23 +0100, Alpaslan AKDAĞ wrote:
> 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.

It is becoming increasingly obvious that you never actually performed
a switchover: it seems that you didn't promote the standby.

Either use

  pg_ctl promote -D /path/to/datadir

on the command line or

  SELECT pg_promote();

in SQL.

Yours,
Laurenz Albe

From what you sent:
 

"After switchover since 01.02.2024 hot standby server does not archive wal files. I couldn't find the problem."

Hot standby normally does not archive WAL files. Only the primary server does that unless you have archive_mode set to "always" but your configuration shows that you have it "on" only.

"related postgresql.conf lines:

archive_mode = on "

Cheers,
Mateusz

Hey,
Yes, it is.

https://www.postgresql.org/docs/current/runtime-config-wal.html#GUC-ARCHIVE-MODE

czw., 8 lut 2024 o 21:35 Alpaslan AKDAĞ <alpaslanakdag@gmail.com> napisał(a):

Hello Mateusz

Thank you for your answer.

If it is like that, there is not any problem. I tried to find an explanation that hot standby archive wal files or not but I couldn't.

Is this information  from the Documentation?

Best Regards,

Alpaslan

On Thu, Feb 8, 2024 at 9:24 PM Mateusz Henicz <mateuszhenicz@gmail.com> wrote:

czw., 8 lut 2024 o 21:10 Laurenz Albe <laurenz.albe@cybertec.at> napisał(a):

On Thu, 2024-02-08 at 09:23 +0100, Alpaslan AKDAĞ wrote:
> 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.

It is becoming increasingly obvious that you never actually performed
a switchover: it seems that you didn't promote the standby.

Either use

  pg_ctl promote -D /path/to/datadir

on the command line or

  SELECT pg_promote();

in SQL.

Yours,
Laurenz Albe

From what you sent:
 

"After switchover since 01.02.2024 hot standby server does not archive wal files. I couldn't find the problem."

Hot standby normally does not archive WAL files. Only the primary server does that unless you have archive_mode set to "always" but your configuration shows that you have it "on" only.

"related postgresql.conf lines:

archive_mode = on "

Cheers,
Mateusz