ApsaraDB RDS：Gunakan ekstensi dblink dan postgres_fdw untuk operasi lintas database

Catatan penggunaan

Saat melakukan operasi lintas database di instance RDS yang menggunakan disk cloud, perhatikan hal-hal berikut:

• Jika instance PostgreSQL yang dikelola sendiri berada di instance Elastic Compute Service (ECS), dan instance ECS serta instance RDS Anda berada dalam VPC yang sama, Anda dapat langsung melakukan operasi lintas database.

• Untuk menghubungkan instance PostgreSQL yang dikelola sendiri ke instance Oracle atau MySQL yang berada di VPC berbeda, gunakan ekstensi oracle_fdw atau mysql_fdw.

• Jika ingin mengelola tabel di berbagai database pada instance RDS yang sama, perhatikan hal-hal berikut:

  • Disarankan untuk menetapkan parameter host secara eksplisit ke 127.0.0.1 daripada localhost. Ini membantu mencegah kegagalan koneksi yang terjadi pada instance RDS yang mendukung IPv6.

  • Disarankan untuk tidak mengonfigurasi parameter port secara eksplisit karena nomor port dapat berubah akibat pemeliharaan Alibaba Cloud atau perubahan spesifikasi. Jika parameter port tidak dikonfigurasi secara eksplisit, nilai default dari database digunakan saat terhubung, memastikan validitas koneksi.

  • Jika ingin mengonfigurasi parameter port secara eksplisit, disarankan untuk terlebih dahulu terhubung ke database, menjalankan pernyataan SHOW PORT; untuk memeriksa port, lalu mengonfigurasi parameter port.

• Tambahkan blok CIDR dari VPC tempat instance RDS Anda berada, seperti 172.XX.XX.XX/16, ke daftar putih alamat IP dari instance tujuan yang ingin dihubungkan.

  Catatan

  Anda dapat melihat blok CIDR dari VPC tempat instance RDS berada di halaman Database Connection konsol ApsaraDB RDS.

Gunakan ekstensi dblink

1. Buat ekstensi.

   create extension dblink;

2. Buat koneksi dblink.

   postgres=> select dblink_connect('<Nama Koneksi>', 'host=<Titik akhir internal dari instance tempat database tujuan berada di VPC yang sama> port=<Port listening internal dari instance tempat database tujuan berada di VPC yang sama> user=<Username untuk masuk ke database tujuan> password=<Password> dbname=<Nama database tujuan>');
   
   postgres=> SELECT * FROM dblink('<Nama Koneksi>', '<Pernyataan SQL>') as <Nama Tabel>(<Nama Kolom> <Tipe Kolom>); 

   Contoh

   postgres=> select dblink_connect('a', 'host=pgm-bpxxxxx.pg.rds.aliyuncs.com port=3433 user=testuser2 password=passwd1234 dbname=postgres'); 
   
   
   postgres=> select * from dblink('a', select * from products') as T(id int,name text,price numeric); //Query tabel di database tujuan.
   
                           

Untuk informasi lebih lanjut, lihat dblink.

Gunakan ekstensi postgres_fdw

1. Buat database.

   postgres=> create database <Nama Database>; //Buat database.
   
   postgres=> \c <Nama Database> //Pindah ke database.

   Contoh

   postgres=> create database db1;  
   CREATE DATABASE  
   
   postgres=> \c db1  

2. Buat ekstensi postgres_fdw.

   db1=> create extension postgres_fdw;

3. Buat objek server yang digunakan untuk terhubung ke database tujuan.

   db1=> CREATE SERVER <Nama Objek Server>                                                              
           FOREIGN DATA WRAPPER postgres_fdw  
           OPTIONS (host '<Titik akhir internal dari instance tempat database tujuan berada di VPC yang sama>,port '<Port listening internal dari instance tempat database tujuan berada di VPC yang sama>', dbname '<Nama database tujuan di VPC yang sama>');  
   
   db1=> CREATE USER MAPPING FOR <Username yang digunakan untuk masuk ke database sumber>      
           SERVER <Nama Objek Server> 
           OPTIONS (user '<Username yang digunakan untuk masuk ke database tujuan>', password '<Password yang digunakan untuk masuk ke database tujuan>');

   Contoh

   db1=> CREATE SERVER foreign_server1                                                              
           FOREIGN DATA WRAPPER postgres_fdw  
           OPTIONS (host 'pgm-bpxxxxx.pg.rds.aliyuncs.com', port '3433', dbname 'postgres');  
   CREATE SERVER  
   
   db1=> CREATE USER MAPPING FOR testuser      
           SERVER foreign_server1  
           OPTIONS (user 'testuser2', password 'passwd1234');  
   CREATE USER MAPPING  

4. Impor tabel asing.

   db1=> import foreign schema public from server foreign_server1 into <Nama Skema>; //Impor tabel asing.
   
   db1=> select * from <Nama Skema>.<Nama Tabel>      //Tabel di database tujuan.

   Contoh

   db1=> import foreign schema public from server foreign_server1 into ft;  
   IMPORT FOREIGN SCHEMA  
   
   db1=> select * from ft.products;  

Untuk informasi lebih lanjut, lihat postgres_fdw.

FAQ

Ketika saya menggunakan ekstensi postgres_fdw untuk mengakses tabel asing yang dipartisi, bagaimana cara mengimpor tabel?

Cukup impor nama tabel yang dipartisi ke instance tujuan.

Kode berikut memberikan contoh cara mengimpor tabel partisi bernama Range Partition:

-- Database sumber pada instance sumber.
CREATE TABLE sales (id int, p_name text, amount int, sale_date date) PARTITION BY RANGE (sale_date);
CREATE TABLE sales_2022_Q1 PARTITION OF sales FOR VALUES FROM ('2022-01-01') TO ('2022-03-31');
CREATE TABLE sales_2022_Q2 PARTITION OF sales FOR VALUES FROM ('2022-04-01') TO ('2022-06-30');
CREATE TABLE sales_2022_Q3 PARTITION OF sales FOR VALUES FROM ('2022-07-01') TO ('2022-09-30');
CREATE TABLE sales_2022_Q4 PARTITION OF sales FOR VALUES FROM ('2022-10-01') TO ('2022-12-31');

INSERT INTO sales VALUES (1,'prod_A',100,'2022-02-02');
INSERT INTO sales VALUES (2,'prod_B', 5,'2022-05-02');
INSERT INTO sales VALUES (3,'prod_C', 5,'2022-08-02');
INSERT INTO sales VALUES (4,'prod_D', 5,'2022-11-02');

-- Impor hanya nama tabel yang dipartisi ke instance RDS tujuan. 
import FOREIGN SCHEMA public limit to (sales) from server pg_fdw_server into public;
select * from sales;

Gambar berikut menunjukkan contoh keluaran.