PostgreSQL SQL 파일 실행 완벽 가이드 – psql 마스터하기 2025

SQL 파일에 수천 개의 INSERT 문이 있는데 하나씩 실행할 수는 없죠? 대용량 데이터 마이그레이션이나 데이터베이스 초기화 작업을 해야 하는데 어떻게 해야 할지 막막하신가요? 실제로 PostgreSQL 초보자의 78%가 SQL 파일 실행 방법에서 어려움을 겪는다고 합니다. 이 글에서 psql을 이용한 SQL 파일 실행 방법을 완벽하게 마스터해보겠습니다!

psql이란?

psql은 PostgreSQL의 터미널 기반 프런트엔드 도구입니다. 명령줄에서 SQL 쿼리를 실행하고, 데이터베이스를 관리하며, SQL 파일을 배치로 실행할 수 있는 강력한 도구입니다.

psql의 주요 기능

• 대화형 쿼리 실행: SQL을 직접 입력하여 실행
• SQL 파일 실행: 대량의 쿼리를 파일로 일괄 처리
• 메타 명령: \\dt, \\d, \\l 등 관리 명령어
• 스크립트 자동화: 배치 작업 및 자동화
• 결과 포맷팅: 다양한 출력 형식 지원

SQL 파일 실행 기본 문법

가장 기본적인 형태

psql -U [사용자명] -d [데이터베이스명] -f [SQL파일경로]

제공하신 예제 분석

psql -U postgres -d postgres -a -f /var/lib/pgsql/11/database.sql

각 옵션의 의미:

옵션	의미	설명
-U postgres	User	postgres 사용자로 접속
-d postgres	Database	postgres 데이터베이스 선택
-a	All	실행되는 모든 명령을 화면에 출력 (echo all)
-f	File	실행할 SQL 파일 지정

psql 명령어 옵션 상세 가이드

연결 관련 옵션

# 호스트 지정
psql -h localhost -U postgres -d mydb

# 포트 지정 (기본: 5432)
psql -p 5432 -U postgres -d mydb

# 비밀번호 입력 프롬프트
psql -U postgres -d mydb -W

# 원격 서버 접속
psql -h 192.168.1.100 -p 5432 -U postgres -d mydb

출력 관련 옵션

# -a: 모든 입력 명령을 출력 (제공하신 옵션)
psql -U postgres -d postgres -a -f script.sql

# -e: 서버에 전송되는 명령만 출력
psql -U postgres -d postgres -e -f script.sql

# -q: 조용히 실행 (메시지 최소화)
psql -U postgres -d postgres -q -f script.sql

# -v: 변수 설정
psql -U postgres -d postgres -v table_name=users -f script.sql

# -o: 출력을 파일로 저장
psql -U postgres -d postgres -f query.sql -o output.txt

에러 처리 옵션

# --set ON_ERROR_STOP=on: 에러 발생 시 중지
psql -U postgres -d postgres --set ON_ERROR_STOP=on -f script.sql

# -1 또는 --single-transaction: 단일 트랜잭션으로 실행
psql -U postgres -d postgres -1 -f script.sql

# 에러 무시하고 계속 실행
psql -U postgres -d postgres -f script.sql

실전 SQL 파일 실행 예제

예제 1: 기본 실행

database.sql 파일 내용:

-- 테이블 생성
CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    username VARCHAR(50) NOT NULL,
    email VARCHAR(100) UNIQUE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- 데이터 삽입
INSERT INTO users (username, email) VALUES ('홍길동', 'hong@email.com');
INSERT INTO users (username, email) VALUES ('김철수', 'kim@email.com');
INSERT INTO users (username, email) VALUES ('이영희', 'lee@email.com');

-- 조회
SELECT * FROM users;

실행 방법:

# 기본 실행
psql -U postgres -d postgres -f database.sql

# 실행 과정 확인 (제공하신 방법)
psql -U postgres -d postgres -a -f database.sql

# 에러 발생 시 중지
psql -U postgres -d postgres --set ON_ERROR_STOP=on -f database.sql

예제 2: 대용량 INSERT 처리

bulk_insert.sql 파일:

-- 트랜잭션 시작
BEGIN;

-- 대량 INSERT
INSERT INTO products (name, price, stock) VALUES
('상품1', 10000, 100),
('상품2', 20000, 200),
('상품3', 30000, 300),
-- ... 수천 개의 데이터
('상품10000', 100000, 1000);

-- 커밋
COMMIT;

실행:

# 단일 트랜잭션으로 실행 (빠름)
psql -U postgres -d mydb -1 -f bulk_insert.sql

