PostgreSQLの拡張機能で古いデータをS3に定期的に退避する

AWS事業本部コンサルティング部のじゅんやです。

ふとRDSのサポートしているPostgreSQLの拡張機能を眺めていたところ
aws_s3という名称が見えなんとなく面白そうだったので触ってみることにしました。

今回作成するもの

pg_cron + aws_s3を使ってDB内のテーブル上の古いデータを定期的にS3に退避する処理を作成します。
今回は確認すぐに確認したいということもあるので5分毎に5分以上前のデータを対象に退避するような処理としています。

導入手順が違う部分はありますがRDS専用の機能ではなくPostgreSQLの拡張機能のためオンプレミス等の他の環境でも導入可能です。

• pg_cron
• aws_s3

調査用に保持しているが古くなってくるにつれ使くなるような変更履歴テーブルなど、
持つ必要はあるが滅多に使わないデータをS3のライフサイクの延長として退避することで、
費用の削減を狙える可能性があります。

サービス	料金
RDS for PostgreSQL(汎用SSD・Single-AZ)	$0.138/GB・月
S3(標準・~50TB)	$0.025/GB・月

※ 料金2022/12/08時点の東京リージョン準拠

保存形式の違いや圧縮有無等の違いがあるため一概に比較できるものではありませんが
単純な容量単価だけ見ればS3はSingle-AZのRDS比でも約18%程の料金となります。

構成

以下の構成で作成しました。
EC2は作業用のインスタンスです。

PostgreSQLは執筆時点でRDS上で利用可能な最新の14.5を採用していますが、
拡張機能の対応を確認する限り12.7以上であれば動作すると思われます。
(pg_cronが12.7以上、aws_s3が10.17以上対応)

VPCエンドポイントをS3との通信のために作成していますが、
既にNAT Gatewayが存在しRDSからS3の通信がインターネットを経由して問題なければそちらを経由するのも良いかと思います。
今回はまっさらな環境のため維持費の安いVPCエンドポイントを採用しました。

構築

AWS側のサービス側として設定が必要なのは
RDSのパラメータグループの設定とIAMロールの割り当てになります。

以降のbacket-nameの箇所は実際には作成したバケット名が入っています。

パラメータグループの変更

pg_cronとaws_s3導入のたために
shared_preload_liblaryとrds.allowed_extensionsのパラメータを変更します。
shared_preload_liblaryは適用タイプがstaticのため既存のRDSの再起動が必要です。
RDSを新規作成する場合はインスタンス作成前に設定しておくと再移動の手間が省けます。

RDSに対してIAMロールを割り当てる

RDSからS3にPutObject処理ができるようにIAM Roleを割り当てます。
マネジメントコンソールの場合対象のDBの「接続とセキュリティ」のタブから設定できます。

割り当てはs3Exportを選択します。

IAM Roleの設定は以下の通りです。

ポリシードキュメント

{
    "Version": "2012-10-17",
    "Statement": [
        {
            "Sid": "s3export",
            "Effect": "Allow",
            "Action": [
                "s3:PutObject"
            ],
            "Resource": [
                "arn:aws:s3:::bucket-name/*"
            ]
        }
    ]
}

信頼関係

{
	"Version": "2012-10-17",
	"Statement": [
		{
			"Effect": "Allow",
			"Principal": {
                            "Service": "rds.amazonaws.com"
			},
			"Action": "sts:AssumeRole"
		}
	]
}

オンプレミスで利用する場合はIAM Userを発行の上DB上の作業でアクセスキーを設定する必要があります。
設定する場合はDB側の作業としてcreate_aws_credentials()を設定をします。

DB上作業

作業用のEC2にpsqlコマンドをインストールし作業を行っています。

拡張機能の導入

必要な拡張機能を追加します。
RDSの場合rds_superuser権限のユーザが必要となるますので注意してください。

-- デフォルトではplpgsqlのみが入っている
postgres=> SELECT * FROM pg_extension;
  oid  | extname | extowner | extnamespace | extrelocatable | extversion | extconfig | extcondition
-------+---------+----------+--------------+----------------+------------+-----------+--------------
 14703 | plpgsql |       10 |           11 | f              | 1.0        |           |
(1 行)

-- 依存関係上aws_commons => aws_s3の順番でCREATE EXTENSIONを実行
postgres=> CREATE EXTENSION aws_commons;
CREATE EXTENSION
postgres=> CREATE EXTENSION aws_s3;
CREATE EXTENSION
postgres=> CREATE EXTENSION pg_cron;
CREATE EXTENSION
-- 追加されていることを確認
postgres=> SELECT * FROM pg_extension ;
  oid  |   extname   | extowner | extnamespace | extrelocatable | extversion |         extconfig         | extcondition
