Postgres Pro
Downloads
Home   >   mailing lists

Thread: To avoid a database shutdown, execute a database-wide VACUUM

To avoid a database shutdown, execute a database-wide VACUUM

From
Jean-Baptiste Vedel
Date:
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.

Best Regards,

Re: To avoid a database shutdown, execute a database-wide VACUUM

From
Guillaume Lelarge
Date:
Hi,

2016-03-02 8:04 GMT+01:00 Jean-Baptiste Vedel <jb.vedel@gmail.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.


--

Re: To avoid a database shutdown, execute a database-wide VACUUM

From
Guillaume Lelarge
Date:
Please, use "Reply all" so that you keep the list updated.

2016-03-02 15:01 GMT+01:00 Jean-Baptiste Vedel <jb.vedel@gmail.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@lelarge.info>:
Hi,

2016-03-02 8:04 GMT+01:00 Jean-Baptiste Vedel <jb.vedel@gmail.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.


--




--