Windows11下Django项目无法连接PostgreSQL执行migrate报错

问题背景 #

在Windows11上开发Django项目，执行python manage.py migrate时触发psycopg2.OperationalError和django.db.utils.OperationalError，无法连接本地PostgreSQL。已删除所有Docker相关容器、镜像、卷，更新并重启PostgreSQL，问题仍存在。

当前数据库配置（settings/base.py） #

DATABASES = {
    'default': {
        'ENGINE': 'django.db.backends.postgresql_psycopg2',
        'NAME': 'my_db_user',
        'USER': 'postgres',
        'PASSWORD': 'my_db_password',
        'HOST': 'localhost', 
        'PORT': '5432',
    }
}

执行的命令 #

python manage.py migrate

完整报错信息 #

(venv) C:\Users\anica\PycharmProjects\digitalocean-deploy-tests\wagtail-pipit-scratch12\src>python manage.py migrate
C:\Users\anica\PycharmProjects\digitalocean-deploy-tests\wagtail-pipit-scratch12\venv\lib\site-packages\wagtail_meta_preview\wagtail_hooks.py:4: RemovedInWagtail50Warning: Importing from wagtail.core.hooks is deprecated. Use wagtail.hooks instead. See https://docs.wagtail.org/en/stable/releases/3.0.html#changes-to-module-paths
  from wagtail.core import hooks
Traceback (most recent call last):
  File "C:\Users\anica\PycharmProjects\digitalocean-deploy-tests\wagtail-pipit-scratch12\venv\lib\site-packages\django\db\backends\base\base.py", line 282, in ensure_connection        
self.connect()
  File "C:\Users\anica\PycharmProjects\digitalocean-deploy-tests\wagtail-pipit-scratch12\venv\lib\site-packages\django\utils\asyncio.py", line 26, in inner
return func(*args, **kwargs)
  File "C:\Users\anica\PycharmProjects\digitalocean-deploy-tests\wagtail-pipit-scratch12\venv\lib\site-packages\django\db\backends\base\base.py", line 263, in connect
self.connection = self.get_new_connection(conn_params)
  File "C:\Users\anica\PycharmProjects\digitalocean-deploy-tests\wagtail-pipit-scratch12\venv\lib\site-packages\django\utils\asyncio.py", line 26, in inner
return func(*args, **kwargs)
  File "C:\Users\anica\PycharmProjects\digitalocean-deploy-tests\wagtail-pipit-scratch12\venv\lib\site-packages\django\db\backends\postgresql\base.py", line 215, in get_new_connection 
connection = Database.connect(**conn_params)
  File "C:\Users\anica\PycharmProjects\digitalocean-deploy-tests\wagtail-pipit-scratch12\venv\lib\site-packages\psycopg2\__init__.py", line 122, in connect
conn = _connect(dsn, connection_factory=connection_factory, **kwasync)
psycopg2.OperationalError

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

Traceback (most recent call last):
  File "C:\Users\anica\PycharmProjects\digitalocean-deploy-tests\wagtail-pipit-scratch12\src\manage.py", line 43, in <module>
main()
  File "C:\Users\anica\PycharmProjects\digitalocean-deploy-tests\wagtail-pipit-scratch12\src\manage.py", line 39, in main
execute_from_command_line(sys.argv)
  File "C:\Users\anica\PycharmProjects\digitalocean-deploy-tests\wagtail-pipit-scratch12\venv\lib\site-packages\django\core\management\__init__.py", line 446, in execute_from_command_line
utility.execute()
  File "C:\Users\anica\PycharmProjects\digitalocean-deploy-tests\wagtail-pipit-scratch12\venv\lib\site-packages\django\core\management\__init__.py", line 440, in execute
self.fetch_command(subcommand).run_from_argv(self.argv)
  File "C:\Users\anica\PycharmProjects\digitalocean-deploy-tests\wagtail-pipit-scratch12\venv\lib\site-packages\django\core\management\base.py", line 402, in run_from_argv
self.execute(*args, **cmd_options)
  File "C:\Users\anica\PycharmProjects\digitalocean-deploy-tests\wagtail-pipit-scratch12\venv\lib\site-packages\django\core\management\base.py", line 448, in execute
output = self.handle(*args, **options)
  File "C:\Users\anica\PycharmProjects\digitalocean-deploy-tests\wagtail-pipit-scratch12\venv\lib\site-packages\django\core\management\base.py", line 96, in wrapped
res = handle_func(*args, **kwargs)
  File "C:\Users\anica\PycharmProjects\digitalocean-deploy-tests\wagtail-pipit-scratch12\venv\lib\site-packages\django\core\management\commands\migrate.py", line 114, in handle        
executor = MigrationExecutor(connection, self.migration_progress_callback)
  File "C:\Users\anica\PycharmProjects\digitalocean-deploy-tests\wagtail-pipit-scratch12\venv\lib\site-packages\django\db\migrations\executor.py", line 18, in __init__
self.loader = MigrationLoader(self.connection)
  File "C:\Users\anica\PycharmProjects\digitalocean-deploy-tests\wagtail-pipit-scratch12\venv\lib\site-packages\django\db\migrations\loader.py", line 58, in __init__
self.build_graph()
  File "C:\Users\anica\PycharmProjects\digitalocean-deploy-tests\wagtail-pipit-scratch12\venv\lib\site-packages\django\db\migrations\loader.py", line 235, in build_graph
self.applied_migrations = recorder.applied_migrations()
  File "C:\Users\anica\PycharmProjects\digitalocean-deploy-tests\wagtail-pipit-scratch12\venv\lib\site-packages\django\db\migrations\recorder.py", line 81, in applied_migrations       
