CYBERTEC erbjuder omfattande tjänster för att flytta från Oracle till PostgreSQL. Intelligenta verktyg finns på vår webbplats. Vi vill hjälpa dig att flytta till PostgreSQL snabbare. Den här snabbstartsguiden för migrering kommer att få dig att snabbt röra dig i rätt riktning.

Det finns olika aspekter som du måste ta hänsyn till när du flyttar från Oracle till PostgreSQL, bland annat:

  • Migrering av datastrukturer
  • Migrering av data
  • Konvertering av vanliga SQL-meddelanden
  • Konvertering av PL/SQL till PL/Java
  • Lagringshantering i PostgreSQL och Oracle

Låt oss ta en titt på dessa viktiga punkter i detalj.

Migrering av datastrukturer

Det första du kommer att se är att PostgreSQL har många fler datatyper än Oracle. Det är därför viktigt att göra rätt val. Att välja rätt datatyp kan påverka prestandan massivt.

Om du är osäker på hur du ska mappa Oracle-datatyper till PostgreSQL har vi sammanställt en konverteringsmatris. Matrisen visar vilka typer som kan mappas till vilken motsvarighet.

Oracle typePossible PostgreSQL type
CHARchar, varchar, text
NCHARchar, varchar, text
VARCHARchar, varchar, text
VARCHAR2char, varchar, text, json, jsonb
NVARCHAR2char, varchar, text
CLOBchar, varchar, text, json, jsonb
LONGchar, varchar, text
RAWuuid, bytea
BLOBbytea
BFILEbytea (read-only)
LONG RAWbytea
NUMBERnumeric, float4, float8, char, varchar, text
NUMBER (n, m) with m<=0numeric, float4, float8, int2, int4, int8, boolean, char, varchar, text
FLOAT numeric, float4, float8, char, varchar, text
BINARY_FLOAT numeric, float4, float8, char, varchar, text
BINARY_DOUBLE numeric, float4, float8, char, varchar, text
DATEdate, timestamp, timestamptz, char, varchar, text
TIMESTAMPdate, timestamp, timestamptz, char, varchar, text
TIMESTAMP WITH TIME ZONEdate, timestamp, timestamptz, char, varchar, text
TIMESTAMP WITH LOCAL TIME ZONEdate, timestamp, timestamptz, char, varchar, text
INTERVAL YEAR TO MOTHinterval, char, varchar, text
INTERVAL DAY TO SECONDinterval, char, varchar, text
XML TYPExml, char, varchar, text
MDSYS.SDO_GEOMETRYgeometry (see ”PostGIS support” below)

 

Helst väljer du en datatyp som ger minst overhead. Vad innebär det? Här är ett exempel: number(10, 0) kan mappas till numeric(10, 0) men du kan också mappa den till int8. Att använda int8 är vanligtvis mycket effektivare. Int8 kommer definitivt att äta 8 bytes – men beräkningar går mycket snabbare om du använder int8 i stället för numeric.

varchar2 kan mappas direkt till varchar eller datatypen text. Vi har dock funnit det användbart att beakta tillägget ”citext” i PostgreSQL. Tanken bakom citext är att ha en textdatatyp som inte tar hänsyn till stor- och småbokstäver, vilket gör sökningen mycket bekvämare. I webbapplikationer är det oftast en skiftlägeskänslig sökning som man är ute efter. Om du är allmänt intresserad av fuzzy search kan du överväga att kolla in vårt blogginlägg om detta exceptionellt viktiga ämne. En migrering är vanligtvis ett bra tillfälle att städa upp och förbättra saker och ting.

BLOBs är också en fråga som många frågar om. I allmänhet erbjuder PostgreSQL datatypen bytea (“byte array”). Den känns som en textkolumn men den kan faktiskt innehålla binära data. Om du inte vill använda en kolumn för att lagra BLOBs är det också möjligt att använda PostgreSQLs BLOB-gränssnitt direkt.

OBS: Det är viktigt att nämna att Oracle Spatial kan mappas till PostGIS och PostgreSQL. Så om du använder GIS-data finns det ett sätt att migrera till PostgreSQL också. Det finns inga praktiska begränsningar i det fallet heller.

Om du vill mappa datatyper automatiskt kan du överväga att kolla in CYBERTEC Migrator. Den har avancerade funktioner för mappning av datatyper och erbjuder ett enkelt sätt att förutsäga den bästa möjliga datatypen för att lagra stora datamängder i PostgreSQL.

CREATE SYNONYM: Den saknade skönheten

