Điểm lưu (savepoints) cũng được quản lý tương tự - cwin222

Jan 24, 2025 · 10 phút đọc · cwin222 ·

Ngày 12 tháng 3 năm 2022 - Công nghệ thông tin

Trình bao dữ liệu bên ngoài của PostgreSQL, còn được gọi là PostgreSQL Foreign Data Wrappers (gọi tắt là FDW), là một tính năng rất hữu ích trong thực tế áp dụng cơ sở dữ liệu. FDW của PostgreSQL tương tự như dblink của Oracle hoặc Federation của DB2, cho phép kết nối giữa cơ sở dữ liệu địa phương và cơ sở dữ liệu bên ngoài, từ đó có thể thao tác với dữ liệu bên ngoài giống như đang làm việc với dữ liệu địa phương.

FDW dùng để làm gì?

Phân mảnh dữ liệu: Sử dụng FDW để phân tán dữ liệu trên nhiều cơ sở dữ liệu nhằm đạt được sự phân mảnh dữ liệu (ví dụ: plugin pg_shardman sử dụng postgres_fdw và pg_pathman để thực hiện phân mảnh dữ liệu).
Đồng bộ hóa dữ liệu: Thiết lập kết nối giữa cơ sở dữ liệu địa phương và bên ngoài để đồng bộ định kỳ dữ liệu từ bên ngoài về địa phương.
Chuyển đổi dữ liệu: Thiết lập kết nối để chuyển dữ liệu từ cơ sở dữ liệu bên ngoài sang địa phương.
ETL (Extract-Transform-Load): Sử dụng FDW để trích xuất dữ liệu từ các loại cơ sở dữ liệu khác nhau vào một kho dữ liệu tập trung, giúp dễ dàng truy cập thống nhất.

Tổng quan về sự phát triển của PostgreSQL FDW

Năm 2003, SQL/MED (SQL Management of External Data) đã được thêm vào tiêu chuẩn SQL, cung cấp khung quản lý dữ liệu bên ngoài. Bắt đầu từ phiên bản PostgreSQL 9.1 ra mắt vào năm 2011, PostgreSQL hỗ trợ đọc dữ liệu bên ngoài; đến năm 2013 với PostgreSQL 9.3, nó cũng hỗ trợ ghi dữ liệu bên ngoài.

Hiện tại, PostgreSQL (bản viết bài này sử dụng phiên bản PostgreSQL 14) cung cấp nhiều phần mở rộng để tương tác với các loại cơ sở dữ liệu hoặc tệp bên ngoài khác nhau (ví dụ: postgres_fdw cho phép kết nối với cơ sở dữ liệu PostgreSQL bên ngoài, oracle_fdw cho Oracle, mysql_fdw cho MySQL, jdbc_fdw sử dụng giao thức JDBC để kết nối với các cơ sở dữ liệu quan hệ phổ biến, file_fdw cho phép kết nối với các tệp có định dạng cụ thể).

Bài viết này chỉ tập trung vào postgres_fdw, tức là cách cơ sở dữ liệu PostgreSQL kết nối và quản lý dữ liệu bên ngoài từ cơ sở dữ liệu PostgreSQL khác.

1. Sử dụng postgres_fdw

Để truy cập cơ sở dữ liệu từ xa bằng postgres_fdw, bạn cần thực hiện các bước sau:

Cài đặt postgres_fdw
Tạo máy game bài đổi thưởng 2025 chủ bên ngoài
Tạo ánh xạ người dùng
Tạo bảng bên ngoài hoặc nhập mô hình bên ngoài

Trước khi bắt đầu, cần chuẩn bị một số công việc cơ bản:

Kiểm tra phiên bản PostgreSQL

1$ psql --version
2psql (PostgreSQL) 14.2

Tạo người dùng trên cơ sở dữ liệu từ xa Sử dụng superuser để tạo người dùng fdw_user trên cơ sở dữ liệu từ xa:

