Search Postgresql Archives

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

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



On Wed, Apr 6, 2016 at 8:13 AM, pedrovlf <pedrovlf@xxxxxxxxx> 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


-- 
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general



[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux