how to truncate/purge the pg_xlog directory?

tfinneid@student.matnat.uio.no wrote:

Hi

As far as I understand pg_xlog is the transaction log i.e. the WAL.

Correct. And it should have files called e.g. 0000000100000005000000E9
each 16MB long.

I have a pg 8.2 which has consumed about 113MB in pg_xlog/, while the
cluster is virtually empty.

OK, so that's 7 files?
What is your checkpoint_segments setting in postgresql.conf? If it's 3
then you can indeed expect a maximum of 7 WAL files. For a low-activity
system you can always turn the setting down.

There are a couple of tables in the postgres
schema, but they contain no data at the moment. My disk is filling up and
I want to purge directories that are larger than need to be.
I performed a VACUUM FULL, because I thought that would help, but it did not.
I searched the net and the forums here and found only one post about
tuning the wal parameters to control the pg_xlog directory size, but when
looking at the documentation for that I found nothing that could be used
for that

Also the base/ directory is 268MB big and still not many tables, is ti
possible to purge that directory as well?.

You might find the "pg_xxx_size()" functions useful - they are detailed
in the functions/system-admin chapter of the manuals.

--
Richard Huxton
Archonet Ltd

tfinneid@student.matnat.uio.no wrote:

OK, so that's 7 files?
What is your checkpoint_segments setting in postgresql.conf? If it's 3
then you can indeed expect a maximum of 7 WAL files. For a low-activity
system you can always turn the setting down.

The setting was not set, so I expect that it used the default value of 3.
I tried setting it to 1, and restarted the server, but nothing happened
with the directories. Is there something that triggers the cleaning of it?

Another thing, I dont understand how a setting of 3 leads to 7 files since
one segment is 16MB, 3 segments should be be 3 files i.e. 48MB not 113MB.
Am I misunderstanding something?

Also the base/ directory is 268MB big and still not many tables, is ti
possible to purge that directory as well?.

You might find the "pg_xxx_size()" functions useful - they are detailed
in the functions/system-admin chapter of the manuals.

Can you be a bit more specific? I tried looking for it, but the sections
are too big to just browse unknown information.

tfinneid@student.matnat.uio.no wrote:

tfinneid@student.matnat.uio.no wrote:

OK, so that's 7 files?
What is your checkpoint_segments setting in postgresql.conf? If it's 3
then you can indeed expect a maximum of 7 WAL files. For a low-activity
system you can always turn the setting down.

The setting was not set, so I expect that it used the default value of 3.
I tried setting it to 1, and restarted the server, but nothing happened
with the directories. Is there something that triggers the cleaning of it?

Another thing, I dont understand how a setting of 3 leads to 7 files since
one segment is 16MB, 3 segments should be be 3 files i.e. 48MB not 113MB.
Am I misunderstanding something?

This parameter actually controls when a checkpoint is forced.
Indirectly, that affects the number of WAL files kept around. It's
actually (2*checkpoint_segments + 1). For those that are interested it's
"XLOGfileslop" in backend/access/transam/xlog.c.

As checkpoints are issued, old WAL files will either be recycled
(renamed) or deleted. Change the setting, then try (as user postgres)
issuing a "vacuum full <tbl>" followed by "SELECT pg_switch_xlog()" that
should recycle them quicker. That'll only get you down to 3 or 4 files
though - that's the minimum.

Also the base/ directory is 268MB big and still not many tables, is ti
possible to purge that directory as well?.

You might find the "pg_xxx_size()" functions useful - they are detailed
in the functions/system-admin chapter of the manuals.

Can you be a bit more specific? I tried looking for it, but the sections
are too big to just browse unknown information.

Try looking again, perhaps?
Go to the online manuals, go to the section on "functions and operators"
then to the section on "system administration functions". The first
ocurrence of the word "size" on that page is a heading for the bit you need.

--
Richard Huxton
Archonet Ltd

As checkpoints are issued, old WAL files will either be recycled
(renamed) or deleted. Change the setting, then try (as user postgres)
issuing a "vacuum full <tbl>" followed by "SELECT pg_switch_xlog()" that
should recycle them quicker. That'll only get you down to 3 or 4 files
though - that's the minimum.

Nothings changed yet, maybe I need to start using the db again before I
something real happens...

You might find the "pg_xxx_size()" functions useful - they are detailed
in the functions/system-admin chapter of the manuals.

Can you be a bit more specific? I tried looking for it, but the sections
are too big to just browse unknown information.

Try looking again, perhaps?
Go to the online manuals, go to the section on "functions and operators"
then to the section on "system administration functions". The first
ocurrence of the word "size" on that page is a heading for the bit you
need.

That was not easy to find, your descriptions were not easy to dechiper.
When you said sys-admin chapter I started looking for a functions chapter
in section 3 (i.e. chapters 14-28), but you were talking about chapter
9... which is in the section about the sql language. Quite confusing.

regards

thomas

tfinneid@student.matnat.uio.no wrote:

As checkpoints are issued, old WAL files will either be recycled
(renamed) or deleted. Change the setting, then try (as user postgres)
issuing a "vacuum full <tbl>" followed by "SELECT pg_switch_xlog()" that
should recycle them quicker. That'll only get you down to 3 or 4 files
though - that's the minimum.

Nothings changed yet, maybe I need to start using the db again before I
something real happens...

The timestamps on the WAL files should be updated if they get recycled.
Check those.

--
Richard Huxton
Archonet Ltd