Rural Hunter escribió:
> 2. Since db1 is a very large database(it is the main db the user is
> using) I can not afford to take long time to vacuum full on that. So
> I thought about to try on other small dbs first.
>
> 3. I stop the instance.
>
> 4. I use "echo 'vacuum full;' | postgres --single -D /pgdata [other
> dbs]" to vacuum some other dbs.
Two things. One is you don't need VACUUM FULL, as already pointed out;
plain VACUUM suffices. The other is that you don't actually need to
vacuum all tables; only those with a very old pg_class.relfrozenxid.
The one with the oldest value is that feeds pg_database.datfrozenxid;
and that's what feeds the "must be vacuumed within XY transactions"
messages. So you can just connect to db1, examine pg_class looking for
tables whose age(relfrozenxid) is old, and vacuum only those. No need
for downtime.
Now the interesting question is why didn't autovacuum get to these.
Normally it does, but when there's conflicting activity (say you have
periodic ALTER TABLE for some reason) it might not be able to. Check
the log for ERRORs that made autovacuum kill itself, for example. In
the long run, the best solution is to not have such conflicting activity
in the first place.
--
Álvaro Herrera http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services
