understanding pg_wal and archiving
0
votes
1
answer
2214
views
So I inherited this database. At the very beginning, there were already some files on
pg_wal
.
Then, after some weeks, I do count SELECT COUNT(*) FROM pg_ls_dir('pg_wal') WHERE pg_ls_dir ~ '^[0-9A-F]{24}';
, it results : **8182** files.
Looking at the file's timestamp on pg_wal
directory, I see some new files being created but the number of files stays the same. I am suspecting that the rotation takes place.
Then I observe the pg_stat_archiver
: archived_count = **2**; failed_count = 0
Some 3 hours later, I do count again SELECT COUNT(*) FROM pg_ls_dir('pg_wal') WHERE pg_ls_dir ~ '^[0-9A-F]{24}';
It results : **8183**. Obviously a new file is created.
Then I do pg_stat_archiver
: archived_count = **3**; failed_count = 0
archived_count last_archived_wal last_archived_time failed_count last_failed_wal last_failed_time
3 000000010000009B00000083 22-09-13 13:14:20. 0
stats_reset
2022-09-13 08:14:19
This database is very low, not busy at all. Sometimes no one access it. But there is logical replication that sync some 300 records once a day to another server.
Here is my settings :
max_wal_size : 1024 Mb
min_wal_size : 2048 Mb
wal_keep_segment : 0
wal_rotation : ON
wal_level : logical
wal_compression : ON
archive_on : ON
archive_mode : test ! -f /Archive/%f && cp %p /Archive/%f
archive_timeout : 900
Questions :
1. Why does the second count of pg_wal
increase? I am expecting that it decreases because the archived number increases.
2. If wal_keep_segment = 0
, why the number of WALs in pg_wal
never 0 ? In the documentation, it says :
> If wal_keep_segments is zero (the default), the system doesn't keep
> any extra segments for standby purposes,
3. Why the minimum number of WALs, during low period, **does not** = min_wal_size
+ wal_keep_segment
?
Please enlighten me.
Thank you so much
Asked by padjee
(337 rep)
Sep 13, 2022, 01:56 PM
Last activity: Sep 13, 2022, 04:21 PM
Last activity: Sep 13, 2022, 04:21 PM