Cách di chuyển từ Oracle sang PostgreSQL

Sách trắng này đề cập đến trải nghiệm di chuyển cơ sở dữ liệu từ Oracle sang PostgreSQL do Bộ chuyển đổi thông minh thực hiện cho một công ty vận chuyển hàng hóa lớn. Lý do chính của việc di chuyển là giảm chi phí của hệ thống quản lý cơ sở dữ liệu và chuyển sang hệ thống mã nguồn mở với đủ khả năng mở rộng, bảo mật và tùy chỉnh.

Dưới đây là đặc điểm kỹ thuật ngắn gọn của cơ sở dữ liệu nguồn:

• Máy chủ cơ sở dữ liệu Oracle 12g

• Cơ sở dữ liệu chứa 190 bảng, 50 GB dữ liệu

• 120000 dòng PL/SQL trong các thủ tục, chức năng và trình kích hoạt được lưu trữ

  
  

Thời gian thực hiện dự án là 2 tháng: nửa tháng để đánh giá và lập kế hoạch, một tháng để di chuyển và nửa tháng để thử nghiệm. Có 3 chuyên gia tham gia vào quá trình di chuyển: 2 nhà phát triển và 1 quản trị viên cơ sở dữ liệu.

Quá trình di chuyển cơ sở dữ liệu Oracle sang PostgreSQL này bao gồm sáu giai đoạn cơ bản:

1. Tìm tất cả các phương thức lưu trữ và xử lý dữ liệu đặc trưng của Oracle trong cơ sở dữ liệu nguồn và phạm vi sử dụng (giai đoạn điều tra và lập kế hoạch)
2. Chọn các công cụ thích hợp để di chuyển lược đồ và triển khai nó
3. Chọn phương pháp di chuyển dữ liệu phù hợp nhất để giảm thời gian ngừng hoạt động của hệ thống Oracle
4. Chạy di chuyển dữ liệu xử lý tất cả các biến đổi theo yêu cầu của PostgreSQL DBMS
5. Chuyển đổi tất cả mã PL/SQL sang định dạng PostgreSQL (sử dụng các công cụ để tự động hóa một phần và xử lý hậu kỳ thủ công)
6. Chạy thử nghiệm hiệu suất và chức năng, tinh chỉnh cơ sở dữ liệu kết quả

Di chuyển các định nghĩa bảng

Có một số loại dữ liệu trong Oracle không tương đương trực tiếp trong PostgreSQL. Một trong những loại như vậy là DATE chứa cả phần ngày và giờ.

PostgreSQL hỗ trợ:

• ngày - ngày thuần túy không có phần thời gian
• thời gian - thời gian thuần túy không có phần ngày với thông số kỹ thuật tùy chọn múi giờ
• dấu thời gian – ngày và giờ với thông số kỹ thuật tùy chọn múi giờ

Có hai tùy chọn ánh xạ ngày của Oracle vào PostgreSQL: sử dụng TIMESTAMP hoặc thiết lập tiện ích mở rộng orafce để sử dụng kiểu ngày kiểu Oracle oracle.date.

Các loại không gian cũng đòi hỏi sự chú ý đặc biệt. Oracle đã tích hợp sẵn kiểu SDO_GEOMETRY trong khi PostgreSQL cần cài đặt PostGIS để hoạt động với kiểu dữ liệu không gian.

Bảng bên dưới minh họa ánh xạ kiểu an toàn của Oracle sang PostgreSQL:

Đối với các loại số, điều quan trọng là phải hiểu phạm vi sử dụng trong cơ sở dữ liệu. Nếu nó tập trung vào độ chính xác, các kiểu số của Oracle phải được ánh xạ trong PostgreSQL NUMERIC. Nếu ưu tiên hàng đầu là tốc độ tính toán, ánh xạ tốt nhất sẽ là CHÍNH XÁC THỰC hoặc NHÂN ĐÔI.

