PostgreSQL 13 - WAL - last file update date doesn't change

885 Views Asked by At

I'm using a recent version of PostgreSQL on Windows and trying to understand the internal mechanisms arround WAL files.

The scenario I want to test is the following.

  1. Look at the active wal file on the file system
SELECT pg_walfile_name(pg_current_wal_lsn());

Returns "000000010000000000000007"

2. Get is last write time

ls 000000010000000000000007

Returns

Mode                LastWriteTime         Length Name
----                -------------         ------ ----
-a----       10/12/2020     13:05       16777216 000000010000000000000007

3. Insert a record into a table of the database

INSERT INTO person(name)
VALUES('Christopher')

4. Check again the last write time of the active wal

ls

Returns

    Répertoire : C:\Program Files\PostgreSQL\13\data\pg_wal

Mode                LastWriteTime         Length Name
----                -------------         ------ ----
d-----       27/11/2020     07:45                archive_status
-a----       10/12/2020     11:05       16777216 000000010000000000000007
-a----       06/12/2020     21:15       16777216 000000010000000000000008

The WAL hasn't been updated ! Why ?

None of the file in pg_wal has been updated !

UPDATE
Thanks to your answers, I've understand. Windows seems to be the cause !

If I check the date of the file using

ls
    Répertoire : C:\Program Files\PostgreSQL\13\data\pg_wal

Mode                LastWriteTime         Length Name
----                -------------         ------ ----
d-----       27/11/2020     07:45                archive_status
-a----       10/12/2020     11:05       16777216 000000010000000000000007
-a----       06/12/2020     21:15       16777216 000000010000000000000008

BUT the following gives the right time !

ls 000000010000000000000007

Returns

Mode                LastWriteTime         Length Name
----                -------------         ------ ----
-a----       10/12/2020     13:05       16777216 000000010000000000000007

How in the world ?

1

There are 1 best solutions below

1
On

The active WAL segment will get updated. You can get its name with

SELECT pg_walfile_name(pg_current_wal_lsn());

The other WAL segments are either old or held in reserve for the future.

There is no reason for the directory modification timestamp to change if one of the files in it changes.