手动修改PostgreSQL与Django模型后迁移报错，如何同步两者？

将遗留数据库导入PostgreSQL并与Django项目建立连接后，直接通过SQL脚本在PostgreSQL中删除了confidence等列，同时手动修改Django的models.py删除对应列。执行python manage.py migrate时，Django尝试删除已手动移除的confidence列，报错：

django.db.utils.ProgrammingError: column "confidence" of relation "Location" does not exist

完整报错日志：

$ python manage.py migrate
D:\projects\map\dataXProject
(0.328)
            SELECT
                c.relname,
                CASE
                    WHEN c.relispartition THEN 'p'
                    WHEN c.relkind IN ('m', 'v') THEN 'v'
                    ELSE 't'
                END
            FROM pg_catalog.pg_class c
            LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
            WHERE c.relkind IN ('f', 'm', 'p', 'r', 'v')
                AND n.nspname NOT IN ('pg_catalog', 'pg_toast')
                AND pg_catalog.pg_table_is_visible(c.oid)
        ; args=None; alias=default
(0.250) SELECT "django_migrations"."id", "django_migrations"."app", "django_migrations"."name", "django_migrations"."applied" FROM "django_migrations"; args=(); alias=default        
(0.235) 
            SELECT
                c.relname,
                CASE
                    WHEN c.relispartition THEN 'p'
                    WHEN c.relkind IN ('m', 'v') THEN 'v'
                    ELSE 't'
                END
            FROM pg_catalog.pg_class c
            LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
            WHERE c.relkind IN ('f', 'm', 'p', 'r', 'v')
                AND n.nspname NOT IN ('pg_catalog', 'pg_toast')
                AND pg_catalog.pg_table_is_visible(c.oid)
        ; args=None; alias=default
(0.343) SELECT "django_migrations"."id", "django_migrations"."app", "django_migrations"."name", "django_migrations"."applied" FROM "django_migrations"; args=(); alias=default        
Operations to perform:
  Apply all migrations: admin, auth, contenttypes, sessions, sites
Running migrations:
(0.281) 
            SELECT
                c.relname,
                CASE
                    WHEN c.relispartition THEN 'p'
                    WHEN c.relkind IN ('m', 'v') THEN 'v'
                    ELSE 't'
                END
            FROM pg_catalog.pg_class c
            LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
            WHERE c.relkind IN ('f', 'm', 'p', 'r', 'v')
                AND n.nspname NOT IN ('pg_catalog', 'pg_toast')
                AND pg_catalog.pg_table_is_visible(c.oid)
        ; args=None; alias=default
  Applying sites.0008_remove_location_approved_remove_location_confidence_and_more...ALTER 
TABLE "Location" DROP COLUMN "approved" CASCADE; (params ())
(0.718) ALTER TABLE "Location" DROP COLUMN "approved" CASCADE; args=(); alias=default
ALTER TABLE "Location" DROP COLUMN "confidence" CASCADE; (params ())
(0.328) ALTER TABLE "Location" DROP COLUMN "confidence" CASCADE; args=(); alias=default
Traceback (most recent call last):
  File "C:\Users\User\AppData\Local\Programs\Python\Python310\lib\site-packages\django\db\backends\utils.py", line 89, in _execute
    return self.cursor.execute(sql, params)
psycopg2.errors.UndefinedColumn: column "confidence" of relation "Location" does not exist 


The above exception was the direct cause of the following exception:

