Làm thế nào để sử dụng database links trong PostgreSQL?

Question

Đào Đăng Khoa · Accepted Answer

Trong PostgreSQL, database links tương tự như khái niệm trong các hệ quản trị cơ sở dữ liệu khác như Oracle, nhưng PostgreSQL không hỗ trợ trực tiếp tính năng này như một thành phần tích hợp sẵn. Tuy nhiên, bạn có thể thiết lập kết nối từ một cơ sở dữ liệu PostgreSQL tới một cơ sở dữ liệu khác thông qua các tiện ích mở rộng (extensions) hoặc công cụ như Foreign Data Wrapper (FDW).
Cách sử dụng database links trong PostgreSQL bằng cách thiết lập Foreign Data Wrapper (FDW)
PostgreSQL cung cấp Foreign Data Wrapper (FDW) để cho phép truy cập dữ liệu từ các cơ sở dữ liệu khác, bao gồm cả PostgreSQL hoặc các hệ quản trị cơ sở dữ liệu khác (MySQL, MongoDB, v.v.). Một trong những FDW phổ biến nhất để kết nối giữa hai cơ sở dữ liệu PostgreSQL là postgres_fdw.
Dưới đây là các bước để thiết lập và sử dụng database links trong PostgreSQL bằng postgres_fdw:
1. Cài đặt postgres_fdw extension
Đầu tiên, bạn cần kích hoạt extension postgres_fdw trong cơ sở dữ liệu hiện tại.
CREATE EXTENSION IF NOT EXISTS postgres_fdw;
2. Tạo kết nối tới cơ sở dữ liệu từ xa
Tiếp theo, bạn cần tạo một server đại diện cho cơ sở dữ liệu từ xa. Giả sử bạn có hai cơ sở dữ liệu PostgreSQL: một cơ sở dữ liệu nguồn (local_db) và một cơ sở dữ liệu từ xa (remote_db), bạn cần tạo một FOREIGN SERVER để kết nối tới cơ sở dữ liệu từ xa.
CREATE SERVER remote_server
FOREIGN DATA WRAPPER postgres_fdw
OPTIONS (host 'remote_host', port '5432', dbname 'remote_db');
Trong đó:
remote_host là địa chỉ của máy chủ chứa cơ sở dữ liệu từ xa.
remote_db là tên của cơ sở dữ liệu từ xa.
5432 là cổng mặc định của PostgreSQL.
3. Tạo thông tin đăng nhập (USER MAPPING)
Tiếp theo, bạn phải thiết lập quyền truy cập vào cơ sở dữ liệu từ xa bằng cách tạo user mapping cho người dùng hiện tại (hoặc cho bất kỳ người dùng nào được phép truy cập):
CREATE USER MAPPING FOR current_user
SERVER remote_server
OPTIONS (user 'remote_user', password 'remote_password');
Trong đó:
remote_user và remote_password là tên người dùng và mật khẩu của cơ sở dữ liệu từ xa.
4. Tạo bảng liên kết (FOREIGN TABLE)
Bước tiếp theo là tạo bảng liên kết với bảng từ xa mà bạn muốn truy cập. Giả sử bạn có bảng products trong cơ sở dữ liệu từ xa (remote_db), bạn có thể tạo bảng tương ứng trong cơ sở dữ liệu hiện tại (local_db) như sau:
CREATE FOREIGN TABLE local_products (
    id INT,
    name TEXT,
    price NUMERIC
)
SERVER remote_server
OPTIONS (schema_name 'public', table_name 'products');
Trong đó:
schema_name 'public' là schema của bảng trong cơ sở dữ liệu từ xa.
table_name 'products' là tên bảng từ xa.
5. Truy vấn bảng từ xa
Sau khi tạo bảng liên kết, bạn có thể truy vấn dữ liệu từ bảng từ xa giống như truy vấn bảng cục bộ:
SELECT * FROM local_products;
6. Cập nhật và quản lý dữ liệu từ xa
Ngoài việc truy vấn, bạn cũng có thể thực hiện các thao tác như INSERT, UPDATE, hoặc DELETE trên bảng liên kết (nếu được phép):
Chèn dữ liệu:
INSERT INTO local_products (id, name, price)
VALUES (4, 'Tablet', 350.00);
Cập nhật dữ liệu:
UPDATE local_products
SET price = 400.00
WHERE id = 4;
Xóa dữ liệu:
DELETE FROM local_products
WHERE id = 4;
7. Tối ưu hóa truy vấn với IMPORT FOREIGN SCHEMA
Nếu bạn có nhiều bảng trong cơ sở dữ liệu từ xa mà muốn liên kết với cơ sở dữ liệu cục bộ, bạn có thể tự động import tất cả các bảng từ một schema nhất định:
IMPORT FOREIGN SCHEMA public
FROM SERVER remote_server
INTO local_schema;
Câu lệnh này sẽ tự động tạo tất cả các bảng từ schema public trong cơ sở dữ liệu từ xa vào schema local_schema trong cơ sở dữ liệu cục bộ.
Tổng kết:
Foreign Data Wrapper (FDW), đặc biệt là postgres_fdw, là phương pháp chính để thực hiện database links trong PostgreSQL.
Bạn có thể thiết lập một foreign server, liên kết với cơ sở dữ liệu từ xa và tạo các bảng liên kết (foreign tables) để truy cập và thao tác dữ liệu từ xa.
PostgreSQL FDW cho phép truy cập không chỉ các cơ sở dữ liệu PostgreSQL mà còn các hệ quản trị cơ sở dữ liệu khác như MySQL, MongoDB, Oracle, v.v.