Postgres Vacuum command fails and gives a Timeout error.

Products

• CA Application Performance Management Agent (APM / Wily / Introscope)
• INTROSCOPE

Issue/Introduction

Description:

This article reviews the Error: "vacuumdb: vacuuming of database "cemdb" failed: ERROR: canceling statement due to statement timeout", when running theVacuum command on a Postgres database(s).

Solution:

When the vacuumdb command runs, it connects to the database and then executes a VACUUM statement. If that statement processes longer than the configured statement timeout value for that database, then the command is canceled, and the above error is logged. To avoid this error, try the following:

1. Change the statement_timeout parameter in postgresql.conf and reload the configuration to run vacuumdb. This is not recommended because it affects all future sessions.
   
   To reload the configuration, use the command:
   
   postgres=# select pg_reload_conf();
   
   OR
   
   
2. Establish a connection through psql or pgadmin and execute SET statement_timeout = 0; before issuing a VACUUM statement on that connection.
   
   A timeout of 0 disables this setting. You can also set a statement timeout value. This configured statement timeout value is in milliseconds. To check the value set for this parameter, use the command:
   
   postgres=# show statement_timeout;

Environment

Release:
Component: APMISP