Обсуждение: pg_clog not getting cleared
Hello. I posted this once before, but I'm encountering it again.
Each Saturday, I run: vacuumdb -a -v. I have autovac on all the time.
However, my pg_clog directory lists clog files going back to July.
This is pg 8.4.4 on Linux (CentOS 5.5).
I know this isn't a whole lot of information at this time. What more would be useful in finding out what's failing and where?
I seem to recall that doing vacuumdb -f -a -v resolved it, which would seem to point to a database not being vacuumed, but that's sort of the point of the -a switch, right? I'm running this as the "postgres" user himself.
Each Saturday, I run: vacuumdb -a -v. I have autovac on all the time.
However, my pg_clog directory lists clog files going back to July.
This is pg 8.4.4 on Linux (CentOS 5.5).
I know this isn't a whole lot of information at this time. What more would be useful in finding out what's failing and where?
I seem to recall that doing vacuumdb -f -a -v resolved it, which would seem to point to a database not being vacuumed, but that's sort of the point of the -a switch, right? I'm running this as the "postgres" user himself.
Scott Whitney <scott@journyx.com> wrote: > Each Saturday, I run: vacuumdb -a -v. I have autovac on all the > time. > > However, my pg_clog directory lists clog files going back to > July. > > This is pg 8.4.4 on Linux (CentOS 5.5). > > I know this isn't a whole lot of information at this time. What > more would be useful in finding out what's failing and where? Nothing is failing -- clog is needed to track what has committed until transactions are "frozen". > I seem to recall that doing vacuumdb -f -a -v resolved it Are you sure that wasn't -F rather than -f ? The uppercase switch freezes tuples, which would allow clog to be cleaned up. This shouldn't normally be needed, unless you have some other reason to need aggressive freezing. -Kevin
I'm certain it was -f, not -F, since it's still in the script I use, albeit commented out.
I understand the purpose of the clogs, but I would think that the transactions would have been frozen on all dbs (I've got about 300 in my cluster) by now. My logs go back to July 13th which, I think, is when the server was last restarted.
I'll try the -F on Sat and see if that resolves the issue. I'll also have logs available at that time, assuming all goes well.
Thanks for the advice.
I understand the purpose of the clogs, but I would think that the transactions would have been frozen on all dbs (I've got about 300 in my cluster) by now. My logs go back to July 13th which, I think, is when the server was last restarted.
I'll try the -F on Sat and see if that resolves the issue. I'll also have logs available at that time, assuming all goes well.
Thanks for the advice.
Scott Whitney <scott@journyx.com> wrote:
> Each Saturday, I run: vacuumdb -a -v. I have autovac on all the
> time.
>
> However, my pg_clog directory lists clog files going back to
> July.
>
> This is pg 8.4.4 on Linux (CentOS 5.5).
>
> I know this isn't a whole lot of information at this time. What
> more would be useful in finding out what's failing and where?
Nothing is failing -- clog is needed to track what has committed
until transactions are "frozen".
> I seem to recall that doing vacuumdb -f -a -v resolved it
Are you sure that wasn't -F rather than -f ? The uppercase switch
freezes tuples, which would allow clog to be cleaned up. This
shouldn't normally be needed, unless you have some other reason to
need aggressive freezing.
-Kevin
Scott Whitney <scott@journyx.com> writes: > I understand the purpose of the clogs, but I would think that the transactions would have been frozen on all dbs (I'vegot about 300 in my cluster) by now. My logs go back to July 13th which, I think, is when the server was last restarted. It doesn't really try to remove clog entries that are younger than vacuum_freeze_table_age (see also vacuum_freeze_min_age). At two bits per transaction, the general feeling is that eating the disk space is better than forcing full-database vacuums more often. But if you're hot to have the space released sooner, those are the knobs to frob. regards, tom lane