[PostgreSQL] PostgreSQL 데이터 추출 - CSV, JSON 및 스키마 덤프 뜨는법.

PostgreSQL를 사용하다 보면 데이터를 특정 형식에 맞춰서 파일로 추출해야 하는 경우가 생기기도 하고, 데이터 덤프가 아닌 스키마를 덤프 떠야 하는 상황도 생깁니다.
PostgreSQL 데이터베이스에서 테이블 데이터를 CSV와 JSON 형식으로 추출하고, 테이블 스키마를 덤프 하는 방법을 알아보도록 하겠습니다.

 

 

테이블 데이터를 CSV 형식으로 추출하기

PostgreSQL에서 테이블 데이터를 CSV 형식으로 추출하려면 COPY 명령어를 사용할 수 있습니다.

예를 들어, 테이블 이름이 your_table이고, 조건, 정렬 값, 파일 경로를 지정하여 데이터를 추출하려면 다음과 같이 작성합니다.

 

COPY (select * from your_table
      where your_condition = your_condition_value
      order by your_order_value
      ) TO '/your/file/path/your_table.csv' with csv header DELIMITER ',';

 

COPY 명령어는 PostgreSQL 서버에서 로컬 파일 시스템으로 파일을 생성하거나 읽는 작업을 수행하기 때문에, 서버에서 실행해야 합니다

 

테이블 스키마와 데이터 베이스 구조를 SQL로 저장하기

pg_dump 명령어를 사용하면 PostgreSQL 데이터베이스의 테이블 및 관련된 DDL SQL 명령어를 추출할 수 있습니다.

테이블의 시퀀스, 제약 조건 및 인덱스를 포함하여 재생성할 수 있습니다.

 

테이블 재생성에 필요한 SQL을 추출하려면 주로 -t 또는 --schema-only 플래그를 사용합니다.

-t 옵션은 특정 테이블을 지정하는 데 사용되며, --schema-only 옵션은 데이터를 제외하고 스키마 정보만 추출하겠다는 것을 의미합니다.

다음과 같은 명령어를 실행하여 테이블 구조에 관한 SQL 명령어를 추출할 수 있습니다.

 

$ pg_dump -t 'users' --schema-only your_database > users.schema.sql

위 명령어를 실행하면 users.schema.sql 파일이 생성되며, 이 파일에는 테이블을 재생성하는데 필요한 SQL 명령어들이 포함됩니다.

 

배치 파일을 통한 일괄 처리

윈도우 환경에서 배치 파일을 사용하여 일괄 처리를 수행할 수 있습니다.

아래 예제는 PostgreSQL pg_dump.exe의 경로, 데이터베이스 정보, 추출할 스키마 명을 입력하면 일괄로 원하는 파일 경로에 SQL 파일로 저장합니다.

 

@echo off
set PG_PORT=${postgres_port}
set DUMP_DIR=%~dp0\schema_dump
set NEW_PG_DUMP="${pg_dump.exe 경로}"
set DATABASE_NAME=${postgres_databaseName}
set PGPASSWORD=${postgres_DB_PW}
set DUMP_SCHEMA=${추출할Schema list ','로 구분지어서 나열해서 입력}

::경로는 임의로 해당 배치파일이 있는 동일 경로에 schema_dump 라는 폴더를 만들어서 안에 저장
if not exist "%DUMP_DIR%" mkdir "%DUMP_DIR%"

set PG_DUMP_EXEC=%NEW_PG_DUMP% -Upostgres --port=%PG_PORT% --schema-only --dbname=%DATABASE_NAME%

for %%t in (%DUMP_SCHEMA%) do (
    %PG_DUMP_EXEC% -t "%%t" > "%DUMP_DIR%\%%t.schema.sql"
)

 

데이터베이스 테이블을 JSON 값으로 반환

PostgreSQL에서 테이블 데이터를 JSON 형식으로 반환하려면 내장 함수 row_to_json()와 json_agg()를 사용합니다.

