英文:
Postgres reindex index concurrently not finishing
问题
我在我的PostgreSQL服务器版本13上遇到了一个奇怪的行为。
索引非常小。当我运行：
reindex index my_index_name;
非常快。但是当我尝试
reindex index concurrently my_index_name;
它无法完成。它运行了几天。服务器上没有锁定，什么都没有。这里可能的问题是什么？
英文:
I have a strange behavior on my postgres server version 13.
The index is very small. When I run:
reindex index my_index_name;
It is very fast. But when I try
reindex index concurrently my_index_name;
It won't finish. It runs for days. There are not lock on the server, nothing. What can be the problem here?
答案1
得分: 2
我找到了问题：
有很多处于事务空闲状态的开放连接。
这些连接阻止了重新索引的启动。它总是在等待旧快照的阶段（通过使用 select * from pg_stat_progress_create_index; 查看）。
我重新启动了PostgreSQL服务器，现在一切正常运行。
英文:
I found the problem:
There were many open connections with the stat Idle in transaction.
This connections prevented the reindex to start. It was always in the phase waiting for old snapshot (seen with select * from pg_stat_progress_create_index;
)
I restarted the postgres server, now everything works fine.
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论