postgresql 杀死 autovacuum 的危险：VACUUM 查询（以防止环绕）
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/18048842/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Danger in killing autovacuum: VACUUM queries (to prevent wraparound)
提问by Henley Chiu
There is an autovacuum query that is taking a very long time to run, and preventing alter queries to run.
有一个 autovacuum 查询需要很长时间才能运行，并阻止了更改查询的运行。
What is the danger is killing this autovacuum process before it's done?
在它完成之前杀死这个 autovacuum 过程有什么危险？
PID QUERY 16967 | autovacuum: VACUUM public.articles (to prevent wraparound)
Here is how I am killing it:
这是我如何杀死它：
select pg_terminate_backend(16967) from pg_stat_activity;
回答by bma
You can issue pg_cancel_backend(16967)rather than "pg_terminate_backend()" (not quite as severe is my understanding). Once you kill that autovacuum process, it will start back up again as you have probably noticed, particularly because it was launched for the reason stated (which was to prevent wraparound). If you issue VACUUM public.articlesmanually, the vacuum will complete faster at the expense of higher disk I/O. That's a generalized answer, but is usually how it turns out.
您可以发出pg_cancel_backend(16967)而不是“pg_terminate_backend()”（我的理解并不那么严重）。一旦您终止了该 autovacuum 进程，它就会重新启动，正如您可能已经注意到的那样，特别是因为它是出于所述原因启动的（这是为了防止回绕）。如果您VACUUM public.articles手动发出，真空会以更高的磁盘 I/O 为代价完成得更快。这是一个笼统的答案，但结果通常是这样。
回答by RunningAdithya
It is better you do the vaccum before getting the situation worse. At one time or on other, you will have to do that to prevent the data loss. You may wonder where all the datas are gone when wraparound id failure happens. The data will be still in the db, but it will be hidden and can not be accessed until the vaccum process is done. So let it be done either by autovaccum or manual vaccum.
最好在情况变得更糟之前进行真空吸尘。有时，您必须这样做以防止数据丢失。您可能想知道当 wraparound id 失败发生时所有数据都去哪儿了。数据将仍然在数据库中，但它会被隐藏并且在 vaccum 过程完成之前无法访问。所以让它通过自动真空或手动真空来完成。
回答by jjanes
If you need to do an alter table just one time, there is probably no harm in killing it this one time. You should submit the cancel and the alter table on the same line in psql, so that the alter table has a chance to start before another autovacuum kicks off and blocks it again.
如果您只需要执行一次更改表，那么这一次杀死它可能没有坏处。您应该在 psql 的同一行上提交取消和更改表，以便更改表有机会在另一个自动清理启动并再次阻止它之前启动。
There is a small chance that cancelling the autovacuum will cause txid wrap-around and an emergency shutdown of your database which will take a bit of work and downtime to clean up. But if this happens, you were almost certainly in a death-race already anyway.
取消 autovacuum 很有可能会导致 txid 环绕和数据库的紧急关闭，这将需要一些工作和停机时间来清理。但如果发生这种情况，你几乎可以肯定已经在一场死亡竞赛中。
If you do this often, then you will be storing up massive problems for yourself, including the aforementioned death-race and emergency shutdown.
如果您经常这样做，那么您将为自己存储大量问题，包括前面提到的死亡竞赛和紧急关闭。
By the way, your select pg_terminate_backend(16967) should not have a FROM clause on it.
顺便说一句，您的选择 pg_terminate_backend(16967) 上不应包含 FROM 子句。
