{Aurora}データを Aurora PostgreSQL DB クラスターから Amazon S3 にエクスポートする

現在、エクスポートは PostgreSQL 10.14、11.9、12.4 以降でサポートされています。

より大きなエクスポートは複数のファイルに格納され、それぞれの最大サイズは約 6 GB です。


-- 1. S3バケット作成

aws s3 mb s3://bucket123
aws s3 ls


-- 2. IAMポリシー作成
vim policy01.json


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

aws iam create-policy \
--policy-name policy01 \
--policy-document file://policy01.json

-- 3. IAMロール作成
vim role01.json

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

aws iam create-role \
--role-name role01 \
--assume-role-policy-document file://role01.json


-- 4. ポリシーをロールにアタッチ
aws iam attach-role-policy \
--policy-arn arn:aws:iam::999999999999:policy/policy01 \
--role-name role01

-- 5. クラスタの作成

aws rds create-db-cluster \
--db-cluster-identifier cluster11 \
--engine aurora-postgresql \
--engine-version 12.6 \
--master-username postgres \
--master-user-password 'password'


aws rds create-db-instance \
--db-instance-identifier cluster11-instance01 \
--db-cluster-identifier cluster11 \
--db-instance-class db.t3.medium \
--engine aurora-postgresql \
--no-auto-minor-version-upgrade


-- 6. IAMロールをクラスタに関連付ける

aws rds add-role-to-db-cluster \
--db-cluster-identifier cluster11 \
--feature-name s3Export \
--role-arn arn:aws:iam::999999999999:role/role01

-- 7. PostgreSQL 拡張機能をインストール
CREATE EXTENSION aws_s3 CASCADE;

\dx

-- 8. テスト用テーブル、データ作成
create table tab1(col1 int,col2 varchar(100) );

insert into tab1 values(1,'AAA');
insert into tab1 values(2,'BBB');
insert into tab1 values(3,'CCC');

select * from tab1;

-- 9. エクスポート実行

SELECT * from aws_s3.query_export_to_s3('select * from tab1', aws_commons.create_s3_uri( 'bucket123', 'tab1.txt', 'ap-northeast-1' ), options :='format csv, delimiter $$,$$');


aws s3 ls s3://bucket123

aws s3 cp s3://bucket123/tab1.txt .

 

-- 10. クリーンアップ

-- ロールの一覧
aws iam list-roles | grep role01

-- ロールの削除

aws iam detach-role-policy \
--role-name role01 \
--policy-arn arn:aws:iam::999999999999:policy/policy01


aws iam delete-role --role-name role01

-- ポリシーの一覧
aws iam list-policies | grep policy01

-- ポリシーの削除
aws iam delete-policy \
--policy-arn arn:aws:iam::999999999999:policy/policy01


-- バケット一覧
aws s3 ls

-- バケット削除
aws s3 rb s3://bucket123 --force


-- クラスタ削除
aws rds delete-db-instance \
--db-instance-identifier cluster11-instance01 \
--skip-final-snapshot

aws rds delete-db-cluster \
--db-cluster-identifier cluster11 \
--skip-final-snapshot

 

{Aurora}Aurora での PostgreSQL 論理レプリケーションの使用

rds.logical_replication パラメータを有効にすると、DB クラスターのパフォーマンスに影響します。


パブリッシャー:
Aurora PostgreSQL バージョン 2.2.0 (PostgreSQL 10.6 と互換) 以降

サブスクライバー:
Aurora PostgreSQL データベースバージョン 2.2.0 (PostgreSQL 10.6 と互換) 以降。
Amazon RDS for PostgreSQL データベース (PostgreSQL DB エンジンバージョン 10.4 以降を搭載)。


-- 1. パブリッシャーDB作成 (Aurora PostgreSQL 12.6)

aws rds create-db-cluster-parameter-group \
--db-parameter-group-family aurora-postgresql12 \
--db-cluster-parameter-group-name cpg01 \
--description cpg01

aws rds modify-db-cluster-parameter-group \
--db-cluster-parameter-group-name cpg01 \
--parameters ParameterName=rds.logical_replication,ParameterValue=1,ApplyMethod=pending-reboot

 

