Is there a query to check the current WAL size in PostgreSQL?

Question

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?

Answer 1

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()

• Evan Carroll: 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()
• Luke404: please note the 16MB size is the default but could be configured to be different
• user1822: @Luke404: good point, I have changed the query.
• Alex: what's the unit for each query? Bytes?
• Alexi Theodore: Use pg_size_pretty(sum(size)) for human-readable.