Alembic迁移报DuplicateColumn错误，但目标列此前不存在

尝试通过Bitbucket Pipeline执行Alembic数据库迁移，相关配置及代码如下：

Bitbucket Pipeline配置 #

steps:
  - step: &health-check
      name: Health Check
      script:
        - export HTTP_ADDR=$BITBUCKET_DOCKER_HOST_INTERNAL
        - export HTTP_PORT=dummy_port_1
        - export FLASK_ENV=development
        - export GOOGLE_APPLICATION_CREDENTIALS=fury/secrets/credit-staging.json
        - export FLASK_APP=fury
        - export CONTAINER_ENV=localhost
        - export TEMP_STORAGE_PATH=/var/tmp
        - export SERVICE_NAME=fury-service
        - export POSTGRES_USER=postgres
        - export POSTGRES_PASSWORD=dummy
        - export POSTGRES_DB=fdummy
        - export POSTGRES_PORT=dummy_port
        - export POSTGRES_URL=dummmy_url_1
        - export GOOGLE_CLOUD_PROJECT=credit-staging
        - export REDIS_HOST=localhost
        - export REDIS_PORT=dummy_port_11
        - pip install --upgrade pip==20.2.1
        - pip install -r requirements.txt
        - apt-get update
        - apt-get install -y postgresql postgresql-client
        - export DOCKER_FLAG=true
        - chmod 777 http_request.sh
        - exec gunicorn -b $HTTP_ADDR:$HTTP_PORT -t 3600 main:app &
        - sleep 10
        - ./http_request.sh
        - python -c "from ci_script_libs import trigger_ci_scripts;trigger_ci_scripts.trigger(_instance_with_code_is_up=true)"

  - step: &prod_migration
      name: db migrations
      image: google/cloud-sdk:latest
      script:
        - ./authorize_user.sh
        - echo "Deployment:- ${BITBUCKET_DEPLOYMENT_ENVIRONMENT}"
        - cp ./migration_dockerfile ./Dockerfile
        - export IMAGE_NAME=gcr.io/$PROJECT/fury-service
        - echo $GCLOUD_API_KEYFILE > ./gcloud-api-key.json
        - gcloud auth activate-service-account $email --key-file=./gcloud-api-key.json

        - gcloud config list
        - gcloud auth configure-docker -q

        - gcloud builds submit --tag $IMAGE_NAME  --project $PROJECT
        - gcloud run deploy $SERVICE_NAME --image $IMAGE_NAME --region asia-south1 --project $PROJECT --cpu $cpu --memory $memory --min-instances $MIN_INSTANCES
        - gcloud run services update-traffic $SERVICE_NAME --to-latest --region asia-south1 --project $PROJECT

        - echo "Bliss..!!!"

deploy_and_migrate_prod:
  - variables:
      - name: SERVICE_NAME
        default: fury-service
        allowed-values:
          - fury-service
          - fury-webhooks
  - step:
      <<: *health-check
      name: Health Check

  - step:
      name: Migrations & Deploy
      <<: *prod_migration
      default: fury-service
      deployment: staging

migration_dockerfile #

FROM python:3.7
WORKDIR /app
COPY . /app
RUN pip install --upgrade pip==20.2.1
RUN pip install -r requirements.txt
ENV FLASK_APP=main.py
CMD ./migration_start.sh

migration_start.sh #

#!/usr/bin/env bash

set -e
DOCKER_FLAG="${DOCKER_FLAG:-true}"
POSTGRES_URL="${POSTGRES_URL:-127.0.0.1}"
flask db upgrade
exec gunicorn

迁移版本文件 #

"""empty message

Revision ID: 6c0f9dcc2bb1
Revises: bdb59e00af3c
Create Date: 2023-06-27 18:06:25.182115

"""
from alembic import op
import sqlalchemy as sa


# revision identifiers, used by Alembic.
revision = '6c0f9dcc2bb1'
down_revision = 'bdb59e00af3c'
branch_labels = None
depends_on = None


