Django无法连接到RDS的PostgreSQL数据库。

Question

当我的Django应用程序部署到EC2上时，无法连接到RDS PostgreSQL。但奇怪的是，在我的桌面运行时它可以正常工作。

EC2服务器和桌面都配置了python3、django1.9、apache2和mod_wsgi_py3。

这是我的settings.py数据库设置：

DATABASES = {
    'default': {
        'ENGINE': 'django.db.backends.postgresql',
        'NAME': 'postgres',
        'USER': 'some_name',
        'PASSWORD': 'some_password',
        'HOST': 'myhostname.cltlezrr85xn.ap-northeast-1.rds.amazonaws.com',
        'PORT': 5432,
    }
}

以及来自Apache2的error.log:

Traceback (most recent call last):
  File "/usr/local/lib/python3.4/dist-packages/django/db/backends/base/base.py", line 199, in ensure_connection
    self.connect()
  File "/usr/local/lib/python3.4/dist-packages/django/db/backends/base/base.py", line 171, in connect
    self.connection = self.get_new_connection(conn_params)
  File "/usr/local/lib/python3.4/dist-packages/django/db/backends/postgresql/base.py", line 175, in get_new_connection
    connection = Database.connect(**conn_params)
  File "/usr/local/lib/python3.4/dist-packages/psycopg2/__init__.py", line 164, in connect
    conn = _connect(dsn, connection_factory=connection_factory, async=async)
psycopg2.OperationalError: could not connect to server: Connection timed out
    Is the server running on host "myhostname.cltlezrr85xn.ap-northeast-1.rds.amazonaws.com" (172.--.--.---) and accepting
    TCP/IP connections on port 5432?


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

Traceback (most recent call last):
  File "manage.py", line 10, in <module>
    execute_from_command_line(sys.argv)
  File "/usr/local/lib/python3.4/dist-packages/django/core/management/__init__.py", line 353, in execute_from_command_line
    utility.execute()
  File "/usr/local/lib/python3.4/dist-packages/django/core/management/__init__.py", line 345, in execute
    self.fetch_command(subcommand).run_from_argv(self.argv)
  File "/usr/local/lib/python3.4/dist-packages/django/core/management/base.py", line 348, in run_from_argv
    self.execute(*args, **cmd_options)
  File "/usr/local/lib/python3.4/dist-packages/django/contrib/auth/management/commands/createsuperuser.py", line 52, in execute
    return super(Command, self).execute(*args, **options)
  File "/usr/local/lib/python3.4/dist-packages/django/core/management/base.py", line 399, in execute
    output = self.handle(*args, **options)
  File "/usr/local/lib/python3.4/dist-packages/django/contrib/auth/management/commands/createsuperuser.py", line 86, in handle
    default_username = get_default_username()
  File "/usr/local/lib/python3.4/dist-packages/django/contrib/auth/management/__init__.py", line 189, in get_default_username
    auth_app.User._default_manager.get(username=default_username)
  File "/usr/local/lib/python3.4/dist-packages/django/db/models/manager.py", line 122, in manager_method
    return getattr(self.get_queryset(), name)(*args, **kwargs)
  File "/usr/local/lib/python3.4/dist-packages/django/db/models/query.py", line 381, in get
    num = len(clone)
  File "/usr/local/lib/python3.4/dist-packages/django/db/models/query.py", line 240, in __len__
    self._fetch_all()
  File "/usr/local/lib/python3.4/dist-packages/django/db/models/query.py", line 1074, in _fetch_all
    self._result_cache = list(self.iterator())
  File "/usr/local/lib/python3.4/dist-packages/django/db/models/query.py", line 52, in __iter__
    results = compiler.execute_sql()
  File "/usr/local/lib/python3.4/dist-packages/django/db/models/sql/compiler.py", line 846, in execute_sql
    cursor = self.connection.cursor()
  File "/usr/local/lib/python3.4/dist-packages/django/db/backends/base/base.py", line 231, in cursor
    cursor = self.make_debug_cursor(self._cursor())
  File "/usr/local/lib/python3.4/dist-packages/django/db/backends/base/base.py", line 204, in _cursor
    self.ensure_connection()
  File "/usr/local/lib/python3.4/dist-packages/django/db/backends/base/base.py", line 199, in ensure_connection
    self.connect()
  File "/usr/local/lib/python3.4/dist-packages/django/db/utils.py", line 95, in __exit__
    six.reraise(dj_exc_type, dj_exc_value, traceback)
  File "/usr/local/lib/python3.4/dist-packages/django/utils/six.py", line 685, in reraise
    raise value.with_traceback(tb)
  File "/usr/local/lib/python3.4/dist-packages/django/db/backends/base/base.py", line 199, in ensure_connection
    self.connect()
  File "/usr/local/lib/python3.4/dist-packages/django/db/backends/base/base.py", line 171, in connect
    self.connection = self.get_new_connection(conn_params)
  File "/usr/local/lib/python3.4/dist-packages/django/db/backends/postgresql/base.py", line 175, in get_new_connection
    connection = Database.connect(**conn_params)
  File "/usr/local/lib/python3.4/dist-packages/psycopg2/__init__.py", line 164, in connect
    conn = _connect(dsn, connection_factory=connection_factory, async=async)
