CODEWITHSUNDEEP
postgresql
RonJohn
RonJohn

Reputation: 425

pg_clog files not being deleted even though I VACUUM FULL

version 8.4.17

(Yes, I know it's old, but that's beyond my control.)

There are 180 pg_clog files out there, many from August.

$ dir /var/lib/pgsql/data/pg_clog | grep 2017-08 | wc
     59     472    4307

I vacuum full using this script, because the DB is very large and I want to see progress. (I've stripped out a lot of logging.) Still, the pg_clog files remain.

vacuumdb -ezd postgres
SQL="select schemaname||'.'||tablename \
     from pg_tables \
     where schemaname not in ('information_schema', 'pg_catalog') \
     order by schemaname,tablename;"
psql -t $DB -c "$SQL" > $TABLES
for T in $(cat $TABLES); do
    psql -q $DB -c "VACUUM FULL ${T};"
done

What am I doing wrong?

EDIT: Added info for @CraigRinger

Columns datminmxid and relminmxid don't exist.

postgres=# select datname, datfrozenxid, age(datfrozenxid)
from pg_database
order by age(datfrozenxid);
  datname  | datfrozenxid |    age   
-----------+--------------+-----------
template0 |   3603470462 |  24747443
template1 |   3603334165 |  24883740
postgres  |   3576970250 |  51247655
TAPd      |   3433741226 | 194476679
(4 rows)

TAPd=# select oid, relname, relfrozenxid, age(relfrozenxid)
from pg_class
where not relfrozenxid = xid '0'
order by age(relfrozenxid) desc;
    oid    |                    relname                    | relfrozenxid |    age   
-----------+-----------------------------------------------+--------------+-----------
      2617 | pg_operator                                   |   3433741226 | 194476689
      2602 | pg_amop                                       |   3433741226 | 194476689
      2753 | pg_opfamily                                   |   3445877061 | 182340854
      1259 | pg_class                                      |   3445877061 | 182340854
      1136 | pg_pltemplate                                 |   3445877061 | 182340854
      1213 | pg_tablespace                                 |   3445877061 | 182340854
      3600 | pg_ts_dict                                    |   3445877061 | 182340854
      1262 | pg_database                                   |   3445877061 | 182340854
      3603 | pg_ts_config_map                              |   3445877061 | 182340854
     11467 | sql_parts                                     |   3445877061 | 182340854
      3602 | pg_ts_config                                  |   3445877061 | 182340854
     11457 | sql_languages                                 |   3445877061 | 182340854
      1261 | pg_auth_members                               |   3445877061 | 182340854
     11452 | sql_implementation_info                       |   3445877061 | 182340854
      1417 | pg_foreign_server                             |   3445877061 | 182340854
      2328 | pg_foreign_data_wrapper                       |   3445877061 | 182340854
      3764 | pg_ts_template                                |   3445877061 | 182340854
      2396 | pg_shdescription                              |   3445877061 | 182340854
      2600 | pg_aggregate                                  |   3445877061 | 182340854
      3601 | pg_ts_parser                                  |   3445877061 | 182340854
      2613 | pg_largeobject                                |   3445877061 | 182340854
      2612 | pg_language                                   |   3445877061 | 182340854
     11477 | sql_sizing_profiles                           |   3445877061 | 182340854
      2603 | pg_amproc                                     |   3445877061 | 182340854
[snip]

So I've got to vacuum the pg_catalog schema. But still, there are many "user" tables with old relfrozenxid ages, even though I ran VACUUM FULL on the user tables.

Upvotes: 1

Views: 416

Answers (2)

RonJohn
RonJohn

Reputation: 425

The solution is to merge the query from the @CraigRinger comment with VACUUM FREEZE which @LaurenzAlbe suggested.

Get the whole list of tables with old relfrozenxid values:

select cl.oid,
       ta.schemaname, 
       cl.relname,
       cl.relfrozenxid,
       age(cl.relfrozenxid)
     from pg_class cl FULL JOIN pg_tables ta
        ON ta.tablename = cl.relname
     where not cl.relfrozenxid = xid '0'
       and age(cl.relfrozenxid) > 60000000
     order by age(cl.relfrozenxid) desc

Get the list of objects to vacuum:

select COALESCE(ta.schemaname, 'pg_toast') || '.' || cl.relname
     from pg_class cl FULL JOIN pg_tables ta
        ON ta.tablename = cl.relname
     where not cl.relfrozenxid = xid '0'
       and age(cl.relfrozenxid) > 60000000
     order by age(cl.relfrozenxid) desc

Upvotes: 0

Laurenz Albe
Laurenz Albe

Reputation: 246578

You should run VACUUM FREEZE as superuser in all databases.

Then, after the next checkpoint, things should improve.

Upvotes: 1

Related Questions