Re: Sv: Conflict between autovacuum and backup restoration

But I don't understand why I'm getting those messages about autovacuum
blocking db restore process. I guess that after one table is created
with COPY sentence, as many rows have been inserted, autoanalyze process
runs to gather statistics for the Execution Planner. But why is
happening this block? Is autoanalyze running before the table gets fully
loaded? Is this really a problem? If so, how can I handle it? This task
is running at night, when nobody is using second database.


Thank you for reading,

Ekaterina

 

It is normal to get these "canceling autovacuum"-messages when restoring a database, just ignore them.

If it bothers you, just turn autovacuum off by setting this in postgresql.conf:

 

autovacuum = off

 

and reload the config (SIGHUP)

The server stores more Production databases and I don't want to interfere with them, and turning off autovacuum will affect all of them.

I've been looking for other parameters that could help me to avoid autoanalyze during  backup restoration but all of them (at least the ones I've been exploring) have sighup context associated, like autovacuum. Is there any parameter (in v8.4) that could somehow stop autoanalyze only in the connection used to restore the database?

 

--
Andreas Joseph Krogh