Sample Header Ad - 728x90

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