Re: database is not accepting commands to avoid wraparound data loss in database

On Wed, Apr 6, 2016 at 8:13 AM, pedrovlf <pedrovlf@gmail.com> wrote:
> Hi,
>
> I'm following error in my DBMS,
>
> < 2016-04-05 17:02:42.053 BRT >ERROR:  database is not accepting commands to
> avoid wraparound data loss in database "zabbix"
> < 2016-04-05 17:02:42.053 BRT >HINT:  Stop the postmaster and vacuum that
> database in single-user mode.
>
>
> I'm running the vacuum in single mode, but is taking too long ... you can
> retrieve the base otherwise? Perhaps with truncate on a table ...

Sure.  Find the single-user process with top, and then strace it to
see what it is doing:

strace -p 28465 -y

read(11</home/jjanes/pgsql/data/base/16384/18837>,
"\1\0\0\0hP\352=\0\0\0\0\214\2\220\2\0 \4
\0\0\0\0\320\237`\0\240\237`\0"..., 8192) = 8192
lseek(11</home/jjanes/pgsql/data/base/16384/18837>, 124485632,
SEEK_SET) = 124485632

So the problem is file 18837.

Interrupt the vacuum, and run:

select * from pg_class where relfilenode=18786;

(I see I actually messed up my example, the strace I showed was
actually form a different session, but the principle still applies.
Pretend that 18837 and 18786 are the same thing)

...
         1: relname = "foo_md5_idx"     (typeid = 19, len = 64, typmod
= -1, byval = f)

So I see that in my case, it is a index which is holding things up
(and I happen to know this index is a gin index.  This make sense,
because gin and gist indexes are horribly slow to vacuum.)  So in my
case I can just drop the index, rather than truncate the table.  You
might not be so lucky.

So, still in single user mode, I drop the index, redo the vacuum, and
abracadabra.

Cheers,

Jeff