서버에서 데이터 로드를 위한 COPY 사용하기

이미 데이터가 서버에 있다면, COPY 명령어를 써서 PostgreSQL 테이블에 데이터를 넣을 수 있어. 이건 특히 데이터가 많거나 PostgreSQL 서버가 별도의 머신에 있을 때 진짜 유용해.

서버 측에서 실행하는 COPY 명령어는 실제 프로젝트에서 특히 편리해. 첫째, 파일이 이미 서버에 있으니까 네트워크로 전송할 필요 없이 훨씬 빠르게 동작해. 그리고 이 과정이 더 안전해져. 데이터를 로컬 컴퓨터에서 복사할 필요가 없으니까 유출 위험도 줄어들지. 게다가 자동화된 서버 스크립트나 백그라운드 서비스에 쉽게 넣을 수 있어 — 예를 들어, 분석용 테이블을 정기적으로 업데이트할 때 말이야.

COPY 명령어 문법

COPY 명령어는 간단하지만, 몇 가지 꼭 알아둬야 할 포인트가 있어:

COPY table_name
FROM '/path/to/file.csv'
WITH (FORMAT CSV, HEADER TRUE);

• table_name — 데이터를 넣고 싶은 테이블 이름이야.
• /path/to/file.csv — 서버에 있는 파일의 전체 경로.
• WITH 옵션으로 파일 포맷, 헤더 유무, 구분자 등등을 지정할 수 있어.

COPY 명령어 사용 예시

작은 예시로 한번 해보자. 예를 들어, 학생 데이터가 들어있는 CSV 파일을 PostgreSQL 데이터베이스에 넣어야 한다고 해보자. 파일은 서버의 /var/lib/postgresql/data/students.csv 경로에 있어.

1단계. 데이터용 테이블 만들기

먼저, 데이터를 넣을 테이블이 데이터베이스에 있는지 확인하자:

CREATE TABLE students (
    id SERIAL PRIMARY KEY,
    name TEXT NOT NULL,
    email TEXT UNIQUE NOT NULL,
    registration_date DATE
);

이 테이블은 학생들의 이름, 이메일, 가입 날짜를 저장할 거야.

2단계. CSV 파일

/var/lib/postgresql/data/students.csv 파일은 이렇게 생겼을 수 있어:

id,name,email,registration_date
1,Alex Lin,alex.lin@example.com,2023-09-01
2,Maria Chi,maria.chi@example.com,2023-09-02
3,Peter Ming,peter.ming@example.com,2023-09-02

3단계. COPY 명령어

이제 COPY를 써서 파일에서 테이블로 데이터를 넣을 수 있어:

COPY students
FROM '/var/lib/postgresql/data/students.csv'
WITH (FORMAT CSV, HEADER TRUE);

여기서:

• FORMAT CSV는 파일이 CSV 포맷이라는 걸 PostgreSQL에 알려줘.
• HEADER TRUE는 파일의 첫 줄이 컬럼 이름(헤더)라는 뜻이야.

명령어를 실행하면 파일의 데이터가 students 테이블에 들어가게 돼.

결과 확인하기

데이터를 넣은 후에, 잘 들어갔는지 확인해보자:

SELECT * FROM students;

테이블에 들어간 파일의 행들을 볼 수 있을 거야:

권한 설정하기

PostgreSQL이 COPY 명령어를 쓰려면 파일에 접근할 수 있어야 해. 권한이 잘못되어 있으면 에러가 나올 수 있어. 예를 들면:

ERROR:  could not open file "/var/lib/postgresql/data/students.csv" for reading: Permission denied

이런 문제를 피하려면 다음을 확인해봐:

1. 파일이 PostgreSQL 유저(보통 postgres)가 읽을 수 있게 되어 있는지 확인해.
2. 파일과 그 폴더의 권한을 체크해봐:

ls -l /var/lib/postgresql/data/students.csv

권한이 부족하다면 이렇게 바꿀 수 있어:

chmod 644 /var/lib/postgresql/data/students.csv
chown postgres:postgres /var/lib/postgresql/data/students.csv

여기서는 Linux 기준이야. Windows에서 권한 설정은 보통 Windows 관리자(admin)가 하고, 우리 강의 범위는 아니야.

COPY 명령어의 제한사항

COPY를 쓸 때 알아둬야 할 특징들이 있어:

파일 경로: PostgreSQL은 상대 경로를 잘 못 알아들을 수 있으니까, 항상 절대 경로를 써줘.

파일 인코딩: 만약 CSV 파일 인코딩이 데이터베이스랑 다르면(예: Windows-1251 대신 UTF-8), 인코딩을 명시적으로 지정해야 해:

COPY students
FROM '/var/lib/postgresql/data/students.csv'
WITH (FORMAT CSV, ENCODING 'WIN1251', HEADER TRUE);

파일 구조 에러: 파일 구조가 테이블이랑 다르거나 데이터가 잘못되어 있으면(예: 숫자 필드에 텍스트가 들어감), 로드가 에러로 끝나. 데이터 넣기 전에 꼭 확인해!

실전 활용

서버에서 데이터 로드를 위해 COPY를 쓰는 건 여러 상황에서 쓸 수 있어:

• 데이터 마이그레이션: 서버나 데이터베이스 간에 대용량 데이터를 옮길 때 쓸 수 있어.
• 외부 시스템 연동: 많은 시스템이 데이터를 CSV 파일로 내보내. COPY로 이런 데이터를 PostgreSQL에 빠르게 넣을 수 있어.
• 분석 리포트 준비: 서버에서 자동으로 데이터를 로드하면 대용량 정보 분석이 훨씬 빨라져.

흔한 에러와 해결법

COPY를 쓰다 보면 이런 에러들을 만날 수 있어:

문제: 파일에 접근 불가

에러 메시지: could not open file for reading: Permission denied.

해결법: 파일이 PostgreSQL 유저(postgres)에게 열려 있는지, 권한을 확인해봐.

문제: 파일 포맷이 잘못됨

에러 메시지: malformed CSV line.

해결법: 파일에 빈 줄, 데이터 오류, 이상한 문자 등이 없는지 확인하고, 구분자가 맞는지 체크해.

문제: 데이터 구조 불일치

에러 메시지: ERROR: invalid input syntax for type integer.

해결법: 테이블 컬럼이 파일 데이터 구조랑 맞는지 확인해. 예를 들어, 숫자는 숫자 컬럼에, 날짜는 DATE 타입 컬럼에 들어가야 해.

이제 서버에서 데이터 로드를 위해 COPY를 제대로 쓸 수 있는 모든 지식을 갖췄어. 이 스킬을 프로젝트에 활용해서 시간도 아끼고 데이터베이스 작업 효율도 높여봐!