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 type | Possible PostgreSQL type |
---|---|
CHAR | char, varchar, text |
NCHAR | char, varchar, text |
VARCHAR | char, varchar, text |
VARCHAR2 | char, varchar, text, json, jsonb |
NVARCHAR2 | char, varchar, text |
CLOB | char, varchar, text, json, jsonb |
LONG | char, varchar, text |
RAW | uuid, bytea |
BLOB | bytea |
BFILE | bytea (read-only) |
LONG RAW | bytea |
NUMBER | numeric, float4, float8, char, varchar, text |
NUMBER (n, m) with m<=0 | numeric, 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 |
DATE | date, timestamp, timestamptz, char, varchar, text |
TIMESTAMP | date, timestamp, timestamptz, char, varchar, text |
TIMESTAMP WITH TIME ZONE | date, timestamp, timestamptz, char, varchar, text |
TIMESTAMP WITH LOCAL TIME ZONE | date, timestamp, timestamptz, char, varchar, text |
INTERVAL YEAR TO MOTH | interval, char, varchar, text |
INTERVAL DAY TO SECOND | interval, char, varchar, text |
XML TYPE | xml, char, varchar, text |
MDSYS.SDO_GEOMETRY | geometry (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
orcurrent_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.