인프런 커뮤니티 질문&답변

제가 설정을 봐야 확실히 알 것 같은데, 중요한 점은 https://github.com/dimz119/learn-apache-airflow/blob/main/docker-compose.yaml#L58-L61 요 부분을 보셔서 모두 postgres로 포인팅이 되어 있다면, docker ps 를 치셔서 혹시라도 다른 postgres가 돌고 있는지 확인해 보세요. 혹시 다른 postgres가 돌고 있다면 다 지우셔야 됩니다.

마지막으로 docker compose down 하셔서 다커가 안 돌고 있는 상태에서도 한번 DB 연결 테스트를 해보세요. 혹시라도 다커가 아닌 로컬에서 설치를 하셨다면 그 데이타 베이스가 돌고 있을 수 있습니다.

일단 선생님께서 말씀해주신 알려주신 방법을 확인해본 결과 "요 부분" 설정을 변경했습니다. 또한 두 번째와 세 번째 말씀해준 신 것 확인 결과 이상 없었습니다. 그래도 오류가 발생했습니다.

"connection to server at "localhost" (::1), port 5432"(아래 로그 첨부했습니다)

위 오류처럼 로컬 pc에서 listening 하고 있지 않은지 확인해보기 위해 확인해 본 결과

잘 실행되고 있습니다.

현재 docker 위에 ubuntu 환경으로 docker-compose를 구성했습니다.

• 추가 확인이 필요한 설정이 있으면 말씀해주세요 ㅠ

  

[오류 로그]

b0ec75311c2c

*** Found local files:

*** * /opt/airflow/logs/dag_id=postgres_loader/run_id=manual__2024-06-27T06:34:54.878565+00:00/task_id=execute_sql_query/attempt=1.log

[2024-06-27T06:34:57.200+0000] {local_task_job_runner.py:120} INFO - ::group::Pre task execution logs

[2024-06-27T06:34:57.232+0000] {taskinstance.py:2076} INFO - Dependencies all met for dep_context=non-requeueable deps ti=<TaskInstance: postgres_loader.execute_sql_query manual__2024-06-27T06:34:54.878565+00:00 [queued]>

[2024-06-27T06:34:57.241+0000] {taskinstance.py:2076} INFO - Dependencies all met for dep_context=requeueable deps ti=<TaskInstance: postgres_loader.execute_sql_query manual__2024-06-27T06:34:54.878565+00:00 [queued]>

[2024-06-27T06:34:57.242+0000] {taskinstance.py:2306} INFO - Starting attempt 1 of 1

[2024-06-27T06:34:57.260+0000] {taskinstance.py:2330} INFO - Executing <Task(PostgresOperator): execute_sql_query> on 2024-06-27 06:34:54.878565+00:00

[2024-06-27T06:34:57.267+0000] {warnings.py:112} WARNING - /home/***/.local/lib/python3.12/site-packages/***/task/task_runner/standard_task_runner.py:61: DeprecationWarning: This process (pid=131) is multi-threaded, use of fork() may lead to deadlocks in the child.

pid = os.fork()

[2024-06-27T06:34:57.268+0000] {standard_task_runner.py:63} INFO - Started process 133 to run task

[2024-06-27T06:34:57.269+0000] {standard_task_runner.py:90} INFO - Running: ['***', 'tasks', 'run', 'postgres_loader', 'execute_sql_query', 'manual__2024-06-27T06:34:54.878565+00:00', '--job-id', '143', '--raw', '--subdir', 'DAGS_FOLDER/postgres_loader.py', '--cfg-path', '/tmp/tmpehr9vin2']

[2024-06-27T06:34:57.272+0000] {standard_task_runner.py:91} INFO - Job 143: Subtask execute_sql_query

[2024-06-27T06:34:57.330+0000] {task_command.py:426} INFO - Running <TaskInstance: postgres_loader.execute_sql_query manual__2024-06-27T06:34:54.878565+00:00 [running]> on host b0ec75311c2c

[2024-06-27T06:34:57.450+0000] {taskinstance.py:2648} INFO - Exporting env vars: AIRFLOW_CTX_DAG_OWNER='***' AIRFLOW_CTX_DAG_ID='postgres_loader' AIRFLOW_CTX_TASK_ID='execute_sql_query' AIRFLOW_CTX_EXECUTION_DATE='2024-06-27T06:34:54.878565+00:00' AIRFLOW_CTX_TRY_NUMBER='1' AIRFLOW_CTX_DAG_RUN_ID='manual__2024-06-27T06:34:54.878565+00:00'

[2024-06-27T06:34:57.453+0000] {taskinstance.py:430} INFO - ::endgroup::

[2024-06-27T06:34:57.454+0000] {sql.py:276} INFO - Executing:

INSERT INTO sample_table (key, value)

VALUES ('hello', 'world')

[2024-06-27T06:34:57.465+0000] {base.py:84} INFO - Using connection ID 'my_postgres_connection' for task execution.

[2024-06-27T06:34:57.478+0000] {base.py:84} INFO - Using connection ID 'my_postgres_connection' for task execution.

[2024-06-27T06:34:57.479+0000] {taskinstance.py:441} INFO - ::group::Post task execution logs

[2024-06-27T06:34:57.480+0000] {taskinstance.py:2905} ERROR - Task failed with exception

