1

I have set archive_timeout to 300 seconds (5 min). Yet WAL files remain open for a very long time (hours). My postgresql.conf file includes:

archive_mode = on
archive_timeout = 300
archive_command = '/usr/local/internal/pgarchive.sh "%p" "%f"'
restore_command = '/usr/local/internal/pgrestore.sh "%f" "%p"'
wal_level = logical

And I don't see any errors on postgresql 13.5 startup. The pgsql 13.5 pg_wal directory contains:

drwx------  3 postgres postgres     4096 Oct 19 15:51 .
drwx------ 19 postgres postgres     4096 Oct 19 15:31 ..
-rw-------  1 postgres postgres      348 Oct 19 15:31 000000010000000000000097.00000028.backup
-rw-------  1 postgres postgres 16777216 Oct 19 15:51 00000001000000000000009A
-rw-------  1 postgres postgres 16777216 Oct 19 15:56 00000001000000000000009B
-rw-------  1 postgres postgres 16777216 Oct 19 15:31 00000001000000000000009C
-rw-------  1 postgres postgres 16777216 Oct 19 15:36 00000001000000000000009D
-rw-------  1 postgres postgres 16777216 Oct 19 15:41 00000001000000000000009E
drwx------  2 postgres postgres     4096 Oct 19 15:56 archive_status

And the archive directory contains:

drwxrwxrwx 2 postgres postgres     4096 Oct 19 15:56 .
drwxrwxrwx 4 root     root         4096 Oct 18 20:57 ..
-rw------- 1 postgres postgres 16777216 Oct 19 15:30 000000010000000000000094
-rw------- 1 postgres postgres 16777216 Oct 19 15:30 000000010000000000000095
-rw------- 1 postgres postgres 16777216 Oct 19 15:31 000000010000000000000096
-rw------- 1 postgres postgres 16777216 Oct 19 15:31 000000010000000000000097
-rw------- 1 postgres postgres      348 Oct 19 15:31 000000010000000000000097.00000028.backup
-rw------- 1 postgres postgres 16777216 Oct 19 15:36 000000010000000000000098
-rw------- 1 postgres postgres 16777216 Oct 19 15:41 000000010000000000000099
-rw------- 1 postgres postgres 16777216 Oct 19 15:51 00000001000000000000009A
-rw------- 1 postgres postgres 16777216 Oct 19 15:56 00000001000000000000009B

As you can see the pg_wal directory contains files 9C, 9D, and 9E which have not yet been archived. (9D and 9E are probably recycled old wal's not yet used as per comment below). But 9B has been open for an hour or more. Why is it not being archived?

Why is postgresql not forcing a new logfile every 5 min?

TSG
  • 4,242
  • 9
  • 61
  • 121
  • 1
    PostgreSQL can create new wal files in pg_wal ahead of time, by renaming old ones for *future* use (rather than deleting them). It does not archive a file until it is done with it, so those won't be in the archive. – jjanes Oct 19 '22 at 21:57
  • That makes sense. So then I still can't figure out why the archive_timeout has no effect – TSG Oct 19 '22 at 23:47
  • 1
    The timeout is not absolute. If the server is not generating any WAL records, it won't archive empty log files. Is the server generating WAL? Based on the timestamp of 9C from the pg_wal directory listing, I would say it has received no writes because your server is either totally idle, or doing only read activity. – jjanes Oct 20 '22 at 02:28

0 Answers0