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@aklaver.com