aws rds create-db-cluster \
--db-cluster-identifier cluster01 \
--engine aurora-postgresql \
--engine-version 12.6 \
--master-username postgres \
--master-user-password 'password' \
--db-cluster-parameter-group-name cpg01

aws rds create-db-instance \
--db-instance-identifier cluster01-instance01 \
--db-cluster-identifier cluster01 \
--db-instance-class db.t3.medium \
--engine aurora-postgresql \
--no-auto-minor-version-upgrade

-- 2. サブスクライバーDB作成 (RDS PostgreSQL 10.4)

aws rds create-db-instance \
--db-instance-identifier postgres01 \
--allocated-storage 20 \
--db-instance-class db.t3.micro \
--engine postgres \
--master-username postgres \
--master-user-password 'password' \
--no-multi-az \
--engine-version 10.4 \
--storage-type gp2 \
--no-publicly-accessible \
--no-enable-performance-insights

-- 3. 論理レプリケーション動作確認

-- パブリッシャーで実行

create table tab1 (col1 int primary key);
insert into tab1 values (generate_series(1,10000));
create publication pub01 for table tab1;

select count(*) from tab1;

-- サブスクライバーで実行
create table tab1(col1 int primary key);

select count(*) from tab1;

create subscription sub01 connection 'host=cluster01-instance01.xxxxxxxxxxxx.ap-northeast-1.rds.amazonaws.com port=5432 dbname=test user=postgres password=password' publication pub01;

select count(*) from tab1;

 

select * from pg_stat_replication;
select * from pg_stat_subscription;
select * from pg_replication_slots;
select * from pg_publication;
select * from pg_publication_tables;

 

-- 4. クリーンアップ

-- RDSインスタンス削除
aws rds delete-db-instance \
--db-instance-identifier postgres01 \
--skip-final-snapshot


-- Auroraクラスタ削除

aws rds delete-db-instance \
--db-instance-identifier cluster01-instance01 \
--skip-final-snapshot

aws rds delete-db-cluster \
--db-cluster-identifier cluster01 \
--skip-final-snapshot

-- クラスタパラメータグループ削除
aws rds delete-db-cluster-parameter-group --db-cluster-parameter-group-name cpg01

 

 

{Aurora}Amazon S3 データを Aurora PostgreSQL DB クラスターにインポートする

-- 1. S3バケット作成

aws s3 mb s3://bucket123
aws s3 ls


-- 2. IAMポリシー作成
vim policy01.json


{
"Version": "2012-10-17",
"Statement": [
{
"Sid": "s3import",
"Effect": "Allow",
"Action": [
"s3:GetObject",
"s3:ListBucket"
],
"Resource": [
"arn:aws:s3:::bucket123/*",
"arn:aws:s3:::bucket123"
]
}
]
}

aws iam create-policy \
--policy-name policy01 \
--policy-document file://policy01.json

-- 3. IAMロール作成
vim role01.json

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

aws iam create-role \
--role-name role01 \
--assume-role-policy-document file://role01.json


-- 4. ポリシーをロールにアタッチ
aws iam attach-role-policy \
--policy-arn arn:aws:iam::999999999999:policy/policy01 \
--role-name role01

-- 5. クラスタの作成

aws rds create-db-cluster \
--db-cluster-identifier cluster11 \
--engine aurora-postgresql \
--engine-version 12.6 \
--master-username postgres \
--master-user-password 'password'


aws rds create-db-instance \
--db-instance-identifier cluster11-instance01 \
--db-cluster-identifier cluster11 \
--db-instance-class db.t3.medium \
--engine aurora-postgresql \
--no-auto-minor-version-upgrade


-- 6. IAMロールをクラスタに関連付ける

aws rds add-role-to-db-cluster \
--db-cluster-identifier cluster11 \
--feature-name s3Import \
--role-arn arn:aws:iam::999999999999:role/role01

-- 7. PostgreSQL 拡張機能をインストール
CREATE EXTENSION aws_s3 CASCADE;

\dx

