WAL is a standard method for data integrity.
When the change is made in the memory, metadata information is written to WAL files
first.
Then, at the most appropriate time, these dirty buffers are written to data files (LRU
Algorithm is used).
If the system shuts down (crash) due to electricity/hardware failure etc. the dirty buffer
in the memory will be lost without being able to write the dirty buffer to disk.
In this case, postgresql will reapply all committed data from WAL files. Thus, there will
be no data loss.
The default size is 16MB. If desired, larger sizes can be given with the initdbparameter –wal-segsize.
It is written to the $PGDATA/pg_wal directory. Keeping it on a different disk
provides a performance advantage. There is no specific parameter for this, but can be
done with a symbolic link.
- Online backup,
- Ability to recover data until the last moment of the problem (complete recovery),
- Point-in-time recovery i.e., hotbackup, to return to a later moment
- Replication (active – passive cluster)
To use all these features, the WAL method is needed.
In order to benefit from these features, archiving must be enabled. So, it is necessary
to import a copy of the WAL files to a different location.
Locating Current WAL (currently written) file
postgres=# SELECT pg_walfile_name(pg_current_wal_lsn()); pg_walfile_name -------------------------- 000000010000000000000006 postgres=# \! ls -ltr $PGDATA/pg_wal postgres 6 Apr 13 10:20 archive_status postgres 16777216 Apr 13 10:49 000000010000000000000007 postgres 16777216 Apr 13 10:49 000000010000000000000008 postgres 16777216 Apr 14 16:59 000000010000000000000009 postgres 16777216 Jul 14 16:32 000000010000000000000006
log switch (switch to new WAL file)
select pg_switch_wal();
Hourly WAL distribution
SELECT date_trunc('hour', modification) as hourly,
sum(size/1024/1024) as size_MB FROM pg_ls_waldir() GROUP BY
hourly ORDER BY hourly;
WAL files distribution per hour
select name, size/1024/1024 as size_MB, modification from pg_ls_waldir() order by 3 desc;
Contents of $PGDATA/pg_wal
select * from pg_ls_dir(‘pg_wal’);
$PGDATA/pg_wal size
select count(*) * 16 as total_size
from pg_ls_dir('pg_wal') as t(fname)
where fname <> 'archive_status';