Integrasi Aurora PostgreSQL yang kompatibel dengan database PostgreSQL jarak jauh - AWS Panduan Preskriptif
kasus penggunaan postgres_fdw dan langkah-langkah tingkat tinggiMenggunakan dblink untuk membuat koneksi

Terjemahan disediakan oleh mesin penerjemah. Jika konten terjemahan yang diberikan bertentangan dengan versi bahasa Inggris aslinya, utamakan versi bahasa Inggris.

Integrasi Aurora PostgreSQL yang kompatibel dengan database PostgreSQL jarak jauh

Bagian ini membahas integrasi Edisi Amazon Aurora PostgreSQL yang kompatibel dengan database PostgreSQL jarak jauh menggunakan ekstensi (pembungkus data asing) atau fitur. postgres_fdw dblink postgres_fdwModul ini menyediakan kemampuan kueri federasi untuk berinteraksi dengan database berbasis PostgreSQL jarak jauh. Database jarak jauh dapat dikelola atau dikelola sendiri di Amazon EC2 atau di tempat. postgres_fdwEkstensi ini tersedia di semua versi Amazon Relational Database Service (Amazon RDS) yang saat ini didukung untuk PostgreSQL dan Aurora PostgreSQL yang kompatibel dengan PostgreSQL.

Dengan menggunakan postgres_fdw ekstensi, Anda dapat mengakses dan menanyakan data dari database PostgreSQL jarak jauh seolah-olah itu adalah tabel lokal. postgres_fdwEkstensi ini juga mendukung yang berikut:

  • Kompatibilitas lintas-versi untuk mengakses data dari server PostgreSQL eksternal yang menjalankan versi yang berbeda.

  • Manajemen transaksi, yang membantu memastikan konsistensi dan integritas data saat Anda melakukan operasi di server PostgreSQL lokal dan eksternal.

  • Transaksi terdistribusi, yang menyediakan atomisitas (properti transaksi ACID) dan jaminan isolasi saat Anda melakukan operasi di beberapa server PostgreSQL eksternal. Ini membantu memastikan bahwa semua operasi dalam suatu transaksi dilakukan atau tidak ada yang berkomitmen, menjaga konsistensi dan integritas data.

Meskipun dblink modul menyediakan cara untuk berinteraksi dengan database PostgreSQL jarak jauh, modul ini tidak mendukung transaksi terdistribusi atau fitur canggih lainnya. Jika Anda membutuhkan fungsionalitas yang lebih canggih, pertimbangkan untuk menggunakan postgres_fdw ekstensi sebagai gantinya. postgres_fdwEkstensi ini memberikan lebih banyak kemampuan integrasi dan pengoptimalan.

kasus penggunaan postgres_fdw dan langkah-langkah tingkat tinggi

Penggunaan postgres_fdw ekstensi dengan Aurora PostgreSQL kompatibel mendukung kasus penggunaan dan skenario berikut:

  • Kueri gabungan dan integrasi data - Menanyakan dan menggabungkan data dari beberapa database PostgreSQL dalam satu instance yang kompatibel dengan Aurora PostgreSQL

  • Membongkar beban kerja baca - Menghubungkan ke replika membaca server PostgreSQL eksternal, membongkar beban kerja yang banyak dibaca, dan meningkatkan kinerja kueri

  • Operasi lintas basis data - MelakukanINSERT,, UPDATEDELETE, dan COPY operasi di beberapa database PostgreSQL, memungkinkan manipulasi data lintas basis data dan tugas pemeliharaan