-- 8. テスト用テーブル、インポート用テキストファイル作成
create table tab1(col1 int,col2 varchar(100) );

vim tab1.txt
1,AAA
2,BBB
3,CCC

aws s3 ls s3://bucket123

aws s3 cp tab1.txt s3://bucket123

 

-- 9. インポート実行

SELECT aws_s3.table_import_from_s3( 'tab1', 'col1,col2', 'DELIMITER '',''', aws_commons.create_s3_uri( 'bucket123', 'tab1.txt', 'ap-northeast-1' ));

入力ファイルの末尾に空行があるとエラーとなる


select * from tab1;


-- 10. クリーンアップ

-- ロールの一覧
aws iam list-roles | grep role01

-- ロールの削除

aws iam detach-role-policy \
--role-name role01 \
--policy-arn arn:aws:iam::999999999999:policy/policy01


aws iam delete-role --role-name role01

-- ポリシーの一覧
aws iam list-policies | grep policy01

-- ポリシーの削除
aws iam delete-policy \
--policy-arn arn:aws:iam::999999999999:policy/policy01


-- バケット一覧
aws s3 ls

-- バケット削除
aws s3 rb s3://bucket123 --force


-- クラスタ削除
aws rds delete-db-instance \
--db-instance-identifier cluster11-instance01 \
--skip-final-snapshot

aws rds delete-db-cluster \
--db-cluster-identifier cluster11 \
--skip-final-snapshot

{Aurora}Aurora リードレプリカを使用した、RDS PostgreSQL DB インスタンスから Aurora PostgreSQL DB クラスターへのデータの移行

問題の Aurora PostgreSQL バージョンと互換性のある RDS for PostgreSQL インスタンスからのみレプリケートできます。
例えば、Aurora PostgreSQL バージョン 12.4 がサポートされている場合、
RDS for PostgreSQL DB インスタンスは、バージョン 12.2 以降で実行する必要があります。


-- テスト用 RDS PostgreSQL作成


aws rds create-db-instance \
--db-instance-identifier postgres01 \
--allocated-storage 20 \
--db-instance-class db.t3.micro \
--engine postgres \
--master-username postgres \
--master-user-password 'password' \
--no-multi-az \
--engine-version 12.2 \
--storage-type gp2 \
--no-publicly-accessible \
--no-enable-performance-insights


-- Aurora リードレプリカの作成

aws rds create-db-cluster \
--db-cluster-identifier cluster01 \
--engine aurora-postgresql \
--engine-version 12.4 \
--replication-source-identifier arn:aws:rds:ap-northeast-1:999999999999:db:postgres01


aws rds create-db-instance \
--db-instance-identifier cluster01-instance01 \
--db-cluster-identifier cluster01 \
--db-instance-class db.t3.medium \
--engine aurora-postgresql \
--no-auto-minor-version-upgrade

-- Aurora リードレプリカの昇格

aws rds promote-read-replica-db-cluster \
--db-cluster-identifier cluster01


-- クリーンアップ

-- RDSインスタンス削除
aws rds delete-db-instance \
--db-instance-identifier postgres01 \
--skip-final-snapshot


-- Auroraクラスタ削除

aws rds delete-db-instance \
--db-instance-identifier cluster01-instance01 \
--skip-final-snapshot

aws rds delete-db-cluster \
--db-cluster-identifier cluster01 \
--skip-final-snapshot

 

{Aurora}RDS PostgreSQL DB スナップショットを Aurora PostgreSQL DB クラスターに移行する

RDS for PostgreSQL スナップショットは、Aurora PostgreSQL と同じかそれ以下のマイナーバージョンを使用する必要があります。

12.6 → 12.6の移行は2021/07/25時点できない模様


-- テスト用 RDS PostgreSQL作成
aws rds describe-db-engine-versions \
--engine postgres \
| jq -c '.DBEngineVersions | [ .Engine, .EngineVersion ]'

aws rds describe-db-engine-versions \
--engine aurora-postgresql \
| jq -c '.DBEngineVersions | [ .Engine, .EngineVersion ]'