Chúng tôi đã sử dụng trình chuyển đổi Oracle-to-PostgreSQL để tự động di chuyển các định nghĩa, chỉ mục và ràng buộc bảng cho dự án này. Nó ánh xạ các loại Oracle thành các loại tương đương PostgreSQL thích hợp nhất và cho phép tùy chỉnh ánh xạ loại cụ thể.

Di chuyển dữ liệu

Vì việc di chuyển dữ liệu có thể tiêu tốn nhiều thời gian cho các cơ sở dữ liệu lớn nên việc chọn chiến lược và công cụ phù hợp cho bước này là vô cùng quan trọng.

Có ba cách tiếp cận phổ biến để di chuyển dữ liệu:

• ảnh chụp nhanh – di chuyển tất cả dữ liệu trong một bước
• ảnh chụp nhanh từng phần – di chuyển dữ liệu theo khối trong các luồng hoặc quy trình song song
• thay đổi sao chép dữ liệu – tải dữ liệu liên tục bằng cách theo dõi các thay đổi gia tăng

Phương pháp chụp nhanh yêu cầu thời gian ngừng hoạt động thiết yếu của cơ sở dữ liệu nguồn trong toàn bộ thời gian đọc dữ liệu để tránh mất hoặc hỏng dữ liệu trong quá trình di chuyển. Thời gian ngừng hoạt động đối với phương pháp chụp nhanh từng phần ít hơn nhiều nhưng vẫn cần thiết. Trong dự án di chuyển này, chúng tôi đã sử dụng phương pháp chụp nhanh từng phần để di chuyển các bảng lớn chứa hàng triệu hàng.

Vậy còn kỹ thuật sao chép dữ liệu thay đổi (CDR) thì sao?

Nó có hai triển khai chính và mỗi triển khai đều có điểm yếu. Cái đầu tiên được gọi là CDR dựa trên trình kích hoạt. Nó yêu cầu tạo các trình kích hoạt khi chèn, cập nhật và xóa cho mỗi bảng được sao chép trong cơ sở dữ liệu nguồn. Những trình kích hoạt đó theo dõi tất cả các thay đổi bằng cách lưu trữ thông tin về các sự kiện thay đổi vào bảng 'lịch sử' đặc biệt. Dựa trên dữ liệu này, công cụ CDR sao chép tất cả các thay đổi đối với cơ sở dữ liệu đích. CDR dựa trên trình kích hoạt gây thêm chi phí hoạt động cho cơ sở dữ liệu nguồn do ghi vào bảng 'lịch sử' cho mỗi lần cập nhật dữ liệu.

Phương pháp thứ hai được gọi là Nhật ký giao dịch CDR. Nó sử dụng nhật ký giao dịch được tạo bởi Oracle DBMS để theo dõi và sao chép các thay đổi vào cơ sở dữ liệu PostgreSQL đích. Ưu điểm của phương pháp này so với CDR dựa trên trình kích hoạt là nó không sửa đổi cơ sở dữ liệu nguồn.

Tuy nhiên, Nhật ký giao dịch CDR cũng có một số điểm yếu:

• Oracle liên tục lưu trữ các tệp nhật ký giao dịch, vì vậy công cụ CDC có thể mất một số thay đổi nếu không đọc nhật ký giao dịch trước khi lưu trữ.
• Trong trường hợp mất kết nối với cơ sở dữ liệu đích trong khi sao chép các thay đổi từ nhật ký giao dịch (hoặc bất kỳ loại lỗi nào khác), dữ liệu có thể bị mất hoặc bị hỏng do thiếu kiểm soát nhật ký giao dịch.

Các khía cạnh thách thức nhất của việc di chuyển dữ liệu từ Oracle sang PostgreSQL là: các định dạng dữ liệu cụ thể của DBMS nguồn không có tương đương trực tiếp trong dữ liệu đích và dữ liệu bên ngoài.

