postgres transaction log or write ahead log full

To check for WAL usage we can run the following commands:

SELECT
    wal_buffers,
    wal_writer_delay,
    wal_writer_flush_after
FROM pg_settings
WHERE name LIKE 'wal%';

SELECT
    pg_size_pretty(pg_wal_lsn_diff(pg_current_wal_lsn(), '0/00000000')::bigint) AS wal_size
;

We can also check where is the WAL file by running this command

-- Current WAL position
SELECT pg_current_wal_lsn();

-- How many WAL files are there
SELECT count(*) FROM pg_ls_waldir();

Then on the OS level we can see how big these files are:

du -sh /var/lib/postgresql/data/pg_wal

We should setup archiving for our postgres database by having the following configuration in our postgres.conf

archive_mode = on

archive_command = 'cp %p /path/to/archive/%f'

To clear it, we just have to force a checkpoint like so 

CHECKPOINT;

mitzen