aws rds create-db-instance \
--db-instance-identifier postgres01 \
--allocated-storage 20 \
--db-instance-class db.t3.micro \
--engine postgres \
--master-username postgres \
--master-user-password 'password' \
--no-multi-az \
--engine-version 11.4 \
--storage-type gp2 \
--no-publicly-accessible


-- スナップショット作成
aws rds create-db-snapshot \
--db-snapshot-identifier snap01 \
--db-instance-identifier postgres01

-- スナップショットの一覧
aws rds describe-db-snapshots
aws rds describe-db-snapshots | jq -c '.DBSnapshots[] | [ .DBInstanceIdentifier, .DBSnapshotArn ] '


-- Auroraへのスナップショットの移行

aws rds restore-db-cluster-from-snapshot \
--db-cluster-identifier cluster01 \
--snapshot-identifier arn:aws:rds:ap-northeast-1:999999999999:snapshot:snap01 \
--engine aurora-postgresql \
--engine-version 11.4

aws rds create-db-instance \
--db-instance-identifier cluster01-instance01 \
--db-cluster-identifier cluster01 \
--db-instance-class db.t3.medium \
--engine aurora-postgresql

 

-- クリーンアップ

-- RDSインスタンス削除
aws rds delete-db-instance \
--db-instance-identifier postgres01 \
--skip-final-snapshot


-- スナップショット削除
aws rds delete-db-snapshot --db-snapshot-identifier snap01


-- Auroraクラスタ削除

aws rds delete-db-instance \
--db-instance-identifier cluster01-instance01 \
--skip-final-snapshot

aws rds delete-db-cluster \
--db-cluster-identifier cluster01 \
--skip-final-snapshot

 

{Aurora}Amazon CloudWatch Logs への Amazon Aurora MySQL ログの発行

https://aws.amazon.com/jp/premiumsupport/knowledge-center/rds-aurora-mysql-logs-cloudwatch/

 

-- 1. IAMポリシー作成
vim policy01.json

{
"Version": "2012-10-17",
"Statement": [
{
"Sid": "EnableCreationAndManagementOfRDSCloudwatchLogEvents",
"Effect": "Allow",
"Action": [
"logs:GetLogEvents",
"logs:PutLogEvents"
],
"Resource": "arn:aws:logs:*:*:log-group:/aws/rds/*:log-stream:*"
},
{
"Sid": "EnableCreationAndManagementOfRDSCloudwatchLogGroupsAndStreams",
"Effect": "Allow",
"Action": [
"logs:CreateLogStream",
"logs:DescribeLogStreams",
"logs:PutRetentionPolicy",
"logs:CreateLogGroup"
],
"Resource": "arn:aws:logs:*:*:log-group:/aws/rds/*"
}
]
}

aws iam create-policy \
--policy-name policy01 \
--policy-document file://policy01.json

-- 2. IAMロール作成
vim role01.json

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

aws iam create-role \
--role-name role01 \
--assume-role-policy-document file://role01.json


-- 3. ポリシーをロールにアタッチ
aws iam attach-role-policy \
--policy-arn arn:aws:iam::999999999999:policy/policy01 \
--role-name role01

-- 4. クラスタパラメータグループ作成
aws rds create-db-cluster-parameter-group \
--db-parameter-group-family aurora-mysql5.7 \
--db-cluster-parameter-group-name cpg01 \
--description cpg01

vim a.json
[
{
"ParameterName": "general_log",
"ParameterValue": "1",
"ApplyMethod": "immediate"
},
{
"ParameterName": "slow_query_log",
"ParameterValue": "1",
"ApplyMethod": "immediate"
},
{
"ParameterName": "long_query_time",
"ParameterValue": "3",
"ApplyMethod": "immediate"
},
{
"ParameterName": "server_audit_logging",
"ParameterValue": "1",
"ApplyMethod": "immediate"
},
{
"ParameterName": "server_audit_events",
"ParameterValue": "CONNECT,QUERY,TABLE",
"ApplyMethod": "immediate"
},
{
"ParameterName": "server_audit_incl_users",
"ParameterValue": "root",
"ApplyMethod": "immediate"
}
]

