Thread: When will old wal segments get removed?
hi we had a problem with archiving. so pg_xlog accumulated more wal segments. archive_command was modified to version that doesn't fail, and it is working ok. but old segments are still in pg_xlog. they are way past anything that could be even remotely needed: # g_controldata . pg_control version number: 843 Catalog version number: 200904091 Database system identifier: 5429353595627434591 Database cluster state: in production pg_control last modified: February 8, 2011 2:01:46 PM GMT Latest checkpoint location: 76E/A607B0B8 Prior checkpoint location: 76E/92057108 Latest checkpoint's REDO location: 76E/93004FE8 Latest checkpoint's TimeLineID: 1 Latest checkpoint's NextXID: 0/47225854 Latest checkpoint's NextOID: 66603928 Latest checkpoint's NextMultiXactId: 1 Latest checkpoint's NextMultiOffset: 0 Time of latest checkpoint: February 8, 2011 1:57:16 PM GMT Minimum recovery ending location: 0/0 Maximum data alignment: 8 Database block size: 8192 Blocks per segment of large relation: 131072 WAL block size: 8192 Bytes per WAL segment: 16777216 Maximum length of identifiers: 64 Maximum columns in an index: 32 Maximum size of a TOAST chunk: 1996 Date/time type storage: 64-bit integers Float4 argument passing: by value Float8 argument passing: by value and pg_xlog/ has: $ ls -l pg_xlog/ | head total 2620721 -rw------- 1 postgres postgres 249 Sep 8 20:14 00000001000003DB0000003E.000061A8.backup -rw------- 1 postgres postgres 16777216 Feb 8 11:39 000000010000076D000000F7 -rw------- 1 postgres postgres 16777216 Feb 8 11:40 000000010000076D000000F8 -rw------- 1 postgres postgres 16777216 Feb 8 11:40 000000010000076D000000F9 -rw------- 1 postgres postgres 16777216 Feb 8 11:40 000000010000076D000000FA -rw------- 1 postgres postgres 16777216 Feb 8 11:41 000000010000076D000000FB -rw------- 1 postgres postgres 16777216 Feb 8 11:42 000000010000076D000000FC -rw------- 1 postgres postgres 16777216 Feb 8 11:43 000000010000076D000000FD -rw------- 1 postgres postgres 16777216 Feb 8 11:44 000000010000076D000000FE they are archived: $ ls -l pg_xlog/archive_status/ | head total 179 -rw------- 1 postgres postgres 0 Sep 8 20:14 00000001000003DB0000003E.000061A8.backup.done -rw------- 1 postgres postgres 0 Feb 8 11:39 000000010000076D000000F7.ready -rw------- 1 postgres postgres 0 Feb 8 11:40 000000010000076D000000F8.ready -rw------- 1 postgres postgres 0 Feb 8 11:40 000000010000076D000000F9.ready -rw------- 1 postgres postgres 0 Feb 8 11:40 000000010000076D000000FA.ready -rw------- 1 postgres postgres 0 Feb 8 11:41 000000010000076D000000FB.ready -rw------- 1 postgres postgres 0 Feb 8 11:42 000000010000076D000000FC.ready -rw------- 1 postgres postgres 0 Feb 8 11:43 000000010000076D000000FD.ready -rw------- 1 postgres postgres 0 Feb 8 11:44 000000010000076D000000FE.ready . checkpoint_segments is 30: $ psql -c 'show checkpoint_segments' checkpoint_segments --------------------- 30 (1 row) what can I do to make them go away? tried select pg_switch_xlog(), but it was just allocating new wal segments. version of pg is 8.4.2. Best regards, depesz -- Linkedin: http://www.linkedin.com/in/depesz / blog: http://www.depesz.com/ jid/gtalk: depesz@depesz.com / aim:depeszhdl / skype:depesz_hdl / gg:6749007
On 8 February 2011 14:07, hubert depesz lubaczewski <depesz@depesz.com> wrote: > hi > we had a problem with archiving. so pg_xlog accumulated more wal > segments. > > archive_command was modified to version that doesn't fail, and it is > working ok. but old segments are still in pg_xlog. > > they are way past anything that could be even remotely needed: > > # g_controldata . > pg_control version number: 843 > Catalog version number: 200904091 > Database system identifier: 5429353595627434591 > Database cluster state: in production > pg_control last modified: February 8, 2011 2:01:46 PM GMT > Latest checkpoint location: 76E/A607B0B8 > Prior checkpoint location: 76E/92057108 > Latest checkpoint's REDO location: 76E/93004FE8 > Latest checkpoint's TimeLineID: 1 > Latest checkpoint's NextXID: 0/47225854 > Latest checkpoint's NextOID: 66603928 > Latest checkpoint's NextMultiXactId: 1 > Latest checkpoint's NextMultiOffset: 0 > Time of latest checkpoint: February 8, 2011 1:57:16 PM GMT > Minimum recovery ending location: 0/0 > Maximum data alignment: 8 > Database block size: 8192 > Blocks per segment of large relation: 131072 > WAL block size: 8192 > Bytes per WAL segment: 16777216 > Maximum length of identifiers: 64 > Maximum columns in an index: 32 > Maximum size of a TOAST chunk: 1996 > Date/time type storage: 64-bit integers > Float4 argument passing: by value > Float8 argument passing: by value > > and pg_xlog/ has: > > $ ls -l pg_xlog/ | head > total 2620721 > -rw------- 1 postgres postgres 249 Sep 8 20:14 00000001000003DB0000003E.000061A8.backup > -rw------- 1 postgres postgres 16777216 Feb 8 11:39 000000010000076D000000F7 > -rw------- 1 postgres postgres 16777216 Feb 8 11:40 000000010000076D000000F8 > -rw------- 1 postgres postgres 16777216 Feb 8 11:40 000000010000076D000000F9 > -rw------- 1 postgres postgres 16777216 Feb 8 11:40 000000010000076D000000FA > -rw------- 1 postgres postgres 16777216 Feb 8 11:41 000000010000076D000000FB > -rw------- 1 postgres postgres 16777216 Feb 8 11:42 000000010000076D000000FC > -rw------- 1 postgres postgres 16777216 Feb 8 11:43 000000010000076D000000FD > -rw------- 1 postgres postgres 16777216 Feb 8 11:44 000000010000076D000000FE > > they are archived: > $ ls -l pg_xlog/archive_status/ | head > total 179 > -rw------- 1 postgres postgres 0 Sep 8 20:14 00000001000003DB0000003E.000061A8.backup.done > -rw------- 1 postgres postgres 0 Feb 8 11:39 000000010000076D000000F7.ready > -rw------- 1 postgres postgres 0 Feb 8 11:40 000000010000076D000000F8.ready > -rw------- 1 postgres postgres 0 Feb 8 11:40 000000010000076D000000F9.ready > -rw------- 1 postgres postgres 0 Feb 8 11:40 000000010000076D000000FA.ready > -rw------- 1 postgres postgres 0 Feb 8 11:41 000000010000076D000000FB.ready > -rw------- 1 postgres postgres 0 Feb 8 11:42 000000010000076D000000FC.ready > -rw------- 1 postgres postgres 0 Feb 8 11:43 000000010000076D000000FD.ready > -rw------- 1 postgres postgres 0 Feb 8 11:44 000000010000076D000000FE.ready > > . > > checkpoint_segments is 30: > > $ psql -c 'show checkpoint_segments' > checkpoint_segments > --------------------- > 30 > (1 row) > > what can I do to make them go away? > > tried select pg_switch_xlog(), but it was just allocating new wal segments. > > version of pg is 8.4.2. Well normally those would get deleted automatically after archiving, but since you're suggesting the previous archive_command didn't return a zero exit status, that process wouldn't have happened. If you're sure those are archived, can't you just go ahead and delete them manually? -- Thom Brown Twitter: @darkixion IRC (freenode): dark_ixion Registered Linux user: #516935
On Tue, Feb 08, 2011 at 02:21:08PM +0000, Thom Brown wrote: > > $ ls -l pg_xlog/ | head > > total 2620721 > > -rw------- 1 postgres postgres 249 Sep 8 20:14 00000001000003DB0000003E.000061A8.backup > > -rw------- 1 postgres postgres 16777216 Feb 8 11:39 000000010000076D000000F7 > > -rw------- 1 postgres postgres 16777216 Feb 8 11:40 000000010000076D000000F8 > > -rw------- 1 postgres postgres 16777216 Feb 8 11:40 000000010000076D000000F9 > > -rw------- 1 postgres postgres 16777216 Feb 8 11:40 000000010000076D000000FA > > -rw------- 1 postgres postgres 16777216 Feb 8 11:41 000000010000076D000000FB > > -rw------- 1 postgres postgres 16777216 Feb 8 11:42 000000010000076D000000FC > > -rw------- 1 postgres postgres 16777216 Feb 8 11:43 000000010000076D000000FD > > -rw------- 1 postgres postgres 16777216 Feb 8 11:44 000000010000076D000000FE > > > > they are archived: > > $ ls -l pg_xlog/archive_status/ | head > > total 179 > > -rw------- 1 postgres postgres 0 Sep 8 20:14 00000001000003DB0000003E.000061A8.backup.done > > -rw------- 1 postgres postgres 0 Feb 8 11:39 000000010000076D000000F7.ready > > -rw------- 1 postgres postgres 0 Feb 8 11:40 000000010000076D000000F8.ready > > -rw------- 1 postgres postgres 0 Feb 8 11:40 000000010000076D000000F9.ready > > -rw------- 1 postgres postgres 0 Feb 8 11:40 000000010000076D000000FA.ready > > -rw------- 1 postgres postgres 0 Feb 8 11:41 000000010000076D000000FB.ready > > -rw------- 1 postgres postgres 0 Feb 8 11:42 000000010000076D000000FC.ready > > -rw------- 1 postgres postgres 0 Feb 8 11:43 000000010000076D000000FD.ready > > -rw------- 1 postgres postgres 0 Feb 8 11:44 000000010000076D000000FE.ready > > Well normally those would get deleted automatically after archiving, > but since you're suggesting the previous archive_command didn't return > a zero exit status, that process wouldn't have happened. If you're > sure those are archived, can't you just go ahead and delete them > manually? right now archiving works, and was called for all older wal segments - so we can see it in archive_status/. I probably could remove them by hand, but I never feel ok to do stuff like this manually, and what's more - i'd prefer to understand why these are not getting removed. Best regards, depesz -- Linkedin: http://www.linkedin.com/in/depesz / blog: http://www.depesz.com/ jid/gtalk: depesz@depesz.com / aim:depeszhdl / skype:depesz_hdl / gg:6749007