Postgresql – How to calculate quantity of WAL in db without archiving


I would like to find out a quantity of WAL files which are being used in PostgreSQL database in last 24h. I was assigned to do Log-shipping but before I do it I want to know how many WAL files are being used in last 24hours in order to predict how much space I need on external storage.
Unfortunately pgmetrics doesn't have such information. Do you know how to do it?

WAL Files:
WAL Archiving?       no
WAL Files:           5
|            Setting |        Value |
|          wal_level |      minimal |
|    archive_timeout |            0 |
|    wal_compression |          off |
|       max_wal_size | 64 (1.0 GiB) |
|       min_wal_size |   5 (80 MiB) |
| checkpoint_timeout |          300 |
|   full_page_writes |           on |
|  wal_keep_segments |            0 |

Best Answer

There is no easy way as such to get a count of the WAL Files. But what you could do is run the SQL below at a certain time and then run it again 24 hours later. Getting the difference should give you some indication of the number.

SELECT COUNT(*) FROM pg_ls_dir('pg_xlog') WHERE pg_ls_dir ~ '^[0-9A-F]{24}';

Some details Postgreal Admi functions and pgDash blog.