1CREATE USER fdw_user WITH ENCRYPTED PASSWORD 'secret';

Tạo bảng trên cơ sở dữ liệu từ xa Tạo bảng weather trên cơ sở dữ liệu từ xa, chèn dữ liệu kiểm tra và cấp quyền truy cập cho fdw_user:

 1CREATE TABLE weather (
 2    city varchar(80), 
 3    temp_low int, 
 4    temp_high int, 
 5    prcp real, 
 6    date date
 7);
 8INSERT INTO weather (city, temp_low, temp_high, prcp, date) VALUES 
 9('Beijing', 18, 32, 0.25, '2021-05-19'),
10('Beijing', 20, 30, 0.0, '2021-05-20'),
11('Dalian', 16, 24, 0.0, '2021-05-21');
12GRANT SELECT, INSERT, UPDATE, DELETE ON TABLE weather TO fdw_user;

Kết nối tới cơ sở dữ liệu từ xa bằng fdw_user và kiểm tra quyền truy cập:

1$ psql -h localhost -U fdw_user postgres
2postgres=> SELECT * FROM weather;
3 city   | temp_low | temp_high | prcp |  date
4--------+----------+-----------+------+------------
5 Beijing |      18 |        32 | 0.25 | 2021-05-19
6 Beijing |      20 |        30 |    0 | 2021-05-20
7 Dalian  |      16 |        24 |    0 | 2021-05-21
8(3 dòng)

Lưu ý: Nếu đây là cơ sở dữ liệu từ xa thực tế, cần cấu hình tệp pg_hba.conf để ban ca doi the mở cổng cho IP truy cập.

Tạo người dùng trên cơ sở dữ liệu địa phương Sử dụng superuser để tạo người dùng local_user trên cơ sở dữ liệu địa phương:

1CREATE USER local_user WITH ENCRYPTED PASSWORD 'secret';

Tất cả công việc chuẩn bị đã hoàn tất, bây giờ có thể bắt đầu các bước chính bằng superuser trên cơ sở dữ liệu địa phương.

Cài đặt postgres_fdw

Sử dụng lệnh CREATE EXTENSION để cài đặt postgres_fdw:

1CREATE EXTENSION postgres_fdw;

Cấp quyền sử dụng postgres_fdw cho local_user:

1GRANT USAGE ON FOREIGN DATA WRAPPER postgres_fdw TO local_user;

Tạo máy chủ bên ngoài

Sử dụng lệnh CREATE SERVER để tạo máy chủ bên ngoài, cần chỉ định tên máy chủ, cổng và tên cơ sở dữ liệu từ xa:

1CREATE SERVER foreign_server 
2FOREIGN DATA WRAPPER postgres_fdw 
3OPTIONS (host 'localhost', port '5432', dbname 'postgres');

Cấp quyền sử dụng máy chủ bên ngoài cho local_user:

1GRANT USAGE ON FOREIGN SERVER foreign_server TO local_user;

Tạo ánh xạ người dùng

Sử dụng lệnh CREATE USER MAPPING để tạo ánh xạ giữa người dùng từ xa và địa phương, cần cung cấp tên người dùng và mật khẩu từ xa:

1CREATE USER MAPPING FOR local_user 
2SERVER foreign_server 
3OPTIONS (user 'fdw_user', password 'secret');

Tạo bảng bên ngoài hoặc nhập mô hình bên ngoài

Sử dụng lệnh CREATE FOREIGN TABLE để tạo bảng từ xa:

1CREATE FOREIGN TABLE foreign_weather (
2    city varchar(80), 
3    temp_low int, 
4    temp_high int, 
5    prcp real, 
6    date date
7) SERVER foreign_server 
8OPTIONS (schema_name 'public', table_name 'weather');

Đối với nhiều bảng bên ngoài, có thể sử dụng lệnh IMPORT FOREIGN SCHEMA để nhập tất cả bảng từ mô hình bên ngoài vào mô hình địa phương:

1-- Nhập tất cả bảng từ mô hình bên ngoài
2IMPORT FOREIGN SCHEMA public FROM SERVER foreign_server INTO public;
3
4-- Nhập bảng cụ thể từ mô hình bên ngoài
5IMPORT FOREIGN SCHEMA public LIMIT TO (weather) FROM SERVER foreign_server INTO public;

Cấp quyền truy cập đầy đủ cho local_user trên tất cả bảng trong mô hình public (bao gồm cả bảng bên ngoài):

1GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA public TO local_user;

Giờ đây, sử dụng local_user để kết nối với cơ sở dữ liệu địa phương và có thể thao tác với bảng bên ngoài:

 1$ psql -U local_user postgres
 2postgres=> SELECT * FROM foreign_weather;
 3 city   | temp_low | temp_high | prcp |  date
 4--------+----------+-----------+------+------------
 5 Beijing |      18 |        32 | 0.25 | 2021-05-19
 6 Beijing |      20 |        30 |    0 | 2021-05-20
 7 Dalian  |      16 |        24 |    0 | 2021-05-21
 8(3 dòng)
 9
10postgres=> UPDATE foreign_weather SET prcp = 0 WHERE city = 'Beijing' AND date = '2021-05-19';
11CẬP NHẬT 1

Như vậy, chúng ta đã nắm vững cách sử dụng postgres_fdw. Tiếp theo, bài viết sẽ tìm hiểu thêm về các bảng hệ thống và hàm liên quan đến FDW, cũng như quản lý giao dịch và tối ưu hiệu suất.

2. Các tham số quan trọng khi thiết lập postgres_fdw

updatable Tham số này xác định bảng bên ngoài có thể được cập nhật hay không, nghĩa là postgres_fdw có cho phép sử dụng lệnh INSERT, UPDATE và DELETE để thay đổi bảng bên ngoài hay không. Mặc định là true. Nó có thể được chỉ định ở cấp độ bảng hoặc máy chủ bên ngoài, và giá trị chỉ định ở cấp độ bảng sẽ ưu tiên hơn.

Các câu lệnh cụ thể để thiết lập hoặc cập nhật tham số này:

 1-- Khi tạo máy chủ bên ngoài
 2CREATE SERVER foreign_server FOREIGN DATA WRAPPER postgres_fdw OPTIONS (... , updatable 'false', ...);
 3
 4-- Khi tạo bảng bên ngoài
 5CREATE FOREIGN TABLE foreign_weather (...) SERVER foreign_server OPTIONS (schema_name ..., table_name ..., updatable 'false', ...);
 6
 7-- Cập nhật tham số của máy chủ bên ngoài
 8ALTER SERVER foreign_server OPTIONS (updatable 'false');
 9
10-- Cập nhật tham số của bảng bên ngoài
11ALTER FOREIGN TABLE foreign_weather OPTIONS (updatable 'false');

Nếu bảng từ xa thực tế không thể cập nhật, lỗi sẽ xảy ra dù giá trị tham số này thế nào. Tham số này chủ yếu dùng để ném lỗi ngay tại địa phương mà không cần truy vấn đến máy chủ từ xa.

truncatable Tham số này xác định bảng bên ngoài có thể bị xóa sạch hay không, nghĩa là postgres_fdw có cho phép sử dụng lệnh TRUNCATE để xóa toàn bộ dữ liệu bảng bên ngoài hay không. Mặc định là true. Cũng giống như updatable, nó có thể được chỉ định ở cấp độ bảng hoặc máy chủ bên ngoài.

Các câu lệnh cụ thể để thiết lập hoặc cập nhật tham số này hoàn toàn giống như updatable.