Trong phần trước của bài viết này, BYTEA được nêu là kiểu dữ liệu PostgreSQL phù hợp nhất cho dữ liệu nhị phân. Tuy nhiên, khi di chuyển dữ liệu nhị phân lớn (kích thước trường trung bình không nhỏ hơn 10 MB), không nên sử dụng BYTEA. Lý do là đặc thù của việc đọc dữ liệu BYTEA – nó chỉ có thể được trích xuất trong một đoạn; đọc từng phần là không thể. Điều này có thể gây ra chi phí RAM cần thiết. Loại PostgreSQL LARGE OBJECT có thể được sử dụng làm giải pháp thay thế cho vấn đề này. Tất cả các giá trị của loại ĐỐI TƯỢNG LỚN được lưu trữ trong bảng hệ thống 'pg_largeobject', là một phần của mỗi cơ sở dữ liệu. Có thể có tới 4 tỷ hàng trong bảng 'pg_largeobject'. Kích thước tối đa của ĐỐI TƯỢNG LỚN là 4TB và có sẵn tính năng đọc từng phần.

Ngoài ra, điều quan trọng là phải di chuyển đúng ROWID là cột giả xác định địa chỉ vật lý của bản ghi trong bảng. PostgreSQL có một trường dịch vụ tương tự gọi là ctid, tuy nhiên nó tương đương trực tiếp với ROWID. Tài liệu PostgreSQL nói rằng ctid có thể bị thay đổi do quy trình hút bụi.

Có ba phương pháp cơ bản để mô phỏng chức năng ROWID trong PostgreSQL:

• Sử dụng khóa chính hiện có (hoặc tạo khóa mới) để xác định các hàng thay vì ROWID
• Thêm một cột serial hoặc bigserial với các giá trị được tạo tự động và biến cột đó thành khóa chính/duy nhất để thay thế chức năng ROWID
• Khi không thể xác định khóa chính của một cột, hãy sử dụng chỉ mục duy nhất được tạo trên nhiều cột (vì mục đích này, hãy xác định tập hợp trường duy nhất tối thiểu cho mỗi hàng)

Oracle có thể liên kết dữ liệu bên ngoài được lưu trữ bên ngoài cơ sở dữ liệu. Tuy nhiên, nó có thể được xử lý như một bảng thông thường bằng cách sử dụng tính năng 'bảng bên ngoài' của DBMS. PostgreSQL sử dụng thư viện Foreign Data Wrapper cho cùng một mục đích. Ví dụ: nó cung cấp tiện ích mở rộng 'file_fdw' để hoạt động với các tệp CSV bên ngoài dưới dạng bảng thông thường.

Những thách thức của việc di chuyển PL/SQL

Phần này khám phá các sự cố đã được giải quyết trong quá trình di chuyển các gói Oracle, lưu trữ các thủ tục, chức năng và trình kích hoạt tới PostgreSQL.

gói. PostgreSQL không có gói. Tính năng còn thiếu này có thể được mô phỏng bằng cách nhóm tất cả các mục thuộc về một gói bên trong lược đồ PostgreSQL có cùng tên, các biến toàn cục có thể được lưu trữ trong một bảng dịch vụ.

Chuỗi rỗng. Không giống như Oracle, trong PostgreSQL các chuỗi rỗng không bằng NULL. Ví dụ: trong Oracle:

 SQL> select NVL('','This is null') as null_test from dual; NULL_TEST ------------ This is null

Tương tự trong PostgreSQL:

 testdb=# select coalesce('','This is null') as null_test; null_test ----------- (1 row)

Ngoài ra, có một hành vi nối chuỗi khác với NULL trong Oracle và PostgreSQL. Trong Oracle:

 SQL> Select 'String' || NULL as cs from dual; CS -- String

Trong PostgreSQL:

 synctest=# Select 'String' || NULL as concat_str; concat_str ------------ (1 row)

Đúc các loại. PostgreSQL yêu cầu ép kiểu nghiêm ngặt khi gọi các hàm, toán tử hoặc khi các câu lệnh INSERT và UPDATE đặt kết quả của các biểu thức vào một bảng.

