尝试使用OCI数据库与PostgreSQL
首先
OCI最新推出的服务中，现已推出了针对PostgreSQL的托管服务，我将尝试使用一下。
顺便提一下，我之前没有过使用PostgreSQL的经验。
设置政策
根据需要确定政策。
创建数据库。
在 OCI 控制台中，点击数据库→PostgreSQL→数据库，然后点击创建 PostgreSQL 数据库。
点击以创建新数据库
数据库架构
数据库系统
节点数的上限在手册中没有提及，但似乎最多可以设置为8个节点。本次我们设为1个节点。
如果超过2个节点，则一个节点将成为主节点，其余的将成为副本节点。
副本节点可以通过升级变成主节点。（自动进行？）
任何读副本节点都可以升级为主节点。
目前，性能层级只能选择300K IOPS。
硬件配置
实例的形状是灵活形状，但内存会根据CPU自动变化。
最小的CPU是2 OCPU，最大的CPU是64 OCPU。（2, 4, 8, 16, 32, 64）
此外，只有E4实例（AMD）可用。
网络设置
选择要配置的VCN。您可以选择公共子网，但只能在私有子网中配置。
（如果选择了公共子网，将导致错误）
数据库管理员资格证明
我们将设置管理用户。
同时，密码可以通过控制台进行设置，或者使用Vault。本次我们将使用事先创建的Vault和Secret。
以下是密码要求。Secret中的密码也必须符合这些要求。
密码长度必须介于8到32个字符之间，并且必须包含至少一个大写字母、一个小写字母、一个数字和一个特殊字符。
管理政策
这是关于备份和维护设置的内容。这次我们保持默认设置。
以上是设定内容，确认摘要并创建数据库。
稍等一下，确保状态已更改为”活动”。
我会在稍后连接到数据库时将需要的终端点记下来。
连接到数据库
安装PostgreSQL CLI。
请按照以下方法在Oracle Linux服务器上安装PostgreSQL CLI：
当您在此选择操作系统、版本和架构后，将显示安装脚本。
这次将使用此脚本进行安装。
#!/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
连接到数据库
使用控制台确认的终端点和在创建数据库时设置的管理用户（本次为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，但我已经完成了从配置到创建表和插入数据的过程。