Untuk mengonfigurasipostgres_fdw, gunakan langkah-langkah tingkat tinggi berikut:

  1. Connect ke klaster Aurora PostgreSQL yang kompatibel dengan menggunakan klien PostgreSQL, dan buat ekstensi: postgres_fdw

    CREATE EXTENSION postgres_fdw;

    Ekstensi ini menyediakan fungsionalitas untuk terhubung ke database PostgreSQL jarak jauh.

  2. Buat server asing bernama my_fdw_target dengan menggunakan CREATE SERVER perintah. Server ini mewakili database PostgreSQL jarak jauh yang ingin Anda sambungkan. Tentukan nama database, nama host, dan mode SSL sebagai opsi untuk server ini.

  3. Pastikan bahwa grup keamanan dan konfigurasi jaringan yang diperlukan tersedia untuk memungkinkan Aurora PostgreSQL kompatibel untuk terhubung ke database PostgreSQL jarak jauh.

    Jika database jarak jauh dihosting di tempat, Anda mungkin perlu mengonfigurasi jaringan pribadi virtual (VPN) atau AWS Direct Connect koneksi.

    Jalankan perintah berikut:

    CREATE SERVER my_fdw_target Foreign Data Wrapper postgres_fdw OPTIONS (DBNAME 'postgres', HOST 'SOURCE_HOSTNAME', SSLMODE 'require');

  4. Buat pemetaan pengguna untuk dbuser pengguna di my_fdw_target server. Pemetaan ini mengaitkan dbuser pengguna dan kata sandi pada instance lokal yang kompatibel dengan Aurora PostgreSQL dengan pengguna terkait pada database jarak jauh.

    CREATE USER MAPPING FOR dbuser SERVER my_fdw_target OPTIONS (user 'DBUSER', password 'PASSWORD');

    Langkah ini diperlukan untuk mengautentikasi dan menyediakan akses ke database jarak jauh.

  5. Buat tabel asing bernama customer_fdw dengan my_fdw_target server dan pemetaan pengguna yang Anda atur sebelumnya:

    CREATE FOREIGN TABLE customer_fdw( id int, name varchar, emailid varchar, projectname varchar, contactnumber bigint) server my_fdw_target OPTIONS( TABLE_NAME 'customers');

    customer_fdwTabel memetakan ke customers tabel dalam database jarak jauh yang ditentukan oleh my_fdw_target server. Tabel asing memiliki struktur yang sama dengan tabel jarak jauh sehingga Anda dapat berinteraksi dengan data jarak jauh seolah-olah itu adalah tabel lokal.

  6. Anda dapat melakukan berbagai operasi manipulasi data pada tabel customer_fdw asing, sepertiINSERT,UPDATE, dan SELECT kueri. Skrip menunjukkan penyisipan baris baru dan memperbarui baris yang ada, menghapus catatan, dan memotong tabel di tabel jarak jauh melalui customers tabel asing: customer_fdw

    INSERT INTO customer_fdw values ( 1, 'Test1', 'Test1@email.com', 'LMS1', '888888888');
INSERT INTO customer_fdw values ( 2, 'Test2', 'Test2@email.com', 'LMS2', '999999999');
INSERT INTO customer_fdw values ( 3, 'Test3', 'Test3@email.com', 'LMS3', '111111111');
UPDATE customer_fdw set contactnumber = '123456789' where id = 2;
DELETE FROM customer_fdw where id = 1;
TRUNCATE TABLE customer_fdw;

  7. Anda dapat memvalidasi rencana kueri SQL dengan menggunakan EXPLAIN pernyataan untuk menganalisis rencana kueri untuk SELECT kueri pada tabelcustomer_fdw:

    EXPLAIN select * from customer_fdw where id =1;

    Ini dapat membantu Anda memahami bagaimana kueri dijalankan dan cara mengoptimalkannya. Untuk informasi selengkapnya tentang penggunaan EXPLAIN pernyataan, lihat Mengoptimalkan kinerja kueri PostgreSQL di Panduan Preskriptif. AWS

  8. Untuk mengimpor beberapa tabel dari database jarak jauh ke dalam skema lokal, gunakan IMPORT FOREIGN SCHEMA perintah:

    CREATE SCHEMA public_fdw;
IMPORT FOREIGN SCHEMA public LIMIT TO (employees, departments)
    FROM SERVER my_fdw_target INTO public_fdw;

    Ini menciptakan tabel asing lokal untuk tabel tertentu dalam public_fdw skema. Dalam contoh ini, tabel spesifik adalah karyawan dan departemen.

  9. Untuk memberikan izin yang diperlukan kepada pengguna database tertentu sehingga mereka dapat mengakses dan menggunakan FDW dan server asing terkait, jalankan perintah berikut:

    GRANT USAGE ON FOREIGN SERVER my_fdw_target TO targetdbuser;
GRANT USAGE ON FOREIGN DATA WRAPPER postgres_fdw TO targetdbuser;

    Langkah ini dapat bermanfaat ketika beberapa pengguna memerlukan akses ke tabel asing yang difasilitasi oleh pembungkus data asing.

