プライベートサブネットのAurora Serverlessに開発環境(Windows端末)から接続する

前提

‐ VPCのプライベートサブネットグループ (マルチAZ) にAurora Serverlessをデプロイ中
‐ postgres
‐ テーブル定義をシェル(対話形式)でやりたい
‐ 格納予定のデータがローカルにCSVで存在 (容量大)

制約

• DBサーバはパブリックに晒したくない
• 共同開発のためシンプルな接続方法求む
• 財布に優しく

選択肢

‐ 踏み台のEC2インスタンスを立てて接続 ←採用
‐ Systems Manager Session Managerを利用
‐ Auroraのあるプライベートサブネットグループに穴を開ける

概要

重要: EC2にSSH接続し、EC2にpsqlクライアントインストールすれば操作は可能
ただし、ローカルPCのCSVを参照できない
→ ポートフォワーディングする

[ローカルPC]
   |
   | (SSH) 
   |
[パブリックサブネット EC2 (踏み台サーバー)]
   |
   | (内部通信)
   |
[プライベートサブネット Aurora]

ローカルPC:
ローカルで動作するpsqlまたはクライアントアプリケーション。
Auroraに直接アクセスする権限やルートがない。

パブリックサブネットのEC2:
SSHで接続可能な「踏み台サーバー」。
プライベートサブネット内のAuroraと通信できる。

プライベートサブネットのAurora:
VPC内からのみアクセス可能。
EC2を経由して接続。

踏み台サーバはメンテ時以外は停止して節約 & セキュアに
なお、セキュリティグループは随時更新する

SSHポートフォワーディング

ローカルPCからプライベートサブネットのAuroraに接続するためのコマンド:

ssh -L 15432:<aurora-endpoint>:5432 ec2-user@<ec2-public-ip> -i /pass/to/pem

ローカルのポート15432を、リモートの<aurora-endpoint>ポート5432にフォワーディング

15432とする: ポイント
5432とすると、ローカルPCでもPostgresが5432で稼働している場合、次のコマンドでローカルに接続されてしまう → 別のポートを使う

EC2に接続されるが、本命は次のコマンドなので、接続は保ったまま別のターミナルで次を実行
オプションでバックグラウンドで接続させるのも可能 (-fNを付加)

psql接続

psql -h 127.0.0.1 -p 15432 -U postgres

パスワードを聞かれるため、AWSのSecretManagerからクレデンシャルを貼り付け

ローカルPCの127.0.0.1:15432からSSHトンネルを通じて踏み台サーバー（EC2）へ。
踏み台サーバーがプライベートサブネットのAuroraに通信を転送。の流れ

\lを叩くと、ロケールがen_US.UTF-8なpostgresとmydbのデータベースがあるのが分かる

Amazon Linuxにpsqlのクライアントをインストール

sudo dnf install postgresql16

データベース作成

CREATE DATABASE mydb
  WITH ENCODING 'UTF8'
       LC_COLLATE 'ja_JP.utf8'
       LC_CTYPE 'ja_JP.utf8'
       TEMPLATE template0;

テーブル定義

CREATE TABLE users (
    id INTEGER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    name TEXT NOT NULL,
);

idは自動増加、手動でも指定可能、ただし手動指定後に自動増加に頼るとシーケンスが同期されてない場合数値が重複し、エラーになる。その時は以下?

SELECT setval(pg_get_serial_sequence('users', 'id'), MAX(id)) FROM users;

ローカルCSVを格納

\copy users (id, name) from 'C:\path\to\users.csv' with csv;

文字コード関連でエラーを吐いていたが

ERROR:  符号化方式"SJIS"においてバイト列0x83 0xbbである文字は符号化方式"UTF8"で等価な文字を持ちません
ERROR:  character with byte sequence 0x83 0xbb in encoding "SJIS" has no equivalent in encoding "UTF8"

以下で対応

SHOW client_encoding; #SJISと表示
SET client_encoding = 'UTF8';

作業後は、SET client_encoding = 'SJIS'で戻す