Hogyan migráljunk adatokat MySQL-ből PostgreSQL-be

Az adatbázisok a modern alkalmazások szívét képezik, és időről időre felmerülhet az igény az egyik rendszerből a másikba való átállásra. Két népszerű nyílt forráskódú relációs adatbázis-kezelő rendszer, a MySQL és a PostgreSQL között sok fejlesztő és cég választja a migrációt. Bár mindkettő kiváló eszköz, a PostgreSQL egyre inkább a teljesítmény, a robusztusság, a fejlett funkciók és a szigorúbb SQL-szabványok betartása miatt kerül előtérbe. Ez az átfogó útmutató végigvezet a MySQL-ből PostgreSQL-be történő adatmigráció lépésein, hogy a folyamat a lehető legzökkenőmentesebb legyen.

Miért migráljunk MySQL-ből PostgreSQL-be?

Mielőtt belevágnánk a technikai részletekbe, érdemes megérteni, miért érdemes egyáltalán megfontolni ezt a migrációt:

Fejlett funkciók: A PostgreSQL számos fejlett funkciót kínál (pl. komplex adattípusok, tábla öröklődés, anyagi nézetek, ablakfüggvények, JSONB támogatás), amelyek gyakran hiányoznak a MySQL-ből vagy másképp implementáltak.
Robusztusság és megbízhatóság: A PostgreSQL rendkívül stabil és megbízható, sokan az „EnterpriseDB” jelzővel illetik nyílt forráskódú megfelelőjeként.
Adatintegritás: Szigorúbban betartja az SQL-szabványokat, ami jobb adatintegritást eredményez.
Teljesítmény: Komplex lekérdezések, nagy mennyiségű adat és egyidejű tranzakciók esetén gyakran felülmúlja a MySQL-t, különösen, ha írási műveletekről van szó.
Licencelés: Mindkettő nyílt forráskódú, de a PostgreSQL BSD licenc alatt fut, ami sokak számára rugalmasabbnak tűnik, mint a MySQL GPL licencelése (bár az Oracle tulajdonában lévő MySQL-nek van kereskedelmi verziója is).

1. Előkészületek: A sikeres migráció alapja

Az adatbázis áttelepítés nem egy „kapkodjuk el” feladat. A gondos tervezés és előkészítés kulcsfontosságú a problémák elkerülése érdekében.

1.1. A két adatbázis közötti különbségek megértése

Míg mindkettő relációs adatbázis, számos alapvető különbség van közöttük, amelyeket fel kell térképezni:

Adattípusok: A leggyakoribb különbség. Például a MySQL INT, BIGINT, DATETIME, TEXT típusai gyakran közvetlenül leképezhetők PostgreSQL-re, de vannak olyanok, amelyeknél módosításra van szükség. A TINYINT(1) a MySQL-ben gyakran booleanként működik, míg a PostgreSQL-ben a dedikált BOOLEAN típust kell használni. A MySQL ENUM és SET típusaihoz a PostgreSQL-ben dedikált ENUM típusokat vagy külön ellenőrző kényszereket kell létrehozni.
Auto-inkrementáló azonosítók: A MySQL AUTO_INCREMENT kulcsszót használ, míg a PostgreSQL SERIAL vagy BIGSERIAL (ami valójában egy SEQUENCE és egy DEFAULT kifejezés együttese).
Karakterkészletek és rendezés (Collation): Győződjön meg róla, hogy a cél PostgreSQL adatbázis karakterkészlete és rendezési beállításai megfelelnek a forrás MySQL adatbáziséinak (pl. UTF-8). A rendezési sorrend (pl. case-insensitivity) eltérő lehet.
SQL szintaxis és funkciók: Néhány gyakran használt MySQL függvénynek nincs közvetlen megfelelője a PostgreSQL-ben, vagy eltérő a szintaxisuk. Például a MySQL NOW() függvénye a PostgreSQL-ben NOW() vagy CURRENT_TIMESTAMP, de a GROUP_CONCAT()-nak a PostgreSQL-ben STRING_AGG() a megfelelője. A UUID() függvényt is máshogy kell kezelni.
Case Sensitivity: A MySQL alapértelmezés szerint nem érzékeny a kis- és nagybetűkre a tábla- és oszlopnevekben a legtöbb operációs rendszeren (bár konfigurálható), míg a PostgreSQL alapértelmezetten érzékeny. Ha a MySQL-ben nem következetesen használtak kis- és nagybetűket, ez problémákat okozhat.
Indexek: A MySQL index típusai (pl. BTREE, HASH, FULLTEXT) eltérhetnek a PostgreSQL-ben elérhető típusoktól. A FULLTEXT indexeket a PostgreSQL-ben a tsvector típus és a GIN vagy GIST indexek segítségével kell implementálni.

1.2. Mentés (Backup)

Mielőtt bármilyen migrációs lépésbe kezdene, készítsen teljes biztonsági másolatot a MySQL adatbázisról. Ez kritikus fontosságú, ha valami balul sülne el, és vissza kellene állítania az eredeti állapotot.

1.3. A migrációs stratégia kiválasztása

Többféle megközelítés létezik. A választás függ az adatbázis méretétől, a rendelkezésre álló leállási időtől, a technikai jártasságtól és a költségvetéstől.

2. Adatmigrációs módszerek

Íme a leggyakoribb és leghatékonyabb módszerek a MySQL-ből PostgreSQL-be történő migrációra:

2.1. Manuális/Scriptelt migráció (Schema & Data Dump)

Ez a módszer magában foglalja a MySQL séma és adatainak exportálását, majd azok kézi módosítását és importálását a PostgreSQL-be. Nagyfokú kontrollt biztosít, de időigényes és hibalehetőségeket rejt, különösen nagy és komplex adatbázisok esetén.

2.1.1. A MySQL séma exportálása

Használja a mysqldump parancsot a séma exportálásához. Fontos, hogy NE exportálja az adatokat ebben a fázisban, csak a táblalétrehozó utasításokat.

mysqldump -u [felhasználónév] -p --no-data [adatbázisnév] > schema.sql

2.1.2. A séma átalakítása PostgreSQL kompatibilissé

Ez a legmunkásabb rész. Nyissa meg a schema.sql fájlt egy szövegszerkesztővel, és végezze el a következő módosításokat:

Engine és Charset eltávolítása: Távolítsa el a ENGINE=InnoDB, DEFAULT CHARSET=utf8mb4 stb. sorokat.
Adattípusok konvertálása: Ahogy fentebb tárgyaltuk (pl. TINYINT(1) -> BOOLEAN, DATETIME -> TIMESTAMP, MEDIUMTEXT -> TEXT).
Auto-inkrementálás: Cserélje a AUTO_INCREMENT kulcsszót SERIAL vagy BIGSERIAL-re az elsődleges kulcs oszlopainál. Ne feledje, hogy a SERIAL típus egy sorozatot is létrehoz.
Idézőjelek: A MySQL kettős backtick (`) karaktereket használ az azonosítók idézésére, míg a PostgreSQL kettős idézőjelet („). Cserélje le a `oszlop_nev`-eket "oszlop_nev"-ekre.
Függvények: Módosítsa az olyan függvényeket, mint a NOW(), GROUP_CONCAT() stb., a PostgreSQL megfelelőire.
ON UPDATE CASCADE: A PostgreSQL nem támogatja az ON UPDATE CASCADE-et bizonyos esetekben (pl. trigger nélkül). Ha van ilyen, manuálisan kell átalakítani triggerre vagy más módon kezelni.

Miután elvégezte a módosításokat, importálja a sémát a PostgreSQL-be:

psql -U [felhasználónév] -d [adatbázisnév] -h [hoszt] -f schema.sql

2.1.3. A MySQL adatok exportálása

Az adatok exportálására több lehetőség is van:

CSV export: Ez a leggyakoribb és ajánlott módja a nagy adatmennyiségek exportálásának.

mysql -u [felhasználónév] -p -B [adatbázisnév] -e "SELECT * FROM tablanév INTO OUTFILE '/tmp/tablanév.csv' FIELDS TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY 'n';"

INSERT utasítások exportálása: Kisebb adatmennyiség esetén működhet, de nagy adatbázisoknál lassú és erőforrás-igényes lehet az importálás.

mysqldump -u [felhasználónév] -p --no-create-info [adatbázisnév] > data.sql

2.1.4. Az adatok importálása a PostgreSQL-be

CSV import: A PostgreSQL COPY parancsát használja.

COPY tablanév FROM '/tmp/tablanév.csv' WITH (FORMAT CSV, DELIMITER ',', ENCODING 'UTF8');

Ne feledje, hogy a SERIAL típusú oszlopok esetén az importálás után frissíteni kell a szekvencia értékét, hogy az új beszúrások ne ütközzenek a már létező ID-kkal:

SELECT setval('tablanév_id_seq', (SELECT MAX(id) FROM tablanév));

INSERT utasítások importálása:

psql -U [felhasználónév] -d [adatbázisnév] -h [hoszt] -f data.sql

2.2. A pgLoader használata

A pgLoader egy kiváló, nyílt forráskódú eszköz, amelyet kifejezetten az adatbázisok közötti migrációra terveztek, beleértve a MySQL-ből PostgreSQL-be történő áttérést is. Automatikusan kezeli a séma konverziójának nagy részét, az adattípusok leképezését, az auto-inkrementáló azonosítókat, és hatékonyan importálja az adatokat.

2.2.1. pgLoader telepítése

A telepítés platformfüggő lehet. Ubuntu/Debian esetén:

sudo apt install pgloader

Más rendszerekre a hivatalos dokumentáció ad útmutatót.

2.2.2. A migrációs parancs futtatása

A pgLoader egyetlen paranccsal képes elvégezni a teljes migrációt. A legegyszerűbb formája a következő:

pgloader mysql://[mysql_user]:[mysql_password]@[mysql_host]:[mysql_port]/[mysql_db] pgsql://[pg_user]:[pg_password]@[pg_host]:[pg_port]/[pg_db]

Például:

pgloader mysql://root:password@localhost/mydb pgsql://postgres:password@localhost/newdb

2.2.3. Részletesebb konfiguráció (LOAD fájl)

Komplexebb esetekben érdemes egy .load kiterjesztésű fájlt létrehozni a pgLoader számára, ami nagyobb rugalmasságot biztosít a leképezések, transzformációk és hibakezelés terén.

-- my_migration.load
LOAD DATABASE
    FROM mysql://[mysql_user]:[mysql_password]@[mysql_host]:[mysql_port]/[mysql_db]
    INTO pgsql://[pg_user]:[pg_password]@[pg_host]:[pg_port]/[pg_db]

WITH include drop, create tables, create indexes, reset sequences,
     no foreign keys, -- Hagyjuk a FK-kat utoljára, vagy kezeljük a pgLoaderrel
     preserve index names, preserve trigger names,
     rows per second, multiple readers,
     data only

ALTER SCHEMA '[mysql_db]' RENAME TO public; -- Alapértelmezett séma

-- Példa adattípus leképezésre
CAST type tinyint to boolean drop typemod;
CAST type text to text drop typemod;

-- Speciális függvények kezelése (ha szükséges)
-- AFTER CREATE TABLE
-- EXECUTE 'ALTER TABLE my_table ALTER COLUMN my_column SET DEFAULT uuid_generate_v4();';

;

Ezt futtassa a következőképpen:

pgloader my_migration.load

A pgLoader a legajánlottabb módszer a legtöbb felhasználó számára, mivel jelentősen csökkenti a kézi munkát és a hibalehetőségeket.

2.3. Foreign Data Wrappers (FDW)

A Foreign Data Wrappers (FDW) lehetővé teszi a PostgreSQL számára, hogy más adatbázisokhoz csatlakozzon és azokat „idegen” táblákként kezelje. Ez nem egy klasszikus migrációs eszköz, de hasznos lehet átmeneti megoldásként, vagy ha folyamatosan szinkronban kell tartani bizonyos adatokat.

Lépések:

Telepítse a mysql_fdw kiegészítőt a PostgreSQL-be.
Hozzon létre egy „idegen szervert” a MySQL adatbázishoz.
Képezzen le felhasználókat.
Importálja az „idegen táblák” sémáját, vagy definiálja manuálisan azokat.

-- Telepítés
CREATE EXTENSION mysql_fdw;

-- Idegen szerver létrehozása
CREATE SERVER mysql_server
    FOREIGN DATA WRAPPER mysql_fdw
    OPTIONS (host 'mysql_host', port '3306');

-- Felhasználói leképezés
CREATE USER MAPPING FOR postgres
    SERVER mysql_server
    OPTIONS (username 'mysql_user', password 'mysql_password');

-- Idegen táblák importálása (séma)
IMPORT FOREIGN SCHEMA [mysql_db] FROM SERVER mysql_server INTO public;

-- Adatok átmásolása (ezzel tudunk migrálást is végezni)
CREATE TABLE local_table AS SELECT * FROM foreign_table;

Ez a módszer akkor lehet hasznos, ha a migrációt szakaszosan szeretné végezni, vagy ha az alkalmazásoknak rövid ideig mindkét adatbázishoz hozzá kell férniük.

2.4. Grafikus felületek (GUI Tools) és felhőszolgáltatások

Számos GUI eszköz (pl. DBeaver, DataGrip, TablePlus) kínál migrációs funkciókat, amelyek leegyszerűsíthetik a folyamatot, különösen kisebb adatbázisok esetén. Emellett a nagy felhőszolgáltatók (AWS DMS, Azure Database Migration Service, Google Cloud Database Migration Service) is kínálnak adatbázis áttelepítésre specializált szolgáltatásokat, amelyek nagy adatmennyiségek és komplex környezetek esetén jöhetnek szóba.

3. Migráció utáni teendők

A nyers adatok átkerülése csak az első lépés. A sikeres átálláshoz további optimalizálásra és ellenőrzésre van szükség.

3.1. Adatok ellenőrzése és validálása

Sorok száma: Ellenőrizze, hogy minden táblában megegyezik-e a sorok száma a forrás és a cél adatbázisban.
Adatintegritás: Végezzen szúrópróbaszerű ellenőrzéseket, hogy az adatok helyesek-e, különösen a speciális karakterek, dátumok és numerikus értékek esetében.
Alkalmazás tesztelése: A legfontosabb lépés. Csatlakoztassa az alkalmazását az új PostgreSQL adatbázishoz, és végezzen alapos teszteket minden funkción, beleértve az olvasási és írási műveleteket is.

3.2. Indexek és kényszerek újraépítése

Bár a pgLoader és más eszközök megpróbálják átvinni az indexeket és a kényszereket, érdemes felülvizsgálni őket. Különösen a nagy táblákon lévő indexek újragondolása és optimalizálása fontos lehet a PostgreSQL sajátosságai szerint.

3.3. Teljesítményhangolás (Performance Tuning)

A PostgreSQL konfigurációja (postgresql.conf) számos paramétert tartalmaz, amelyek finomhangolhatók a teljesítmény optimalizálása érdekében. Ilyenek például a shared_buffers, work_mem, maintenance_work_mem és a wal_buffers. A MySQL-ből származó beállítások nem biztos, hogy optimálisak a PostgreSQL-hez.

3.4. Biztonság és jogosultságok

Hozza létre az összes szükséges felhasználót és szerepkört a PostgreSQL-ben, és állítsa be a megfelelő jogosultságokat. Ne feledje, hogy a jogosultságkezelés modellje eltérhet a MySQL-étől.

3.5. Alkalmazáskonfiguráció

Frissítse az alkalmazása adatbázis-kapcsolati stringjeit (connection strings), hogy az új PostgreSQL adatbázisra mutassanak. Ellenőrizze a használt adatbázis-illesztőprogramokat (driver), hogy kompatibilisek-e a PostgreSQL-lel.

3.6. Monitoring és logolás

Állítson be monitoringot és logolást az új PostgreSQL szerveren, hogy nyomon követhesse a teljesítményt és a hibákat.

4. Lehetséges kihívások és hibaelhárítás

Adattípus-konverziós hibák: A leggyakoribb probléma. Gondosan vizsgálja felül a séma átalakítását.
Karakterkészlet-problémák: Győződjön meg róla, hogy a forrás és cél karakterkészletek (különösen UTF-8) konzisztensek, és a kliens kapcsolódási beállításai is helyesek.
SQL szintaxis eltérések: A MySQL egy lazább SQL dialektust használ. A PostgreSQL szigorúbb. Ez hibákat okozhat a triggerekben, tárolt eljárásokban és nézetekben. Ezeket gyakran manuálisan kell átírni.
Teljesítményproblémák: Nagy adatbázisok migrációja lassú lehet. Használjon pgLoader-t több szálon, vagy a COPY parancsot a INSERT utasítások helyett.
Triggerek és tárolt eljárások: Ezeket szinte mindig manuálisan kell átírni, mivel a MySQL és PostgreSQL szintaxisuk jelentősen eltér.
UUID() függvény: A MySQL UUID() függvénye a PostgreSQL-ben a uuid_generate_v4() függvénnyel pótolható, ehhez telepíteni kell az uuid-ossp kiegészítőt: CREATE EXTENSION "uuid-ossp";

Összefoglalás

A MySQL-ből PostgreSQL-be való migrálás egy összetett feladat lehet, de a megfelelő tervezéssel, eszközökkel és ellenőrzéssel zökkenőmentesen elvégezhető. A pgLoader egy rendkívül hatékony eszköz, amely nagymértékben leegyszerűsíti a folyamatot, de a manuális schema átalakítás és a gondos ellenőrzés továbbra is elengedhetetlen. Ne feledje, a kulcs a részletes tervezés, a többszöri tesztelés és a biztonsági mentés. Miután sikeresen áttelepítette az adatait, élvezheti a PostgreSQL által kínált robusztusságot, fejlett funkciókat és kiváló teljesítményt.