12

I'm using a hosted PostgreSQL database where I don't have shell access. Is there a query I can use to check the current WAL log size?

1 Answer 1

27

If you have superuser access, you can use:

select * 
from pg_ls_dir('pg_xlog');

which will return one row for each file in the directory pg_xlog. As the size for a WAL segment is fixed, you can easily calculate the total size by multiplying the number of rows by 16MB:

select count(*) * pg_size_bytes(current_setting('wal_segment_size')) as total_size
from pg_ls_dir('pg_xlog') as t(fname)
where fname <> 'archive_status';

Alternatively you can use pg_stat_file() to return information about the files:

select sum((pg_stat_file('pg_wal/'||fname)).size) as total_size
from pg_ls_dir('pg_xlog') as t(fname);

Starting with Postgres 10 you can use:

select sum(size) 
from pg_ls_waldir()
6
  • 1
    Pg 10 also provides for pg_current_wal_flush_lsn(), pg_current_wal_insert_lsn(), pg_current_wal_lsn() which you can feed to pg_walfile_name() Commented Dec 6, 2017 at 22:27
  • 1
    please note the 16MB size is the default but could be configured to be different
    – Luke404
    Commented Feb 9, 2021 at 13:42
  • @Luke404: good point, I have changed the query.
    – user1822
    Commented Feb 9, 2021 at 13:47
  • 4
    what's the unit for each query? Bytes?
    – Alex
    Commented Mar 18, 2021 at 0:59
  • @Alex it seems that for the last one it is indeed bytes. Commented Aug 10, 2021 at 6:39

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.