Oracle से PostgreSQL में माइग्रेट कैसे करें

यह श्वेतपत्र एक बड़ी कार्गो डिलीवरी कंपनी के लिए इंटेलिजेंट कन्वर्टर्स द्वारा बनाए गए Oracle से PostgreSQL में डेटाबेस माइग्रेशन के अनुभव को कवर करता है। प्रवासन का प्रमुख कारण डेटाबेस प्रबंधन प्रणाली की लागत को कम करना और पर्याप्त मापनीयता, सुरक्षा और अनुकूलन क्षमताओं के साथ ओपन-सोर्स सिस्टम में स्थानांतरित करना था। यहाँ स्रोत डेटाबेस का संक्षिप्त विवरण दिया गया है: डेटाबेस सर्वर Oracle 12g डेटाबेस में 190 टेबल, 50 जीबी डेटा है संग्रहित प्रक्रियाओं, कार्यों और ट्रिगर्स में PL/SQL की 120000 लाइनें मूल्यांकन और योजना का आधा महीना, माइग्रेशन के लिए एक महीना और परीक्षण के लिए आधा महीना। माइग्रेशन में 3 विशेषज्ञ शामिल थे: 2 डेवलपर और 1 डेटाबेस एडमिनिस्ट्रेटर। परियोजना की अवधि 2 महीने थी: इस Oracle से PostgreSQL डेटाबेस माइग्रेशन में छह बुनियादी चरण शामिल हैं: स्रोत डेटाबेस और उपयोग की गुंजाइश (जांच और योजना चरण) में डेटा के भंडारण और प्रसंस्करण के सभी ओरेकल-विशिष्ट तरीकों का पता लगाएं स्कीमा माइग्रेशन के लिए उपयुक्त टूल्स का चयन करें और इसे लागू करें Oracle सिस्टम डाउनटाइम को कम करने के लिए डेटा माइग्रेशन का सबसे उपयुक्त तरीका चुनें PostgreSQL DBMS द्वारा आवश्यक सभी परिवर्तनों को संभालने वाला डेटा माइग्रेशन चलाएँ सभी पीएल/एसक्यूएल कोड को पोस्टग्रेएसक्यूएल प्रारूप में कनवर्ट करें (आंशिक ऑटोमेटन और मैन्युअल पोस्ट-प्रोसेसिंग के लिए टूल का उपयोग करके) परिणामी डेटाबेस की फाइन-ट्यूनिंग, प्रदर्शन और कार्यात्मक परीक्षण चलाएं तालिका परिभाषाओं का प्रवासन Oracle में कुछ डेटा प्रकार हैं जिनका PostgreSQL में कोई प्रत्यक्ष समतुल्य नहीं है। इनमें से एक प्रकार DATE है जिसमें दिनांक और समय दोनों भाग होते हैं। पोस्टग्रेएसक्यूएल समर्थन करता है: तिथि - समय भाग के बिना शुद्ध तिथि समय - समय क्षेत्र वैकल्पिक विनिर्देश के साथ दिनांक भाग के बिना शुद्ध समय टाइमस्टैम्प - समय क्षेत्र वैकल्पिक विनिर्देश के साथ दिनांक और समय Oracle दिनांकों को PostgreSQL में मैप करने के दो विकल्प हैं: या तो TIMESTAMP का उपयोग करें या Oracle-शैली दिनांक प्रकार oracle.date का उपयोग करने के लिए एक्सटेंशन सेट करें। orafce स्थानिक प्रकारों पर भी विशेष ध्यान देने की आवश्यकता होती है। Oracle में अंतर्निहित प्रकार SDO_GEOMETRY है जबकि PostgreSQL को स्थानिक डेटा प्रकारों के साथ काम करने के लिए PostGIS स्थापना की आवश्यकता है। नीचे दी गई तालिका Oracle से PostgreSQL सुरक्षित प्रकार की मैपिंग को दर्शाती है: आकाशवाणी पोस्टग्रेएसक्यूएल बाइनरी_फ्लोट असली BINARY_INTEGER पूर्णांक बिनरी_डबल दोहरी सुनिश्चितता ब्लॉब, रॉ (एन), लॉन्ग रॉ BYTEA (1GB सीमा) सीएलओबी, लंबा पाठ (1 जीबी सीमा) तारीख TIMESTAMP संख्या, संख्या (*) डबल प्रेसिजन या बिगिनट अगर यह प्राथमिक कुंजी का हिस्सा है संख्या (एन, 0), संख्या (एन) n =19 – DECIMAL(n) संख्या (पी, एस) दशमलव (पी, एस) असली दोहरी सुनिश्चितता संख्यात्मक प्रकारों के लिए डेटाबेस में उपयोग के दायरे को समझना महत्वपूर्ण है। यदि यह सटीकता पर केंद्रित है, तो Oracle न्यूमेरिक प्रकार को PostgreSQL NUMERIC में मैप किया जाना चाहिए। यदि सर्वोच्च प्राथमिकता गणना की गति है, तो सबसे अच्छी मैपिंग वास्तविक या दोहरी सटीकता होगी। हमने इस परियोजना के लिए टेबल परिभाषाओं, इंडेक्स और बाधाओं के माइग्रेशन को स्वचालित करने के लिए उपयोग किया। यह Oracle प्रकारों को सबसे उपयुक्त PostgreSQL समकक्षों में मैप करता है और विशेष प्रकार के मानचित्रण को अनुकूलित करने की अनुमति देता है। Oracle-to-PostgreSQL कन्वर्टर का आंकड़ों का विस्थापन चूंकि डेटा माइग्रेशन बड़े डेटाबेस के लिए अधिक समय ले सकता है, इसलिए इस चरण के लिए सही रणनीति और उपकरण चुनना अत्यंत महत्वपूर्ण है। डेटा माइग्रेशन के तीन सामान्य तरीके हैं: स्नैपशॉट - सभी डेटा को एक चरण में माइग्रेट करें टुकड़ेवार स्नैपशॉट - समानांतर थ्रेड्स या प्रक्रियाओं में विखंडू द्वारा डेटा माइग्रेट करें डेटा प्रतिकृति बदलें - वृद्धिशील परिवर्तनों को ट्रैक करके डेटा को लगातार लोड करना माइग्रेशन के दौरान डेटा हानि या भ्रष्टाचार से बचने के लिए स्नैपशॉट विधि को डेटा पढ़ने की पूरी अवधि के लिए स्रोत डेटाबेस के आवश्यक डाउनटाइम की आवश्यकता होती है। टुकड़ावार स्नैपशॉट दृष्टिकोण के लिए डाउनटाइम बहुत कम है लेकिन फिर भी आवश्यक है। इस माइग्रेशन प्रोजेक्ट में हमने लाखों पंक्तियों वाली बड़ी टेबलों के माइग्रेशन के लिए टुकड़ेवार स्नैपशॉट विधि का उपयोग किया। तो, परिवर्तन डेटा प्रतिकृति (सीडीआर) तकनीक के बारे में क्या? इसके दो प्रमुख कार्यान्वयन हैं और उनमें से प्रत्येक की अपनी कमजोरी है। पहले वाले को ट्रिगर-आधारित सीडीआर के रूप में जाना जाता है। स्रोत डेटाबेस में दोहराई जाने वाली प्रत्येक तालिका के लिए सम्मिलित करने, अद्यतन करने और हटाने पर ट्रिगर बनाने की आवश्यकता होती है। वे ट्रिगर घटनाओं को विशेष 'इतिहास' तालिका में बदलने के बारे में जानकारी संग्रहीत करके सभी परिवर्तनों को ट्रैक करते हैं। इस डेटा के आधार पर CDR टूल लक्ष्य डेटाबेस में सभी परिवर्तनों को दोहराता है। प्रत्येक डेटा अद्यतन के लिए 'इतिहास' तालिका में लिखने के कारण ट्रिगर-आधारित सीडीआर स्रोत डेटाबेस के अतिरिक्त ओवरहेड का कारण बनता है। दूसरी विधि को लेनदेन लॉग सीडीआर कहा जाता है। यह लक्ष्य PostgreSQL डेटाबेस में परिवर्तनों को ट्रैक और दोहराने के लिए Oracle DBMS द्वारा बनाए गए लेनदेन लॉग का उपयोग करता है। ट्रिगर-आधारित सीडीआर की तुलना में इस दृष्टिकोण का लाभ यह है कि यह स्रोत डेटाबेस को संशोधित नहीं करता है। हालाँकि, लेन-देन लॉग सीडीआर में कुछ कमजोरियाँ भी हैं: Oracle लेन-देन लॉग फ़ाइलों को लगातार संग्रहीत करता है, इसलिए CDC उपकरण कुछ परिवर्तनों को खो सकता है यदि यह संगृहीत होने से पहले लेन-देन लॉग को नहीं पढ़ता है। लेन-देन लॉग (या किसी अन्य प्रकार की त्रुटि) से परिवर्तन की नकल करते समय लक्ष्य डेटाबेस से कनेक्शन खोने के मामले में, लेन-देन लॉग पर नियंत्रण की कमी के कारण डेटा खो या क्षतिग्रस्त हो सकता है। स्रोत DBMS के विशिष्ट डेटा प्रारूप जिनका लक्ष्य और बाहरी डेटा में कोई प्रत्यक्ष समतुल्य नहीं है। Oracle से PostgreSQL में डेटा माइग्रेशन के सबसे चुनौतीपूर्ण पहलू हैं: इस लेख के पिछले भाग में BYTEA को बाइनरी डेटा के लिए सबसे उपयुक्त PostgreSQL डेटा प्रकार के रूप में बताया गया है। हालांकि, बड़े बाइनरी डेटा (औसत फ़ील्ड आकार 10MB से कम नहीं है) को माइग्रेट करते समय BYTEA का उपयोग करने की अनुशंसा नहीं की जाती है। कारण BYTEA डेटा पढ़ने की ख़ासियत है - इसे केवल एक टुकड़े में निकाला जा सकता है; टुकड़ों में पढ़ना संभव नहीं है। यह आवश्यक रैम ओवरहेड का कारण बन सकता है। इस समस्या के समाधान के लिए PostgreSQL प्रकार LARGE OBJECT का उपयोग किया जा सकता है। LARGE OBJECT प्रकार के सभी मान सिस्टम तालिका 'pg_largeobject' में संग्रहीत होते हैं जो प्रत्येक डेटाबेस का एक भाग होता है। तालिका 'pg_largeobject' में 4 बिलियन तक पंक्तियाँ हो सकती हैं। बड़ी वस्तु का अधिकतम आकार 4TB है और टुकड़ों में पढ़ने की सुविधा उपलब्ध है। साथ ही, तालिका में रिकॉर्ड के भौतिक पते की पहचान करने वाले छद्म-स्तंभ वाले ROWID को ठीक से माइग्रेट करना महत्वपूर्ण है। PostgreSQL का एक समान सेवा क्षेत्र है जिसे ctid कहा जाता है, हालाँकि यह ROWID का प्रत्यक्ष समकक्ष है। कहता है कि वैक्यूमिंग प्रक्रिया के कारण ctid को बदला जा सकता है। PostgreSQL प्रलेखन PostgreSQL में ROWID कार्यक्षमता का अनुकरण करने की तीन बुनियादी विधियाँ हैं: ROWID के बजाय पंक्तियों की पहचान करने के लिए मौजूदा प्राथमिक कुंजी का उपयोग करें (या नया बनाएं)। ऑटो-जेनरेट किए गए मानों के साथ एक सीरियल या बिगसीरियल कॉलम जोड़ें और इसे ROWID कार्यक्षमता को बदलने के लिए प्राथमिक/अद्वितीय कुंजी बनाएं जब एकल स्तंभ प्राथमिक कुंजी को परिभाषित करना संभव न हो, तो एकाधिक स्तंभों पर निर्मित अद्वितीय अनुक्रमणिका का उपयोग करें (इस उद्देश्य के लिए प्रत्येक पंक्ति के लिए फ़ील्ड का न्यूनतम अद्वितीय सेट निर्धारित करें) Oracle बाहरी डेटा को लिंक कर सकता है जो डेटाबेस के बाहर संग्रहीत है। हालाँकि, इसे DBMS की 'बाहरी तालिका' सुविधा का उपयोग करके एक नियमित तालिका के रूप में संसाधित किया जा सकता है। PostgreSQL इसी उद्देश्य के लिए विदेशी डेटा रैपर लाइब्रेरी का उपयोग करता है। उदाहरण के लिए, यह नियमित तालिका के रूप में बाहरी CSV फ़ाइलों के साथ काम करने के लिए एक्सटेंशन 'file_fdw' प्रदान करता है। पीएल/एसक्यूएल प्रवासन की चुनौतियाँ यह खंड उन मुद्दों की पड़ताल करता है जो Oracle पैकेज, स्टोर प्रक्रियाओं, कार्यों और ट्रिगर के PostgreSQL के प्रवास के दौरान हल किए गए थे। PostgreSQL में संकुल नहीं है। PostgreSQL स्कीमा के अंदर एक ही नाम के साथ सभी प्रविष्टियों को समूहीकृत करके इस लापता सुविधा का अनुकरण किया जा सकता है, वैश्विक चर को एक सेवा तालिका में संग्रहीत किया जा सकता है। संकुल। Oracle के विपरीत, PostgreSQL में खाली तार NULL के बराबर नहीं होते हैं। उदाहरण के लिए, ओरेकल में: खाली तार। SQL> select NVL('','This is null') as null_test from dual; NULL_TEST ------------ This is null PostgreSQL में वही: testdb=# select coalesce('','This is null') as null_test; null_test ----------- (1 row) इसके अलावा, Oracle और PostgreSQL में NULL के साथ स्ट्रिंग संघनन का एक अलग व्यवहार है। ओरेकल में: SQL> Select 'String' || NULL as cs from dual; CS -- String पोस्टग्रेएसक्यूएल में: synctest=# Select 'String' || NULL as concat_str; concat_str ------------ (1 row) फ़ंक्शन, ऑपरेटरों को कॉल करते समय या जब INSERT और UPDATE स्टेटमेंट अभिव्यक्ति के परिणामों को तालिका में रखते हैं, तो PostgreSQL को सख्त प्रकार की कास्टिंग की आवश्यकता होती है। कास्टिंग के प्रकार। कामकाज छद्म-प्रकार 'एनीमेंट' का उपयोग करना है: create or replace function my_concat(str1 anyelement, str2 anyelement) returns varchar language plpgsql as $$ begin return str1::varchar || str2::varchar; end; $$; नोट: आप या तो संग्रहीत कार्यविधि/फ़ंक्शन में किसी एक एलीमेंट तर्क का उपयोग कर सकते हैं या एक ही प्रकार के किसी भी एलीमेंट के सभी पैरामीटर का उपयोग कर सकते हैं। अनुक्रम घोषणा का सिंटैक्स Oracle और PostgreSQL के लिए समान है। उदाहरण के लिए: अनुक्रम। CREATE SEQUENCE SEQ1 START WITH 10 INCREMENT BY 2 MAXVALUE 1000000 CACHE 20 NO CYCLE; हालाँकि, अनुक्रम के अगले मान को संदर्भित करना अलग है। Oracle में यह 'sequence.nextval' है और PostgreSQL nextval('sequence') में है। Oracle में ट्रिगर की बॉडी का सोर्स कोड CREATE TRIGGER स्टेटमेंट में शामिल है। PostgreSQL के लिए आवश्यक है कि ट्रिगर स्रोत कोड को 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(); Oracle में स्वायत्त लेन-देन एक उपप्रोग्राम को मुख्य लेन-देन को कमिट या रोल-बैक किए बिना SQL संचालन को कमिट या रोलबैक करने की अनुमति देता है। उदाहरण के लिए, कुछ डेटा इन्सर्ट-ट्रिगर के एक भाग के रूप में टेबल में डाला जाता है। स्वायत्त लेनदेन। इंसर्ट-ट्रिगर का मुख्य लेन-देन विफल होने पर भी यह इंसर्ट ऑपरेशन सफल होना चाहिए। इस मामले में संबंधित INSERT विवरण स्वायत्त लेनदेन में संलग्न होना चाहिए: 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; स्वायत्त लेन-देन सीधे PostgreSQL में समर्थित नहीं हैं। एक संभावित समाधान कोड को रिफलेक्टर करना है, इसलिए इसे स्वायत्त लेनदेन की आवश्यकता नहीं है। अन्यथा, आप PostgreSQL के मॉड्यूल 'dblink' का उपयोग कर सकते हैं। डब्लिंक के साथ, एक नया कनेक्शन स्थापित किया जाता है, और इस कनेक्शन का उपयोग करके एक क्वेरी निष्पादित की जाती है और मुख्य लेन-देन के बावजूद तुरंत कमिट किया जाता है। उदाहरण के लिए, निम्न फ़ंक्शन एक तालिका में एक पंक्ति सम्मिलित करता है, और सम्मिलन ऑपरेशन तब भी किया जाएगा जब कॉलिंग लेनदेन वापस लुढ़का हो: 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; Oracle और PostgreSQL बिल्ट-इन फ़ंक्शंस के समान लेकिन समान सेट नहीं प्रदान करते हैं। नीचे दी गई तालिका में Oracle फ़ंक्शंस शामिल हैं जिन्हें PostgreSQL समकक्षों में पोर्ट करने की आवश्यकता होती है: अंतर्निहित कार्य। आकाशवाणी पोस्टग्रेएसक्यूएल ADD_MONTH($तारीख,$n_माह) $तारीख + $n_महीना * अंतराल '1 महीना' DECODE ($ ऍक्स्प, $ कब, $ तब, ...) मामला $exp जब $जब $फिर $फिर ... अंत INSTR($str1, $str2) *स्थिति($str2 $str1 में) राउनम **row_number() से अधिक () SYSDATE आज की तारीख SYS_GUID () uuid_generate_v1 () *PostgreSQL के लिए Oracle INSTR फ़ंक्शन का जटिल पोर्टिंग यहां पाया जा सकता है: https://www.postgresql.org/docs/9.0/static/plpgsql-porting.html#PLPGSQL-PORTING-APPENDIX ओरेकल की स्थिति 'जहां राउनम <एन' को पोस्टग्रेएसक्यूएल में 'लिमिट एन' में परिवर्तित किया जाना चाहिए हमने ट्रिगर्स, संग्रहीत प्रक्रियाओं और कार्यों के रूपांतरण को आंशिक रूप से स्वचालित करने के लिए ओरेकल-टू-पोस्टग्रेएसक्यूएल कोड कन्वर्टर का उपयोग किया। यह Oracle बिल्ट-इन फ़ंक्शंस को PostgreSQL समकक्षों में बदलने का समर्थन करता है और PL/SQL के अधिकांश सिंटैक्स पैटर्न को प्रोसेस करता है। हालाँकि, जटिल स्रोत कोड को आउटपुट के मैनुअल पोस्ट-प्रोसेसिंग की आवश्यकता हो सकती है। Oracle से PostgreSQL माइग्रेशन के लिए उपयोगी मॉड्यूल इस खंड में PostgreSQL मॉड्यूल का संक्षिप्त विवरण है जो Oracle से PostgreSQL में माइग्रेट करने और माइग्रेशन के परिणामों का परीक्षण करने में मदद कर सकता है। Oracle से माइग्रेट करते समय सबसे महत्वपूर्ण PostgreSQL मॉड्यूल में से एक है। यह Oracle डेटाबेस प्रबंधन प्रणाली के विशिष्ट प्रकारों, कार्यों और ऑपरेटरों का अनुकरण करता है। orafce एक्सटेंशन 'pgTAP' और 'pg_prove' का उपयोग PostgreSQL फ़ंक्शंस के कार्यात्मक परीक्षण के लिए किया जा सकता है। 'pgTAP' स्थापित करने के बाद, यह परीक्षण लिखने के लिए उपयोग किए जाने वाले बहुत सारे संग्रहीत कार्य बनाता है। टेस्ट फ़ंक्शन को 'टेक्स्ट के रिटर्न सेट' के रूप में घोषित किया जाना चाहिए। तब इसे psql कंसोल क्लाइंट से चलाया जा सकता है: psql -h $db_host -p $db_port -U $db_user $db_name -f tests/name_of_test.sql पीजीटीएपी के बारे में विस्तृत जानकारी यहां पाई जा सकती है: http://pgtap.org/documentation.html PostgreSQL एक्सटेंशन PL/pgSQL कोड को मान्य करने में मदद करता है। उदाहरण के लिए, यदि PostgreSQL फ़ंक्शन के अंदर लापता चर घोषणा या गलत वर्तनी वाला स्तंभ नाम है, तो यह निष्पादन के दौरान निश्चित रूप से विफल हो जाएगा। उत्पादन पर जाने से पहले ऐसी समस्याओं का निदान करने के लिए 'plpgsql_check' की क्षमताओं का उपयोग करें: plpgsql_check select * from plpgsql_check_function_tb('{name of function}'); मॉड्यूल कार्यों और संग्रहीत प्रक्रियाओं में पीएल/पीजीएसक्यूएल कोड के प्रदर्शन का विश्लेषण करने में मदद करता है। प्लप्रोफाइलर PostgreSQL एक्सटेंशन DBMS के विदेशी डेटा आवरण सुविधा के माध्यम से Oracle डेटाबेस से कनेक्शन प्रदान करता है। उदाहरण के लिए, यदि Oracle डेटाबेस 'OCRL' सर्वर 'server.mydomain.com' पर चल रहा है, तो oracle_fdw को निम्नानुसार कॉन्फ़िगर किया जाना चाहिए: oracle_fdw 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'); फिर, आप नियमित पोस्टग्रेएसक्यूएल तालिका के रूप में ' ' का उपयोग कर सकते हैं। oratable