aws rds modify-db-cluster-parameter-group \
--db-cluster-parameter-group-name cpg01 \
--parameters file://a.json

-- 5. クラスタの作成

aws rds create-db-cluster \
--db-cluster-identifier cluster12 \
--engine aurora-mysql \
--engine-version 5.7.mysql_aurora.2.10.0 \
--master-username root \
--master-user-password 'password' \
--db-cluster-parameter-group-name cpg01 \
--enable-cloudwatch-logs-exports '["error","general","audit","slowquery"]'

aws rds create-db-instance \
--db-instance-identifier cluster12-instance01 \
--db-cluster-identifier cluster12 \
--db-instance-class db.t3.small \
--engine aurora-mysql \
--no-auto-minor-version-upgrade


-- 6. IAMロールをクラスタに関連付ける

aws rds add-role-to-db-cluster \
--db-cluster-identifier cluster12 \
--role-arn arn:aws:iam::999999999999:role/role01


-- 7. ログ確認
aws rds describe-db-log-files --db-instance-identifier cluster12-instance01

select sleep(10);

 

-- 8. クリーンアップ

-- ロールの一覧
aws iam list-roles | grep role01

-- ロールの削除


aws iam detach-role-policy \
--role-name role01 \
--policy-arn arn:aws:iam::999999999999:policy/policy01

aws iam delete-role --role-name role01

-- ポリシーの一覧
aws iam list-policies | grep policy01

-- ポリシーの削除
aws iam delete-policy \
--policy-arn arn:aws:iam::999999999999:policy/policy01

 

-- クラスタ削除
aws rds delete-db-instance \
--db-instance-identifier cluster12-instance01 \
--skip-final-snapshot

aws rds delete-db-cluster \
--db-cluster-identifier cluster12 \
--skip-final-snapshot

-- クラスタパラメータグループ削除
aws rds delete-db-cluster-parameter-group --db-cluster-parameter-group-name cpg01

 

{Aurora}Amazon Aurora MySQL DB クラスターからの Lambda 関数の呼び出し

(1) Lambda関数作成

-- 1. IAMロール作成
vim role01.json

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

aws iam create-role \
--role-name role01 \
--assume-role-policy-document file://role01.json


-- 2. ポリシーをロールにアタッチ
aws iam attach-role-policy \
--policy-arn arn:aws:iam::aws:policy/service-role/AWSLambdaVPCAccessExecutionRole \
--role-name role01

-- 3. Lambda関数作成

vim test.py

#!/usr/bin/python
def lambda_handler(event, context):
print(event)
return 'OK'


chmod 755 test.py
zip -r test.zip test.py

aws lambda create-function \
--region ap-northeast-1 \
--function-name test \
--zip-file fileb://test.zip \
--role arn:aws:iam::999999999999:role/role01 \
--handler test.lambda_handler \
--runtime python3.8 \
--timeout 60 \
--vpc-config SubnetIds="subnet-11111111111111111,subnet-22222222222222222,subnet-33333333333333333",SecurityGroupIds="sg-44444444444444444"

※以下のようなエラーが出た場合は、しばらく待ってから再実行する
An error occurred (InvalidParameterValueException) when calling the CreateFunction operation:
The provided execution role does not have permissions to call CreateNetworkInterface on EC2

aws lambda list-functions
aws lambda list-functions | jq -c '.Functions | [ .FunctionName ]'

aws lambda get-function --function-name test

"State"がactiveになるまで待つ


-- 4. Lambda関数の実行テスト

aws lambda invoke \
--function-name test \
--region ap-northeast-1 \
--payload '{ "key1": "val1" }' \
output.txt

(2) DB作成


-- 5. IAMポリシー作成
vim policy02.json

{
"Version": "2012-10-17",
"Statement": [
{
"Sid": "AllowAuroraToExampleFunction",
"Effect": "Allow",
"Action": "lambda:InvokeFunction",
"Resource": "arn:aws:lambda:ap-northeast-1:999999999999:function:test"
}
]
}

