Google Cloud Composer连接Cloud SQL PostgreSQL失败，请求技术排查

搭建Google Cloud Composer环境时，使用Apache Airflow的CloudSQLExecuteQueryOperator向Cloud SQL PostgreSQL数据库提交查询TRUNCATE TABLE mytable，执行失败，报错：

psycopg2.OperationalError: server closed the connection unexpectedly. This probably means the server terminated abnormally before or while processing the request.

[2023-08-23, 22:01:46 UTC] {taskinstance.py:1091} INFO - 所有依赖项已满足，dep_context=non-requeueable deps ti=<TaskInstance: from_gcs_to_cloudsql.delete_older_data_task manual__2023-08-23T21:53:59.693259+00:00 [queued]>
[2023-08-23, 22:01:46 UTC] {taskinstance.py:1091} INFO - 所有依赖项已满足，dep_context=requeueable deps ti=<TaskInstance: from_gcs_to_cloudsql.delete_older_data_task manual__2023-08-23T21:53:59.693259+00:00 [queued]>
[2023-08-23, 22:01:46 UTC] {taskinstance.py:1289} INFO - --------------------------------------------------------------------------------
[2023-08-23, 22:01:46 UTC] {taskinstance.py:1290} INFO - 开始第2次尝试（共2次）
[2023-08-23, 22:01:47 UTC] {taskinstance.py:1310} INFO - 正在执行<Task(CloudSQLExecuteQueryOperator): delete_older_data_task>，执行时间：2023-08-23 21:53:59.693259+00:00
[2023-08-23, 22:01:47 UTC] {standard_task_runner.py:55} INFO - 启动进程9949以运行任务
[2023-08-23, 22:01:47 UTC] {standard_task_runner.py:82} INFO - 运行命令：['airflow', 'tasks', 'run', 'from_gcs_to_cloudsql', 'delete_older_data_task', 'manual__2023-08-23T21:53:59.693259+00:00', '--job-id', '69591', '--raw', '--subdir', 'DAGS_FOLDER/from_gcs_to_cloudsql_v6.py', '--cfg-path', '/tmp/tmppozedbav']
[2023-08-23, 22:01:47 UTC] {standard_task_runner.py:83} INFO - 任务69591：子任务delete_older_data_task
[2023-08-23, 22:01:47 UTC] {task_command.py:393} INFO - 在主机airflow-worker-cwhv4上运行<TaskInstance: from_gcs_to_cloudsql.delete_older_data_task manual__2023-08-23T21:53:59.693259+00:00 [running]>
[2023-08-23, 22:01:47 UTC] {taskinstance.py:1518} INFO - 导出以下环境变量：AIRFLOW_CTX_DAG_EMAIL=my_email@test.com AIRFLOW_CTX_DAG_OWNER=airflow AIRFLOW_CTX_DAG_ID=from_gcs_to_cloudsql AIRFLOW_CTX_TASK_ID=delete_older_data_task AIRFLOW_CTX_EXECUTION_DATE=2023-08-23T21:53:59.693259+00:00 AIRFLOW_CTX_TRY_NUMBER=2 AIRFLOW_CTX_DAG_RUN_ID=manual__2023-08-23T21:53:59.693259+00:00
[2023-08-23, 22:01:47 UTC] {base.py:73} INFO - 使用连接ID 'google_cloud_default'执行任务。
[2023-08-23, 22:01:47 UTC] {base.py:73} INFO - 使用连接ID 'proxy_postgres_tcp'执行任务。
[2023-08-23, 22:01:47 UTC] {cloud_sql.py:948} INFO - 数据库连接URI：postgresql://XXXXXXXXXXXX:XXXXXXXXXXXX@127.0.0.1:44687/postgres
[2023-08-23, 22:01:47 UTC] {cloud_sql.py:973} INFO - 创建连接a7ca1b7c-4200-11ee-9707-ae5f45f562f7
[2023-08-23, 22:01:47 UTC] {warnings.py:109} WARNING - /opt/python3.8/lib/python3.8/site-packages/airflow/providers/google/cloud/hooks/cloud_sql.py:1003: AirflowProviderDeprecationWarning: 参数"schema"已重命名为"database"（因为它存储的是数据库名称），请使用"database"设置数据库名称。 db_hook: PostgresHook | MySqlHook = PostgresHook(connection=connection, schema=self.database)

[2023-08-23, 22:01:47 UTC] {cloud_sql.py:523} INFO - 从https://dl.google.com/cloudsql/cloud_sql_proxy.linux.amd64下载cloud_sql_proxy到/tmp/dqnjbnio_cloud_sql_proxy.tmp
[2023-08-23, 22:01:49 UTC] {cloud_sql.py:540} INFO - 将sql_proxy二进制文件从/tmp/dqnjbnio_cloud_sql_proxy.tmp移动到/tmp/dqnjbnio_cloud_sql_proxy
[2023-08-23, 22:01:49 UTC] {cloud_sql.py:610} INFO - 创建目录/tmp/dqnjbnio
[2023-08-23, 22:01:49 UTC] {base.py:73} INFO - 使用连接ID 'google_cloud_default'执行任务。
[2023-08-23, 22:01:49 UTC] {cloud_sql.py:576} INFO - GCP连接google_cloud_default未通过key_path或keyfile_dict提供凭据，将回退到默认激活账户
[2023-08-23, 22:01:49 UTC] {cloud_sql.py:613} INFO - 运行命令：/tmp/dqnjbnio_cloud_sql_proxy -dir /tmp/dqnjbnio -instances gcp_my_project_id:northamerica-northeast1:test=tcp:44687
[2023-08-23, 22:01:49 UTC] {cloud_sql.py:616} INFO - cloud_sql_proxy的进程ID：9951
[2023-08-23, 22:01:49 UTC] {cloud_sql.py:630} INFO - 2023/08/23 22:01:49 当前文件描述符限制为1048576，期望限制为8500，无需操作。