예를 들어, customers테이블을 JSON으로 추출하려면 다음 쿼리를 사용할 수 있습니다.

 

SELECT json_agg(row_to_json(t))
FROM (
  SELECT * FROM customers
) t;

 

위 쿼리는 PostgreSQL에서 JSON 형식으로 데이터를 변환하는 데 사용되는 내장 함수 row_to_json()와 json_agg()를 사용합니다.

row_to_json() 함수는 테이블의 각 행을 JSON 객체로 변환합니다.

이 함수는 각 행의 컬럼 이름을 JSON 키로 사용하고, 해당 컬럼의 값을 JSON 값으로 사용.

이렇게 생성된 JSON 객체는 원본 행이 표현된 값입니다.

json_agg() 함수는 입력되는 JSON 객체들을 하나의 JSON 배열로 집계합니다.

이 함수는 모든 행에 대해 row_to_json() 함수가 반환한 JSON 객체를 JSON 배열로 결합합니다.

FROM 절의 서브쿼리는 원하는 테이블에서 모든 행을 선택합니다.

이 경우에는 customers 테이블입니다.

그리고 메인 쿼리의 row_to_json() 함수가 이 서브쿼리 결과를 사용하여 각 행을 JSON 객체로 변환한 다음, json_agg() 함수로 결과 JSON 객체들을 하나의 JSON 배열로 집계합니다.

임시테이블을 생성하는가?

위 쿼리는 메모리에서 작업을 수행.

결과 JSON 배열은 클라이언트에 반환되고, 데이터베이스 내부에는 저장되지 않습니다.

따라서 이 쿼리는 임시 테이블을 생성하지 않습니다.

다른 작업을 수행하더라도 결과 데이터는 이미 반환되었기 때문에 영향을 받지 않습니다.

Json 파일로 저장하고 싶은 경우

psql과 쉘에서 사용하는 > 리다이렉션을 활용하여 저장할 수 있습니다.

윈도우 환경에서 배치 파일을 사용하여 일괄 처리를 수행할 수 있습니다.

 

@echo off

::postgres setting
set OUTPUTDIR=%~dp0
set PGUSER=${youruser}
set PGPASSWORD=${yourpassword}
set PGDATABASE=${yourdatabase}
set PGHOST=${yourhost}
set PGPORT=${yourport}

set NEW_PSQL="C:\Program Files\PostgreSQL\14\bin\psql.exe" //ex 사용자 postgresql psql 경로

set PSQL=%NEW_PSQL% -U %PGUSER% -d %PGDATABASE% -h %PGHOST% -p %PGPORT% --tuples-only -A -c 

:: Query to export data as JSON
set QUERY="SELECT json_agg(row_to_json(t)) FROM (SELECT * FROM metric_classmetric) t;"

:: Ask for the output directory
set OUTPUT_PATH="%OUTPUTDIR%"

:: Export data as JSON to the specified directory
%PSQL% %QUERY% > "%OUTPUT_PATH%\test_output.json"

echo Data exported successfully to %OUTPUT_PATH%\output.json

 

여기서 포인트는

-tuples-only 옵션과 A 옵션입니다.

 

-tuples-only 또는 t

 

이 옵션은 결과 출력 시 컬럼 이름과 테이블의 구분선을 숨겨줍니다.

해당 옵션을 적용하지 않으면 json 데이터 앞뒤로 불필요한 선, 공백 등의 데이터가 같이 출력되는데.

옵션을 적용하면 데이터 튜플(행)만 출력됩니다.

이 옵션을 사용하면 결과가 더 깔끔하게 보이며, 특히 파일로 내보낼 때 유용합니다.

 

-no-align 또는 A

 

이 옵션은 출력 결과의 정렬을 해제합니다.

기본적으로 psql은 출력 결과를 정렬하여 표 형식으로 보여주지만, 이 옵션을 사용하면 각 필드가 공백 없이 서로 붙어서 출력됩니다.