From: | Guillaume Lelarge <guillaume(at)lelarge(dot)info> |
---|---|
To: | Jean-Baptiste Vedel <jb(dot)vedel(at)gmail(dot)com>, "pgsql-admin(at)postgresql(dot)org" <pgsql-admin(at)postgresql(dot)org> |
Subject: | Re: To avoid a database shutdown, execute a database-wide VACUUM |
Date: | 2016-03-02 14:09:12 |
Message-ID: | CAECtzeXHDUbfNc0o8btfcXihQxdkgb5GgPDXtPUDa7=cSfiCXw@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-admin |
Please, use "Reply all" so that you keep the list updated.
2016-03-02 15:01 GMT+01:00 Jean-Baptiste Vedel <jb(dot)vedel(at)gmail(dot)com>:
> Hello,
>
> I was unable to execute VACUUM in zabbix database because VACUUM process
> was stuck in WAITING status.
> Using vacuum in "single user mode" seems to solve this problem.
>
> /usr/pgsql-9.4/bin/postgres --single -D /dbzab/data -O zabbix backend>
> VACUUM FULL;
>
> I hope everything will be ok, i m waiting the end of VACUUM process.
>
>
You didn't need to launch a VACUUM FULL. A VACUUM would have been enough.
> Do you think that i need to commit somme transactions after VACUUM ends ?
>
>
Which transactions as you said you were in single mode? :)
Moreover, there's no way you can commit transactions on other sessions.
> < 2016-03-02 07:04:19.406 CET >WARNING: database "zabbix" must be
> vacuumed within 7989357 transactions
> < 2016-03-02 07:04:19.406 CET >HINT: To avoid a database shutdown,
> execute a database-wide VACUUM in that database.
> -> You might also need to commit or roll back old prepared transactions.
>
>
Oh, so you were speaking about prepared transactions. I'm pretty sure you
don't have any.
> Best Regards,
>
>
>
> 2016-03-02 10:05 GMT+01:00 Guillaume Lelarge <guillaume(at)lelarge(dot)info>:
>
>> Hi,
>>
>> 2016-03-02 8:04 GMT+01:00 Jean-Baptiste Vedel <jb(dot)vedel(at)gmail(dot)com>:
>>
>>> Hello,
>>>
>>> I've got a problem with my postgresql database 9.4 this morning.
>>> Few days ago we've moved many data in the database to create new data
>>> partitions and delete many entries.
>>> Auto-vacuum seems to be inefficient and i am unable to vacuum my zabbix
>>> database.
>>> Do you have any idea about this problem ?
>>>
>>> < 2016-03-02 07:04:19.406 CET >WARNING: database "zabbix" must be
>>> vacuumed within 7989357 transactions
>>> < 2016-03-02 07:04:19.406 CET >HINT: To avoid a database shutdown,
>>> execute a database-wide VACUUM in that database.
>>>
>>>
>> Why are you unable to VACUUM your zabbix database? because it is the
>> solution to your issue.
>>
>>
>> --
>> Guillaume.
>> http://blog.guillaume.lelarge.info
>> http://www.dalibo.com
>>
>
>
--
Guillaume.
http://blog.guillaume.lelarge.info
http://www.dalibo.com
From | Date | Subject | |
---|---|---|---|
Next Message | Artem Tomyuk | 2016-03-02 15:25:10 | autovacuum disk IO |
Previous Message | Guillaume Lelarge | 2016-03-02 09:05:41 | Re: To avoid a database shutdown, execute a database-wide VACUUM |