OCIでPostgreSQLインスタンスの作成

　検証のためOCIのPostgreSQLインスタンスを作成したので備忘までメモを残しておく。細かな手順は別として、実際に構築してみてわかったのは以下の点である。

• OSにログインはできない。psql等クライアントからの接続となる
• バージョンは14.9（現時点で他に選択肢はない）
• リードレプリカを作成可能（未検証）
• postgresql.confは直接編集不可
• ノード停止できない（起動したままとなる）
• シェイプの変更はできない

実施した内容は以下の通り。詳細な手順は参考[1]のマニュアルを参照されたい。

• 1.準備
• 2.PostgreSQLインスタンス作成
• 3.クライアントからの接続
• 4.設定変更（timezone)

1.準備

　前提として、パブリックネットワークとプライベートネットワークから構成されるVCNがあるとする。PostgreSQLへ接続するコンピュートVMはパブリックネットワーク、PostgreSQLインスタンスはプライベートネットワークに配置するものとする。このため、プライベートネットワークのセキュリティリストにPostgreSQLの通信用に5432ポートの通信許可を追加する。

　パブリックネットワークのクライアントVMには、PostgreSQLのクライアントを構成しておく。今回実施した手順は以下の通り（参考[2]）。結果、psqlが使えることを確認する。

sudo dnf install -y https://download.postgresql.org/pub/repos/yum/reporpms/EL-8-x86_64/pgdg-redhat-repo-latest.noarch.rpm
sudo dnf -qy module disable postgresql
sudo dnf install -y postgresql14-server

2.PostgreSQLインスタンス作成

　OCIコンソールのPostgreSQLメニューから、Create Database systemを選択し、以下を入力する。