Cách giải quyết là sử dụng loại 'bất kỳ phần tử' giả:

 create or replace function my_concat(str1 anyelement, str2 anyelement) returns varchar language plpgsql as $$ begin return str1::varchar || str2::varchar; end; $$;

Lưu ý: bạn có thể sử dụng một đối số phần tử bất kỳ trong thủ tục/hàm được lưu trữ hoặc tất cả các tham số của phần tử bất kỳ cùng loại.

trình tự. Cú pháp khai báo trình tự tương tự đối với Oracle và PostgreSQL. Ví dụ:

 CREATE SEQUENCE SEQ1 START WITH 10 INCREMENT BY 2 MAXVALUE 1000000 CACHE 20 NO CYCLE;

Tuy nhiên, tham chiếu giá trị tiếp theo của chuỗi là khác nhau. Trong Oracle nó là 'sequence.nextval' và trong PostgreSQL nextval('sequence').

Gây nên. Trong Oracle, mã nguồn của phần thân trình kích hoạt được bao gồm trong câu lệnh CREATE TRIGGER. PostgreSQL yêu cầu mã nguồn trình kích hoạt được soạn dưới dạng hàm độc lập với tham chiếu từ câu lệnh CREATE TRIGGER:

 CREATE OR REPLACE FUNCTION store_changes() RETURNS TRIGGER LANGUAGE PLPGSQL AS $$ BEGIN IF (NEW.first_name <> OLD.first_name) OR (NEW.last_name <> OLD.last_name) OR (NEW.email <> OLD.email) THEN INSERT INTO changes_log(id,changed_on) VALUES(OLD.id,now()); END IF; RETURN NEW; END; $$ CREATE TRIGGER make_changes BEFORE UPDATE ON employees FOR EACH ROW EXECUTE PROCEDURE store_changes();

Giao dịch độc lập. Các giao dịch tự trị trong Oracle cho phép một chương trình con thực hiện hoặc khôi phục các hoạt động SQL mà không cần thực hiện hoặc khôi phục giao dịch chính. Ví dụ: một số dữ liệu được chèn vào bảng như một phần của trình kích hoạt chèn.

Thao tác chèn này phải thành công ngay cả khi giao dịch chính của trình kích hoạt chèn không thành công. Trong trường hợp này, câu lệnh INSERT tương ứng phải được đặt trong giao dịch tự trị:

 CREATE OR REPLACE PROCEDURE insert_critical_data(v_data varchar2) IS PRAGMA AUTONOMOUS_TRANSACTION; BEGIN INSERT INTO info_table (data) VALUES (v_data); commit; END;

Giao dịch tự trị không được hỗ trợ trực tiếp trong PostgreSQL. Một cách giải quyết khác có thể là cấu trúc lại mã để không cần các giao dịch tự trị.

Nếu không, bạn có thể sử dụng mô-đun 'dblink' của PostgreSQL. Với dblink, một kết nối mới được thiết lập và một truy vấn được thực thi bằng kết nối này và được cam kết ngay lập tức, bất kể giao dịch chính là gì. Ví dụ: hàm sau đây chèn một hàng vào bảng và thao tác chèn sẽ được thực hiện ngay cả khi giao dịch gọi được khôi phục:

 CREATE OR REPLACE FUNCTION insert_critical_data(v_data TEXT) RETURNS void AS $BODY$ DECLARE v_sql text; BEGIN PERFORM dblink_connect('myconn', 'dbname=mydbname user=… password=… host=… port=…'); v_sql := format('INSERT INTO info_table (data) VALUES (%L)', v_data); PERFORM dblink_exec('myconn', v_sql); PERFORM dblink_disconnect('myconn'); END; $BODY$ LANGUAGE plpgsql;

Chức năng tích hợp sẵn. Oracle và PostgreSQL cung cấp các bộ chức năng tích hợp tương tự nhưng không bằng nhau. Bảng bên dưới bao gồm các chức năng của Oracle yêu cầu chuyển sang các giá trị tương đương của PostgreSQL:

*Có thể tìm thấy quá trình chuyển phức hợp chức năng Oracle INSTR sang PostgreSQL tại: https://www.postgresql.org/docs/9.0/static/plpgsql-porting.html#PLPGSQL-PORTING-APPENDIX

Điều kiện của Oracle 'trong đó rownum < N' phải được chuyển đổi thành 'giới hạn N' trong PostgreSQL Chúng tôi đã sử dụng Trình chuyển đổi mã Oracle-to-PostgreSQL để tự động hóa một phần quá trình chuyển đổi trình kích hoạt, thủ tục được lưu trữ và chức năng. Nó hỗ trợ chuyển đổi các hàm tích hợp sẵn của Oracle thành các hàm tương đương PostgreSQL và xử lý hầu hết các mẫu cú pháp của PL/SQL. Tuy nhiên, mã nguồn phức tạp có thể yêu cầu xử lý hậu kỳ đầu ra thủ công.

Các mô-đun hữu ích để di chuyển từ Oracle sang PostgreSQL

Phần này chứa mô tả ngắn gọn về các mô-đun PostgreSQL có thể giúp di chuyển từ Oracle sang PostgreSQL và kiểm tra kết quả di chuyển.

Một trong những mô-đun PostgreSQL quan trọng nhất khi di chuyển từ Oracle là orafce . Nó mô phỏng các loại, chức năng và toán tử cụ thể của hệ thống quản lý cơ sở dữ liệu Oracle.

Tiện ích mở rộng 'pgTAP' và 'pg_prove' có thể được sử dụng để kiểm tra chức năng của các chức năng PostgreSQL. Sau khi cài đặt 'pgTAP', nó tạo ra rất nhiều chức năng được lưu trữ được sử dụng để viết bài kiểm tra. Chức năng kiểm tra phải được khai báo là 'trả về văn bản setof'. Sau đó, nó có thể được chạy từ máy khách bảng điều khiển psql:

 psql -h $db_host -p $db_port -U $db_user $db_name -f tests/name_of_test.sql

Thông tin chi tiết về pgTAP có thể được tìm thấy ở đây: http://pgtap.org/documentation.html

Phần mở rộng postgreSQL plpgsql_check giúp xác thực mã PL/pgSQL. Ví dụ, nếu thiếu khai báo biến hoặc tên cột sai chính tả bên trong hàm PostgreSQL, điều này chắc chắn sẽ thất bại trong quá trình thực thi. Sử dụng khả năng của 'plpgsql_check' để chẩn đoán các sự cố như vậy trước khi đưa vào sản xuất:

 select * from plpgsql_check_function_tb('{name of function}');

Mô-đun plprofiler giúp phân tích hiệu suất của mã PL/pgSQL trong các hàm và thủ tục được lưu trữ.

Phần mở rộng PostgreSQL oracle_fdw cung cấp kết nối tới cơ sở dữ liệu Oracle thông qua tính năng Foreign Data Wrapper của DBMS. Ví dụ: nếu cơ sở dữ liệu Oracle 'OCRL' đang chạy trên máy chủ 'server.mydomain.com', thì Oracle_fdw phải được cấu hình như sau:

 CREATE EXTENSION oracle_fdw; CREATE SERVER oraserver FOREIGN DATA WRAPPER oracle_fdw OPTIONS (dbserver '//server.mydomain.com:1521/ORCL'); GRANT USAGE ON FOREIGN SERVER oraserver TO pguser; CREATE USER MAPPING FOR pguser SERVER oraserver OPTIONS (user 'oracle user', password 'oracle password'); CREATE FOREIGN TABLE oratable ( { column definitions } ) SERVER oraserver OPTIONS (schema 'ORAUSER', table 'ORATAB');

Sau đó, bạn có thể sử dụng ' oratable ' như một bảng PostgreSQL thông thường.