IBM Cloud Databases for PostgreSQL 使用メモ #PostgreSQL

IBM Cloud Databases for PostgreSQLを利用して感じたことをメモします。
記事の内容は2021年11月時点のものです。

暗号化 (Key Protect)

IBM® Key Protect for IBM Cloudによるディスク暗号化が利用できる。
- これはプロビジョン時に指定する必要がある。後付はできない。
BYOK(鍵の持ち込み)もできるが、バックアップのストレージもBYOKしたい場合はUS-SOUTHなど限られたリージョンにインスタンスを作る必要がある。(東京リージョンなどは非対応)
- BYOKで作成したインスタンスのバックアップをリストアする場合、リストア先のインスタンスにもBYOKの鍵を指定する必要がある。この時CRNが間違っている場合はエラーは出ず、自動的にIBM Cloud生成の鍵が使われる。
プライベートエンドポイントのみを指定した場合、現時点では、UIからの鍵管理操作はできない
鍵の操作を行う際に、現時点では、Key ProtectのAPIでないとできない操作がかなりある。この時に指定するCRNはURL全部ではなく、コロン(:)で区切られた部分のうち最後の部分を指定する
鍵から紐付いているリソースがある場合は鍵を削除できない (何が紐付いているかはAPIで表示できる)
鍵のインスタンスを削除したい場合、すべての鍵を削除した後、数日(最大9日)待つ必要がある
鍵の名前は同じものを複数作成できる。
- Tips: 同じ名前の鍵が複数あると、UIからは区別できない。何と紐付いているのか調べるのは非常に大変なので極力ユニークになるようにすること。

プロビジョニング時

UIでオーダーする際は、あとから2セット作成されるため、入力した値 x 2倍が課金対象として横のサマリー欄に表示され、プロビジョニングされる。
IBM Cloud Console (CLI)でオーダーする場合は、はじめから2倍の値を指定してプロビジョニングする必要がある
CPUコア数とメモリはオーダー後に増やしたり減らしたりできるが、ディスクはオーダーした後増やせるものの減らすことはできない。
- ちなみに、現時点でUIから選択可能な範囲の最大値は「RAM 112GB、Disk 4096GB、Core=28」である。
メジャーバージョンはオーダー後に変更することはできない。現時点の最新は14だが、IBM Cloudでオーダーできるのは12まで。
プロビジョニングは通常40分くらいで完了する。一時間超えている場合は何らかの要因で詰まっていることがあるので、CASE起票して問い合わせすると良い。

バックアップ・リストア

バックアップはデイリーで7日分の増分バックアップが自動的に取得される。そのタイミング変更はできない。ただし手動で追加でバックアップを行うことも可能である。
バックアップが保存される領域も課金対象なので、あらかじめ見積もっておく必要がある。
リストアは最新か、過去7日間の任意の時点か、特定バックアップファイルを指定してリストアできる。
現在存在しているインスタンスにリストアはできない。必ず新規インスタンスにリストアとなる。これも課金対象なので、なるべくオプションで小さめに指定するとよい。
リストアの際にBYOKのディスクにリストアしたい場合は現時点ではAPIで実施する必要がある。このときのCPU・メモリ・ディスクはUIで指定した場合の2倍の値を指定してやる。

拡張機能

IBM CloudでサポートしているPostgreSQLのプラグインのバージョンは、現在の最新のバージョンよりかなり低い事が多い。

下記はpostgisの例。現時点のPostgisの最新は3.2だが、IBM CloudのPostgre12で対応しているのはpostgisで対応している下限近くである2.5.5まで。(サポートによれば、それより新しいバージョンには、次のPostgre13で対応するとのこと)

対応表 https://trac.osgeo.org/postgis/wiki/UsersWikiPostgreSQLPostGIS

ibmclouddb=> CREATE EXTENSION postgis;
CREATE EXTENSION
ibmclouddb=> \dx
                                     List of installed extensions
  Name   | Version |   Schema   |                             Description
---------+---------+------------+---------------------------------------------------------------------
 plpgsql | 1.0     | pg_catalog | PL/pgSQL procedural language
 postgis | 2.5.5   | public     | PostGIS geometry, geography, and raster spatial types and functions
(2 rows)


ibmclouddb=>

パスワード

IBM CloudのPostgreSQLのパスワードは10文字以上である必要がある。長い方の制限はない。(1000文字でも設定可能だった)

config-schemaの値

IBM Cloudのconfig-schemaはデフォルトの設定値を表示するのみ。configurationで設定を変更してもこちらには反映されない

config-schemaの実行例

% ibmcloud cdb config-schema "Databases for PostgreSQL"
Retrieving database configuration schema for Databases for PostgreSQL...
OK


Setting              Default       Kind     Choices                  Requires Restart
log_connections      off           choice   [off, on]                false
log_disconnections   off           choice   [off, on]                false
synchronous_commit   local         choice   [local, off]             false
wal_level            hot_standby   choice   [hot_standby, logical]   true


