On 08/05/2016 11:57 PM, Philippe Girolami wrote:
Hi all,
We’ve got a 16TB database that’s run up against the wrap-around tx id
issue (despite running auto-vacuum):
What Postgres version?
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)
In single user mode you need to specify the log file on the command line:
https://www.postgresql.org/docs/9.5/static/app-postgres.html
-r filename
Send all server log output to filename. This option is only honored
when supplied as a command-line option.
The file will show up the $DATA directory.
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.
What was the exact command you gave?
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;
See here:
https://www.postgresql.org/docs/9.5/static/routine-vacuuming.html#VACUUM-FOR-WRAPAROUND
4) Assuming I could have just identified individual tables that
required vacuuming, can I CTRL-C the current vacuum process without
running into issues ?
Running a test on a small database here, a Ctrl-D in the single user
session closed it down successfully while running a VACUUM. Seems it
comes down to which is the bigger issue, running out of disk space or
having to redo the VACUUM.
Thanks
Philippe Girolami
--
Adrian Klaver
adrian.klaver@xxxxxxxxxxx
--
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