def upgrade():
    # ### commands auto generated by Alembic - please adjust! ###
    op.add_column('KYC_Test', sa.Column('test_column_prod', sa.VARCHAR(length=20), autoincrement=False, nullable=True))
    # ### end Alembic commands ###


def downgrade():
    # ### commands auto generated by Alembic - please adjust! ###
    op.drop_column('KYC_Test', 'test_column_prod')
    # ### end Alembic commands ###

models.py #

class KYC_Test(BASE):
    # pylint: disable = invalid-name
    __tablename__ = 'KYC_Test'

    test_col1 = Column(String(20), primary_key=True)

    dt_created = Column(DateTime, default=datetime.datetime.now)
    dt_updated = Column(DateTime, default=datetime.datetime.now, onupdate=datetime.datetime.now)
    test_column_prod = Column(String(20))

错误信息 #

执行Pipeline后出现如下错误：

psycopg2.errors.DuplicateColumn: column "test_column_prod" of relation "KYC_Test" already exists

错误详情：

Traceback (most recent call last):
  File "/usr/local/lib/python3.7/site-packages/sqlalchemy/engine/base.py", line 1820, in _execute_context
    cursor, statement, parameters, context
  File "/usr/local/lib/python3.7/site-packages/sqlalchemy/engine/default.py", line 732, in do_execute
    cursor.execute(statement, parameters)
psycopg2.errors.DuplicateColumn: column "test_column_prod" of relation "KYC_Test" already exists

确认迁移前目标数据库中不存在test_column_prod列，请问为何会出现此错误？

核心原因：迁移脚本被重复执行 #

你的迁移逻辑写在容器启动脚本migration_start.sh中，每次Cloud Run实例启动（包括部署新版本、自动扩缩容、健康检查重启）都会执行flask db upgrade。当MIN_INSTANCES设置大于1时，部署过程中会同时启动多个实例，多个实例并行执行迁移命令：

• 两个实例同时检查alembic_version表，都发现目标revision未记录
• 同时执行add_column语句，其中一个实例先成功创建列，另一个实例执行时就会触发“列已存在”的错误

即使MIN_INSTANCES设为1，也可能因为部署过程中实例重启，导致迁移被重复执行；如果之前迁移执行成功但alembic_version表未正确更新（比如中途中断），也会出现同样的问题。

其他可能原因 #

1. Alembic版本表状态异常：检查目标数据库的alembic_version表，若其中没有6c0f9dcc2bb1的记录，但列已被创建，说明之前迁移执行到一半失败，版本记录未更新。
2. 环境变量配置错误：确认Pipeline中是否正确传递了生产环境的数据库连接参数，是否误连接到已执行过迁移的测试库。

解决办法 #

1. 将迁移从容器启动流程中剥离：迁移应该作为Pipeline的独立步骤执行，而不是让每个服务实例启动时都跑迁移。修改prod_migration步骤，在部署Cloud Run服务前，单独执行迁移：

   # 在prod_migration的script中添加迁移步骤（示例）
   - pip install --upgrade pip==20.2.1
   - pip install -r requirements.txt
   - export POSTGRES_URL=你的生产库地址
   - flask db upgrade
   

   或者使用临时容器执行迁移后再部署服务。

2. 添加迁移锁：如果必须在容器启动时执行迁移，需添加分布式锁防止并行执行，比如使用PostgreSQL的pg_advisory_lock，在迁移文件中先获取锁再执行升级：

   # 在迁移文件的upgrade函数开头添加锁逻辑
   def upgrade():
       op.execute("SELECT pg_advisory_lock(12345);")  # 12345为自定义锁ID
       # 原迁移逻辑
       op.add_column('KYC_Test', sa.Column('test_column_prod', sa.VARCHAR(length=20), nullable=True))
       op.execute("SELECT pg_advisory_unlock(12345);")
   

3. 修复版本表状态：如果alembic_version表缺少目标revision记录，但列已存在，可手动插入记录：

   INSERT INTO alembic_version (version_num) VALUES ('6c0f9dcc2bb1');
   

内容的提问来源于stack exchange，提问作者Paras jain