[2023-08-23, 22:01:55 UTC] {cloud_sql.py:630} INFO - 2023/08/23 22:01:55 正在监听127.0.0.1:44687，对应实例：my_gcp_project_id:northamerica-northeast1:test

[2023-08-23, 22:01:55 UTC] {cloud_sql.py:630} INFO - 2023/08/23 22:01:55 已准备好接受新连接

[2023-08-23, 22:01:55 UTC] {cloud_sql.py:1217} INFO - 正在执行："TRUNCATE TABLE mytable"
[2023-08-23, 22:04:05 UTC] {cloud_sql.py:646} INFO - 停止cloud_sql_proxy，进程ID：9951
[2023-08-23, 22:04:05 UTC] {cloud_sql.py:650} INFO - 删除套接字目录：/tmp/dqnjbnio
[2023-08-23, 22:04:05 UTC] {cloud_sql.py:653} INFO - 删除已下载的代理：/tmp/dqnjbnio_cloud_sql_proxy
[2023-08-23, 22:04:05 UTC] {taskinstance.py:1778} ERROR - 任务执行失败，异常信息如下：Traceback (most recent call last): File "/opt/python3.8/lib/python3.8/site-packages/airflow/providers/google/cloud/operators/cloud_sql.py", line 1236, in execute self._execute_query(hook, database_hook) File "/opt/python3.8/lib/python3.8/site-packages/airflow/providers/google/cloud/operators/cloud_sql.py", line 1218, in _execute_query database_hook.run(self.sql, self.autocommit, parameters=self.parameters) File "/opt/python3.8/lib/python3.8/site-packages/airflow/providers/common/sql/hooks/sql.py", line 385, in run with closing(self.get_conn()) as conn: File "/opt/python3.8/lib/python3.8/site-packages/airflow/providers/postgres/hooks/postgres.py", line 154, in get_conn self.conn = psycopg2.connect(**conn_args) File "/opt/python3.8/lib/python3.8/site-packages/psycopg2/init.py", line 122, in connect conn = _connect(dsn, connection_factory=connection_factory, **kwasync) psycopg2.OperationalError: server closed the connection unexpectedly This probably means the server terminated abnormally before or while processing the request.

[2023-08-23, 22:04:05 UTC] {taskinstance.py:1328} INFO - 标记任务为失败状态。dag_id=from_gcs_to_cloudsql, task_id=delete_older_data_task, execution_date=20230823T215359, start_date=20230823T220146, end_date=20230823T220405
[2023-08-23, 22:04:05 UTC] {warnings.py:109} WARNING - /opt/python3.8/lib/python3.8/site-packages/airflow/providers/sendgrid/utils/emailer.py:123: AirflowProviderDeprecationWarning: 从环境变量获取Sendgrid凭据将在未来版本中弃用，请使用连接设置凭据。
_post_sendgrid_mail(mail.get(), conn_id)

[2023-08-23, 22:04:05 UTC] {emailer.py:145} INFO - 主题为“Airflow alert: <TaskInstance: from_gcs_to_cloudsql.delete_older_data_task manual__2023-08-23T21:53:59.693259+00:00 [failed]>”的邮件已成功发送至收件人：[{'to': [{'email': 'my_email@test.com'}]}]
[2023-08-23, 22:04:05 UTC] {standard_task_runner.py:100} ERROR - 任务69591执行失败，对应任务：delete_older_data_task（错误信息：server closed the connection unexpectedly This probably means the server terminated abnormally before or while processing the request. ; 进程ID：9949）
[2023-08-23, 22:04:05 UTC] {local_task_job.py:212} INFO - 任务退出，返回码：1
[2023-08-23, 22:04:05 UTC] {taskinstance.py:2599} INFO - 后续调度检查未计划下游任务

• 检查Cloud SQL实例状态：确认实例是否正常运行，有无重启、资源耗尽（CPU/内存/磁盘）情况，查看Cloud SQL的监控面板和日志
• 验证Cloud SQL代理权限：确保google_cloud_default连接使用的默认账户拥有Cloud SQL Client角色，能访问目标Cloud SQL实例
• 调整查询超时设置：TRUNCATE TABLE若涉及大数据量可能耗时较长，可在CloudSQLExecuteQueryOperator中增加timeout参数，或检查Cloud SQL实例的连接超时配置
• 修复DeprecationWarning：按照提示将连接配置中的schema参数替换为database，避免潜在的配置冲突
• 检查网络连接：确认Composer worker节点与Cloud SQL实例之间的网络通路正常，VPC peering或私有服务访问配置正确

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