OCI Database with PostgreSQLを試してみる

# はじめに
OCIの新サービスとしてPostgreSQLのマネージドサービスがGAになりましたので、試してみます。
ちなみに、私はこれまでPostgreSQLの経験はありません。

https://docs.public.oneportal.content.oci.oraclecloud.com/en-us/iaas/releasenotes/changes/9a4b73b5-d4d6-4c89-bd31-b1fa2098fa34/

# ポリシーの設定

必要に応じてポリシーを設定します。

https://docs.oracle.com/en-us/iaas/Content/postgresql/getting-started.htm#iam

# DBの作成

https://docs.oracle.com/en-us/iaas/Content/postgresql/create-db.htm

OCIコンソールの`データベース`→`PostgreSQL`→`データベース`をクリックし、`PostgreSQLデータベースの作成`をクリックします。


`新規データベースの作成`をクリック

![スクリーンショット 2023-11-15 12.51.40.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/492530/9551ef89-b2ee-60ed-6fb3-144aba0355cf.png)

## データベース構成
任意の名称を指定します。
バージョンは現時点では`14`のみが選択できます。
![スクリーンショット 2023-11-15 12.53.32.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/492530/1e82280e-ffd8-c91b-00f1-ef7f589b1473.png)

## データベース・システム
ノード数の上限はマニュアルに記載はありませんが、最大8ノードまで設定できるようです。今回は1ノードにしています。
2ノード以上の場合は、1つがプライマリでそのほかはリードレプリカになります。
リードレプリカはプライマリにPromoteできます。（自動で？）

>Any read replica node can be promoted to the primary node.

Performance Tiersは現時点では300K IOPSのみ選択できます。

![スクリーンショット 2023-11-15 12.56.41.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/492530/7ef6c3b6-5da0-16b2-7e47-ed215d00e286.png)

## ハードウェアの構成

インスタンスのシェイプはFlexibleシェイプですが、CPUに合わせてメモリも自動で変更になります。
最小が2 OCPU、最大が64 OCPUです。（2, 4, 8, 16, 32, 64）
また、E4インスタンス（AMD）のみが利用できます。

![スクリーンショット 2023-11-15 13.11.05.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/492530/a8ff8198-c78a-cf8f-331b-562d41bd3a97.png)

## ネットワーキングの設定

プロビジョニングするVCNを選択します。パブリックサブネットも選択できますが、プライベートサブネットにのみプロビジョニングできます。
（パブリックサブネットを選択した場合、エラーになります）

![スクリーンショット 2023-11-15 13.16.47.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/492530/1428d254-175d-59a8-8b6d-473dd40ae863.png)

## データベース管理者資格証明

管理ユーザを設定します。
また、パスワードはコンソールで設定、またはVaultを使用します。今回は事前に作成したVaultとSecretを使用します。
パスワードの条件は以下になります。Secretに設定するパスワードもこの要件を満たす必要があります。

>8文字から32文字までの長さで、大文字、小文字、数字および特殊文字をそれぞれ1つ以上含める必要があります。

![スクリーンショット 2023-11-15 13.20.23.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/492530/8814952c-21ea-e5a7-1e69-66fdbb144930.png)

## 管理ポリシー
バックアップとメンテナンスの設定です。今回はデフォルトのままです。

![スクリーンショット 2023-11-15 13.24.33.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/492530/14244c6d-a0b9-7de9-52b8-3c4ed55ab14c.png)

設定は以上で、サマリーを確認してDBを作成します。

しばらく待ってStatusがActiveになることを確認します。
後ほどDBに接続する際に必要になるEndpointをメモしておきます。

![スクリーンショット 2023-11-15 13.30.42.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/492530/6af48ee9-feaf-6760-01cd-41a4170a873b.png)


# DBへの接続

## PostgreSQL CLIのインストール
こちらを参考にPostgreSQL CLIをOracle Linuxのサーバにインストールします。

https://docs.oracle.com/en-us/iaas/Content/postgresql/connect-to-db.htm

こちらでOSと利用するバージョン、アーキテクチャを選択するとインストールスクリプトが表示されます。

https://www.postgresql.org/download/

今回はこちらのスクリプトでインストールします。