django.db.utils.OperationalError: could not connect to server: Connection timed out
    Is the server running on host "myhostname.cltlezrr85xn.ap-northeast-1.rds.amazonaws.com" (172.--.--.---) and accepting
    TCP/IP connections on port 5432?

感谢您的建议。

- makerj

---

你检查了RDS服务器和EC2实例的安全组吗？看起来像是RDS服务器不允许来自EC2实例的访问，或者EC2实例有限制出站规则，从而无法访问RDS实例。 - wkl

这不奇怪，你正在使用两个不同的数据库，连接也是不同的。你确定数据库在主机上运行吗？你检查过 EC2 防火墙规则了吗？你能够使用 psql 命令行连接到数据库 shell 吗？ - Shang Wang

感谢birryree和Shang Wang！我查看了你们的建议，并找出了问题所在。RDS防火墙规则不允许来自EC2实例的请求。再次感谢。对于我的愚蠢问题，非常抱歉。 - makerj

@makerj 你是如何处理的？ - shuboy2014

Answer 1

编辑:

我再次运行了一遍，发现有更简单的方法。

当你尝试在EC2或EB上使用现有的RDS数据库与Django配合使用时，你需要调整安全组，然后获取正确的参数并将其设置为环境变量（RDS_*）。

1）创建RDS，并匹配以下内容：

Environment variables - RDS console label

RDS_HOSTNAME - Endpoint (this is the hostname)
RDS_PORT - Port
RDS_DB_NAME –  DB Name
RDS_USERNAME –  Username
RDS_PASSWORD – Password you set for your DB

2）例如使用eb setenv进行设置。

3）进入您的EC2 / EB实例并获取该实例的安全组，例如awseb-z-afsafdsaf-stack-AWSEBSecurityGroup-asfdsadfasdf。

4）转到RDS实例的面板，向下滚动到安全组并注意其安全组，例如rds-launch-wizard-1（ab-sdjfalkajsdf39）。

5）选择RDS安全组，并添加一个入站规则，类型为：PostgreSQL（或您使用的任何数据库），并使用步骤2中获得的EC2或EB实例作为源（awseb-z-afsafdsaf-stack-AWSEBSecurityGroup-asfdsadfasdf）。协议和端口范围应自动填充。

6）保存。

就是这样。

---

原文：

对于任何发现此问题的人：

当您尝试在EC2或EB上使用现有的RDS数据库与Django配合使用时，您将不得不调整安全组，然后获取正确的参数并将它们设置为环境变量（RDS_*）

1）创建RDS，并匹配这些内容：

Environment variables - RDS console label

RDS_HOSTNAME - Endpoint (this is the hostname)
RDS_PORT - Port
RDS_DB_NAME –  DB Name
RDS_USERNAME –  Username
RDS_PASSWORD – Password you set for your DB

2) 例如使用eb setenv设置它们。

3) 进入您的EC2/EB实例，获取该实例的安全组，例如：awseb-z-afsafdsaf-stack-AWSEBSecurityGroup-asfdsadfasdf和负载均衡器：awseb-e-adsfadsf-stack-AWSEBLoadBalancerSecurityGroup-asdfadsf

4) 转到RDS实例面板，向下滚动到安全组并记录其所属的安全组。例如：rds-launch-wizard-1（ab-sdjfalkajsdf39）

5) 单击RDS实例的修改按钮，在中间的安全组设置中添加上一步中发现的负载均衡器安全组。它应该有建议选项。

6) 转到EC2仪表板，从左侧菜单中选择安全组。

7) 选择负载均衡器安全组，并添加一个出站规则。类型应为您的RDS类型（PostgreSQL），目的地应该是自定义的，即RDS实例的安全组。保存。

8) 对于入站规则，使用相同的RDS类型和目的地进行相同的操作

9) 选择RDS安全组，并添加一个入站规则，类似于第8步，但使用您在第2步中获取的EC2或EB实例。

10) 保存，你完成了。它们现在应该能够一起工作。

我无法百分之百确定所有这些步骤是否都必要，可能其中一两个不需要，但这对我完成了工作。

- Toruitas