archive command doesnt work

From: Alpaslan AKDAĞ <alpaslanakdag(at)gmail(dot)com>
To: "pgsql-general(at)lists(dot)postgresql(dot)org" <pgsql-general(at)lists(dot)postgresql(dot)org>
Subject: archive command doesnt work
Date: 2024-02-07 11:07:50
Message-ID: CAHKeUX-MoL9gwrWBi=X47EZFo4kvF+d8ZZFQgc3wAOpW9gwdiA@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

Hello

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.

Hot Standby Server infos:
Redhat Enterprise Linux 9.2
postgreSQL v13.10

related postgresql.conf lines:
archive_mode = on
archive_command = 'cp %p /archive/archived_wal/%f && cp %p
/opt/postgres/backup/archived_wal/%f'
archive_timeout = 300
wal_keep_size = 80
wal_level = 'replica'
wal_log_hints = on
max_wal_size = 10GB
min_wal_size = 80MB

archived WAL files per day in /archive/archived_wal/ folder. All of them
are before the switchover.
Number Day
421 2024-01-19
672 2024-01-20
1374 2024-01-21
564 2024-01-22
569 2024-01-23
607 2024-01-24
510 2024-01-25
612 2024-01-26
495 2024-01-27
1497 2024-01-28
556 2024-01-29
506 2024-01-30
549 2024-01-31
228 2024-02-01

and in log files there are lines like below but no error or failure lines:

2024-02-02 02:07:01.978 CET [979081] LOG: restartpoint complete: wrote
26904 buffers (2.6%); 0 WAL file(s) added, 0 removed, 0 recycled; write=445.
874 s, sync=0.005 s, total=445.887 s; sync files=88, longest=0.001 s,
average=0.001 s; distance=732035 kB, estimate=732035 kB
2024-02-02 02:07:01.978 CET [979081] LOG: recovery restart point at
252/2D7E5E68
2024-02-02 02:07:01.978 CET [979081] DETAIL: Last completed transaction
was at log time 2024-02-02 02:07:01.889865+01.
--
2024-02-02 02:07:02.024 CET [979081] LOG: restartpoint complete: wrote
310 buffers (0.0%); 0 WAL file(s) added, 0 removed, 0 recycled; write=0.037
s, sync=0.004 s, total=0.046 s; sync files=54, longest=0.002 s,
average=0.001 s; distance=90216 kB, estimate=667853 kB
2024-02-02 02:07:02.024 CET [979081] LOG: recovery restart point at
252/33000028
2024-02-02 02:07:02.024 CET [979081] DETAIL: Last completed transaction
was at log time 2024-02-02 02:07:01.889865+01.

postgresql-2024-02-03_000000.log:2024-02-03 09:03:20.383 CET [979081]
LOG: restartpoint complete: wrote 102950 buffers (9.8%); 0 WAL file(s)
added,
1286 removed, 117 recycled; write=1079.221 s, sync=0.008 s, total=1082.510
s; sync files=146, longest=0.001 s, average=0.001 s; distance=1143627 kB,
estimate=1143627 kB
postgresql-2024-02-03_000000.log-2024-02-03 09:03:20.383 CET [979081]
LOG: recovery restart point at 255/43D56D80
postgresql-2024-02-03_000000.log-2024-02-03 09:03:20.383 CET [979081]
DETAIL: Last completed transaction was at log time 2024-02-03
09:03:20.311619
+01.
--
postgresql-2024-02-03_000000.log:2024-02-03 17:47:29.480 CET [979081]
LOG: restartpoint complete: wrote 1305 buffers (0.1%); 0 WAL file(s)
added, 1
removed, 3 recycled; write=131.069 s, sync=0.005 s, total=131.088 s; sync
files=104, longest=0.001 s, average=0.001 s; distance=65368 kB, estimate=13
9744 kB
postgresql-2024-02-03_000000.log-2024-02-03 17:47:29.480 CET [979081]
LOG: recovery restart point at 255/E40CE260
postgresql-2024-02-03_000000.log-2024-02-03 17:47:29.480 CET [979081]
DETAIL: Last completed transaction was at log time 2024-02-03
17:47:28.055165
+01.

--
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.

and last thing that i want to share

select pg_last_wal_receive_lsn(), pg_last_wal_replay_lsn(),
pg_last_xact_replay_timestamp() \gx
-[ RECORD 1 ]-----------------+------------------------------
pg_last_wal_receive_lsn | 261/C10BB318
pg_last_wal_replay_lsn | 261/C10BB318
pg_last_xact_replay_timestamp | 2024-02-07 11:40:57.536997+01

Everything seems fine except archiving.
for now i share these informations but if you need more, i can give.

Thanks in advance.
Best Regards
Alpaslan

Responses

Browse pgsql-general by date

  From Date Subject
Next Message David Rowley 2024-02-07 12:18:58 Re: Why doesn't Postgres apply limit on groups when retrieving N results per group using WHERE IN + ORDER BY
Previous Message Carsten Klein 2024-02-07 10:28:25 Generic File Access Function to read program output