Oracle tillhandahåller kommandot CREATE SYNONYM som inte finns tillgängligt i PostgreSQL. För en tid sedan implementerade vi på CYBERTEC CREATE SYNONYM för PostgreSQL, men denna ändring förkastades eftersom den inte passar in i PostgreSQL:s övergripande koncept.

Resultatet är att du måste använda en lösning:

  • Synonymer för tabeller: Använd en vy i stället (SELECT * …)
  • Synonymer för vy: Använd en vy ovanpå vyn. (SELECT * …)
  • Synonymer för funktioner/förfaranden: Använd wrapper-funktioner
  • Synonymer för materialiserade vyer: Användning av vyer

I allmänhet är det bättre att göra sig av med dessa synonymer under migreringen. Det gör framtida utveckling mycket enklare. Att ställa in  search_path har visat sig vara allmänt användbart och minskar ofta behovet av att använda synonymer överhuvudtaget.

Ogiltiga vyer i Oracle – giltiga vyer i PostgreSQL

När du migrerar datastrukturer från Oracle till PostgreSQL kommer du att märka att PostgreSQL använder strikt spårning av beroenden. Låt mig ge dig ett exempel: Om en tabell släpps kommer PostgreSQL automatiskt att släppa alla dess beroende objekt. I Oracle är detta inte fallet. Du kan behöva hantera ogiltiga vyer, vilket är ett problem som du bör vara medveten om under ett migreringsprojekt.

Migrera data till PostgreSQL

När datastrukturerna i Oracle har flyttats till PostgreSQL är det dags att tänka på att flytta data i allmänhet.

Mycket kan göras med migreringsverktyget, men det finns en del saker som behöver uppmärksammas även på Oracle-databasens sida. Här är några av de viktigaste aspekterna:

  • Oändliga tal (“minus infinite” och “plus infinite”)
    • Kan mappas till ”infinity” om dubbel precision används (vilket kanske inte är vad du vill, trots allt).
  • Korrupta strängar i Oracle
    • Ogiltig bytessekvens för kodning ”UTF8”: 0x80
    • Korrupta strängar är vanligare än du tror.
    • Det är viktigt att ha en plan för att hantera dessa
  • Noll bytes i Oracle
    • Ogiltig bytessekvens för kodning ”UTF8”: 0x00
    • Ogiltiga sekvenser är också ganska vanliga

Som tidigare nämnts kan de flesta av ovanstående problem inte enkelt lösas under migrationsprocessen, utan kräver viss förbehandling på källsidan. Migrationsverktyget kan naturligtvis lösa många problem, men inte alla. Det måste du vara medveten om.

När data läses från Oracle måste det göras i en enda transaktion för att säkerställa konsistens (eller åtminstone i många transaktioner som kan se samma ögonblicksbild av data som CYBERTEC Migrator gör). För att uppnå detta kommer du att köra en lång transaktion i Oracle. Detta kan vara ett problem i sig, eftersom Oracle avslutar en transaktion i händelse av ”ORA-01555: snapshot too old”

Lösningen är att konfigurera den så att den kan hantera mycket långa transaktioner innan du börjar migrera data. Detta görs genom att använda riktigt stora UNDO tablespaces.

Det är också lite svårt att flytta data. I Oracle finns det ett par begränsningar i OCI-gränssnittet som kan leda till mycket dålig prestanda när stora fält migreras till PostgreSQL. Dessa begränsningar och prestandaproblem orsakas av hur Oracle läser data, och i många fall kan klientkoden inte riktigt göra mycket åt dem.

PostgreSQL och tabeller

I Oracle är begreppet ”tablespace” mycket viktigt. Man skulle kunna hävda att Oracle i princip implementerar sitt eget filsystem. I motsats till detta förlitar sig PostgreSQL i större utsträckning på funktionaliteten hos driftssystemet (POSIX). Motstå uppmaningen att skapa tablespaces under migreringen. Tablespace är inte särskilt meningsfullt i PostgreSQL-världen.

I en modern molnmiljö är tablespaces dessutom oftast något som hör till det förflutna. Så om du skapar en ny infrastruktur för PostgreSQL på Kubernetes, som den som tillhandahålls av Scalefield, finns det ingen anledning att använda tablespaces alls.

Konvertering av vanliga SQL-uttalanden

Oracle har vissa specialiteter i sin SQL-stil som måste beaktas när man migrerar kod från Oracle till PostgreSQL. Det här avsnittet visar några av de vanligaste problemen som människor stöter på under sina migreringar.

Omskrivning av fogningar för PostgreSQL

