From: Alvaro Herrera <alvherre@2ndquadrant.com>
To: Rural Hunter <ruralhunter@gmail.com>
Cc: pgsql-admin@postgresql.org
Subject: Re: wrong database name in error message?
Date: Sat, 14 Sep 2013 22:39:59 -0300
Message-ID: <20130915013959.GA5285@eldon.alvh.no-ip.org> (raw)
In-Reply-To: <5233F3A9.30901@gmail.com>
References: <5233BA90.3020107@gmail.com>
<20130914045519.GD5033@eldon.alvh.no-ip.org>
<5233F3A9.30901@gmail.com>
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-admin>
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
--
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-admin@postgresql.org
Cc: alvherre@2ndquadrant.com, ruralhunter@gmail.com
Subject: Re: wrong database name in error message?
In-Reply-To: <20130915013959.GA5285@eldon.alvh.no-ip.org>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox