[ADMIN] Vacuum full - disk space eaten by WAL logfiles

[Lee Wu]

Hi all,   When we do weekly “vacuum full”, PG uses all space and causes PG down.   checkpoint_segments            | 30 checkpoint_timeout                | 300   select version();                            version -------------------------------------------------------------  PostgreSQL 7.3.2 on i686-pc-linux-gnu, compiled by GCC 2.96   The error message is: Jan  8 20:25:33 mybox postgres[1602]: [13] PANIC:  ZeroFill failed to write /my/pg_xlog/xlogtemp.1602: No space left on device Jan  8 20:25:35 mybox postgres[8213]: [163] LOG:  removing transaction log file 00001AB2000000EC Jan  8 20:25:35 mybox postgres[1602]: [14-1] LOG:  statement: COPY xxxxx (domain, domain_id, customer_id, action_unspecified, action_unknown, Jan  8 20:25:35 mybox postgres[8213]: [164] LOG:  removing transaction log file 00001AB2000000ED Jan  8 20:25:35 mybox postgres[8213]: [165] LOG:  removing transaction log file 00001AB2000000EE Jan  8 20:25:35 mybox postgres[1602]: [14-2]  action_none, action_deny, action_fail, action_strip, action_tag, action_quarantine, action_clean, action_copy, action_allow, Jan  8 20:25:35 mybox postgres[8213]: [166] LOG:  removing transaction log file 00001AB2000000F0 Jan  8 20:25:35 mybox postgres[1602]: [14-3]  module, period, created) FROM stdin Jan  8 20:25:35 mybox postgres[8213]: [167] LOG:  removing transaction log file 00001AB2000000F1 Jan  8 20:25:35 mybox postgres[8213]: [168] LOG:  removing transaction log file 00001AB2000000F2 Jan  8 20:25:36 mybox postgres[8213]: [169] LOG:  removing transaction log file 00001AB2000000F3 Jan  8 20:25:36 mybox postgres[8213]: [170] LOG:  removing transaction log file 00001AB2000000F4 Jan  8 20:25:36 mybox postgres[8213]: [171] LOG:  removing transaction log file 00001AB2000000F5 Jan  8 20:25:36 mybox postgres[862]: [13] LOG:  server process (pid 1602) was terminated by signal 6 Jan  8 20:25:36 mybox postgres[862]: [14] LOG:  terminating any other active server processes Jan  8 20:25:36 mybox postgres[8601]: [13-1] WARNING:  copy: line 1, Message from PostgreSQL backend: Jan  8 20:25:36 mybox postgres[8601]: [13-2] ^IThe Postmaster has informed me that some other backend Jan  8 20:25:36 mybox postgres[8601]: [13-3] ^Idied abnormally and possibly corrupted shared memory. Jan  8 20:25:36 mybox postgres[8601]: [13-4] ^II have rolled back the current transaction and am Jan  8 20:25:36 mybox postgres[7284]: [13-1] WARNING:  Message from PostgreSQL backend: Jan  8 20:25:36 mybox postgres[3658]: [13-1] WARNING:  Message from PostgreSQL backend:   When the error is happening, we see at one time, there are 563 WAL files under pg_xlog. My understanding is we should expect 61 WAL files, each 16M.   Our /my/pg_xlog is 10G partition and typically has 8G left.   The table size of being vacuumed full is 35G.   What can I do to fix this mess??   Regards,