postgresql 如何使用alembic运行alter table迁移-耗时太长且永不结束

z8dt9xmd  于 2022-12-18  发布在  PostgreSQL
关注(0)|答案(1)|浏览(133)

我尝试用alembic运行一个迁移(添加一列),但是时间太长了,而且永远不会结束。表有100行,我没有看到错误。
这是我用python编写的迁移代码
““”

from alembic import op
import sqlalchemy as sa

# revision identifiers, used by Alembic.
revision = 'd6fe1dec4bcd'
down_revision = '3f532791c5f3'
branch_labels = None
depends_on = None

def upgrade() -> None:
    op.add_column('products2', sa.Column(
        'product_status', sa.String(255)))

def downgrade() -> None:
    op.drop_column('products2', 'product_status')

这是我在postgres看到的,当我检查

SELECT * FROM pg_stat_activity WHERE state = 'active';
ALTER TABLE products2 ADD COLUMN product_status VARCHAR(255)

这是我在终端看到的

INFO  [alembic.runtime.migration] Context impl PostgresqlImpl.
INFO  [alembic.runtime.migration] Will assume transactional DDL.
INFO  [alembic.runtime.migration] Running upgrade 3f532791c5f3 -> d6fe1dec4bcd, create product status column


我该如何解决这个问题?我在Google Cloud控制台上运行postgres,但是我在他们的平台上没有看到任何错误

9gm1akwq

9gm1akwq1#

从pg_locks获取活动锁:

SELECT t.relname, l.locktype, page, virtualtransaction, pid, mode, granted
FROM pg_locks l, pg_stat_all_tables t 
WHERE l.relation = t.relid 
ORDER BY relation asc;
Copy the pid(ex: 14210) from above result and substitute in the below command.

SELECT pg_terminate_backend(14210)

相关问题