使用psycopg2+Django连接PostgreSQL时主机名解析失败求助

尝试使用psycopg2和Django应用连接PostgreSQL数据库时，运行应用的Pod报告主机名解析错误：

127.0.0.1 - - [13/Jul/2023:17:35:05 -0500] "GET /read HTTP/1.1" 301 0 "-" "PostmanRuntime/7.32.3"  
处理GET请求时出错：could not translate host name "resume-postgresql-primary-hl" to address: Name or service not known

Internal Server Error: /read/

相关代码片段 #

views.py #

from django.http import JsonResponse
from django.views.decorators.csrf import csrf_exempt
from django.db import connection
from django.conf import settings
import psycopg2
import logging

# Set up logging
logging.basicConfig(level=logging.INFO)
logger = logging.getLogger(__name__)

@csrf_exempt
def read(request):
    try:
        conn = psycopg2.connect(
            host=settings.DATABASES['default']['HOST'],
            dbname=settings.DATABASES['default']['NAME'],
            user=settings.DATABASES['default']['USER'],
            password=settings.DATABASES['default']['PASSWORD']
        )
        with conn.cursor() as cur:
            cur.execute('SELECT * FROM visitor_count;')
            data = cur.fetchall()

        conn.close()

        for item in data:
            response = item[1]
            return JsonResponse({'statusCode': 200, 'data': response})

    except Exception as e:
        logger.error("An error occurred while processing the GET request: %s", str(e))
        return JsonResponse({'statusCode': 500, 'message': 'Internal Server Error'}, status=500)

settings.py 数据库配置 #

# Database
DATABASES = {
    'default': {
        'ENGINE': 'django.db.backends.postgresql_psycopg2',
        'NAME': os.environ.get('DATABASE'),
        'USER': os.environ.get('DB_USERNAME'),
        'PASSWORD': os.environ.get('DB_PASSWORD'),
        'HOST': os.environ.get('DB_HOST'),
        'PORT': os.environ.get('DB_PORT', '5432'),
    }
}

Kubernetes Secret 配置 #

apiVersion: v1
kind: Secret
metadata:
  name: backend-secret
  namespace: resume
data:
  host: cmVzdW1lLXBvc3RncmVzcWwtcHJpbWFyeS1obAo= 
  database: REDACTED
  db_username: REDACTED
  db_password: REDACTED
  db_port: NTQzMgo=
  secret_key: REDACTED

Kubernetes Deployment 配置 #

apiVersion: apps/v1
kind: Deployment
metadata:
  name: resume-backend
  labels:
    app: resume-backend
spec:
  template:
    metadata:
      name: resume-backend
      labels:
        app: resume-backend
    spec:
      containers:
      - name: resume-backend
        image: rustybridge/resume-backend:1.0
        imagePullPolicy: Always
        env:
        - name: DB_HOST
          valueFrom:
            secretKeyRef:
              name: backend-secret
              key: host
        - name: DATABASE
          valueFrom:
            secretKeyRef:
              name: backend-secret
              key: database
        - name: DB_USERNAME
          valueFrom:
            secretKeyRef:
              name: backend-secret
              key: db_username
        - name: DB_PASSWORD
          valueFrom:
            secretKeyRef:
              name: backend-secret
              key: db_password
        - name: DB_PORT
          valueFrom:
            secretKeyRef:
              name: backend-secret
              key: db_port
        - name: SECRET_KEY
          valueFrom:
            secretKeyRef:
              name: backend-secret
              key: secret_key
        ports:
        - containerPort: 8000
          name: listen
  selector:
    matchLabels:
      app: resume-backend
  replicas: 2

已排查情况 #

• 容器内环境变量检查正常：

  root@resume-backend-677cd68846-9vrm6:/srv/app# env | grep DB_HOST
  DB_HOST=resume-postgresql-primary-hl
  

• Pod内执行nslookup能正常解析主机名：

  root@resume-backend-677cd68846-m22h8:/srv/app# nslookup resume-postgresql-primary-hl
  Server: 10.96.0.10
  Address: 10.96.0.10#53
  
  Name: resume-postgresql-primary-hl.resume.svc.cluster.local
  Address: 10.244.0.114

• 本地运行应用可正常连接数据库并返回数据

1. 修复Secret中host字段的换行符问题
   解码Secret中的host字段值cmVzdW1lLXBvc3RncmVzcWwtcHJpbWFyeS1obAo=，结果为resume-postgresql-primary-hl\n，末尾带有换行符。这个换行符会被传递给环境变量，导致psycopg2尝试解析的是带换行符的无效主机名。

   • 修复方式：用echo -n "resume-postgresql-primary-hl" | base64生成无换行的base64编码，替换原Secret中的host字段值，然后重新应用Secret配置。

2. 验证Django加载的HOST实际值
   在views.py中添加日志，打印settings.DATABASES['default']['HOST']的原始值，确认是否包含不可见字符：

   logger.info("DB Host from settings: %r", settings.DATABASES['default']['HOST'])
   

   查看Pod日志，检查输出的主机名是否带有换行、空格等多余字符。

3. 改用Django内置数据库连接
   当前代码手动调用psycopg2创建连接，可直接使用Django已配置好的connection对象执行查询，避免手动传递参数的潜在问题：

   @csrf_exempt
   def read(request):
       try:
           with connection.cursor() as cur:
               cur.execute('SELECT * FROM visitor_count;')
               data = cur.fetchall()
   
           for item in data:
               response = item[1]
               return JsonResponse({'statusCode': 200, 'data': response})
   
       except Exception as e:
           logger.error("An error occurred while processing the GET request: %s", str(e))
           return JsonResponse({'statusCode': 500, 'message': 'Internal Server Error'}, status=500)
   

4. 添加连接重试逻辑
   应用启动时可能存在DNS解析延迟，可在代码中添加重试机制：

   import time
   max_retries = 5
   retry_delay = 2
   conn = None
   for attempt in range(max_retries):
       try:
           conn = psycopg2.connect(
               host=settings.DATABASES['default']['HOST'],
               dbname=settings.DATABASES['default']['NAME'],
               user=settings.DATABASES['default']['USER'],
               password=settings.DATABASES['default']['PASSWORD']
           )
           break
       except psycopg2.OperationalError as e:
           if attempt < max_retries -1:
               logger.info("Connection failed, retrying in %s seconds...", retry_delay)
               time.sleep(retry_delay)
           else:
               raise
   

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