aws iam create-policy \
--policy-name policy02 \
--policy-document file://policy02.json

-- 6. IAMロール作成
vim role02.json

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

aws iam create-role \
--role-name role02 \
--assume-role-policy-document file://role02.json


-- 7. ポリシーをロールにアタッチ
aws iam attach-role-policy \
--policy-arn arn:aws:iam::999999999999:policy/policy02 \
--role-name role02

-- 8. クラスタパラメータグループ作成
aws rds create-db-cluster-parameter-group \
--db-parameter-group-family aurora-mysql5.7 \
--db-cluster-parameter-group-name cpg01 \
--description cpg01

vim a.json
[
{
"ParameterName": "aws_default_lambda_role",
"ParameterValue": "arn:aws:iam::999999999999:role/role02",
"ApplyMethod": "immediate"
}
]

aws rds modify-db-cluster-parameter-group \
--db-cluster-parameter-group-name cpg01 \
--parameters file://a.json

-- 9. クラスタの作成

aws rds create-db-cluster \
--db-cluster-identifier cluster11 \
--engine aurora-mysql \
--engine-version 5.7.mysql_aurora.2.10.0 \
--master-username root \
--master-user-password 'password' \
--db-cluster-parameter-group-name cpg01

aws rds create-db-instance \
--db-instance-identifier cluster11-instance01 \
--db-cluster-identifier cluster11 \
--db-instance-class db.t3.small \
--engine aurora-mysql \
--no-auto-minor-version-upgrade


-- 10. IAMロールをクラスタに関連付ける

aws rds add-role-to-db-cluster \
--db-cluster-identifier cluster11 \
--role-arn arn:aws:iam::999999999999:role/role02


(3) ネイティブ関数を使用した Lambda 関数の呼び出し

-- 11. テスト用ユーザとデータの作成
create user 'user1'@'%' identified by 'password';

GRANT INVOKE LAMBDA ON *.* TO 'user1'@'%';

SHOW GRANTS FOR 'user1'@'%';

※DB クラスターのマスターユーザー名にはデフォルトで INVOKE LAMBDA 権限が付与されている


SELECT lambda_sync( 'arn:aws:lambda:ap-northeast-1:999999999999:function:test', '{"key1": "val1"}');

SELECT lambda_async( 'arn:aws:lambda:ap-northeast-1:999999999999:function:test', '{"key2": "val2"}');


ERROR 63996 (HY000): Lambda API returned error: Network Connection. Unable to connect to endpoint
→ lambdaインタフェースエンドポイントを作成してエラー解消
VPC: Auroraが使用しているVPC
サブネット: Auroraが使用しているサブネット
プライベートDNS名を有効にする: YES
セキュリティグループ: Auroraが使用しているセキュリティグループ
ポリシー: フルアクセス


-- 12. クリーンアップ

-- ロールの一覧
aws iam list-roles | grep role01
aws iam list-roles | grep role02

-- ロールの削除

aws iam detach-role-policy \
--role-name role01 \
--policy-arn arn:aws:iam::aws:policy/service-role/AWSLambdaVPCAccessExecutionRole


aws iam detach-role-policy \
--role-name role02 \
--policy-arn arn:aws:iam::999999999999:policy/policy02

aws iam delete-role --role-name role01
aws iam delete-role --role-name role02

-- ポリシーの一覧
aws iam list-policies | grep policy02

-- ポリシーの削除
aws iam delete-policy \
--policy-arn arn:aws:iam::999999999999:policy/policy02


-- lambda関数の一覧
aws lambda list-functions | jq -c '.Functions | [ .FunctionName ]'

-- lambda関数の削除
aws lambda delete-function --function-name test

 

-- クラスタ削除
aws rds delete-db-instance \
--db-instance-identifier cluster11-instance01 \
--skip-final-snapshot

aws rds delete-db-cluster \
--db-cluster-identifier cluster11 \
--skip-final-snapshot

-- クラスタパラメータグループ削除
aws rds delete-db-cluster-parameter-group --db-cluster-parameter-group-name cpg01

-- lambdaインタフェースエンドポイントの削除