Setting                      Default   Kind      Min/Max             Requires Restart
archive_timeout              1800      integer   [300, 1073741823]   false
deadlock_timeout             10000     integer   [100, 2147483647]   false
effective_io_concurrency     12        integer   [1, 1000]           false
log_min_duration_statement   100       integer   [100, 2147483647]   false
max_connections              115       integer   [115, null]         true
max_prepared_transactions    0         integer   [0, null]           true
max_replication_slots        10        integer   [10, 262143]        true
max_wal_senders              12        integer   [12, 262143]        true
shared_buffers               32000     integer   [16, null]          true
tcp_keepalives_count         6         integer   [0, 2147483647]     false
tcp_keepalives_idle          300       integer   [0, 2147483647]     false
tcp_keepalives_interval      10        integer   [0, 2147483647]     false
%

configurationで変更した値は、リストアしたインスタンスには持ち越されない。リストア後に再設定する必要がある

configurationの実行例
ここでは log_min_duration_statement をデフォルトの100 から 10000にする

1. 作業前の確認

(IBM Cloud Consoleからpsqlで接続)

% ibmcloud cdb deployment-connections "Databases for PostgreSQL" --start

Database Password>>　　　　<--- パスワードを入力する
psql (14.0, server 12.7)
SSL connection (protocol: TLSv1.3, cipher: TLS_AES_256_GCM_SHA384, bits: 256, compression: off)
Type "help" for help.

ibmclouddb=> show log_min_duration_statement;

 log_min_duration_statement
----------------------------
 100m       <---   100m (100ミリセカンド)となっている
(1 row)

ibmclouddb=>

2.設定変更

% ibmcloud cdb configuration "Databases for PostgreSQL" '{"configuration":{"log_min_duration_statement":10000}}'
Applying new configuration to Databases for PostgreSQL...
The deployment's configuration is being changed with this task:


Key                   Value
ID                    crn:v1:bluemix:public:databases-for-postgresql:以下割愛
Deployment ID         crn:v1:bluemix:public:databases-for-postgresql:以下割愛::
Description           Applying new configuration
Created At            2021-11-01T16:59:48Z
Status                running
Progress Percentage   0


Status   queued
Status   running
Progress Percentage   100
OK

3. 設定後の確認

% ibmcloud cdb deployment-connections "Databases for PostgreSQL" --start

Database Password>>　　　　<--- パスワードを入力する
psql (14.0, server 12.7)
SSL connection (protocol: TLSv1.3, cipher: TLS_AES_256_GCM_SHA384, bits: 256, compression: off)
Type "help" for help.

ibmclouddb=> show log_min_duration_statement;

 log_min_duration_statement
----------------------------
 10s       <---   10s (10000ミリセカンド)となっている
(1 row)

ibmclouddb=>

なお、この手順を行った時に下記のエラーが出る場合は、psql未導入、もしくはパスが通っていない。後述の手順で導入する。

% ibmcloud cdb deployment-connections "Databases for PostgreSQL" --start
Database Password>>
FAILED
could not find psql on path: exec: "psql": executable file not found in $PATH

psql導入 (Mac, zsh)

DB操作を行う場合には、psqlの導入がほぼ必須となる。
Macの場合の導入手順を下記に記す。 (brewを使用)

1. まずbrewが入っているか確認する。

入っている場合はコマンドのパスが出る

% which brew
/usr/local/bin/brew

入っていない場合はnot foundと出る

% which brew
brew not found

入っていない場合は、下記のコマンドでbrewを導入する　(5分程度かかる)
brew導入の過程で、Xcodeも未導入の場合は、途中でXcodeを入れるか聞かれる。enterキーで続行する

 %  /bin/bash -c "$(curl -fsSL https://raw.githubusercontent.com/Homebrew/install/master/install.sh)"

2. libpqのインストール

brewが入っている場合は引き続きlibpqをインストールする。(再導入したい場合は installではなく、reinstallとする)

% brew install libpq

3. 環境変数登録

上記手順で、"If you need to have libpq first in your PATH, run:" という行が出てくる。
その下のコマンドを実行することで、シェル起動時の環境変数に登録できる。
なお、ここはそのPCのシェルによって出力が異なるので、実際のインストーラの出力内容を参考にすること。
下記はzshの場合

%  echo 'export PATH="/usr/local/opt/libpq/bin:$PATH"' >> ~/.zshrc

現在のシェルでこのあと引き続き設定を行う場合は、以下のようにする。これで現在のシェルにも環境変数が反映される。

%  export PATH="/usr/local/opt/libpq/bin:$PATH"

psql導入 (Linuxの場合)

Linuxにpsqlを導入する手順を下記に記す。
ここでは、PostgreSQLのクライアントを導入することになる。

