[ PostgreSQL ] 테이블 데이터 CSV파일로 저장하기

PostgreSQL에서 테이블 데이터를 CSV 파일로 저장하는 방법은 여러 가지가 있습니다. 가장 일반적인 방법은 COPY 명령어를 사용하는 것입니다.

PostgreSQL 서버에서 COPY 명령어를 사용해 테이블 데이터를 CSV 파일로 저장하려면 몇 가지 전제 조건과 절차가 필요합니다. 아래에 단계별로 자세히 설명드리겠습니다.

---

 

 

 

1. COPY 명령어 (PostgreSQL 서버에서 직접 파일로 저장)

SQL문이 서버로 전달되어 서버에서 직접 server 폴더에 csv 파일을 저장합니다.

COPY table_name TO '/경로/파일이름.csv' WITH (FORMAT csv, HEADER);

• 이 명령어는 PostgreSQL 서버가 직접 파일을 생성하는 방식입니다.
• 즉, 파일이 저장되는 위치는 PostgreSQL 서버가 실행 중인 머신의 디렉토리입니다.

 

에러 예시 및 해결법

에러메시지	원인	해결방법
must be superuser to COPY to a file	일반 사용자로 실행	superuser로 로그인
Permission denied	경로에 쓰기 권한 없음	다른 경로 선택 (/tmp/ 등)
No such file or directory	경로가 존재하지 않음

 

1. 필요한 조건

 

(1) PostgreSQL 서버가 해당 경로에 접근 및 쓰기 권한이 있어야 함

• 예: /tmp/, /var/lib/postgresql/, /home/postgres/ 등 PostgreSQL 사용자가 접근 가능한 디렉토리여야 함
• PostgreSQL은 보안 상 이유로 일반적으로 루트 디렉토리(/root, /etc)에는 쓰기 불가능

 

(2) PostgreSQL 사용자가 OS 파일 시스템에 쓰기 권한을 가지고 있어야 함

• PostgreSQL 프로세스를 실행하는 OS 사용자(보통 postgres)가 해당 경로에 쓰기 가능해야 함

 

(3) superuser 권한 필요

• COPY TO '/경로/파일.csv'는 PostgreSQL superuser만 실행할 수 있습니다.
• 일반 유저는 실행 시 다음과 같은 오류가 납니다:
• ERROR: must be superuser to COPY to a file

해결 방법: postgres 계정 등 superuser 계정을 사용하거나, 대신 \COPY 사용

---

 

2. 실행 절차

 

1단계: 저장하고자 하는 경로 확인

예를 들어 /tmp/ 경로는 대부분 OS에서 쓰기 가능한 위치입니다. 테스트용으로 적합합니다.

ls -ld /tmp/

 

2단계: PostgreSQL에 superuser로 접속

psql -U postgres -d my_database

 

3단계: COPY 명령어 실행

COPY my_table TO '/tmp/my_table.csv' WITH (FORMAT csv, HEADER);

 

옵션 설명:

• FORMAT csv: CSV 형식
• HEADER: 첫 줄에 컬럼명 포함
• DELIMITER ',': 구분자 (기본값이 콤마라서 생략 가능)
• ENCODING 'UTF8': 인코딩 지정 가능 (필요 시)

예시 (인코딩 추가):

COPY my_table TO '/tmp/my_table.csv' WITH (FORMAT csv, HEADER, ENCODING 'UTF8');

 

경로를 지정하지 않으면 '/var/lib/postgresql/' 에 파일이 저장됩니다.
주의: 이 방법은 PostgreSQL 서버가 해당 경로에 쓰기 권한이 있어야 하며, 로컬 클라이언트가 아니라 서버 머신 기준 경로입니다.

---

 

 

 

2. \COPY 명령어 (psql 클라이언트에서 사용 — 로컬 파일로 저장)

만약 로컬 클라이언트에서 직접 저장하고 싶다면 psql에서 다음과 같이 입력합니다:
psql에 의해서 csv 파일에 저장됩니다.
psql은 local에서 실행되므로 파일은 서버가 아니라 psql을 실행한 client에 저장됩니다.

 

1. psql로 서버에 접속합니다.

psql -h host_address -U myuser -d mydatabase

 

2. '\COPY' 명령어를 사용해서 데이터를 csv파일로 저장

\COPY table_name TO '파일이름.csv' WITH (FORMAT csv, HEADER);

예시:

\COPY employees TO 'employees.csv' WITH (FORMAT csv, HEADER);

쓰기 권한이 있는 디렉토리를 경로로 지정할 수 있습니다.

\COPY employee TO '/home/myuser/pg/employee.csv' WITH (FORMAT csv, HEADER);

---

 

3. '-c' 옵션으로 \copy 명령어를 직접 실행해 CSV로 저장하기

psql의 \copy는 클라이언트에서 실행되기 때문에, 호스트 터미널에서 바로 다음처럼 사용할 수 있습니다:

psql -h localhost -U postgres -d mydb -c "\copy employees TO 'employees.csv' WITH (FORMAT csv, HEADER true)"

위 명령은 현재 작업 중인 디렉토리에 employees.csv 파일을 생성합니다.
비밀번호 입력을 요구할 수 있으므로 .pgpass 파일을 설정하거나 PGPASSWORD 환경변수를 사용해 자동화할 수 있습니다.

---

 

4. 환경변수로 비밀번호 전달 (주의 필요)

PGPASSWORD=비밀번호 psql -h localhost -U postgres -d mydb -c "SELECT * FROM employees;"

스크립트 자동화에는 편리하지만, 보안상 노출에 주의하셔야 합니다.

이 방법은 PostgreSQL 클라이언트가 실행되는 로컬 컴퓨터에 파일을 저장합니다. 대부분 이 방법이 더 간편합니다.

---