Oracle använder en annan syntax för yttre sammanfogningar än resten av världen. Det bör du ha i åtanke när du konverterar kod från Oracle till PostgreSQL. Följande exempel visar hur detta fungerar.

Oracle

SELECT b.col1, a.col2
FROM base_table b, attributes a
WHERE b.id=a.b_id(+);

 

måste översättas till

PostgreSQL

SELECT b.col1, a.col2
FROM base_table b
LEFT JOIN attributes a ON b.id = a.b_id;

Ändringen är faktiskt enkel, men ganska irriterande att göra.

Underval och alias

I Oracle behöver ett underval inte ha något namn. Den kan vara ”anonymous”:

SELECT * FROM (SELECT * FROM some_table)

Du kan enkelt skriva om detta i PostgreSQL. Följande kod visar hur motsvarigheten i arbetet med öppen källkod faktiskt skulle se ut:

SELECT * FROM (SELECT * FROM some_table) AS your_fancy_name

Hantering av NULL och tomma strängar

Oracle behandlar NULL som en tom sträng. Detta har ett par konsekvenser:
'hello' || NULL är inte NULL i Oracle. Därför måste det översättas till följande SQL-fragment för att få samma semantik i PostgreSQL:

concat('hello', NULL)
eller använd
coalesce(strcol, '')

Migrering av null-angivelser kan leda till subtila fel som kan vara svåra att spåra. Du bör hålla ett vaksamt öga på sådana detaljer och explicit kontrollera dem.

Tidsfunktioner i Oracle och PostgreSQL

De flesta Oracle-koder använder proprietära funktioner som …

  • SYSDATE
  • SYSTIMESTAMP

Det är nödvändigt att översätta dessa anrop för att passa PostgreSQL:

  • clock_timestamp()
  • current_date or current_timestamp

Lyckligtvis kan denna förändring enkelt göras. Det räcker oftast med att söka och ersätta.

Migrering av sekvenser till PostgreSQL

Oracle-databaser hanterar sekvenser på ett helt annat sätt. Du kommer att se följande metod för att producera nästa värde:

asequence.NEXTVAL

PostgreSQL motsvarigheten är:

nextval('asequence')

Båda databaserna använder inte ANSI SQL-systemet för att hämta värden från en sekvens, vilket skulle se ut så här: NEXT VALUE FOR asequence

Inget behov av DUAL

I PostgreSQL behöver en fråga inte nödvändigtvis ha en FROM-klausul. Det är helt okej att inte använda en sådan. Därför finns det inget behov av DUAL.

SELECT * FROM dual;

… är inte nödvändigt i PostgreSQL. orafce kan simulera DUAL, men det är bättre att undvika det på PostgreSQL-sidan. Det finns helt enkelt inget behov av det.

DECODE: Den allsmäktige

DECODE är en Oracle-specifik sak:

decode(expr, search, result [, search, result...] [, default])

Sättet att ändra saker i PostgreSQL är att …

  • Ersätt med CASE / WHEN
  • Ersätt med funktionen ”decode” som tillhandahålls av orafce.

Vanlig PostgreSQL fungerar på följande sätt:

CASE WHEN expr THEN expr [...] ELSE expr END

I allmänhet kan DECODE ersättas ganska enkelt. Den innehåller ingen komplicerad logik.

Hantering av rekursioner: CONNECT BY

I Oracle finns det två sätt att hantera rekursioner:

WITH RECURSIVE: Ny syntax
CONNECT BY: Gammal syntax

PostgreSQL har en fullständig WITH RECURSIVE-implementering. Den gamla CONNECT BY-syntaxen är mycket Oracle-specifik och kan inte användas i PostgreSQL (eller någon annan SQL-databasmotor som vi känner till).

Här är ett exempel som visar hur CONNECT BY kan översättas till WITH RECURSIVE:


SELECT empno, ename, level
FROM emp
START WITH empno = 7788
CONNECT BY PRIOR mgr = empno;

… mot …

WITH hierarchy (empno, ename, mgr, "level") AS (
SELECT empno, ename, mgr, 1 AS "level"
FROM emp
WHERE empno = 7788
UNION ALL
SELECT e.empno, e.ename, e.mgr, h."level" + 1
FROM hierarchy h
JOIN emp e ON h.mgr = e.empno
) SELECT empno, ename, "level" FROM hierarchy;

Konvertering av PL/SQL och PL/Java

