この記事は、NTTテクノクロス Advent Calendar 2020 の 6日目の記事です。
はじめに
こんにちは、NTTテクノクロスの中村です。
普段もPostgreSQLやOracleデータベースを中心としたお仕事（OSSクラウド基盤トータルサービス）をしています。
今回はピンポイントかつ浅めの記事になりますが、きっと誰かの役に立つと信じて投稿します。
さて、皆さんは Amazon RDS for PostgreSQL のサーバーパラメータのデフォルト値 が知りたい時ってないですか？
オンプレであれば、PostgreSQLのサーバーパラメータは postgresql.conf に記載されているので、エディタでさくっと確認できるのですが、Amazon RDS for PostgreSQL(以下、RDS PostgreSQL) の場合、いくつかの手順を踏んで確認する必要があります。
その理由として、
- postgresql.conf を直接参照できない
- AWSのドキュメントにはパラメータの設定値が記載されていない
- RDSのダッシュボードからは具体的な値がわからないパラメータがある
ということが挙げられます。
そもそも、AWSのドキュメントやRDS PostgreSQLのダッシュボードには全パラメータの一覧がありません。マネージドなサービスなので不要なものはユーザに見せていないのだと思いますが、やっぱり全パラメータが知りたいですよね＾＾；
ということで、今回は、自分のパソコンから RDS PostgreSQLに接続する環境を構築したいと思います。
イメージする構成はこちら。
EC2を踏み台にする構成ですね。こうすることで、RDS PostgreSQL が直接インターネットと接続しなくてよいので、セキュリティが向上します。
目次
初めてAWSに触るんだけど？という方でも対応できるように、以下の手順構成としました。
前提として、WindowsパソコンにはSSHクライアントのPuTTYがインストールされているものとします。
1. IAMで管理者ユーザを作成する
2. VPC、サブネットを作成する
3. 踏み台用のEC2を作成する
4. EC2にWindowsからPuTTYでSSH接続する
5. EC2にPostgreSQLをインストールする
6. RDS PostgreSQLを作成する
7. psqlでRDS PostgreSQLに接続し、サーバーパラメータのデフォルト値を取得する
1. 管理者用のIAMユーザを作成する
まず、AWSのマネジメントコンソールにログインします。
すでに管理者権限を持つIAMユーザを作成済み、もしくはルートユーザーで進める場合は「2. VPC、サブネットを作成する」に進んでください。
ただし、ルートユーザーで色々な操作を行うのはセキュリティ上推奨されないため、急ぎでなければ IAMユーザを作成しましょう。
管理者権限を持つIAMユーザを作成する
「IAM」のダッシュボードを開きます。
グループを作成する
・IAMダッシュボードの左メニューから「グループ」を選択し、「新しいグループの作成」ボタンを押下します。
・「グループ名」に「DBAdmin」と入力し、「次のステップ」ボタンを押下します。
・「ポリシーのアタッチ」で、「AdministratorAccess」「Billing」を選択し、「次のステップ」ボタンを押下します。
※この2つポリシー（権限）があれば、大体何でもできます。
・「グループの作成」ボタンを押下します。
これで、管理者権限を持つポリシーを持つグループが作成されました。
ユーザーを作成する
・ダッシュボードの左メニューから「ユーザー」を選択し、「ユーザーを追加」ボタンを押下します。
・「ユーザー名」に「postgres」を入力し、
「AWSマネジメントコンソールへのアクセス」にチェックを入れ、
「カスタムパスワード」を選択し、パスワードを入力します。
★図（レイヤー12）
・「パスワードのリセットが必要」のチェックを外し、「次のステップ」ボタンを押下します。
・「ユーザーをグループに追加」を選択し、先ほど作成した「DBAdmin」グループを選択し、「次のステップ」ボタンを押下します。
・「タグの追加」は何もせず、「次のステップ」ボタンを押下します。
・「ユーザーの作成」ボタンを押下します。
これで、AWSマネジメントコンソールに接続可能な、管理者権限を持つ IAMユーザーが作成されました。
AWSマネジメントコンソールにルートユーザーで接続している場合は、いったんログアウトし、作成したIAMユーザーで接続してください。
また、ここでは触れていませんが、IAMユーザには二段階認証(MFA)を用いておくと安全です。あわせて設定することをお勧めします。
2. VPC(Virtual Private Cloud)および、サブネットを作成する
「VPC」のダッシュボードを開きます。
VPCを作成する
・VPCダッシュボードの左メニューから「VPC」を選択し、「VPCを作成」ボタンを押下します。
・「名前」に「ec2-rds-vpc」を入力し、「CIDRブロック」を「10.0.0.0/16」と入力し、「VPCを作成」ボタンを押下します。
これで、VPCが作成されました。
1つ目のサブネットを作成する（EC2を格納するパブリックサブネット）
・VPCダッシュボードの左メニューから「サブネット」を選択し、「サブネットを作成」ボタンを押下します。
・「VPC ID」に、先ほど作ったVPC（ec2-rds-vpc）を選択します。
・「サブネット名」に「ec2-subnet」を入力し、「アベイラビリティゾーン」に「ap-northeast-1a」を選択します。
・「CIDRブロック」を「10.0.1.0/24」として、「サブネットを作成」ボタンを押下します。
これで、1つ目のサブネットが作成されました。
2つ目のサブネットを作成する（RDS PostgreSQLを格納するプライベートサブネット）
・VPCダッシュボードの左メニューから「サブネット」を選択し、「サブネットを作成」ボタンを押下します。
・「VPC ID」に、先ほど作ったVPC（ec2-rds-vpc）を選択します。
・「サブネット名」に「rds-subnet」を入力し、「アベイラビリティゾーン」に「ap-northeast-1c」を選択します。
・「CIDRブロック」を「10.0.2.0/24」として、「サブネットを作成」ボタンを押下します。
2つ目のサブネットが作成されました。2つ目のサブネットはCIDRブロックと、アベイラビリティゾーンが異なることに注意してください。
RDS PostgreSQL を作成する際には、VPC内に2つのアベイラビリティゾーンのサブネットを作成しておく必要があるため、あえてアベイラビリティゾーンを分けます。
インターネットゲートウェイを作成し、VPCにアタッチする
・VPCダッシュボードの左メニューから「インターネットゲートウェイ」を選択し、「インターネットゲートウェイの作成」ボタンを押下します。
・「名前タグ」に「ec2-igw」を入力し、「インターネットゲートウェイを作成」ボタンを押下します。
これで、インターネットゲートウェイが作成されました。
・作成したインターネットゲートウェイを、VPC(ec2-rds-vpc)にアタッチします。
これで、VPC(ec2-rds-vpc)がインターネットに接続可能になりました。
ルートテーブルを作成する
・VPCダッシュボードの左メニューから「ルートテーブル」を選択し、「ルートテーブルを作成」ボタンを押下します。
・「名前タグ」に「ec2」を入力し、「VPC」に先ほど作成したVPC(ec2-rds-vpc)を選択して、「作成」ボタンを押下します。
これで、ルートテーブルが作成されました。
ルートテーブル「ec2」に、インターネットゲートウェイを追加する
・ルートテーブル「ec2」のルートの編集を行い、インターネットゲートウェイを追加します。
サブネット「ec2-subnet」のルートテーブルを「ec2」に変更する
・VPCダッシュボードの左メニューから「サブネット」を選択し、「ec2-subnet」のルートテーブルを「ec2」に変更します。
これで、サブネット「ec2-subnet」がパブリックサブネットになりました。このサブネットに作成したEC2インスタンスはインターネットと接続が可能になります。
自分のパソコンのグローバルIPアドレスを確認する
自分のパソコンからサブネット「ec2-subnet」に接続するためには、自分のパソコンのグローバルIPアドレスからの接続許可をしておく必要があります。
googleで「自分のIPアドレス」を検索すると、cman.jpが一番上に来ますので、自分のパソコンに割り振られたグローバルIPアドレスを確認し、控えておきましょう。
ec2-rds-vpcのデフォルトセキュリティグループを編集する
自分のパソコンからSSH接続を許可するための設定を行います。
本来は新しいセキュリティグループを作成するのが筋ですが、後で気づいたので今回はこのまま進めます…＾＾；
・VPCダッシュボードの左メニューから「セキュリティグループ」を選択し、VPC(ec2-rds-vpc)のデフォルトのセキュリティグループのインバウンドルールを編集します。
・「ルールを追加」ボタンを押下し、自分のパソコンのグローバルIPアドレスからのSSH接続を許可する設定を行います。
この際、「ソース」はカスタムを選択し、「自分ののパソコンのグローバルIPアドレス/32」を入力します。
これで、VPC(ec2-rds-vpc)のデフォルトのセキュリティグループを指定しているパブリックサブネット(ec2-subent)には、自分のパソコンからのSSH接続が許可されるようになりました。
※この時点でパブリックサブネット(ec2-subnet)にサーバはないので、設定上許可されているだけです。
3. 踏み台用のEC2を作成する
「EC2」のダッシュボードを開きます。
EC2インスタンスを作成する
・EC2ダッシュボードの左メニューから「インスタンス」を選択し、「インスタンスを起動」ボタンを押下します。
・「ステップ1：Amazonマシンイメージ」で「Amazon Linux 2 AMI」を選択します。
・「ステップ2：インスタンスタイプの選択」で「t2.micro(無料利用枠)」を選択し、「次のステップ」ボタンを押下します。
・「ステップ3：インスタンスの詳細の設定」で以下を選択します。
「ネットワーク」に「ec2-rds-vpc」を選択、
「サブネット」に「ec2-subnet」を選択、
「自動割り当てパブリックIP」を有効にし、「次のステップ」ボタンを押下します。
※「自動割り当てパブリックIP」を有効にすると、EC2インスタンスの起動時にグローバルIPアドレスが割り当てられます。
このグローバルIPアドレスをターゲットにして、自分のパソコンからSSH接続を行うことができます。
なお、自動割り当てなので、IPアドレスは起動するたびに変わります。固定で設定することも可能です。
・「ステップ4：ストレージの追加」は何もせず「次のステップ」ボタンを押下します。
・「ステップ5：タグの追加」もそのまま「次のステップ」ボタンを押下します。
・「ステップ6：セキュリティグループの設定」では、
「既存のセキュリティグループを選択する」で、「default」を選択し、「確認と作成」ボタンを押下します。
・「ステップ7：インスタンス作成の確認」で、「起動」ボタンを押下します。
・「既存のキーペアを選択するか、新しいキーペアを作成します」ダイアログが表示されるので、
「新しいキーペアの作成」を選択し、
「キーペア名」に「ec2」と入力して、「キーペアのダウンロード」ボタンを押下します。
・Windowsデスクトップに「ec2.pem」ファイルがダウンロードされたことを確認し、「インスタンスの作成」ボタンを押下します。
・しばらく待機し、「インスタンスの状態」が「実行中」になるのを待ちます。
・「インスタンスの状態」が「実行中」になると、パブリックIPv4アドレスが割り当てられるので控えます。
4. EC2にWindowsからPuTTYでSSH接続する
ダウンロードしたキーペア「ec2.pem」ファイルを、「ec2.ppk」ファイルに変換する
・Windows上で、PuTTY Key Generator（PuTTYgen）を起動し、
「Load」ボタンで「ec2.pem」を選択します。
次に、「Save private key」ボタンで「ec2.ppk」を保存します。
EC2にSSH接続する
・PuTTYを起動し、
「Host Name」に、EC2に割り当てられたパブリックIPv4アドレスを入力します。
「Save Sessions」（保存する接続情報の名称）に、「Amazon EC2」と入力します。
「Connection」→「SSH」→「Auth」の「Private Key file for authentication」に
先ほど変換した「ec2.ppk」を選択します。
・PuTTYの設定を保存し、「Open」ボタンを押下します。
「login as:」のプロンプトが表示されたら、「ec2-user」（AmazonLinux2のOSユーザ）を入力し、接続できることを確認します。
5. EC2にPostgreSQLをインストールする
Amazon Linux 2 には psqlユーティリティが入っていないので、作成した EC2インスタンスに PostgreSQLをインストールし、psqlを使えるようにします。
yumコマンドを使ってインストールしますので、こちらの記事を参考に、最新版のPostgreSQLをEC2にインストールしましょう。
Amazon Linux 2 にPostgreSQL 11 をインストールする
（libra_lt様の記事を参照）
もしくは、WinSCPなどを利用してPostgreSQLのRPMをコピーしてインストールする、でもOKです。
Amazon Linux 2 は Red Hat Enterprise Linux 7 相当なので、RPMを自分で入手する際はご留意ください。
6. RDS PostgreSQLを作成する
「RDS」ダッシュボードを開きます。
RDS PostgreSQLを作成する
・RDSダッシュボードの左メニューから「データベース」を選択し、「データベースの作成」ボタンを押下します。
・データベース作成方法を選択
「標準作成」を選択します。
・エンジンのオプション
「エンジンのタイプ」は「PostgreSQL」を選択します。
「バージョン」は「PostgreSQL 12.4-R1」を選択（デフォルト）します。
・テンプレート
「無料利用枠」を選択します。
・設定
「マスターユーザー名」に「postgres」を入力します。
パスワードは適宜設定してください。
・DBインスタンスサイズ
「DBインスタンスクラス」は「db.t2.micro」を選択します。
※ここは、postgreql.confパラメータのデフォルト値を確認したいDBインスタンスクラスを選択してください。
・ストレージ
「ストレージタイプ」は「汎用SSD」、「ストレージ割り当て」は「20GiB」を選択し、
「ストレージの自動スケーリングを有効にする」はチェックを外します。
・接続
「VPC」は「ec2-rds-vpc」を選択、
「サブネットグループ」は「新しいDBサブネットグループの作成」を選択、
「パブリックアクセス可能」は「なし」を選択、
「VPCセキュリティグループ」は「新規作成」を選択、
「新しいVPCセキュリティグループ名」に「rds-security-group」を入力し、
「アベイラビリティゾーン」に「ap-northeast-1c」を選択します。
※RDS PostgreSQLはプライベートサブネットに格納し、インターネットから直接アクセスできないようにするのがセキュリティ上望ましいため、「パブリックアクセス可能」を「なし」にしています。
・データベース認証
「データベース認証オプション」は「パスワード認証」を選択します。
・追加設定
「最初のデータベース名」に「postgres」を入力し、
「DBパラメータグループ」に「default.postgres12」を選択します。
「自動バックアップの有効化」のチェックを外します。
「Performance Insights を有効にする」のチェックを外します。
「拡張モニタリングの有効化」のチェックを外します。
「postgresql ログ」、「アップグレードログ」のチェックを外します。
「マイナーバージョン自動アップグレードの有効化」のチェックを外します。
「メンテナンスウィンドウ」は「設定なし」を選択します。
「削除保護の有効化」のチェックを外します。
※ここでは料金を気にして全チェックを解除しています。実運用では必要に応じて各機能を利用します。
・「データベースの作成」ボタンを押下します。
これで、RDS PostgreSQLのインスタンスが作成されました。
RDS PostgreSQLのセキュリティグループを変更する
・「VPC」のダッシュボードを開きます。
・VPCダッシュボードの左メニューから、「セキュリティグループ」を選択します。
・先ほどRDS PostgreSQLを作成する際に作成したセキュリティグループ「rds-security-group」のインバウンドルールを編集し、
ec2-rds-vpcのVPCデフォルトのセキュリティグループ(default)からの接続を許可するように変更します。
これで、以下の構成が完成しました。
さっそく、PuTTYから RDS PostgreSQL に接続してみましょう！
7. psqlでRDS PostgreSQL に接続し、サーバーパラメータのデフォルト値を取得する
・RDS PostgreSQLのエンドポイントを確認する。
psql -h の引数には、RDS PostgreSQLのエンドポイントを指定しますので、あらかじめ確認しておきます。
・PuTTYで接続したEC2から、psqlでRDS PostgreSQLに接続します。
[ec2-user@ip-10-0-1-90 ~]$ psql -h database-1.cr0c1gjnpyza.ap-northeast-1.rds.amazonaws.com -U postgres
Password for user postgres:
psql (12.5, server 12.4)
SSL connection (protocol: TLSv1.2, cipher: ECDHE-RSA-AES256-GCM-SHA384, bits: 256, compression: off)
Type "help" for help.
postgres=>
RDS PostgreSQLに接続することができました。
・全パラメータの設定値デフォルト値を取得します。
postgres=> select name,setting,context from pg_settings;
name | setting | context
-------------------------+---------------------------------------------+-------------------
allow_system_table_mods | off | postmaster
application_name | psql | user
archive_cleanup_command | | sighup
archive_command | /etc/rds/dbbin/pgscripts/rds_wal_archive %p | sighup
archive_mode | on | postmaster
(略)
checkpoint_timeout | 300 | sighup
(略)
data_checksums | on | internal
(略)
max_connections | 87 | postmaster
(略)
jit | off | user
(略)
shared_buffers | 25454 | postmaster
(略)
transaction_isolation | read committed | user
(略)
wal_level | replica | postmaster
(略)
(328 rows)
postgres=>
無事取得することができました！そして(略)が多くてすみません。
最初の5行は出力内容そのままですが、その他のパラメータについて簡単に見てみます。
- data_checksums, transaction_isolation, wal_level はダッシュボードに記載のない、PostgreSQLに直接接続しないと確認できないパラメータです。
- checkpoint_timeout は ダッシュボードでは値が空欄になっていますが、実際には値が入っています。
- max_connections, shared_buffers はダッシュボードには計算式が記載されています。インスタンスタイプで値が変わるパラメータですね。shared_buffers はページ数（単位8KB）が記載されているので、値としては 25454*8KB=198MBくらいになります。
- jit はダッシュボードで「0」と記載されているパラメータですが、実際には「off」ということです。
なお、RDS PostgreSQLを構築する際に「自動バックアップの有効化」を無効にすると、「archive_mode」が「off」になり、「archive_command」が空欄になります。今回は「自動バックアップの有効化」を無効にしてRDSを作成したのですが、あとから有効にして、pg_settings の値を取り直しました＾＾；
RDS PostgreSQLのパラメータの重複について
今回取得した、pg_settingsシステムカタログから取得した RDS PostgreSQL のパラメータと、RDS PostgreSQL のダッシュボードから参照可能なパラメータの数を比較しました。
- ダッシュボードに記載のあるパラメータ数 325
- pg_settings から取得したパラメータ数 328
- どちらにも含まれるパラメータ 238
ダッシュボードには、auto_explain.log_analyze などの周辺ツールのパラメータが多く記載されています。
一方、pg_settings には周辺ツールのパラメータは含まれていません。
かわりに、recovery_target など PostgreSQL12で recovery.conf から postgresql.conf に移動されたパラメータや、transaction_isolation、wal_level などは pg_settings のほうからしか確認できませんでした。
どちらかが全てのパラメータを包括しているわけではないので、注意が必要です。
pg_settingsから、各パラメータの具体的なデフォルト値は確認できた
RDS PostgreSQLのパラメータグループのダッシュボードでは、パラメータの設定値は空欄のものも多かったり、設定値に「0」「1」が記載されているもの、計算式が記載されているものなど、見た目ですぐに判断できないものも多いです。
そのため、pg_settings を直接参照し、RDS PostgreSQL の具体的なデフォルト設定値を確認することができるようにしておくことは手段として持っておくことは有用と考えます。
まとめ
結構長い手順になってしまいました。ここまでおつかれさまでした。
最初は「？」な項目も多いかと思いますが、一度実施してみれば、二回目以降は意外にすんなり進めると思います。
なお、AWSは使用していなくてもリソースを確保しているとお金がかかるので、RDS PostgreSQLのサーバーパラメータの確認が終わったら、EC2とRDS PostgreSQLは削除しておくと安心ですね。
明日は、@ttttfx702pさんによる記事「Oracle® SQLclからJavaScriptを使ってみる」です。引き続き、NTTテクノクロス Advent Calendar 2020をお楽しみください！