Traceback (most recent call last):

File "/home/airflow/.local/lib/python3.12/site-packages/airflow/models/taskinstance.py", line 465, in _execute_task

result = _execute_callable(context=context, **execute_callable_kwargs)

^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^

File "/home/airflow/.local/lib/python3.12/site-packages/airflow/models/taskinstance.py", line 432, in _execute_callable

return execute_callable(context=context, **execute_callable_kwargs)

^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^

File "/home/airflow/.local/lib/python3.12/site-packages/airflow/models/baseoperator.py", line 401, in wrapper

return func(self, *args, **kwargs)

^^^^^^^^^^^^^^^^^^^^^^^^^^^

File "/home/airflow/.local/lib/python3.12/site-packages/airflow/providers/common/sql/operators/sql.py", line 282, in execute

output = hook.run(

^^^^^^^^^

File "/home/airflow/.local/lib/python3.12/site-packages/airflow/providers/common/sql/hooks/sql.py", line 431, in run

with self._create_autocommit_connection(autocommit) as conn:

File "/usr/local/lib/python3.12/contextlib.py", line 137, in __enter__

return next(self.gen)

^^^^^^^^^^^^^^

File "/home/airflow/.local/lib/python3.12/site-packages/airflow/providers/common/sql/hooks/sql.py", line 551, in _create_autocommit_connection

with closing(self.get_conn()) as conn:

^^^^^^^^^^^^^^^

File "/home/airflow/.local/lib/python3.12/site-packages/airflow/providers/postgres/hooks/postgres.py", line 175, in get_conn

self.conn = psycopg2.connect(**conn_args)

^^^^^^^^^^^^^^^^^^^^^^^^^^^^^

File "/home/airflow/.local/lib/python3.12/site-packages/psycopg2/__init__.py", line 122, in connect

conn = _connect(dsn, connection_factory=connection_factory, **kwasync)

^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^

psycopg2.OperationalError: connection to server at "localhost" (::1), port 5432 failed: Connection refused

Is the server running on that host and accepting TCP/IP connections?

connection to server at "localhost" (127.0.0.1), port 5432 failed: Connection refused

Is the server running on that host and accepting TCP/IP connections?

[2024-06-27T06:34:57.493+0000] {taskinstance.py:1206} INFO - Marking task as FAILED. dag_id=postgres_loader, task_id=execute_sql_query, run_id=manual__2024-06-27T06:34:54.878565+00:00, execution_date=20240627T063454, start_date=20240627T063457, end_date=20240627T063457

[2024-06-27T06:34:57.509+0000] {standard_task_runner.py:110} ERROR - Failed to execute job 143 for task execute_sql_query (connection to server at "localhost" (::1), port 5432 failed: Connection refused

Is the server running on that host and accepting TCP/IP connections?

connection to server at "localhost" (127.0.0.1), port 5432 failed: Connection refused

Is the server running on that host and accepting TCP/IP connections?

; 133)

[2024-06-27T06:34:57.523+0000] {local_task_job_runner.py:240} INFO - Task exited with return code 1

[2024-06-27T06:34:57.536+0000] {warnings.py:112} WARNING - /home/***/.local/lib/python3.12/site-packages/***/models/baseoperator.py:1297: AirflowProviderDeprecationWarning: Call to deprecated class PostgresOperator. (Please use `***.providers.common.sql.operators.sql.SQLExecuteQueryOperator`.Also, you can provide `hook_params={'schema': <database>}`.)

result = cls.__new__(cls)

[2024-06-27T06:34:57.556+0000] {taskinstance.py:3503} INFO - 0 downstream tasks scheduled from follow-on schedule check

[2024-06-27T06:34:57.561+0000] {local_task_job_runner.py:222} INFO - ::endgroup::

오류가 난 로그입니다.

앗.......... 윈도우군요...... ㅠㅠ

아마 윈도우에서 postgres 접근을 이 IP주소로 못하는 것 같네요. 뭔가 방화벽 문제인 것 같은데...

connection to server at "localhost" (127.0.0.1), port 5432 failed: Connection refused

위에 에러가 중요 문제입니다.

근데... 이 질문 조회 수 왜 이렇게 많죠? ㅎㅎㅎ

그러면 docker-compose 실행하고 psql로 접속해서 conf 파일을 만들어야 하는 건가요?

윈도우의 postgres 가 아닌 docker위에 postgres를 사용하는 걸로 알고 있는데 이게 맞나요? 윈도우에는 DBeaver만 깔고 postgres는 깔지 않았습니다

흐음 한번 localhost 대신에 postgres라고 넣어보실래요? 다커컴포즈 내에서는 postgres로 서비스 이름이 설정 되어 있으실 겁니다. UI에서 postgres로 넣어보세요 ㅎㅎㅎ

드디어 다른 로그가 나오네요!!!! 인증 오류입니다. postgres 의 패스워드를 postgres로 해봤는데 오류네요.

제가 지하철이라 기억은 잘 나지 않지만 다커컴포즈 보시면 유저명과 비밀번호 있을 겁니다. 거의 다 오셨네요

docker-compose.yaml 파일 확인해 보니 있습니다..! 선생님 도움 덕분에 드디어 해결했습니다! 감사합니다

수고하셨어요 ㅎㅎ