if self.has_table():
  File "C:\Users\anica\PycharmProjects\digitalocean-deploy-tests\wagtail-pipit-scratch12\venv\lib\site-packages\django\db\migrations\recorder.py", line 57, in has_table
with self.connection.cursor() as cursor:
  File "C:\Users\anica\PycharmProjects\digitalocean-deploy-tests\wagtail-pipit-scratch12\venv\lib\site-packages\django\utils\asyncio.py", line 26, in inner
return func(*args, **kwargs)
  File "C:\Users\anica\PycharmProjects\digitalocean-deploy-tests\wagtail-pipit-scratch12\venv\lib\site-packages\django\db\backends\base\base.py", line 323, in cursor
return self._cursor()
  File "C:\Users\anica\PycharmProjects\digitalocean-deploy-tests\wagtail-pipit-scratch12\venv\lib\site-packages\django\db\backends\base\base.py", line 299, in _cursor
self.ensure_connection()
  File "C:\Users\anica\PycharmProjects\digitalocean-deploy-tests\wagtail-pipit-scratch12\venv\lib\site-packages\django\utils\asyncio.py", line 26, in inner
return func(*args, **kwargs)
  File "C:\Users\anica\PycharmProjects\digitalocean-deploy-tests\wagtail-pipit-scratch12\venv\lib\site-packages\django\db\backends\base\base.py", line 282, in ensure_connection        
self.connect()
  File "C:\Users\anica\PycharmProjects\digitalocean-deploy-tests\wagtail-pipit-scratch12\venv\lib\site-packages\django\db\utils.py", line 91, in __exit__
raise dj_exc_value.with_traceback(traceback) from exc_value
  File "C:\Users\anica\PycharmProjects\digitalocean-deploy-tests\wagtail-pipit-scratch12\venv\lib\site-packages\django\db\backends\base\base.py", line 282, in ensure_connection        
self.connect()
  File "C:\Users\anica\PycharmProjects\digitalocean-deploy-tests\wagtail-pipit-scratch12\venv\lib\site-packages\django\utils\asyncio.py", line 26, in inner
return func(*args, **kwargs)
  File "C:\Users\anica\PycharmProjects\digitalocean-deploy-tests\wagtail-pipit-scratch12\venv\lib\site-packages\django\db\backends\base\base.py", line 263, in connect
self.connection = self.get_new_connection(conn_params)
  File "C:\Users\anica\PycharmProjects\digitalocean-deploy-tests\wagtail-pipit-scratch12\venv\lib\site-packages\django\utils\asyncio.py", line 26, in inner
return func(*args, **kwargs)
  File "C:\Users\anica\PycharmProjects\digitalocean-deploy-tests\wagtail-pipit-scratch12\venv\lib\site-packages\django\db\backends\postgresql\base.py", line 215, in get_new_connection 
connection = Database.connect(**conn_params)
  File "C:\Users\anica\PycharmProjects\digitalocean-deploy-tests\wagtail-pipit-scratch12\venv\lib\site-packages\psycopg2\__init__.py", line 122, in connect
conn = _connect(dsn, connection_factory=connection_factory, **kwasync)
django.db.utils.OperationalError

(venv) C:\Users\anica\PycharmProjects\digitalocean-deploy-tests\wagtail-pipit-scratch12\src>python manage.pya migrate
C:\Users\anica\AppData\Local\Programs\Python\Python39\python.exe: can't open file 'C:\Users\anica\PycharmProjects\digitalocean-deploy-tests\wagtail-pipit-scratch12\src\manage.pya': [Errno 2] No such file or directory

排查与修复步骤 #

1. 修正数据库配置字段 #

• 配置中NAME字段应为数据库名称，而非用户名。当前配置写的是my_db_user，先确认本地PostgreSQL是否存在该数据库：
  • 使用psql登录后执行\l查看所有数据库。
  • 如果不存在，执行createdb my_db_user创建，或者修改NAME为已存在的数据库名称。
• 核对USER、PASSWORD、PORT与本地PostgreSQL实际配置一致，重点确认密码正确性。

2. 检查PostgreSQL服务状态 #

• 按下Win + R输入services.msc打开服务管理器，找到PostgreSQL服务（通常命名为postgresql-x64-xx，xx为版本号）：
  • 若未运行，右键启动；若已运行，尝试重启服务。

3. 验证端口占用情况 #

• 打开命令提示符执行：

  netstat -ano | findstr :5432
  

• 若结果中有非PostgreSQL进程占用端口，结束该进程或修改PostgreSQL监听端口（修改postgresql.conf中的port参数后重启服务）。

4. 直接测试PostgreSQL连接 #

• 使用psql命令行工具跳过Django直接连接，验证数据库本身是否可访问：

  psql -U postgres -d my_db_user -h localhost -p 5432
  

• 输入密码后若能进入数据库，说明问题在Django侧；若连接失败，直接排查PostgreSQL的连接配置。

5. 调整PostgreSQL访问控制 #

• 找到PostgreSQL安装目录下的data/pg_hba.conf文件，确认存在以下允许本地连接的配置：

  host    all             all             127.0.0.1/32            md5
  host    all             all             ::1/128                 md5
  

• 修改后重启PostgreSQL服务。

6. 重新安装兼容的psycopg2 #

• Windows环境下优先安装psycopg2-binary以避免编译问题，同时确保版本与Django、PostgreSQL兼容：

  pip uninstall psycopg2 psycopg2-binary
  pip install psycopg2-binary
  

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