Den dyraste delen av varje migrering är att migrera koden för den lagrade proceduren. Även om PL/pgSQL och PL/SQL är ganska lika varandra finns det fortfarande skillnader som måste beaktas. Om du använder PL/Java på Oracle-sidan kan du använda PostgreSQL:s motsvarighet, men vi föreslår att du hellre byter ut koden helt och hållet. Att köra Java på serversidan är inte särskilt vanligt i PostgreSQL-världen.

PL/pgSQL är en klon av PL/SQL, men den är tillräckligt annorlunda för att kräva viss uppmärksamhet (t.ex. RETURNS vs. RETURN). CYBERTEC Migrator har möjlighet att automatiskt konvertera koden.

Autonoma transaktioner i Oracle och PostgreSQL

PostgreSQL har inget sådant som PRAGMA AUTONOMOUS_TRANSACTION. Man måste arbeta runt denna saknade funktionalitet. I allmänhet är det bättre att ersätta autonoma transaktioner med savepoints och försöka se till att transaktionerna faktiskt kan bekräfta. I vissa sällsynta fall finns det inget sätt att arbeta runt autonoma transaktioner. Sättet att modellera detta i PostgreSQL är att använda tillägget dblink.

BULK COLLECT: Använd rader

BULK COLLECT är inte tillgängligt på PostgreSQL-sidan. Sättet att arbeta runt detta i PostgreSQL är att använda ett tillvägagångssätt rad för rad.

Var försiktig i det här fallet. Effektiviteten kan bli ett problem. Det är därför bättre att använda SQL-meddelanden än slingor.

Simulering av Oracle-funktioner

I många fall erbjuder PostgreSQL samma funktionalitet som Oracle. Ofta är dock namnen på funktionerna helt enkelt olika.

Orafce-tillägget för PostgreSQL implementerar många av dessa Oracle-specifika funktioner i PostgreSQL. Genom att använda tillägget kan antalet manuella kodändringar minskas dramatiskt.

Följande lista visar några kodexempel och visar hur saker och ting kommer att exponeras för slutanvändaren:

SELECT last_day('2000-02-01 121143');
SELECT next_day ('2008-01-01 121212', 'sunAAA');
SELECT months_between ('2007-02-28 111111', '2007-04-30 112121');
select dbms_pipe.purge('bob');
select instr('Tech on the net', 'e') = 2;

 

orafce har funnits i många år och är mogen. Man kan lita på den.

PL/SQL-paket

I PostgreSQL finns det inget koncept för PL/SQL-paket. Det finns dock sätt att simulera paket. Det finns olika sätt att närma sig problemet:

  • Använd CREATE EXTENSION – Du kan alltid packa in kod i tillägg.
  • Använd scheman för varje ”package”.

Du kommer att upptäcka att paket inte är något problem och att du lätt kan leva utan dem på PostgreSQL-sidan.

Migrera triggers till PostgreSQL

I PostgreSQL anropar en trigger alltid en funktion. Kommandot CREATE TRIGGER innehåller inte koden direkt:

demo=# \h CREATE TRIGGER
Command: CREATE TRIGGER
Description: define a new trigger
Syntax:
CREATE [ CONSTRAINT ] TRIGGER name { BEFORE | AFTER | INSTEAD OF } { event [ OR ... ]
}
ON table_name
[ FROM referenced_table_name ]
[ NOT DEFERRABLE | [ DEFERRABLE ] [ INITIALLY IMMEDIATE | INITIALLY DEFERRED ]
]
[ REFERENCING { { OLD | NEW } TABLE [ AS ] transition_relation_name } [ ... ] ]
[ FOR [ EACH ] { ROW | STATEMENT } ]
[ WHEN ( condition ) ]
EXECUTE { FUNCTION | PROCEDURE } function_name ( arguments )

där händelsen kan vara en av följande:

INSERT
UPDATE [ OF column_name [, ... ] ]
DELETE
TRUNCATE

 

Det fina med PostgreSQL är att koden lätt kan återanvändas. Det som också är värt att notera här är att triggers för att öka värden inte behövs. Du kan helt enkelt använda DEFAULT-kolumner för att automatiskt bestämma nästa värde och ställa in det.

LOGON triggers stöds inte i PostgreSQL. Om du behöver den här typen av funktionalitet måste du flytta koden till programmet eller undvika det helt och hållet.

Professionell hjälp

Vi på CYBERTEC har hjälpt otaliga kunder att övergå från Oracle till PostgreSQL. Dessutom erbjuder vi CYBERTEC Migrator för att underlätta övergången. Kontakta oss idag och få ett personligt erbjudande om migration. Vi erbjuder över 20 års erfarenhet av PostgreSQL, en professionell hantering och en leverans i tid.

Kontakta oss >>