-------+-------------+----------+--------------+----------------+------------+---------------------------+---------------
 14703 | plpgsql     |       10 |           11 | f              | 1.0        |                           |
 16421 | aws_commons |       10 |         2200 | f              | 1.2        |                           |
 16435 | aws_s3      |       10 |         2200 | t              | 1.1        |                           |
 24613 | pg_cron     |       10 |         2200 | f              | 1.4-1      | {24616,24615,24636,24635} | {"","","",""}
(4 行)

検証用に適当なテーブル作成しデータを流し込みます。
作成日(create_date)カラムは後ほど退避するデータの対比条件に使います。

postgres=> CREATE TABLE history(id serial, text text, create_date timestamp DEFAULT now());
postgres=> INSERT INTO history(text) SELECT md5(generate_series::text) FROM generate_series(1,20);
INSERT 0 20
-- 過去データをS3に格納する予定なので対象外データの確認として一件未来日データを作成しておく
postgres=> UPDATE history SET create_date = now() + '1 day' WHERE id = 10;
UPDATE 1
postgres=> SELECT * FROM history;
 id |               text               |        create_date
----+----------------------------------+----------------------------
  1 | c4ca4238a0b923820dcc509a6f75849b | 2022-11-25 08:58:23.151356
  2 | c81e728d9d4c2f636f067f89cc14862c | 2022-11-25 08:58:23.151356
  3 | eccbc87e4b5ce2fe28308fd9f2a7baf3 | 2022-11-25 08:58:23.151356
  4 | a87ff679a2f3e71d9181a67b7542122c | 2022-11-25 08:58:23.151356
  5 | e4da3b7fbbce2345d7772b0674a318d5 | 2022-11-25 08:58:23.151356
  6 | 1679091c5a880faf6fb5e6087eb1b2dc | 2022-11-25 08:58:23.151356
  7 | 8f14e45fceea167a5a36dedd4bea2543 | 2022-11-25 08:58:23.151356
  8 | c9f0f895fb98ab9159f51fd0297e236d | 2022-11-25 08:58:23.151356
  9 | 45c48cce2e2d7fbdea1afc51c7c6ad26 | 2022-11-25 08:58:23.151356
 11 | 6512bd43d9caa6e02c990b0a82652dca | 2022-11-25 08:58:23.151356
 12 | c20ad4d76fe97759aa27a0c99bff6710 | 2022-11-25 08:58:23.151356
 13 | c51ce410c124a10e0db5e4b97fc2af39 | 2022-11-25 08:58:23.151356
 14 | aab3238922bcc25a6f606eb525ffdc56 | 2022-11-25 08:58:23.151356
 15 | 9bf31c7ff062936a96d3c8bd1f8f2ff3 | 2022-11-25 08:58:23.151356
 16 | c74d97b01eae257e44aa9d5bade97baf | 2022-11-25 08:58:23.151356
 17 | 70efdf2ec9b086079795c442636b55fb | 2022-11-25 08:58:23.151356
 18 | 6f4922f45568161a8cdf4ad2299f6d23 | 2022-11-25 08:58:23.151356
 19 | 1f0e3dad99908345f7439f8ffabdffc4 | 2022-11-25 08:58:23.151356
 20 | 98f13708210194c475687be6106a3b84 | 2022-11-25 08:58:23.151356
 10 | d3d9446802a44259755d38e6d163e820 | 2022-11-26 09:00:24.923556

準備ができたので実際にS3にテーブルのデータを退避する処理を組み込みます。

トランザクションを貼る場合now()の時刻はトランザクション開始というのは知ってましたが
同一関数内での実行の場合も同一時刻になるのは今回の試行で初めて知りました。

postgres=> SELECT cron.schedule('*/5 * * * *', $$
postgres=>     SELECT * FROM aws_s3.query_export_to_s3(
postgres=>         'SELECT * FROM history WHERE create_date < now() - interval ''5 min''',
postgres=>         aws_commons.create_s3_uri(
postgres=>             'backet-name',
postgres=>             to_char(now(), 'YYYYMMDDHHMI'),
postgres=>             'ap-northeast-1'
postgres=>         )
postgres=>     );
postgres=>     DELETE FROM history WHERE create_date < now() - interval '5 min';
postgres=> $$);

 schedule
----------
        1

時間を置いてcron.schedule()に指定された処理が実行されていることを確認しましょう。

postgres=> SELECT * FROM cron.job_run_details ;
 jobid | runid | job_pid | database | username |                                     command                                     |  status   | return_message |          start_time           |           end_time
