AWS RDS(Postgresql) to S3

필요 이유

• RDS의 Prod 데이터를 Dev로 옮기는 과정에서 S3로 옮겨서 Copy하는 방식을 취하기 위함
• dblink라는 방법도 있지만 Redshift를 계속해서 사용해온 나로서 unload & copy 방식이 익숙해져 있음
• 추후 백업에 사용 가능성
• 추후 dblink에 대한 내용도 기술할 예정

aws_s3 확장 설치

• 설치 방법

CREATE EXTENSION aws_s3 CASCADE;

• 설치 확인

select * from pg_extension;

• 설치 가능한 extension 확인

select * from pg_available_extensions;

S3 Path 설정

• Bucket 설정
• 파일 경로 → Prefix
  ex) /exports/query-1-export
• 그냥 사용할 bucket, path 설정하면 됨. 파일이 클 경우 postfix가 붙은 파일 여러개가 생성됨
  • _part_NN의 파일 생성(각 파일의 최대 크기 6GB)
    ex) /exports/query-1-export_part_00

Amazon S3 버킷에 대한 액세스 권한 설정

• IAM 설정은 이미되어 있어서 직접해보지 못함.
• s3Export role 추가 필요
  • https://console.aws.amazon.com/rds/ 에서 Amazon RDS 콘솔 접속
  • 세부 정보를 표시하고자 하는 PostgreSQL DB 인스턴스 이름을 선택
  • Connectivity & security(연결성 및 보안) 탭에 있는 Manage IAM roles(IAM 역할 관리) 섹션의 이 인스턴스에 IAM 역할 추가에서 추가할 역할을 선택합니다.
  • s3Export를 선택 & Add role

Data Export to S3

aws_commons.create_s3_uri(
   bucket text,
   file_path text,
   region text
)

aws_s3.query_export_to_s3(
    query text,    
    s3_info aws_commons._s3_uri_1,    
    options text
)

SELECT *
from aws_s3.query_export_to_s3(
        'select * from sample-table',
        aws_commons.create_s3_uri(
                'sample-bucket',
                'sample-path',
                'sample-region'),
        options := 'format csv, delimiter $$,$$, header true'
    );

• Options는 PostgreSQL COPY 명령의 인수 및 형식을 사용
• delimiter는 $$로 감싸주어야 함
• query문에 '를 사용할 query문이 ''로 감싸져 있기 때문에 '를 ''로 치환해야 동작함
  ex) select * from smaple-table where create_date > ''2023.04.15''
• 다음과 같은 형식으로도 사용 가능한데 속도가 조금 느림

  aws_s3.query_export_to_s3(
      query text,    
      bucket text,    
      file_path text,    
      region text,    
      options text
  )

  • create_s3_uri 사용
    1 row retrieved starting from 1 in 1 s 110 ms
    (execution: 1 s 101 ms, fetching: 9 ms)
  • create_s3_uri 미사용
    1 row retrieved starting from 1 in 1 s 341 ms
    (execution: 1 s 325 ms, fetching: 16 ms)
  • 시간상 큰 차이가 아닐수 있지만 현재는 데이터가 그리 많지 않은 상황이므로 추후 데이터가 많아질 것을 생각하면 create_s3_uri를 사용하는게 좋을듯.

Data Import from S3

• 사전에 s3Import role 추가 필요

aws_s3.table_import_from_s3 (
   table_name text, 
   column_list text, 
   options text, 
   s3_info aws_commons._s3_uri_1
) 

SELECT aws_s3.table_import_from_s3(
               'temp_table',
               'a,b,c,d',
               '(format csv, delimiter $$,$$, HEADER true)',
               aws_commons.create_s3_uri(
                       'sample-bucket',
                       'sample-filepath',
                       'sample-region'
                   ));

참고

• AWS 공식 문서 - Export
• AWS 공식 문서 - Imprt
• Postgresql 공식 문서