# 조용히 실행 (출력 최소화)
psql -U postgres -d mydb -q -1 -f bulk_insert.sql

# 실행 시간 측정
time psql -U postgres -d mydb -q -1 -f bulk_insert.sql

예제 3: 데이터베이스 초기화

init_db.sql:

-- 기존 테이블 삭제
DROP TABLE IF EXISTS users CASCADE;
DROP TABLE IF EXISTS orders CASCADE;
DROP TABLE IF EXISTS products CASCADE;

-- 스키마 생성
CREATE SCHEMA IF NOT EXISTS myschema;

-- 테이블 생성
CREATE TABLE myschema.users (
    user_id SERIAL PRIMARY KEY,
    username VARCHAR(50) NOT NULL UNIQUE,
    password_hash VARCHAR(255) NOT NULL,
    email VARCHAR(100) UNIQUE,
    is_active BOOLEAN DEFAULT true,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE myschema.products (
    product_id SERIAL PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    description TEXT,
    price DECIMAL(10, 2) NOT NULL,
    stock INT DEFAULT 0,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE myschema.orders (
    order_id SERIAL PRIMARY KEY,
    user_id INT REFERENCES myschema.users(user_id),
    product_id INT REFERENCES myschema.products(product_id),
    quantity INT NOT NULL,
    total_price DECIMAL(10, 2) NOT NULL,
    order_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- 인덱스 생성
CREATE INDEX idx_users_email ON myschema.users(email);
CREATE INDEX idx_orders_user_id ON myschema.orders(user_id);
CREATE INDEX idx_orders_product_id ON myschema.orders(product_id);

-- 초기 데이터 삽입
INSERT INTO myschema.users (username, password_hash, email) VALUES
('admin', 'hash123', 'admin@example.com'),
('user1', 'hash456', 'user1@example.com');

-- 권한 설정
GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA myschema TO myuser;

실행:

# 전체 과정 출력하며 실행
psql -U postgres -d mydb -a -f init_db.sql

# 에러 시 롤백
psql -U postgres -d mydb --set ON_ERROR_STOP=on -1 -f init_db.sql

다양한 실행 방법

방법 1: 명령줄에서 직접 실행 (제공하신 방법)

[postgres@localhost ~]$ psql -U postgres -d postgres -a -f /var/lib/pgsql/11/database.sql

장점:

• 가장 일반적이고 안전한 방법
• 로그 확인 가능
• 자동화 스크립트 작성 용이

방법 2: psql 내부에서 실행

# psql 접속
psql -U postgres -d postgres

# psql 프롬프트에서
\\i /var/lib/pgsql/11/database.sql

# 또는 상대 경로
\\i ./database.sql

장점:

• 대화형으로 확인하며 실행
• 에러 발생 시 즉시 대응 가능

방법 3: 표준 입력 리다이렉션

# 파일 내용을 표준 입력으로
psql -U postgres -d postgres < database.sql

# 압축 파일 실행
gunzip -c backup.sql.gz | psql -U postgres -d postgres

방법 4: 원격 실행

# SSH를 통한 원격 실행
ssh user@server "psql -U postgres -d mydb -f /path/to/script.sql"

# 로컬 파일을 원격 서버에서 실행
cat local_script.sql | ssh user@server "psql -U postgres -d mydb"

변수 사용하기

SQL 파일 내에서 변수를 사용할 수 있습니다.

변수 정의

# 명령줄에서 변수 전달
psql -U postgres -d mydb -v table_name=users -v limit=100 -f query.sql

query.sql:

-- 변수 사용
SELECT * FROM :table_name LIMIT :limit;

-- 문자열 변수는 따옴표로
SELECT * FROM :table_name WHERE status = :'status_value';

스크립트 내 변수 설정

-- SQL 파일 내에서 변수 설정
\\set table_name users
\\set limit 100

-- 변수 사용
SELECT * FROM :table_name LIMIT :limit;

-- 현재 날짜를 변수로
\\set today `date +%Y-%m-%d`
SELECT * FROM orders WHERE order_date = :'today';

결과 출력 옵션

다양한 출력 형식

# HTML 형식으로 출력
psql -U postgres -d mydb -H -f query.sql > result.html

# CSV 형식으로 출력
psql -U postgres -d mydb -c "COPY (SELECT * FROM users) TO STDOUT WITH CSV HEADER" > users.csv

# JSON 형식으로 출력 (PostgreSQL 9.2+)
psql -U postgres -d mydb -t -c "SELECT row_to_json(users) FROM users" > users.json

출력 포맷 제어

-- SQL 파일 내에서 포맷 설정
\\pset format aligned
\\pset border 2
\\pset null '(null)'

SELECT * FROM users;

에러 처리 전략

전략 1: 에러 발생 시 중지

# 에러 시 즉시 중지하고 롤백
psql -U postgres -d mydb --set ON_ERROR_STOP=on -1 -f critical_script.sql

critical_script.sql:

BEGIN;

-- 에러 발생 시 여기서 중지되고 모든 변경사항 롤백
CREATE TABLE test_table (id INT PRIMARY KEY);
INSERT INTO test_table VALUES (1);
INSERT INTO test_table VALUES (1); -- 중복 키 에러!

COMMIT; -- 실행되지 않음

전략 2: 에러 무시하고 계속

# 에러가 있어도 계속 실행
psql -U postgres -d mydb -f script_with_errors.sql 2> errors.log

전략 3: 조건부 실행

-- 테이블이 없을 때만 생성
CREATE TABLE IF NOT EXISTS users (
    id SERIAL PRIMARY KEY,
    username VARCHAR(50)
);

-- 테이블이 있으면 삭제 후 재생성
DROP TABLE IF EXISTS temp_table CASCADE;
CREATE TABLE temp_table (data TEXT);

자동화 스크립트

배치 실행 스크립트

run_sql.sh:

#!/bin/bash

# 설정
DB_USER="postgres"
DB_NAME="mydb"
SQL_DIR="/var/lib/pgsql/sql"
LOG_DIR="/var/log/pgsql"
DATE=$(date +%Y%m%d_%H%M%S)

# 로그 파일
LOG_FILE="$LOG_DIR/sql_execution_$DATE.log"

# SQL 파일 실행 함수
run_sql_file() {
    local sql_file=$1
    echo "$(date): Executing $sql_file" >> $LOG_FILE

    psql -U $DB_USER -d $DB_NAME --set ON_ERROR_STOP=on -a -f "$sql_file" >> $LOG_FILE 2>&1

    if [ $? -eq 0 ]; then
        echo "$(date): $sql_file executed successfully" >> $LOG_FILE
    else
        echo "$(date): ERROR executing $sql_file" >> $LOG_FILE
        exit 1
    fi
}

# SQL 디렉토리의 모든 .sql 파일 실행
for sql_file in $SQL_DIR/*.sql; do
    if [ -f "$sql_file" ]; then
        run_sql_file "$sql_file"
    fi
done

echo "$(date): All SQL files executed" >> $LOG_FILE

실행:

chmod +x run_sql.sh
./run_sql.sh

순서대로 실행하는 스크립트

execute_in_order.sh:

#!/bin/bash

DB_USER="postgres"
DB_NAME="mydb"

# 순서대로 실행할 SQL 파일 목록
SQL_FILES=(
    "01_drop_tables.sql"
    "02_create_schema.sql"
    "03_create_tables.sql"
    "04_create_indexes.sql"
    "05_insert_data.sql"
    "06_create_views.sql"
    "07_set_permissions.sql"
)

for sql_file in "${SQL_FILES[@]}"; do
    echo "Executing $sql_file..."
    psql -U $DB_USER -d $DB_NAME --set ON_ERROR_STOP=on -f "$sql_file"

    if [ $? -ne 0 ]; then
        echo "Error executing $sql_file. Stopping."
        exit 1
    fi
    echo "$sql_file executed successfully."
done

echo "All SQL files executed successfully!"

데이터베이스 백업 및 복원

백업 생성

# 전체 데이터베이스 백업 (SQL 형식)
pg_dump -U postgres -d mydb > mydb_backup.sql

# 압축 백업
pg_dump -U postgres -d mydb | gzip > mydb_backup.sql.gz

# 특정 테이블만 백업
pg_dump -U postgres -d mydb -t users -t orders > tables_backup.sql

# 스키마만 백업
pg_dump -U postgres -d mydb --schema-only > schema_only.sql

# 데이터만 백업
pg_dump -U postgres -d mydb --data-only > data_only.sql

# 커스텀 포맷 (빠른 복원)
pg_dump -U postgres -d mydb -Fc -f mydb_backup.dump

백업 복원

# SQL 파일로 복원
psql -U postgres -d mydb -f mydb_backup.sql

# 압축 파일 복원
gunzip -c mydb_backup.sql.gz | psql -U postgres -d mydb

# 새 데이터베이스에 복원
createdb -U postgres newdb
psql -U postgres -d newdb -f mydb_backup.sql

# 커스텀 포맷 복원
pg_restore -U postgres -d mydb mydb_backup.dump

# 병렬 복원 (빠름)
pg_restore -U postgres -d mydb -j 4 mydb_backup.dump

성능 최적화 팁

1. 배치 크기 조정

-- 작은 배치로 나누기
BEGIN;
INSERT INTO large_table SELECT * FROM source LIMIT 10000 OFFSET 0;
COMMIT;

BEGIN;
INSERT INTO large_table SELECT * FROM source LIMIT 10000 OFFSET 10000;
COMMIT;

2. COPY 명령 사용

-- INSERT보다 10배 빠름
COPY users (username, email) FROM '/tmp/users.csv' WITH CSV HEADER;

-- 또는 psql \\copy 메타명령
\\copy users (username, email) FROM '/tmp/users.csv' WITH CSV HEADER

3. 인덱스 비활성화

-- 대량 INSERT 전 인덱스 삭제
DROP INDEX IF EXISTS idx_users_email;

-- 데이터 삽입
INSERT INTO users ...;

-- 인덱스 재생성
CREATE INDEX idx_users_email ON users(email);

4. 외래 키 제약 일시 해제

-- 외래 키 제약 해제
ALTER TABLE orders DISABLE TRIGGER ALL;

-- 데이터 삽입
INSERT INTO orders ...;

-- 외래 키 제약 복구
ALTER TABLE orders ENABLE TRIGGER ALL;

5. 트랜잭션 사용

# 단일 트랜잭션으로 실행 (훨씬 빠름)
psql -U postgres -d mydb -1 -f large_script.sql

유용한 psql 메타 명령어

데이터베이스 관리

-- 데이터베이스 목록
\\l

-- 현재 데이터베이스
\\c

-- 다른 데이터베이스로 전환
\\c mydb

-- 데이터베이스 크기
\\l+

테이블 관리

-- 테이블 목록
\\dt

-- 테이블 상세 정보
\\d users

-- 테이블 구조만
\\d+ users

-- 인덱스 목록
\\di

-- 뷰 목록
\\dv

-- 스키마 목록
\\dn

사용자 및 권한

-- 사용자 목록
\\du

-- 현재 사용자
\\conninfo

-- 테이블 권한 확인
\\dp users

쿼리 실행

-- SQL 파일 실행
\\i /path/to/file.sql

-- 쿼리 실행 시간 표시
\\timing on

-- 결과를 파일로 저장
\\o output.txt
SELECT * FROM users;
\\o

기타 유용한 명령

-- 명령어 기록 보기
\\s

-- 마지막 쿼리 편집
\\e

-- 도움말
\\?

-- SQL 명령 도움말
\\h SELECT

-- psql 종료
\\q

문제 해결 (Troubleshooting)

문제 1: “permission denied” 에러

# 에러 예시
psql: error: could not open file "/var/lib/pgsql/11/database.sql" for reading: Permission denied

해결:

# 파일 권한 확인
ls -l /var/lib/pgsql/11/database.sql

# 권한 변경
chmod 644 /var/lib/pgsql/11/database.sql

# 또는 소유자 변경
sudo chown postgres:postgres /var/lib/pgsql/11/database.sql

# 또는 postgres 사용자로 실행
sudo -u postgres psql -d postgres -f /var/lib/pgsql/11/database.sql

문제 2: “database does not exist” 에러

# 에러 예시
psql: error: FATAL:  database "mydb" does not exist

해결:

# 데이터베이스 생성
createdb -U postgres mydb

# 또는 psql에서
psql -U postgres -d postgres -c "CREATE DATABASE mydb;"

# 데이터베이스 목록 확인
psql -U postgres -l

문제 3: “authentication failed” 에러

# 에러 예시
psql: error: FATAL:  password authentication failed for user "postgres"

해결:

# 비밀번호 입력 프롬프트 사용
psql -U postgres -d mydb -W

# 또는 환경 변수 설정
export PGPASSWORD='your_password'
psql -U postgres -d mydb -f script.sql

# 또는 .pgpass 파일 사용 (~/.pgpass)
# 형식: hostname:port:database:username:password
localhost:5432:mydb:postgres:password
chmod 600 ~/.pgpass

문제 4: 인코딩 문제

# 한글 깨짐 현상

해결:

# 클라이언트 인코딩 설정
psql -U postgres -d mydb --set client_encoding=UTF8 -f script.sql

# 또는 SQL 파일 내에서
\\encoding UTF8

# 데이터베이스 생성 시 인코딩 지정
createdb -U postgres -E UTF8 -T template0 mydb

문제 5: 파일 경로 문제

# 상대 경로가 작동하지 않을 때

해결:

# 절대 경로 사용
psql -U postgres -d mydb -f /full/path/to/script.sql

# 현재 디렉토리 확인
pwd

# psql 내에서 현재 경로
\\! pwd

# 작업 디렉토리로 이동 후 실행
cd /var/lib/pgsql/11
psql -U postgres -d postgres -f database.sql

실전 시나리오

시나리오 1: 대용량 데이터 마이그레이션

#!/bin/bash

# 1. 기존 데이터 백업
pg_dump -U postgres -d olddb > olddb_backup.sql

# 2. 새 데이터베이스 생성
createdb -U postgres newdb

# 3. 스키마만 먼저 복원
pg_dump -U postgres -d olddb --schema-only | psql -U postgres -d newdb

# 4. 인덱스 삭제 (성능 향상)
psql -U postgres -d newdb -c "DROP INDEX IF EXISTS idx_users_email;"

# 5. 데이터 복원
pg_dump -U postgres -d olddb --data-only | psql -U postgres -d newdb -q

# 6. 인덱스 재생성
psql -U postgres -d newdb -c "CREATE INDEX idx_users_email ON users(email);"

# 7. VACUUM ANALYZE
psql -U postgres -d newdb -c "VACUUM ANALYZE;"

시나리오 2: 정기 데이터 업데이트

daily_update.sh:

#!/bin/bash

DB_USER="postgres"
DB_NAME="mydb"
SQL_FILE="/var/lib/pgsql/daily_update.sql"
LOG_FILE="/var/log/pgsql/daily_update_$(date +%Y%m%d).log"

echo "$(date): Starting daily update" >> $LOG_FILE

# SQL 파일 실행
psql -U $DB_USER -d $DB_NAME --set ON_ERROR_STOP=on -1 -f $SQL_FILE >> $LOG_FILE 2>&1

if [ $? -eq 0 ]; then
    echo "$(date): Daily update completed successfully" >> $LOG_FILE
else
    echo "$(date): Daily update failed" >> $LOG_FILE
    # 관리자에게 이메일 발송
    mail -s "Daily Update Failed" admin@example.com < $LOG_FILE
fi

cron 등록:

# crontab 편집
crontab -e

# 매일 새벽 2시에 실행
0 2 * * * /var/lib/pgsql/daily_update.sh

시나리오 3: 개발 환경 초기화

reset_dev_db.sh:

#!/bin/bash

DB_USER="postgres"
DB_NAME="dev_db"

echo "Resetting development database..."

# 1. 기존 연결 종료
psql -U $DB_USER -d postgres -c "SELECT pg_terminate_backend(pid) FROM pg_stat_activity WHERE datname='$DB_NAME';"

# 2. 데이터베이스 삭제 및 재생성
psql -U $DB_USER -d postgres -c "DROP DATABASE IF EXISTS $DB_NAME;"
psql -U $DB_USER -d postgres -c "CREATE DATABASE $DB_NAME;"

# 3. 스키마 생성
psql -U $DB_USER -d $DB_NAME -f /var/lib/pgsql/schema.sql

# 4. 테스트 데이터 삽입
psql -U $DB_USER -d $DB_NAME -f /var/lib/pgsql/test_data.sql

echo "Development database reset completed!"

Docker에서 SQL 파일 실행

컨테이너 내부에서 실행

# 파일을 컨테이너로 복사
docker cp database.sql postgres_container:/tmp/

# 컨테이너 내에서 실행
docker exec -it postgres_container psql -U postgres -d mydb -f /tmp/database.sql

호스트에서 직접 실행

# 호스트의 SQL 파일을 컨테이너에서 실행
docker exec -i postgres_container psql -U postgres -d mydb < database.sql

# 또는
cat database.sql | docker exec -i postgres_container psql -U postgres -d mydb

Docker Compose 초기화

docker-compose.yml:

version: '3.8'

services:
  postgres:
    image: postgres:15
    environment:
      POSTGRES_PASSWORD: password
      POSTGRES_DB: mydb
    volumes:
      - ./init.sql:/docker-entrypoint-initdb.d/init.sql
    ports:
      - "5432:5432"

/docker-entrypoint-initdb.d/ 디렉토리의 SQL 파일은 컨테이너 최초 실행 시 자동으로 실행됩니다.

마치며

PostgreSQL의 psql을 이용한 SQL 파일 실행, 이제 자신 있게 하실 수 있으시죠?