ApsaraDB RDS:Operasi lintas database (dblink, postgres_fdw)

Catatan Penggunaan

Perhatikan hal-hal berikut saat menggunakan dblink dan postgres_fdw untuk operasi lintas database:

• Instans ECS dan ApsaraDB RDS for PostgreSQL dalam VPC yang sama dapat langsung melakukan operasi lintas database.

• Instans PostgreSQL yang dikelola sendiri dapat menggunakan oracle_fdw atau mysql_fdw untuk terhubung ke instans Oracle atau MySQL di luar VPC.

• Saat terhubung ke database berbeda dalam instans yang sama:

  • Tetapkan host ke 127.0.0.1 alih-alih localhost untuk mencegah kegagalan koneksi pada instans yang mendukung IPv6.

  • Jangan menetapkan port secara eksplisit. Nomor port dapat berubah selama pemeliharaan atau perubahan spesifikasi, yang dapat menyebabkan kegagalan koneksi. Jika Anda tidak mencantumkan port, database akan secara otomatis menggunakan port saat ini sehingga koneksi tetap valid.

  • Jika Anda harus menetapkan port secara eksplisit, hubungkan ke database dan jalankan pernyataan SQL SHOW PORT; untuk mengetahui nomor port saat ini sebelum menetapkannya.

• Tambahkan Blok CIDR VPC dari instans ApsaraDB RDS for PostgreSQL, misalnya 172.XX.XX.XX/16, ke daftar putih alamat IP database tujuan.

  Catatan

  Anda dapat melihat Blok CIDR VPC pada halaman Database Connection untuk instans ApsaraDB RDS for PostgreSQL Anda.

dblink

1. Buat ekstensi dblink.

   create extension dblink;

2. Buat koneksi dblink.

   postgres=> select dblink_connect('<connection_name>', 'host=<internal_endpoint_of_the_destination_instance> port=<listening_port_of_the_destination_instance> user=<destination_database_username> password=<password> dbname=<destination_database_name>');
   
   postgres=> SELECT * FROM dblink('<connection_name>', '<sql_command>') as <table_name>(<column_name> <column_type>); 

   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);  // Kueri tabel di database tujuan.
   
                           

Untuk informasi lebih lanjut, lihat dokumentasi dblink.

postgres_fdw

1. Buat database baru.

   postgres=> create database <database_name>;  // Buat database.
   
   postgres=> \c <database_name>  // Beralih ke database baru.

   Contoh

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

2. Buat ekstensi postgres_fdw.

   db1=> create extension postgres_fdw;

3. Buat server eksternal untuk database tujuan.

   db1=> CREATE SERVER <server_name>                                                               
           FOREIGN DATA WRAPPER postgres_fdw  
           OPTIONS (host '<internal_endpoint_of_the_destination_instance>', port '<listening_port_of_the_destination_instance>', dbname '<destination_database_name>');  
   
   db1=> CREATE USER MAPPING FOR <local_database_username>      
           SERVER <server_name> 
           OPTIONS (user '<destination_database_username>', password '<destination_database_password>');  

   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 eksternal.

   db1=> import foreign schema public from server foreign_server1 into <schema_name>;  // Impor tabel eksternal.
   
   db1=> select * from <schema_name>.<table_name>      // 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 dokumentasi postgres_fdw.

FAQ

T: Bagaimana cara menggunakan postgres_fdw untuk mengimpor tabel eksternal yang terpartisi?

J: Pada instans tujuan, Anda hanya perlu mengimpor nama tabel partisi induk. Anda tidak perlu mengimpor partisi individualnya. Contoh berikut menggunakan tabel terpartisi rentang:

-- Instans sumber: database 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');

-- Jalankan pada instans tujuan. Impor hanya tabel partisi induk, bukan partisinya.
import FOREIGN SCHEMA public limit to (sales) from server pg_fdw_server into public;
select * from sales;

Hasil berikut dikembalikan:

T: Apa perbedaan antara pg_net dan postgres_fdw?

J: ApsaraDB RDS for PostgreSQL mendukung kedua ekstensi pg_net dan postgres_fdw. Keduanya menggunakan protokol yang berbeda dan memiliki kasus penggunaan yang berbeda. Ekstensi pg_net mengirim permintaan melalui HTTP/HTTPS untuk memanggil RESTful API atau webhook dan tidak dapat mengakses database yang menggunakan protokol PostgreSQL. Ekstensi postgres_fdw terhubung ke database PostgreSQL lain melalui protokol PostgreSQL untuk melakukan kueri lintas database dan akses data.