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