한 테이블에 거의 1억 개의 데이터가 쌓여있어서 읽기 작업이 불필요하게 많이 발생해 속도가 느려진 상황이었다. 사용 빈도수가 낮은 2020년까지의 데이터는 백업해두고 지워버리기로 했다.
기존에 사용하던 pg_dump
로는 조건(2021년 이전)적으로 데이터를 dump할 수 없었고, copy
를 쓰자니 RDS를 쓰고 있기에 superuser가 아니어서 \copy
로 실행하기 위해 psql로 RDS에 접속해서 파일을 s3로 바로 보내야겠다고 고민하던 찰나 aws_s3
라는 PostgreSQL extension으로 바로 내보낼 수 있다고 하여 시도해보기로 했다.
s3: PutObject
, s3:AbortMultipartUpload
Bucket name: test, Object name: *
))AWS Service
-> RDS
-> RDS - Add Role to Database
s3:GetObject
, s3:ListBucket
이 되어야 한다.RDS
-> DB instance 선택
-> Manage IAM roles
-> Add role
테스트를 위해 import, export를 동일한 RDS에 하고있어서 role 하나에 import, export를 하려고 했으나 role 하나로 각각 다른 feature를 지정할 수가 없었다.
psql
로 RDS의 DB에 접속하여 aws_s3를 인스톨한다.
CREATE EXTENSION IF NOT EXISTS aws_e CASCADE;
SELECT aws_commons.create_s3_uri('bucket_name', 'file_path(file 이름 포함)', 'region') AS s3_uri_1 \gset
\echo :s3_uri_1
여기서 굉장히 헤맸던 부분이 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)
데이터가 들어갈 테이블을 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)