RDS와 S3를 활용한 데이터 백업과 복구

rang-dev·2021년 10월 15일
1

발단

한 테이블에 거의 1억 개의 데이터가 쌓여있어서 읽기 작업이 불필요하게 많이 발생해 속도가 느려진 상황이었다. 사용 빈도수가 낮은 2020년까지의 데이터는 백업해두고 지워버리기로 했다.

기존에 사용하던 pg_dump로는 조건(2021년 이전)적으로 데이터를 dump할 수 없었고, copy를 쓰자니 RDS를 쓰고 있기에 superuser가 아니어서 \copy로 실행하기 위해 psql로 RDS에 접속해서 파일을 s3로 바로 보내야겠다고 고민하던 찰나 aws_s3라는 PostgreSQL extension으로 바로 내보낼 수 있다고 하여 시도해보기로 했다.

준비

필요한 것

  • S3 버킷
  • PostgreSQL RDS (사용 버전: 10.15)
  • psql (사용 버전: 13.3)
    • 로컬에 설치된 버전과 rds 버전이 달랐지만 큰 문제는 없었다.

AWS Create Role & Policy

  • IAM 역할을 통해 DB instance에서 S3에 엑세스 가능한 권한을 부여해야 한다.
  • Create Policy (For export)
    • Service: S3
    • Actions: s3: PutObject, s3:AbortMultipartUpload
    • Resources: Specific -> Bucket에 대한 ARN
      ((ex. test 버킷에 있는 모든 오브젝트의 엑세스 허용: Bucket name: test, Object name: *))
  • Create IAM Role
    • AWS Service -> RDS -> RDS - Add Role to Database
    • 위에서 생성한 policy를 attach
  • Import를 위한 정책과 역할도 동일한 방법으로 따로 생성해야 한다. 단, 정책 생성시 actions는 s3:GetObject, s3:ListBucket이 되어야 한다.

RDS Instance에 IAM Role 추가

  • RDS -> DB instance 선택 -> Manage IAM roles -> Add role

테스트를 위해 import, export를 동일한 RDS에 하고있어서 role 하나에 import, export를 하려고 했으나 role 하나로 각각 다른 feature를 지정할 수가 없었다.

Export

aws_s3

  • Install

psql로 RDS의 DB에 접속하여 aws_s3를 인스톨한다.

CREATE EXTENSION IF NOT EXISTS aws_e CASCADE;
  • Create s3_uri
SELECT aws_commons.create_s3_uri('bucket_name', 'file_path(file 이름 포함)', 'region') AS s3_uri_1 \gset
  • Check s3_uri
\echo :s3_uri_1

Export query result to s3

여기서 굉장히 헤맸던 부분이 timestamp를 조건으로 쿼리를 해야하는데 내가 찾은 예시들은 그냥 select * 하는 것 밖에 없었다. 그래서 원래 하던대로 dt < '2021-01-01'로 쿼리를 넣으면 계속 Syntax Error가 났다. 날짜에 type cast를 해봐도 계속 에러만 나고 왜맞틀 시전...

날짜에 따옴표를 지워보기도 하고 to_timestamp도 써보고.. 계속 에러 나는 것을 보아하니 string을 string으로 인식하지 못하고 있는 것 같았다. 그래서 아니야 이건 문자야~~ 하고 "도 써보고 쿼리를 감싸는 따옴표도 바꿔보고 아무튼 그렇게 계속 삽질하다보니 뭔가 특수문자에 대한 이유일 것 같다는 감이 왔다. 그래서 따옴표 안에 따옴표를 따옴표 자체로 인식 하지 못해서 에러가 난 것을 깨닫고 ''를 쓰니 해결되었다.

SELECT aws_s3.query_export_to_s3('SELECT * FROM table_name WHERE dt < ''2021-01-01''', :'s3_uri_1', options :='format csv, delimiter $$,$$, HEADER true');

데이터가 많으면 시간이 좀 걸리는데 아래와 같은 결과가 나오면 업로드가 잘 된 것이다.

 rows_uploaded | files_uploaded | bytes_uploaded
---------------+----------------+----------------
      68112190 |              1 |     5650961521
(1 row)

Import table from s3

데이터가 들어갈 테이블을 DB에 먼저 생성해두어야 한다.

SELECT aws_s3.table_import_from_s3('table_name', '', '(FORMAT csv, HEADER true)', :'s3_uri_1');

성공하면 아래와 같은 결과가 출력된다.

                                         table_import_from_s3
------------------------------------------------------------------------------------------------------
 68112190 rows imported into relation "table_name" from file ........
(1 row)

참고

profile
지금 있는 곳에서, 내가 가진 것으로, 할 수 있는 일을 하기 🐢

0개의 댓글