When will old wal segments get removed?

From: hubert depesz lubaczewski <depesz(at)depesz(dot)com>
To: pgsql-general(at)postgresql(dot)org
Subject: When will old wal segments get removed?
Date: 2011-02-08 14:07:40
Message-ID: 20110208140739.GA28081@depesz.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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(at)depesz(dot)com / aim:depeszhdl / skype:depesz_hdl / gg:6749007

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Michael 2011-02-08 14:19:38 Re: Displaying text appears as hex data
Previous Message Michael 2011-02-08 14:01:23 Re: Displaying text appears as hex data