PostgreSQL – How to Check Current WAL Size in PostgreSQL 9.6

postgresqlpostgresql-9.6

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?

Best Answer

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