Re: When will old wal segments get removed? - Mailing list pgsql-general

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