From: | Philippe Girolami <philippe(dot)girolami(at)mosaik(dot)com> |
---|---|
To: | "pgsql-general(at)postgresql(dot)org" <pgsql-general(at)postgresql(dot)org> |
Subject: | Should a DB vacuum use up a lot of space ? |
Date: | 2016-08-06 06:57:42 |
Message-ID: | 00953EDF-E336-4B61-B96C-5C3ECA60BA8D@mosaik.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
Hi all,
We’ve got a 16TB database that’s run up against the wrap-around tx id issue (despite running auto-vacuum):
ERROR: database is not accepting commands to avoid wraparound data loss in database "public"
HINT: Stop the postmaster and use a standalone backend to vacuum that database.
The message says to VACUUM the whole database so we’ve dropped down to single user mode and issued a “vacuum verbose” command on the backend
1) Unlike other times I’ve run vacuum verbose, I’m not getting any logging message at all (for the past 9 hours)
2) File system usage is going steadily up (3TB over the past 9 hours). Why is that ? I didn’t expect additional disk usage and we’re going to run into disk space usage if this keeps up.
3) Was a database-wide vacuum required or could I have just figured out which tables needed to be vacuumed and vacuumed those (my understanding was no), for example using http://dba.stackexchange.com/a/35234 and a query such as SELECT c.oid::regclass as table_name, greatest(age(c.relfrozenxid),age(t.relfrozenxid)) as age FROM pg_class c LEFT JOIN pg_class t ON c.reltoastrelid = t.oid WHERE c.relkind IN ('r', 'm') order by 2 desc limit 10;
4) Assuming I could have just identified individual tables that required vacuuming, can I CTRL-C the current vacuum process without running into issues ?
Thanks
Philippe Girolami
From | Date | Subject | |
---|---|---|---|
Next Message | Adrian Klaver | 2016-08-06 14:14:23 | Re: Should a DB vacuum use up a lot of space ? |
Previous Message | Patrick B | 2016-08-06 04:32:41 | Re: Question about wal files / pg_xlogs |