-------+-------+---------+----------+----------+---------------------------------------------------------------------------------+-----------+----------------+-------------------------------+-------------------------------
     1 |     1 |   11664 | postgres | postgres |                                                                                +| succeeded | DELETE 19      | 2022-11-25 12:30:00.042993+00 | 2022-11-25 12:30:00.392309+00
       |       |         |          |          |     SELECT * FROM aws_s3.query_export_to_s3(                                   +|           |                |                               |
       |       |         |          |          |         'SELECT * FROM history WHERE create_date < now() - interval ''5 min''',+|           |                |                               |
       |       |         |          |          |         aws_commons.create_s3_uri(                                             +|           |                |                               |
       |       |         |          |          |             'backet-name',                                                     +|           |                |                               |
       |       |         |          |          |             to_char(now(), 'YYYYMMDDHHMI'),                                    +|           |                |                               |
       |       |         |          |          |             'ap-northeast-1'                                                   +|           |                |                               |
       |       |         |          |          |         )                                                                      +|           |                |                               |
       |       |         |          |          |     );                                                                         +|           |                |                               |
       |       |         |          |          |     DELETE FROM history WHERE create_date < now() - interval '5 min';          +|           |                |                               |
       |       |         |          |          |                                                                                 |           |                |                               |
(1 行)

postgres=> SELECT * FROM history;
 id |               text               |        create_date
----+----------------------------------+----------------------------
 10 | d3d9446802a44259755d38e6d163e820 | 2022-11-26 09:00:24.923556
(1 行)

未来日を指定したデータ以外が消えていることが確認できます。

S3に指定したファイルを確認しにいきます。

$ aws s3 ls s3://backet-name
2022-11-25 21:30:01       1188 202211251230
$ s3 cp s3://backet-name/202211251230 ./202211251230
download: s3://backetname/202211251230 to ./202211251230
$ cat 202211251230
1	c4ca4238a0b923820dcc509a6f75849b	2022-11-25 08:58:23.151356
2	c81e728d9d4c2f636f067f89cc14862c	2022-11-25 08:58:23.151356
3	eccbc87e4b5ce2fe28308fd9f2a7baf3	2022-11-25 08:58:23.151356
4	a87ff679a2f3e71d9181a67b7542122c	2022-11-25 08:58:23.151356
5	e4da3b7fbbce2345d7772b0674a318d5	2022-11-25 08:58:23.151356
6	1679091c5a880faf6fb5e6087eb1b2dc	2022-11-25 08:58:23.151356
7	8f14e45fceea167a5a36dedd4bea2543	2022-11-25 08:58:23.151356
8	c9f0f895fb98ab9159f51fd0297e236d	2022-11-25 08:58:23.151356
9	45c48cce2e2d7fbdea1afc51c7c6ad26	2022-11-25 08:58:23.151356
11	6512bd43d9caa6e02c990b0a82652dca	2022-11-25 08:58:23.151356
12	c20ad4d76fe97759aa27a0c99bff6710	2022-11-25 08:58:23.151356
13	c51ce410c124a10e0db5e4b97fc2af39	2022-11-25 08:58:23.151356
14	aab3238922bcc25a6f606eb525ffdc56	2022-11-25 08:58:23.151356
15	9bf31c7ff062936a96d3c8bd1f8f2ff3	2022-11-25 08:58:23.151356
16	c74d97b01eae257e44aa9d5bade97baf	2022-11-25 08:58:23.151356
17	70efdf2ec9b086079795c442636b55fb	2022-11-25 08:58:23.151356
18	6f4922f45568161a8cdf4ad2299f6d23	2022-11-25 08:58:23.151356
19	1f0e3dad99908345f7439f8ffabdffc4	2022-11-25 08:58:23.151356
20	98f13708210194c475687be6106a3b84	2022-11-25 08:58:23.151356

未来日データの入っているid=10以外のデータが無事出力されていることが確認できました。
デフォルトではTSV形式ですがCOPY文に準じているのでoptions引数のDELIMITERの指定でCSVの出力も可能です。

終わりに

今回はPostgreSQLの拡張機能であるpg_cronとaws_s3を利用してRDSを基点としS3に対してデータ退避を行ってみました。
スケジューラを利用することでS3のライフサイクルの延長としてPostgreSQLのテーブルのデータを巻き込むような処理を組み込むことができます。

今回は試していませんが逆にS3からDB側にインポートすることも可能ですし、
DB側に取り込まなくともAthenaを利用することでS3上で分析することもできます。

DB側での処理はアプリ側から見えずブラックボックスになるため忌避されることもありますが、
保存要件にアプリ側の実装を関与させたくない場合やAWS SDKを入れられない事情がある等
条件次第では使い所があるので是非選択肢の一つとして検討してみてください。

参考

Exporting data from an RDS for PostgreSQL DB instance to Amazon S3