엑셀(Excel)과 PostgreSQL을 연동하면 강력한 데이터 분석 환경을 만들 수 있습니다. 대시보드 제작, 통계분석, 실시간 데이터 조회 등 다양한 업무에 활용할 수 있죠. 이 글에서는 처음 설치부터 실무 적용까지 모든 과정을 순서대로 안내합니다.
📌 1. ODBC 드라이버 설치 및 설정
엑셀은 PostgreSQL과 직접 통신하지 못하므로, ODBC(Open Database Connectivity) 드라이버가 필요합니다. PostgreSQL 공식 드라이버인 psqlODBC를 설치해야 합니다.
- 설치 링크: https://odbc.postgresql.org/
- Windows용 .msi 파일 다운로드 후 설치
- 엑셀이 64비트라면 64bit 드라이버 설치 필수
- 설치 후, Windows에서 ODBC 데이터 소스(64비트) 실행
설정 창에서 "시스템 DSN" 탭에 들어가 PostgreSQL 드라이버를 추가하고 다음 정보를 입력합니다:
- Data Source Name: psql_excel
- Server: localhost 또는 실제 IP
- Database: 연결하려는 DB명
- User/Password: 로그인 정보
“Test” 버튼을 눌러 연결 성공 여부를 확인하세요.
📌 2. PostgreSQL 서버 설정 (pg_hba.conf, postgresql.conf)
엑셀이 PostgreSQL에 접속하려면 서버 설정도 열어줘야 합니다.
- postgresql.conf에서 다음 라인을 수정합니다:
- listen_addresses = '*'
- pg_hba.conf에서 외부 IP를 허용합니다:
- 실무에서는 0.0.0.0/0 대신 특정 IP 범위를 제한적으로 설정하는 것이 안전합니다.
- host all all 0.0.0.0/0 md5
- 서버를 재시작합니다:
- sudo systemctl restart postgresql
📌 3. Excel에서 PostgreSQL 연결
엑셀에서 데이터 → 데이터 가져오기 → 기타 원본 → ODBC를 선택하면, 방금 설정한 DSN이 나타납니다. 해당 DSN을 선택한 뒤, 로그인하면 PostgreSQL의 테이블 목록이 표시됩니다.
여기서 원하는 테이블을 선택해 불러오면 엑셀 시트에 바로 데이터가 나타납니다. Power Query를 통해 필터링, 열 선택 등 전처리 작업도 가능합니다.
📌 4. Power Query 고급 기능
Excel의 Power Query는 단순한 데이터 불러오기를 넘어서, 다양한 데이터 가공을 지원합니다.
- 조건별 필터링 (예: 최근 한 달 데이터만 보기)
- 열 추가 및 계산
- 그룹화 및 요약
- 병합 및 조인 등 복잡한 처리도 GUI로 가능
Power Query의 장점은, 설정한 모든 변환 로직이 자동으로 저장되고 반복 실행 가능하다는 점입니다.
📌 5. SQL 직접 쿼리 활용
기본 UI 대신 SQL 문으로 원하는 데이터를 가져올 수도 있습니다.
ODBC로 연결할 때 **“고급 옵션”**을 열고 아래와 같은 SQL을 입력해보세요:
SELECT name, age, created_at
FROM users
WHERE age >= 30
ORDER BY created_at DESC;
이렇게 하면 쿼리 결과만 가져오기 때문에 속도도 빠르고, 필요한 데이터만 추출할 수 있어 효율적입니다.
📌 6. 자동 새로고침 설정
연결한 데이터를 실시간처럼 활용하려면 자동 새로고침 기능을 켜는 것이 좋습니다.
- 데이터 → 쿼리 및 연결 → 쿼리 우클릭 → "속성"
- "파일을 열 때 새로고침", "n분 간격 새로고침" 기능 사용 가능
보고서, 대시보드 등에서 실시간 데이터 모니터링에 매우 유용합니다.
📌 7. 연결 문제 해결법 (FAQ)
문제: 드라이버가 보이지 않음
해결: psqlODBC가 설치되지 않았거나, 32/64비트 불일치
문제: 연결 오류 (Connection Refused)
해결: pg_hba.conf에서 IP 허용 여부 확인, 포트 5432 열려 있는지 체크
문제: 테이블 목록이 안 보임
해결: PostgreSQL 스키마 설정 확인 또는 직접 SQL 쿼리 사용
📌 8. 보안 주의사항
- ODBC 연결은 데이터베이스 계정을 직접 사용하므로, 읽기 전용 계정을 만들어 사용하세요.
- pg_hba.conf에서 IP 접근을 제한하세요.
- Excel 파일 자체에 암호를 설정하거나, 민감 정보는 저장하지 않는 것이 좋습니다.
- DSN 설정 시 "비밀번호 저장" 옵션은 되도록 비활성화하세요.
📌 9. Excel로 데이터 분석하는 방법 (피벗, 차트 등)
엑셀의 분석 기능과 PostgreSQL의 데이터가 결합되면 매우 강력한 분석 도구가 됩니다.
- 피벗 테이블: 연령대별 사용자 수, 월별 매출 요약
- 차트: 트렌드 분석, 카테고리 비교
- 조건부 서식: 기준을 넘는 값 강조
- 슬라이서, 타임라인: 인터랙티브 필터 제공
분석용 리포트를 자동화하는 데 최적화된 환경입니다.
📌 10. Python과 Excel 연계 확장 (보너스)
Excel에서 한계가 있을 경우 Python을 활용해 데이터를 자동으로 가공하고 저장할 수 있습니다. 예를 들어:
import pandas as pd
import psycopg2
conn = psycopg2.connect(host='localhost', dbname='mydb', user='me', password='pass')
df = pd.read_sql("SELECT * FROM sales", conn)
df.to_excel("report.xlsx", index=False)
Python을 이용하면 PostgreSQL → Excel 리포트 자동화도 쉽게 구축할 수 있습니다.