Amazon RDS for PostgreSQL

2019年12月7日
(2024年3月30日更新)

はじめに

Amazon RDS for PostgreSQL で PostgreSQL サーバーを立てる。

前準備

IAM ユーザーを用意し、グループにポリシー "AmazonRDSFullAccess"、"AmazonVPCFullAccess" をアタッチする。

DB インスタンスの作成

  • AWS マネジメントコンソール で、Amazon RDS を検索して開く。
  • Amazon RDS で [データベースの作成] をクリック。
  • エンジンの選択で "PostgreSQL" を選択。[RDS 無料利用枠の対象オプションのみを有効化] にチェックを入れる。[次へ]。
  • DB の設定は、デフォルトのまま。"設定" で、DB インスタンス識別子、ユーザー名、パスワードを設定。[次へ]。
  • ネットワークの設定で、パブリックアクセシビリティを "はい" に設定。外部から接続できるようになる。その他はデフォルト。データベース名を適当に設定する。バックアップが不要な場合は、バックアップの保存期間で "0 日間" を選ぶ 。その他はデフォルト。
  • [データベースの作成] をクリック。しばし待つ。
  • [DB インスタンスの詳細の表示] をクリック。状態が "作成中" から "利用可能" になるまで待つ。

接続

[VPC セキュリティグループ] の [インバウンド] で [ルールの追加] をクリック、[タイプ] を "PostgreSQL"、ソースで IP を設定する。簡単にはソースに "マイ IP" でよいかも。

ローカルの PostgreSQL クライアントから接続。

$ psql --host=mydbinstance.xxxxxxxxxxxx.ap-northeast-1.rds.amazonaws.com --port=5432 --username=mydbuser --dbname=mydbuser

インスタンスの削除

  • [変更] で削除保護を無効化する。
  • [アクション] - [削除] を選択。
  • あとでインスタンスを復元するつもりなら、最終スナップショットを作成するオプションを有効にする。

※無料枠の注意: インスタンスを削除してスナップショットだけを残していると、費用が発生する？

DB スナップショットの復元

  • 左のメニューの [スナップショット] を選択。
  • 復元するスナップショットをクリック。[スナップショットのアクション] で [スナップショットの復元] を選ぶ。
  • [DB インスタンスのクラス] で "db.t2.micro" を選ぶ。DB インスタンス識別子を指定。他はそのまま。
  • [DB インスタンスの復元] をクリック。

S3 からデータを取り込む

S3 上にあるデータを RDS 上の PostgreSQL サーバーに取り込むことができる。PostgreSQL 10.7 くらい以上が必要そう。

psql で接続し、拡張機能を有効化する。

=> create extension aws_s3 cascade;
NOTICE:  installing required extension "aws_commons"
CREATE EXTENSION

IAM のロールの作成で "RDS" の "RDS - Add Role to Database" を選び次へ。ポリシーで "AmazonS3ReadOnlyAccess" を選択。適当に名前をつけ ("rds-s3-role" など) ロールの作成。

RDS のデータベースでインスタンスを選択し、[IAM ロールの管理] で作成した IAM ロールを指定。機能に "s3Import" を指定し [ロールの追加] をクリック。

S3 上の csv ファイルを取り込む。

create table sample (
test1 text,
test2 text,
);
select aws_s3.table_import_from_s3(
  'sample',
  '',
  '(format csv)',
  '(バケット名)/(フォルダ名)',
  'sample.csv',
  'ap-northeast-1'
);
¥q

Lambda の利用

Node.js の node-postgres (pg) モジュールを使えば、psql を使わずに Lambda によりデータベースにアクセスし、S3 からデータをインポートさせることできる。 ただしこの場合、Unicode の CSV が要求される。

psql を使わないのであれば、DB インスタンスのパブリックアクセシビリティを有効にする必要はない。ただ、そのまま無効にすると S3 が VPC 内にある RDS にアクセスできないので、VPC のエンドポイントを作成する必要がある。

  • AWS マネジメントコンソールで VPC を開く。
  • 左の [エンドポイント] を選んで、[エンドポイントの作成]。
  • "サービスカテゴリ" は "AWS サービス" を選択。
  • "サービス名" は "com.amazonaws.ap-northeast-1.s3" を選択。
  • "VPC" で設定したい VPC の名前を設定。ルートテーブル ID にチェックを入れる。
  • "ポリシー" を設定。とりあえずは "フルアクセス" を設定。

S3 にデータを書き出す

(2024.3.30 追記)

S3 のバケットにデータを書き出すには、次のようにする。

IAM のロールの作成で "RDS" の "RDS - Add Role to Database" を選び次へ。ポリシーで "AmazonS3FullAccess" を選択。適当に名前をつけ ("rds-s3-role2" など) ロールの作成。

RDS のデータベースでインスタンスを選択し、[IAM ロールの管理] で作成した IAM ロールを指定。機能に "s3Export" を指定し [ロールの追加] をクリック。

ここでは Lambda で書き出しを実行する (VPC へのアクセスロールと VPC の設定が必要)。

const { Client } = require('pg');

const db_info = {
    user: '(ユーザー名)',
    host: '(RDS ホスト URL)',
    database: '(データベース名)',
    password: '(パスワード)',
    port: 5432,
};

exports.handler = async (event, context) => {
    const client = new Client(db_info);
    await client.connect();
    
    // 拡張機能を有効化
    //await client.query("drop extension aws_s3 cascade;");
    //await client.query("drop extension aws_commons cascade;");
    //await client.query("create extension aws_s3 cascade;");
            
    await client.query("select * from aws_s3.query_export_to_s3(" +
            "'select * from sample'," +
            "aws_commons.create_s3_uri(" +
            "'(バケット名)'," +
            "'sample.csv'," +
            "'ap-northeast-1'" +
            "), options :='format csv, delimiter $$,$$');").then(res => {
    })
    .catch(e => {
        console.log("export error");
        console.error(e.stack);
    });
    
    client.end();
    context.succeed();
};

拡張機能がうまく動かない場合

上の例でコメントで書いてあるが、すでに拡張機能を使ったことがあって、その上拡張機能がうまく動かない場合、一度リセットしたほうがよさそうである。おそらく、PostgreSQL のバージョンアップを行った影響。

    await client.query("drop extension aws_s3 cascade;");
    await client.query("drop extension aws_commons cascade;");
    await client.query("create extension aws_s3 cascade;");