Re: Should a DB vacuum use up a lot of space ?

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