• Database system name: pg14
• PostgreSQL major version: 14 （固定）
• Node count: 1 (リードレプリカなし）
• Performance tier: 300K IOPS (固定）
• OCPU count: 2 (2,4,8,16,32,64から選択）
• Network configuration: VCNとプライベートネットワーク、IPアドレスを指定
• administrator credentials: pgadmin/xxxを指定　（OCI Vaultが利用可能）
• Automatic backups: Enable/Daily/retention 1 dayを選択
• Maintenance: set by oracleを指定（schdule your own maintenanceも選択可能）
• Configuration: PostgreSQL.VM.Standard.E4.Flex.2.32GB-14-0_34 を指定（シェイプで決定される）

　レビュー画面で内容を確認しSubmitボタンを押下すると10分程度でインスタンス作成が完了しACTIVE状態になる。Connection detailから、EndpointのFQDNを確認しておく（今回は検証のためIPアドレスを直接指定する）。バックアップを取得しておく。

　設定（postgresql.conf）はシェイプ毎にOracleお勧めの設定がすでに用意されているので、普通に使うだけなら特に変更の必要はない。configurationメニューから設定されているパラメータセットの内容を確認することができる。ただ、ここで直接パラメータの変更はできない。パラメータセットを新規作成すると値を入力できるようだが、1つずつ入れていくのは現実的ではないので、何か良い方法があるのだろう。参考[4]に今回作成したpostgreSQLのパラメータを載せておく。

3.クライアントからの接続

　クライアントのコンピュートVMから、psqlで以下の通り接続する。

[opc@bastvm1 ~]$ psql -h 10.0.1.XX -U pgadmin -d postgres
Password for user pgadmin:
psql (14.11, server 14.9)
SSL connection (protocol: TLSv1.2, cipher: ECDHE-RSA-AES256-GCM-SHA384, bits: 256, compression: off)
Type "help" for help.

postgres=> \l
                                         List of databases
   Name    |     Owner     | Encoding |   Collate   |    Ctype    |        Access privileges
-----------+---------------+----------+-------------+-------------+---------------------------------
 postgres  | pgadmin       | UTF8     | en_US.UTF-8 | en_US.UTF-8 |
 template0 | oci_superuser | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/oci_superuser               +
           |               |          |             |             | oci_superuser=CTc/oci_superuser
 template1 | oci_superuser | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/oci_superuser               +
           |               |          |             |             | oci_superuser=CTc/oci_superuser
(3 rows)

postgres=>

必要に応じて.pgpassを設定しておくと、パスワードを聞かれずに済むので楽になる。

[opc@bastvm1 ~]$ cat .pgpass
10.0.1.XX:5432:postgres:pgadmin:password
[opc@bastvm1 ~]$ ll .pgpass
-rw-------. 1 opc opc 45 Mar  3 05:13 .pgpass

4.設定変更

　検証用のDBを作成する。タイムゾーンがデフォルトUTCのため、Asia/Tokyoに変更する。postgresql.confを直接変更したいところだが、コンソールから直接変更することができないため、alter databaseで変更した。

[opc@bastvm1 ~]$ psql -h 10.0.1.XX -U pgadmin -d postgres
Password for user pgadmin:
...
postgres=> create database pgdb;
CREATE DATABASE
postgres=> \l
                                         List of databases
   Name    |     Owner     | Encoding |   Collate   |    Ctype    |        Acces
s privileges        
-----------+---------------+----------+-------------+-------------+-------------
--------------------
 pgdb★    | pgadmin       | UTF8     | en_US.UTF-8 | en_US.UTF-8 | 
 postgres  | pgadmin       | UTF8     | en_US.UTF-8 | en_US.UTF-8 | 
...(4 rows)

pgdb=> alter database pgdb set timezone='Asia/Tokyo';
ALTER DATABASE
postgres=> exit
[opc@bastvm1 ~]$ psql -h 10.0.1.XX -U pgadmin -d pgdb
Password for user pgadmin:
...
pgdb=> select now();
              now
-------------------------------
 2024-03-03 07:17:27.088856+09
(1 row)

pgdb=> show timezone;
  TimeZone
------------
 Asia/Tokyo
(1 row)

5.まとめ

　本稿ではOCIでPostgreSQLインスタンスを作成しクライアントから接続する手順を確認した。今回は単純なシングルインスタンスであったが、簡単にリードレプリカのノードを追加もできるようなので、時間があれば検証してみたい。さすがにOracleのBaseDBと比較すると、バージョンが固定であったり、CPUのスケーリングや停止ができないことなど制約があると感じた。postgresql.confの設定変更については便利な方法が望まれる。

参考

[1]OCI Database with PostgreSQL
[2]PostgreSQL Linux downloads (Red Hat family)
[3]PostgreSQLクライアントの構成例

[root@bastvm1 opc]# sudo dnf install -y https://download.postgresql.org/pub/repos/yum/reporpms/EL-8-x86_64/pgdg-redhat-repo-latest.noarch.rpm
Last metadata expiration check: 0:06:25 ago on Sun 03 Mar 2024 04:32:27 AM JST.
pgdg-redhat-repo-latest.noarch.rpm               11 kB/s |  14 kB     00:01
Dependencies resolved.
================================================================================
 Package                Architecture Version           Repository          Size
================================================================================
Installing:
 pgdg-redhat-repo       noarch       42.0-38PGDG       @commandline        14 k

Transaction Summary
================================================================================
Install  1 Package

Total size: 14 k
Installed size: 15 k
Downloading Packages:
Running transaction check
Transaction check succeeded.
Running transaction test
Transaction test succeeded.
Running transaction
  Preparing        :                                                        1/1
  Installing       : pgdg-redhat-repo-42.0-38PGDG.noarch                    1/1
  Verifying        : pgdg-redhat-repo-42.0-38PGDG.noarch                    1/1

Installed:
  pgdg-redhat-repo-42.0-38PGDG.noarch

Complete!
[root@bastvm1 opc]#
[root@bastvm1 opc]# sudo dnf -qy module disable postgresql
Importing GPG key 0x08B40D20:
 Userid     : "PostgreSQL RPM Repository "
 Fingerprint: D4BF 08AE 67A0 B4C7 A1DB CCD2 40BC A2B4 08B4 0D20
 From       : /etc/pki/rpm-gpg/PGDG-RPM-GPG-KEY-RHEL
Importing GPG key 0x08B40D20:
 Userid     : "PostgreSQL RPM Repository "
 Fingerprint: D4BF 08AE 67A0 B4C7 A1DB CCD2 40BC A2B4 08B4 0D20
 From       : /etc/pki/rpm-gpg/PGDG-RPM-GPG-KEY-RHEL
Importing GPG key 0x08B40D20:
 Userid     : "PostgreSQL RPM Repository "
 Fingerprint: D4BF 08AE 67A0 B4C7 A1DB CCD2 40BC A2B4 08B4 0D20
 From       : /etc/pki/rpm-gpg/PGDG-RPM-GPG-KEY-RHEL
Importing GPG key 0x08B40D20:
 Userid     : "PostgreSQL RPM Repository "
 Fingerprint: D4BF 08AE 67A0 B4C7 A1DB CCD2 40BC A2B4 08B4 0D20
 From       : /etc/pki/rpm-gpg/PGDG-RPM-GPG-KEY-RHEL
Importing GPG key 0x08B40D20:
 Userid     : "PostgreSQL RPM Repository "
 Fingerprint: D4BF 08AE 67A0 B4C7 A1DB CCD2 40BC A2B4 08B4 0D20
 From       : /etc/pki/rpm-gpg/PGDG-RPM-GPG-KEY-RHEL
Importing GPG key 0x08B40D20:
 Userid     : "PostgreSQL RPM Repository "
 Fingerprint: D4BF 08AE 67A0 B4C7 A1DB CCD2 40BC A2B4 08B4 0D20
 From       : /etc/pki/rpm-gpg/PGDG-RPM-GPG-KEY-RHEL
[root@bastvm1 opc]#
[root@bastvm1 opc]# sudo dnf install -y postgresql14-server
Last metadata expiration check: 0:03:35 ago on Sun 03 Mar 2024 04:40:25 AM JST.
Dependencies resolved.
================================================================================
 Package               Arch     Version               Repository           Size
================================================================================
Installing:
 postgresql14-server   x86_64   14.11-1PGDG.rhel8     pgdg14              5.7 M
Installing dependencies:
 lz4                   x86_64   1.8.3-3.el8_4         ol8_baseos_latest   103 k
 postgresql14          x86_64   14.11-1PGDG.rhel8     pgdg14              1.5 M
 postgresql14-libs     x86_64   14.11-1PGDG.rhel8     pgdg14              281 k

Transaction Summary
================================================================================
Install  4 Packages

Total download size: 7.6 M
Installed size: 32 M
Downloading Packages:
(1/4): postgresql14-libs-14.11-1PGDG.rhel8.x86_ 111 kB/s | 281 kB     00:02
(2/4): postgresql14-14.11-1PGDG.rhel8.x86_64.rp 604 kB/s | 1.5 MB     00:02
(3/4): lz4-1.8.3-3.el8_4.x86_64.rpm              32 kB/s | 103 kB     00:03
(4/4): postgresql14-server-14.11-1PGDG.rhel8.x8 2.6 MB/s | 5.7 MB     00:02
--------------------------------------------------------------------------------
Total                                           1.6 MB/s | 7.6 MB     00:04
PostgreSQL 14 for RHEL / Rocky / AlmaLinux 8 -  2.4 MB/s | 2.4 kB     00:00
Importing GPG key 0x08B40D20:
 Userid     : "PostgreSQL RPM Repository "
 Fingerprint: D4BF 08AE 67A0 B4C7 A1DB CCD2 40BC A2B4 08B4 0D20
 From       : /etc/pki/rpm-gpg/PGDG-RPM-GPG-KEY-RHEL
Key imported successfully
Running transaction check
Transaction check succeeded.
Running transaction test
Transaction test succeeded.
Running transaction
  Preparing        :                                                        1/1
  Installing       : postgresql14-libs-14.11-1PGDG.rhel8.x86_64             1/4
  Running scriptlet: postgresql14-libs-14.11-1PGDG.rhel8.x86_64             1/4
  Installing       : lz4-1.8.3-3.el8_4.x86_64                               2/4
  Installing       : postgresql14-14.11-1PGDG.rhel8.x86_64                  3/4
  Running scriptlet: postgresql14-14.11-1PGDG.rhel8.x86_64                  3/4
  Running scriptlet: postgresql14-server-14.11-1PGDG.rhel8.x86_64           4/4
  Installing       : postgresql14-server-14.11-1PGDG.rhel8.x86_64           4/4
  Running scriptlet: postgresql14-server-14.11-1PGDG.rhel8.x86_64           4/4
  Verifying        : lz4-1.8.3-3.el8_4.x86_64                               1/4
  Verifying        : postgresql14-14.11-1PGDG.rhel8.x86_64                  2/4
  Verifying        : postgresql14-libs-14.11-1PGDG.rhel8.x86_64             3/4
  Verifying        : postgresql14-server-14.11-1PGDG.rhel8.x86_64           4/4

Installed:
  lz4-1.8.3-3.el8_4.x86_64
  postgresql14-14.11-1PGDG.rhel8.x86_64
  postgresql14-libs-14.11-1PGDG.rhel8.x86_64
  postgresql14-server-14.11-1PGDG.rhel8.x86_64

Complete!
[root@bastvm1 opc]#
[root@bastvm1 opc]# which psql
/bin/psql

[4]PostgreSQLパラメータ一覧:20240303_postgresqlconf.txt