Traceback (most recent call last):
  File "D:\projects\map\dataXProject\manage.py", line 22, in <module>
    main()
  File "D:\projects\map\dataXProject\manage.py", line 18, in main
    execute_from_command_line(sys.argv)
  File "C:\Users\User\AppData\Local\Programs\Python\Python310\lib\site-packages\django\core\management\__init__.py", line 446, in execute_from_command_line
    utility.execute()
  File "C:\Users\User\AppData\Local\Programs\Python\Python310\lib\site-packages\django\core\management\__init__.py", line 440, in execute
    self.fetch_command(subcommand).run_from_argv(self.argv)
  File "C:\Users\User\AppData\Local\Programs\Python\Python310\lib\site-packages\django\core\management\base.py", line 414, in run_from_argv
    self.execute(*args, **cmd_options)
  File "C:\Users\User\AppData\Local\Programs\Python\Python310\lib\site-packages\django\core\management\base.py", line 460, in execute
    output = self.handle(*args, **options)
  File "C:\Users\User\AppData\Local\Programs\Python\Python310\lib\site-packages\django\core\management\base.py", line 98, in wrapped
    res = handle_func(*args, **kwargs)
  File "C:\Users\User\AppData\Local\Programs\Python\Python310\lib\site-packages\django\core\management\commands\migrate.py", line 290, in handle
    post_migrate_state = executor.migrate(
  File "C:\Users\User\AppData\Local\Programs\Python\Python310\lib\site-packages\django\db\migrations\executor.py", line 131, in migrate
    state = self._migrate_all_forwards(
  File "C:\Users\User\AppData\Local\Programs\Python\Python310\lib\site-packages\django\db\migrations\executor.py", line 163, in _migrate_all_forwards
    state = self.apply_migration(
  File "C:\Users\User\AppData\Local\Programs\Python\Python310\lib\site-packages\django\db\migrations\executor.py", line 248, in apply_migration
    state = migration.apply(state, schema_editor)
  File "C:\Users\User\AppData\Local\Programs\Python\Python310\lib\site-packages\django\db\migrations\migration.py", line 131, in apply
    operation.database_forwards(
  File "C:\Users\User\AppData\Local\Programs\Python\Python310\lib\site-packages\django\db\migrations\operations\fields.py", line 170, in database_forwards
    schema_editor.remove_field(
  File "C:\Users\User\AppData\Local\Programs\Python\Python310\lib\site-packages\django\db\backends\base\schema.py", line 683, in remove_field
    self.execute(sql)
  File "C:\Users\User\AppData\Local\Programs\Python\Python310\lib\site-packages\django\db\backends\base\schema.py", line 192, in execute
    cursor.execute(sql, params)
  File "C:\Users\User\AppData\Local\Programs\Python\Python310\lib\site-packages\django\db\backends\utils.py", line 103, in execute
    return super().execute(sql, params)
  File "C:\Users\User\AppData\Local\Programs\Python\Python310\lib\site-packages\django\db\backends\utils.py", line 67, in execute
    return self._execute_with_wrappers(
  File "C:\Users\User\AppData\Local\Programs\Python\Python310\lib\site-packages\django\db\backends\utils.py", line 80, in _execute_with_wrappers
    return executor(sql, params, many, context)
  File "C:\Users\User\AppData\Local\Programs\Python\Python310\lib\site-packages\django\db\backends\utils.py", line 84, in _execute
    with self.db.wrap_database_errors:
  File "C:\Users\User\AppData\Local\Programs\Python\Python310\lib\site-packages\django\db\utils.py", line 91, in __exit__
    raise dj_exc_value.with_traceback(traceback) from exc_value
  File "C:\Users\User\AppData\Local\Programs\Python\Python310\lib\site-packages\django\db\backends\utils.py", line 89, in _execute
    return self.cursor.execute(sql, params)
django.db.utils.ProgrammingError: column "confidence" of relation "Location" does not exist

问题根源是Django的迁移记录（django_migrations表）与实际数据库状态不一致——你手动删除了列，但Django仍认为需要执行对应的删除迁移操作。以下是几种修复方式：

方法一：标记迁移为已应用（推荐） #

1. 连接到PostgreSQL数据库（使用命令行或pgAdmin）
2. 查询目标迁移记录：

   SELECT * FROM django_migrations WHERE app = 'sites' AND name = '0008_remove_location_approved_remove_location_confidence_and_more';
   

3. 如果该记录存在且applied字段为false，更新为true以标记迁移完成：

   UPDATE django_migrations SET applied = true WHERE app = 'sites' AND name = '0008_remove_location_approved_remove_location_confidence_and_more';
   

4. 重新执行python manage.py migrate，Django会跳过已标记为应用的迁移，不再尝试删除不存在的列。

方法二：修改迁移文件（临时应急） #

1. 找到对应应用的迁移文件，路径类似sites/migrations/0008_remove_location_approved_remove_location_confidence_and_more.py
2. 打开文件，删除涉及删除confidence列的代码（通常是RemoveField类的实例）
3. 保存后执行python manage.py migrate，迁移将仅执行剩余的有效操作（如删除approved列）

方法三：重置迁移状态（适用于迁移历史简单的场景） #

如果项目迁移历史不复杂，可以重置整个应用的迁移状态：

1. 备份数据库，避免数据丢失
2. 删除对应应用下的所有迁移文件（保留__init__.py）
3. 清空django_migrations表中该应用的记录：

   DELETE FROM django_migrations WHERE app = 'sites';
   

4. 生成新的初始迁移：

   python manage.py makemigrations --name initial sites
   

5. 标记初始迁移为已应用（不实际修改数据库）：

   python manage.py migrate --fake-initial sites
   

• 后续避免手动修改数据库结构，所有结构变更通过Django的makemigrations和migrate命令完成，确保迁移记录与数据库状态一致
• 操作数据库前务必备份，防止数据丢失

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