Saat menggunakan tabel asing, perhatikan batasan berikut:

  • Mengakses data dari sumber jarak jauh dapat memperkenalkan biaya transfer data dan overhead kinerja yang disebabkan oleh latensi jaringan. Masalah kinerja dapat terlihat untuk kumpulan data besar atau kueri yang memerlukan transfer data yang signifikan antara instance yang kompatibel dengan Aurora PostgreSQL dan sumber data jarak jauh.

  • Dalam kueri kompleks yang melibatkan fitur seperti fungsi jendela, kueri rekursif mungkin tidak berfungsi seperti yang diharapkan atau mungkin tidak didukung.

  • Saat ini, enkripsi kata sandi tidak didukung. Menerapkan kontrol untuk memastikan bahwa hanya pengguna yang berwenang yang dapat mengakses FDWs dan mengambil data dari database jarak jauh.

  • Batasan kunci primer tidak dapat didefinisikan pada tabel asing, seperti yang ditunjukkan oleh upaya skrip pembuatan tabel berikut:

    CREATE FOREIGN TABLE customer_fdw2( id int primary key, name varchar, emailid varchar, projectname varchar, contactnumber bigint) server my_fdw_target OPTIONS( TABLE_NAME 'customers');
Primary keys cannot be defined on Foreign table

  • ON CONFLICTKlausa untuk INSERT pernyataan tidak didukung pada tabel asing, seperti yang ditunjukkan pada contoh berikut:

    INSERT INTO customer_fdw (id, name, emailid, projectname, contactnumber) VALUES
(1, 'test1', 'test@email.com', 'LMS', 11111111 ),
(3, 'test3', 'test3@email.com', 'LMS', 22222222 )
ON CONFLICT (id) DO UPDATE 
SET name = EXCLUDED.name;
On Conflict option doesnot work.

Pembersihan

Untuk membersihkan objek yang dibuat, termasuk menjatuhkan postgres_fdw ekstensi, my_fdw_target server, pemetaan pengguna, dan tabel asing, jalankan perintah berikut:

DROP FOREIGN TABLE customer_fdw;
DROP USER MAPPING for postgres;
DROP SERVER  my_fdw_target;
DROP EXTENSION postgres_fdw cascade;

Fungsi dblink modul menyediakan cara alternatif untuk membuat koneksi dan menjalankan pernyataan SQL pada database PostgreSQL jarak jauh. dblinkSolusinya adalah cara yang lebih sederhana dan lebih fleksibel untuk menjalankan kueri atau operasi satu kali pada database jarak jauh. Untuk skenario yang lebih kompleks yang melibatkan integrasi data skala besar, optimasi kinerja, dan persyaratan integritas data, sebaiknya gunakan. postgres_fdw

Menggunakan dblink melibatkan langkah-langkah tingkat tinggi berikut:

  1. Buat dblink ekstensi:

    CREATE EXTENSION dblink;

    Ekstensi ini menyediakan fungsionalitas untuk terhubung ke database PostgreSQL jarak jauh.

  2. Untuk membuat koneksi ke database PostgreSQL jarak jauh, gunakan fungsi: dblink_connect

    SELECT dblink_connect('myconn', 'dbname=postgres port=5432 host=SOURCE_HOSTNAME user=postgres password=postgres');

  3. Setelah Anda terhubung ke database PostgreSQL jarak jauh, jalankan pernyataan SQL pada database jarak jauh dengan menggunakan fungsi: dblink

    SELECT  FROM dblink('myconn', 'SELECT col1, col2 FROM remote_table') AS remote_data(col1 int, col2 text);

    Kueri ini menjalankan SELECT * FROM remote_table pernyataan pada database jarak jauh dengan menggunakan myconn koneksi. Query mengambil hasil ke dalam tabel sementara lokal dengan kolom col1 dancol2.

  4. Anda juga dapat menjalankan pernyataan non-query, sepertiINSERT,UPDATE, atauDELETE, pada database jarak jauh dengan menggunakan dblink_exec fungsi:

    SELECT dblink_exec('myconn', 'INSERT INTO remote_table VALUES (1, ''value'')');