```install.sh
#!/bin/bash

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
sudo /usr/pgsql-14/bin/postgresql-14-setup initdb
sudo systemctl enable postgresql-14
sudo systemctl start postgresql-14
```

```
$ chmod +x install.sh 
$ ./install.sh 
This system is receiving updates from OSMS server.
Last metadata expiration check: 1:39:55 ago on Wed 15 Nov 2023 01:21:30 AM GMT.
pgdg-redhat-repo-latest.noarch.rpm                                                                                 7.6 kB/s |  13 kB     00:01    
Dependencies resolved.
===================================================================================================================================================
 Package                                Architecture                 Version                              Repository                          Size
===================================================================================================================================================
Installing:
 pgdg-redhat-repo                       noarch                       42.0-35PGDG                          @commandline                        13 k

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

Total size: 13 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-35PGDG.noarch                                                                                       1/1 
  Verifying        : pgdg-redhat-repo-42.0-35PGDG.noarch                                                                                       1/1 

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

Complete!
Importing GPG key 0x442DF0F8:
 Userid     : "PostgreSQL RPM Building Project <pgsql-pkg-yum@postgresql.org>"
 Fingerprint: 68C9 E2B9 1A37 D136 FE74 D176 1F16 D2E1 442D F0F8
 From       : /etc/pki/rpm-gpg/RPM-GPG-KEY-PGDG
Importing GPG key 0x442DF0F8:
 Userid     : "PostgreSQL RPM Building Project <pgsql-pkg-yum@postgresql.org>"
 Fingerprint: 68C9 E2B9 1A37 D136 FE74 D176 1F16 D2E1 442D F0F8
 From       : /etc/pki/rpm-gpg/RPM-GPG-KEY-PGDG
Importing GPG key 0x442DF0F8:
 Userid     : "PostgreSQL RPM Building Project <pgsql-pkg-yum@postgresql.org>"
 Fingerprint: 68C9 E2B9 1A37 D136 FE74 D176 1F16 D2E1 442D F0F8
 From       : /etc/pki/rpm-gpg/RPM-GPG-KEY-PGDG
Importing GPG key 0x442DF0F8:
 Userid     : "PostgreSQL RPM Building Project <pgsql-pkg-yum@postgresql.org>"
 Fingerprint: 68C9 E2B9 1A37 D136 FE74 D176 1F16 D2E1 442D F0F8
 From       : /etc/pki/rpm-gpg/RPM-GPG-KEY-PGDG
Importing GPG key 0x442DF0F8:
 Userid     : "PostgreSQL RPM Building Project <pgsql-pkg-yum@postgresql.org>"
 Fingerprint: 68C9 E2B9 1A37 D136 FE74 D176 1F16 D2E1 442D F0F8
 From       : /etc/pki/rpm-gpg/RPM-GPG-KEY-PGDG
Importing GPG key 0x442DF0F8:
 Userid     : "PostgreSQL RPM Building Project <pgsql-pkg-yum@postgresql.org>"
 Fingerprint: 68C9 E2B9 1A37 D136 FE74 D176 1F16 D2E1 442D F0F8
 From       : /etc/pki/rpm-gpg/RPM-GPG-KEY-PGDG
Importing GPG key 0x442DF0F8:
 Userid     : "PostgreSQL RPM Building Project <pgsql-pkg-yum@postgresql.org>"
 Fingerprint: 68C9 E2B9 1A37 D136 FE74 D176 1F16 D2E1 442D F0F8
 From       : /etc/pki/rpm-gpg/RPM-GPG-KEY-PGDG
This system is receiving updates from OSMS server.
Last metadata expiration check: 0:00:15 ago on Wed 15 Nov 2023 03:02:25 AM GMT.
Dependencies resolved.
===================================================================================================================================================
 Package                              Architecture            Version                              Repository                                 Size
===================================================================================================================================================
Installing:
 postgresql14-server                  x86_64                  14.10-1PGDG.rhel8                    pgdg14                                    5.7 M
Installing dependencies:
 lz4                                  x86_64                  1.8.3-3.el8_4                        ol8_baseos_latest-x86_64                  103 k
 postgresql14                         x86_64                  14.10-1PGDG.rhel8                    pgdg14                                    1.5 M
 postgresql14-libs                    x86_64                  14.10-1PGDG.rhel8                    pgdg14                                    280 k

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

Total download size: 7.6 M
Installed size: 32 M
Downloading Packages:
(1/4): lz4-1.8.3-3.el8_4.x86_64.rpm                                                                                4.8 MB/s | 103 kB     00:00    
(2/4): postgresql14-libs-14.10-1PGDG.rhel8.x86_64.rpm                                                              103 kB/s | 280 kB     00:02    
(3/4): postgresql14-14.10-1PGDG.rhel8.x86_64.rpm                                                                   463 kB/s | 1.5 MB     00:03    
(4/4): postgresql14-server-14.10-1PGDG.rhel8.x86_64.rpm                                                            1.3 MB/s | 5.7 MB     00:04    
---------------------------------------------------------------------------------------------------------------------------------------------------
Total                                                                                                              1.7 MB/s | 7.6 MB     00:04     
PostgreSQL 14 for RHEL / Rocky 8 - x86_64                                                                          1.6 MB/s | 1.7 kB     00:00    
Importing GPG key 0x442DF0F8:
 Userid     : "PostgreSQL RPM Building Project <pgsql-pkg-yum@postgresql.org>"
 Fingerprint: 68C9 E2B9 1A37 D136 FE74 D176 1F16 D2E1 442D F0F8
 From       : /etc/pki/rpm-gpg/RPM-GPG-KEY-PGDG
Key imported successfully
Running transaction check
Transaction check succeeded.
Running transaction test
Transaction test succeeded.
Running transaction
  Preparing        :                                                                                                                           1/1 
  Installing       : postgresql14-libs-14.10-1PGDG.rhel8.x86_64                                                                                1/4 
  Running scriptlet: postgresql14-libs-14.10-1PGDG.rhel8.x86_64                                                                                1/4 
  Installing       : lz4-1.8.3-3.el8_4.x86_64                                                                                                  2/4 
  Installing       : postgresql14-14.10-1PGDG.rhel8.x86_64                                                                                     3/4 
  Running scriptlet: postgresql14-14.10-1PGDG.rhel8.x86_64                                                                                     3/4 
  Running scriptlet: postgresql14-server-14.10-1PGDG.rhel8.x86_64                                                                              4/4 
  Installing       : postgresql14-server-14.10-1PGDG.rhel8.x86_64                                                                              4/4 
  Running scriptlet: postgresql14-server-14.10-1PGDG.rhel8.x86_64                                                                              4/4 
  Verifying        : lz4-1.8.3-3.el8_4.x86_64                                                                                                  1/4 
  Verifying        : postgresql14-14.10-1PGDG.rhel8.x86_64                                                                                     2/4 
  Verifying        : postgresql14-libs-14.10-1PGDG.rhel8.x86_64                                                                                3/4 
  Verifying        : postgresql14-server-14.10-1PGDG.rhel8.x86_64                                                                              4/4 

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

Complete!
Initializing database ... OK

Created symlink /etc/systemd/system/multi-user.target.wants/postgresql-14.service → /usr/lib/systemd/system/postgresql-14.service.
```