keep_connections Tham số này xác định postgres_fdw có giữ lại kết nối với máy chủ từ xa trong phiên làm việc địa phương (local session) để tái sử dụng hay không. Mặc định là on (nếu thiết lập thành off, tất cả kết nối với máy chủ bên ngoài sẽ bị hủy sau khi kết thúc mỗi giao dịch). Nó chỉ có thể được chỉ định ở cấp độ máy chủ bên ngoài.

Các câu lệnh cụ thể để thiết lập hoặc cập nhật tham số này:

1-- Khi tạo máy chủ bên ngoài
2CREATE SERVER foreign_server FOREIGN DATA WRAPPER postgres_fdw OPTIONS (... , keep_connections 'off', ...);
3
4-- Cập nhật tham số của máy chủ bên ngoài
5ALTER SERVER foreign_server OPTIONS (keep_connections 'off');

3. Bảng hệ thống và hàm liên quan đến FDW

Bảng hệ thống Các bảng hệ thống liên quan đến FDW bao gồm:

 1information_schema._pg_foreign_data_wrappers
 2information_schema._pg_foreign_servers
 3information_schema._pg_foreign_tables
 4information_schema._pg_foreign_table_columns
 5information_schema._pg_user_mappings
 6information_schema.foreign_data_wrappers
 7information_schema.foreign_data_wrapper_options
 8information_schema.foreign_server_options
 9information_schema.foreign_servers
10information_schema.foreign_tables
11information_schema.foreign_table_options

Hàm

postgres_fdw_get_connections() Hàm này trả về danh sách tất cả kết nối hiện hoạt từ phiên làm việc địa phương đến máy chủ bên ngoài, bao gồm tên máy chủ và trạng thái kết nối.
postgres_fdw_disconnect(server_name text) Hàm này ngắt tất cả kết nối từ phiên làm việc địa phương đến máy chủ bên ngoài theo tên được chỉ định.
postgres_fdw_disconnect_all() Hàm này ngắt tất cả kết nối từ phiên làm việc địa phương đến mọi máy chủ bên ngoài.

4. Quản lý giao dịch và tối ưu hiệu suất FDW

Quản lý giao dịch Khi truy vấn bảng từ xa, nếu chưa có giao dịch tương ứng với giao dịch địa phương hiện tại, postgres_fdw sẽ mở một giao dịch mới trên máy chủ từ xa. Giao dịch từ xa sẽ được cam kết hoặc hủy bỏ cùng lúc với giao dịch địa phương. Điểm lưu (savepoints) cũng được quản lý tương tự.

Tối ưu hiệu suất Postgres_fdw sẽ tự động quyết định xem liệu một truy vấn có nên được thực hiện trên máy chủ từ xa hay không. Trong trường hợp lý tưởng, khi tất cả bảng đều nằm trên máy chủ từ xa và các toán tử, hàm đều là kiểu nội bộ, postgres_fdw sẽ gửi toàn bộ truy vấn đến máy chủ từ xa để tính toán và chỉ lấy kết quả về. Trong hầu hết các trường hợp, postgres_fdw sẽ tối ưu hóa truy vấn gửi đi (tối ưu điều kiện WHERE và không lấy các cột không cần thiết) để giảm thiểu lượng dữ liệu truyền tải từ máy chủ từ xa.

Với những ví dụ minh họa, bài viết đã trình bày chi tiết cách postgres_fdw hoạt động và tối ưu hiệu suất.

Như vậy, chúng ta đã có cái nhìn tổng quan về các khái niệm cơ bản và cách sử dụng postgres_fdw.

[1] PostgreSQL: Tài liệu: 14: F.35. postgres_fdw
[2] Foreign data wrappers - PostgreSQL Wiki
[3] CARTO’s Use of Foreign Data Wrappers
[4] PostgreSQL fdw giải thích chi tiết
[5] Nguyên lý và cách sử dụng postgres_fdw của Postgresql fdw
[6] Mở rộng postgres_fdw trong PostgreSQL
[7] Nâng cấp chức năng postgres_fdw trong PostgreSQL 14