1. psqlが導入されているか確認

未導入の場合はwhichコマンドが「no psql」というメッセージを出力する。

$ which psql
/usr/bin/which: no psql in (/usr/local/bin:/usr/bin:/usr/local/sbin:/usr/sbin:/home/apl05/.local/bin:/home/apl05/bin)
$

導入されている場合はpsqlのコマンドのパス名が出てくる。

$ which psql
/usr/bin/psql
$

2. 導入コマンドの準備

導入パスは時々変わるので、最新の導入パスいりのコマンドを下記のサイトで取得する。
https://www.postgresql.org/download/linux/redhat/

このリンクの真ん中あたりに、バージョン等を入れると適切なコマンドラインを生成してくれる場所がある。

今回の例では下記を指定

Select version → 12
Select platform → Red Hat Enterprise, CentOS, Scientiffic or Oracle version 7
Select architecture → x86_64

すると、「Copy, paste and run the relevant parts of the setup script:」という欄に下記のようにコマンドが出力される。

注意点
- コマンドの先頭に「sudo」とある。「sudo」ではなく「su -」や「sudo su -」して実行する場合は適宜置換する。
- 2つめのコマンドは、このまま実行すると postgreSQLクライアントではなく、postgreSQLサーバ が導入されてしまう。今回はクライアントでよいので、後ろの「-server」は削って実行する。
- 今回はクライアントでよいので、3つめ〜5つめのコマンドは実行しない。これらはpostgreSQLサーバが導入された後、それを自動起動させるコマンドである。

3. 導入コマンドの実行

1つめ

# Install the repository RPM:
sudo yum install -y https://download.postgresql.org/pub/repos/yum/reporpms/EL-7-x86_64/pgdg-redhat-repo-latest.noarch.rpm

2つめ

# Install PostgreSQL:
sudo yum install -y postgresql12-server
　→ 「sudo yum install -y postgresql12」だけを実行する。

3つめ〜5つめは実施しない

# Optionally initialize the database and enable automatic start:
sudo /usr/pgsql-12/bin/postgresql-12-setup initdb
sudo systemctl enable postgresql-12
sudo systemctl start postgresql-12

admin以外で接続

ibmcloudコマンドで、admin以外のユーザでログインする場合は、ibmcloudコマンドの引数に -u ユーザ名というオプションを追加する。

サービス接続情報を使って接続する場合、サービス接続情報「ibm_cloud_XXXXXX」を開くと、クレデンシャルの中に使うべきユーザIDとパスワードが記載されている。

ここの部分である。(ユーザ名、パスワードはマスキングしている)

    "postgres": {
      "authentication": {
        "method": "direct",
        "password": "15db54XXXXXXXXXXXXXXXXXXX",
        "username": "ibm_cloud_XXXXXX"
      },

ibmcloudコマンドでの実行例

$ ibmcloud cdb deployment-connections "Databases for PostgreSQL" -u ibm_cloud_XXXXXX --start
Database Password>>. <--パスワードを入力
psql (12.8、サーバ 12.7)
SSL 接続 (プロトコル: TLSv1.2、暗号化方式: ECDHE-RSA-AES256-GCM-SHA384、ビット長: 256、圧縮: オフ)
"help"でヘルプを表示します。

ibmclouddb=>

psqlでの実行例

1. 証明書保管

まずIBM CloudのUIで表示できる証明書をローカルに保管する。
証明書のファイル名と中身はそれぞれ「Overview」タブの下に表示されている。

2. 環境変数設定

その後、環境変数にユーザ名とパスワードを登録する。

$ USERNAME=ibm_cloud_XXXXXX
$ PASSWORD=15db54XXXXXXXXXXXXXXXXXXX

3. ログイン

その後ログイン操作を行う。ログインのコマンドはIBMCloudのUIに表示されるものをペーストすればよい。
なお、以下の例では証明書「CERTFILE」としている。

$ PGPASSWORD=$PASSWORD PGSSLROOTCERT=CERTFILE psql "host=XXXXXXXX port=31027 dbname=ibmclouddb user=$USERNAME sslmode=verify-full"
psql (12.8、サーバ 12.7)
SSL 接続 (プロトコル: TLSv1.2、暗号化方式: ECDHE-RSA-AES256-GCM-SHA384、ビット長: 256、圧縮: オフ)
"help"でヘルプを表示します。

ibmclouddb=>

4. ログイン情報確認

ibmclouddb=> \conninfo
データベース"ibmclouddb"にユーザ"admin"として、ホスト"XXXXXX"(アドレス"XXX.XXX.XXX.XXX")上のポート"31027"で接続しています。
SSL 接続 (プロトコル: TLSv1.2、暗号化方式: ECDHE-RSA-AES256-GCM-SHA384、ビット長: 256、圧縮: オフ)
ibmclouddb=>