確認します。

```
$ psql -V
psql (PostgreSQL) 14.10
```

## DBへの接続

コンソールで確認したEndpointとDB作成時に設定した管理ユーザ（今回はadmin）を指定して接続します。
パスワードはSecretに設定したパスワードです。


```
$ psql -h <endpoint_IP> -U <admin_username> -d postgres
Password for user admin: 
psql (14.10, server 14.9)
SSL connection (protocol: TLSv1.2, cipher: ECDHE-RSA-AES256-GCM-SHA384, bits: 256, compression: off)
Type "help" for help.

postgres=> 
```

## テーブルの確認と作成
PostgreSQLのシステムカタログの一つであるpg_tablesを確認します。

```
postgres=> select schemaname, tablename, tableowner from pg_tables;
     schemaname     |        tablename        |  tableowner   
--------------------+-------------------------+---------------
 pg_catalog         | pg_statistic            | oci_superuser
 pg_catalog         | pg_type                 | oci_superuser
 pg_catalog         | pg_foreign_table        | oci_superuser
 pg_catalog         | pg_authid               | oci_superuser
 pg_catalog         | pg_statistic_ext_data   | oci_superuser
 pg_catalog         | pg_user_mapping         | oci_superuser
 pg_catalog         | pg_subscription         | oci_superuser
 pg_catalog         | pg_attribute            | oci_superuser
 pg_catalog         | pg_proc                 | oci_superuser
 pg_catalog         | pg_class                | oci_superuser
 pg_catalog         | pg_attrdef              | oci_superuser
 pg_catalog         | pg_constraint           | oci_superuser
 pg_catalog         | pg_inherits             | oci_superuser
 pg_catalog         | pg_index                | oci_superuser
 pg_catalog         | pg_operator             | oci_superuser
 pg_catalog         | pg_opfamily             | oci_superuser
 pg_catalog         | pg_opclass              | oci_superuser
 pg_catalog         | pg_am                   | oci_superuser
 pg_catalog         | pg_amop                 | oci_superuser
 pg_catalog         | pg_amproc               | oci_superuser
 pg_catalog         | pg_language             | oci_superuser
 pg_catalog         | pg_largeobject_metadata | oci_superuser
 pg_catalog         | pg_aggregate            | oci_superuser
 pg_catalog         | pg_statistic_ext        | oci_superuser
 pg_catalog         | pg_rewrite              | oci_superuser
 pg_catalog         | pg_trigger              | oci_superuser
 pg_catalog         | pg_event_trigger        | oci_superuser
 pg_catalog         | pg_description          | oci_superuser
 pg_catalog         | pg_cast                 | oci_superuser
 pg_catalog         | pg_enum                 | oci_superuser
 pg_catalog         | pg_namespace            | oci_superuser
 pg_catalog         | pg_conversion           | oci_superuser
 pg_catalog         | pg_depend               | oci_superuser
 pg_catalog         | pg_database             | oci_superuser
 pg_catalog         | pg_db_role_setting      | oci_superuser
 pg_catalog         | pg_tablespace           | oci_superuser
 pg_catalog         | pg_auth_members         | oci_superuser
 pg_catalog         | pg_shdepend             | oci_superuser
 pg_catalog         | pg_shdescription        | oci_superuser
 pg_catalog         | pg_ts_config            | oci_superuser
 pg_catalog         | pg_ts_config_map        | oci_superuser
 pg_catalog         | pg_ts_dict              | oci_superuser
 pg_catalog         | pg_ts_parser            | oci_superuser
 pg_catalog         | pg_ts_template          | oci_superuser
 pg_catalog         | pg_extension            | oci_superuser
 pg_catalog         | pg_foreign_data_wrapper | oci_superuser
 pg_catalog         | pg_foreign_server       | oci_superuser
 pg_catalog         | pg_policy               | oci_superuser
 pg_catalog         | pg_replication_origin   | oci_superuser
 pg_catalog         | pg_default_acl          | oci_superuser
 pg_catalog         | pg_init_privs           | oci_superuser
 pg_catalog         | pg_seclabel             | oci_superuser
 pg_catalog         | pg_shseclabel           | oci_superuser
 pg_catalog         | pg_collation            | oci_superuser
 pg_catalog         | pg_partitioned_table    | oci_superuser
 pg_catalog         | pg_range                | oci_superuser
 pg_catalog         | pg_transform            | oci_superuser
 pg_catalog         | pg_sequence             | oci_superuser
 pg_catalog         | pg_publication          | oci_superuser
 pg_catalog         | pg_publication_rel      | oci_superuser
 pg_catalog         | pg_subscription_rel     | oci_superuser
 pg_catalog         | pg_largeobject          | oci_superuser
 information_schema | sql_parts               | oci_superuser
 information_schema | sql_implementation_info | oci_superuser
 information_schema | sql_features            | oci_superuser
 information_schema | sql_sizing              | oci_superuser
(66 rows)
```

新規のテーブルを作成してインサートします。

```
postgres=> CREATE TABLE accounts (
postgres(> user_id serial primary key,
postgres(> username VARCHAR ( 50 ) UNIQUE NOT NULL
postgres(> );
CREATE TABLE
postgres=> insert into accounts (user_id, username)
postgres-> values ('100','tanaka');
INSERT 0 1
postgres=> select * from accounts;
 user_id | username 
---------+----------
     100 | tanaka
(1 row)
```

初めてのPostgreSQLでしたが、プロビジョニングからテーブル作成、インサートまでできました。