はじめに
Autonomous DatabaseからAmazon Redshift、MySQL、PostgreSQL、SnowflakeといったOracle以外のデータベースに対してデーターベース・リンクが作成できるようになりました。
現時点では、以下の前提条件があります。
・ターゲット・データベースがパブリック・インターネットからアクセスできること
・ターゲット・データベースがSSL/TLS接続を許可する設定になっていること
ということで、今回はAutonomous DatabaseからAurora PostgreSQLに対してデータベース・リンクを作成し、Autonomous DatabaseからAurora PostgreSQL内のデータを参照してみました。
1.Aurora PostgreSQLの準備
今回はAWSの東京リージョン（ap-northeast-1）にAurora PostgreSQLを作成し、パブリック・インターネットからの接続を許可しました。
Aurora PostgreSQLはデフォルトでSSL/TLS接続を許可する設定になっています。
psqlコマンドでAurora PostgreSQLに接続します。
[opc@pg ~]$ psql -U postgres -h xxxxxx-instance-1.aaaaaaaaaaaa.ap-northeast-1.rds.amazonaws.com
Password for user postgres:
psql (14.2, server 13.6)
SSL connection (protocol: TLSv1.2, cipher: ECDHE-RSA-AES256-GCM-SHA384, bits: 256, compression: off)
Type "help" for help.
postgres=>
データベース testdb を作成します
postgres=> CREATE DATABASE testdb;
CREATE DATABASE
postgres=> \l
List of databases
Name | Owner | Encoding | Collate | Ctype | Access privileges
-----------+----------+----------+-------------+-------------+-----------------------
postgres | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 |
rdsadmin | rdsadmin | UTF8 | en_US.UTF-8 | en_US.UTF-8 | rdsadmin=CTc/rdsadmin
template0 | rdsadmin | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/rdsadmin +
| | | | | rdsadmin=CTc/rdsadmin
template1 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/postgres +
| | | | | postgres=CTc/postgres
testdb | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 |
(5 rows)
postgres=>
接続先を testdb に切り替えます。
postgres=> \c testdb
psql (14.2, server 13.6)
SSL connection (protocol: TLSv1.2, cipher: ECDHE-RSA-AES256-GCM-SHA384, bits: 256, compression: off)
You are now connected to database "testdb" as user "postgres".
testdb=>
テーブル testtable を作成し、データを追加します。
testdb=> CREATE TABLE testtable (
testdb(> id INTEGER,
testdb(> name VARCHAR(100)
testdb(> );
CREATE TABLE
testdb=> INSERT INTO testtable VALUES (1,'Name1');
INSERT 0 1
testdb=> INSERT INTO testtable VALUES (2,'Name2');
INSERT 0 1
testdb=> INSERT INTO testtable VALUES (3,'Name3');
INSERT 0 1
testdb=> select * from public.testtable;
id | name
----+-------
1 | Name1
2 | Name2
3 | Name3
(3 rows)
testdb=>
psqlを終了します。
testdb=> exit
[opc@pg ~]$
2. Autonomous Databaseへの接続
SQL*Plusから東京リージョンにあるAutonomous Databaseに接続します。
[opc@pg ~]$ sqlplus admin/Demo#1Demo#1@testatp1_medium
SQL*Plus: Release 21.0.0.0.0 - Production on Wed Apr 6 07:04:30 2022
Version 21.5.0.0.0
Copyright (c) 1982, 2021, Oracle. All rights reserved.
Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.15.0.1.0
SQL>
3. Autonomous DatabaseからAurora PostgreSQLに接続するためのクレデンシャルの作成
DBMS_CLOUD.CREATE_CREDENTIALプロシージャを使用して、Autonomous DatabaseからAurora PostgreSQLに接続するためのクレデンシャル AURORA_PG_CRED を作成します。
usernameにPostgreSQLのユーザ名、passwordにそのユーザのパスワードを指定します。
SQL> BEGIN
2 DBMS_CLOUD.CREATE_CREDENTIAL(
3 credential_name => 'AURORA_PG_CRED',
4 username => 'postgres',
5 password => 'Demo#1Demo#1');
6 END;
7 /
PL/SQL procedure successfully completed.
SQL>
4. Autonomous DatabaseからAurora PostgreSQLに接続するデータベース・リンクの作成
DBMS_CLOUD_ADMIN.CREATE_DATABASE_LINK プロシージャを使用して、Autonomous DatabaseからAurora PostgreSQLに接続するデータベース・リンクを作成します。
DBMS_CLOUD_ADMIN.CREATE_DATABASE_LINKの各パラメータは以下のように指定します。
| 項目 | 値 |
|---|---|
| db_link_name | 作成するデータベース・リンクの名前 |
| hostname | RDSマネジメントコンソールに表示されるAurora PostgreSQLのエンドポイント |
| port | RDSマネジメントコンソールに表示されるAurora PostgreSQLのポート |
| service_name | データベース名 |
| credential_name | 3.で作成したAurora PostgreSQLに接続するためのクレデンシャルの名前 |
| gateway_params | 接続先DBに応じてdb_typeを設定、PostgreSQLの場合は'POSTGRES'。詳細はこちらを参照 |
| directory_name | NULL |
| ssl_server_cert_dn | NULL |
SQL> BEGIN
2 DBMS_CLOUD_ADMIN.CREATE_DATABASE_LINK(
3 db_link_name => 'AURORA_PG_LINK',
4 hostname => 'xxxxxx-instance-1.aaaaaaaaaaaa.ap-northeast-1.rds.amazonaws.com',
5 port => '5432',
6 service_name => 'testdb',
7 credential_name => 'AURORA_PG_CRED',
8 gateway_params => JSON_OBJECT('db_type' value 'POSTGRES'),
9 directory_name => NULL,
10 ssl_server_cert_dn => NULL);
11 END;
12 /
PL/SQL procedure successfully completed.
SQL>
5. Autonomous Databaseからデータベース・リンク経由でAurora PostgreSQL内のデータを参照
Autonomous DatabaseからAurora PostgreSQLへのデータベースリンクが作成できたので、Autonomous Databaseからデータベース・リンク経由でAurora PostgreSQL内のデータを参照してみます。
参照先のテーブルを指定する際は、"testtable"のようにテーブル名を小文字で記述してダブルクウォートで囲む必要があります。
スキーマを指定してテーブルを参照する際は、同様に、"スキーマ名"."テーブル名"のように記述する必要があります。
SQL> col name for a20
SQL> SELECT * FROM "testtable"@AURORA_PG_LINK;
id name
---------- --------------------
1 Name1
2 Name2
3 Name3
SQL>
Autonomous Databaseからデータベース・リンク経由でAurora PostgreSQL内のデータを参照することができました。
参考
Create Database Links to Non-Oracle Databases with Oracle-Managed Heterogeneous Connectivity
CREATE_CREDENTIAL Procedure
CREATE_DATABASE_LINK Procedure
Access Amazon Redshift, Snowflake and Other Non-Oracle Databases from Your Autonomous Database