PostgreSQLMySQL

1 PostgreSQL és MySQL összehasonlítása adminisztrátor szemszögéből

Először külön áttekintem a két DBMS-t, majd a végén térek rá a különbségekre.

1.1 PostgreSQL

• Többfelhasználós RDBMS
• Berkeley Egyetemen kifejlesztett POSTGRES objektum-relációs adatbáziskezelő rendszer nyílt forrású bővítése
• Természetesen jelentős szolgáltatás-növelés történt azóta
• Kliens-szerver architektúrájú (a szerver program neve postmaster)
• UNIX-alapú rendszereken fut csak, ezért Windowson csak is Cygwin vagy MinGW telepítése után használható (mármint a szerverprogram)

1.1.1 Telepítés

• RedHat esetén rpm csomagból. Le kell tölteni az összes szükséges csomagot, majd futtatni kell a következő parancsot:
  • rpm -ivh postgresql-*
• Néhány főbb csomag, amikre szükség lehet:
  • postgresql : alapcsomagok
  • postgresql-contrib : kiegészítő csomagok
  • postgresql-devel : alkalmazások fejlesztéséhez és fordításához. A kiszolgáló telepítéséhez feltétlenül szükséges
  • postgresql-jdbc : Java DataBase Connectivity-t lehetővé tevő csomag
  • postgresql-libs : megosztott programkönyvtárak
  • postgresql-odbc : Open DataBase Connectivity, API amit nagyon sok eszköz támogat
  • postgresql-perl : Perlben lehessen kiegészítőket írni
  • postgresql-python : ugyanez Pythonban
  • postgresql-server : akkor kell, ha nem csak távoli hosthoz kapcsolódunk, hanem helyileg is működtetünk szervert

• Debian-alapú rendszer esetén mindössze egy parancs a telepítés:
  • apt-get install postgresql

• Természetesen forrásból is telepíthető a szokásos ./configure, make, make install hármassal. A forrás letölthető a http://www.postgresql.org oldalról

• A szerver elindítása /etc/init.d/postgresql start paranccsal történik. Előfordulhat, hogy a postgresql szó után kötőjelet, majd a verziószámot meg kell adni.

1.1.2 Adminisztratív feladatok ellátása

1.1.2.1 Adatbázis-klaszter létrehozása

• Először létre kell hozni új adatbázis-klasztert
• Egy PostgreSQL adatbázis-klaszter adatbázisok gyűjteménye, amelyekért ugyanazon szerver folyamat felel
• initdb program segítségével tehetjük meg
• A program a következő műveleteket hajtja végre:
  • Könyvtárak létrehozása, amelyekben az adatbázisok tárolásra kerülnek majd
  • Közös katalógus táblák generálása (ezek a teljes klaszterhez tartoznak)
  • template1 és postgres adatbázisok létrehozása
    • template1 sablon adatbázis, amelynek objektumai automatikusan belekerülnek minden később, újonnan létrehozott adatbázisba
    • postgres az alapértelmezett adatbázis mindenki számára
• Az initdb programot annak a felhasználónak kell lefuttatnia, akié a szerver folyamat is (mivel a szerver folyamat hozzá kell hogy férjen a létrehozott könyvtárakhoz). Ez nem egyezik meg az operációs rendszer rootjával, alapértelmezés szerint egy postgres nevű felhasználó, amely a PostgreSQL telepítésekor jön létre
• initdb néhány fontosabb kapcsolója:
  • -A, --auth : milyen azonosítása legyen az operációs rendszer felhasználóinak (pg_hba.conf fájlban kerül tárolásra, ahol hba = host-based authentication). Lehetséges értékei:
    • trust : nincs szükség autentikációra, megbízunk a host felhasználóiban
    • md5 : hashelve tároljuk a jelszavakat
    • crypt : titkosított
    • password : plain-text jelszó, távoli eléréshez ilyet ne használjunk
    • lehet ident-alapú is, ami azt jelenti hogy az operációs rendszer részeként kapott ident szervert használja fel a szerver adabázis-felhasználók és operációs-rendszer felhasználók párosításához (ez azonban legtöbbször nem biztonságos, feltételezi hogy a kliens gép nem kompromittált, az RFC-ben is azt írják hogy ne használjuk)
    • PAM (pluggable authentication module)
    • lehet kerberos-szal is, de ilyenkor már telepítéskor meg kell adnunk a kerberos-szerver nevét
  • -D, --pgdata : melyik könyvtárban tároljuk az adatbázis-klasztert. Megadása kötelező
  • -U, --username: mi legyen a superuser neve. Alapesetben a szerverfolyamat tulajdonosának neve, postgres. Meg is változhatható, kényelmi szempontból
  • -W, --pwprompt: az initdb jelszót is bekér a superuser accounthoz

1.1.2.2 Felhasználók, adatbázisok létrehozása

• Kezdetben egyetlen felhasználó létezik, melynek neve postgres.
• A rendszergazda beléphet az 'su postgres' paranccsal (Ubuntu esetén 'sudo su postgres')
• Felhasználó létrehozása a 'createuser <username>' paranccsal. Néhány kapcsoló:
  • -s, --superuser: a felhasználó superuser lesz
  • -d, --createdb: a felhasználó hozhat majd létre adatbázisokat
  • -D, --nocreatedb
  • -r, --createrole: a felhasználó hozhat majd létre szerepeket (tehát felhasználókat vagy csoportokat)
  • -R, --nocreaterole
  • -c, --connection-limit: egyszerre hány kapcsolatot létesíthet a felhasználó
  • -P: a parancs kiadása után meg kell adni jelszót
  • -E: a jelszót titkosítva tárolja a szerver
  • -N: jelszó ne legyen titkosítva
• Felhasználó törlése 'dropuser <username>' paranccsal
• Adatbázis létrehozása 'createdb <adatbazisnev>' paranccsal. Néhány kapcsoló:
  • -D, --tablespace: tablespace megadása. A tablespace egy könyvtár a file rendszeren, ahová a tablespace-hez tartozó táblák eltárolásra kerülnek. A tablespace-nek van neve, amivel hivatkozni lehet rá (hasznos lehet pl., ha több különböző sebességű lemezünk van)
  • -O, --owner: melyik felhasználó lesz az adatbázis tulajdonosa
  • -T, --template: sablon adatbázis, amely alapján az új adatbázis felépül. Kezdetben két sablon van:
    • template1: ez az alapértelmezett sablon, ha nem adjuk meg a -T kapcsolót, akkor ezt az adatbázist másolja át az új helyére is. Ilyen módon állíthatunk be telepítés-specifikus beállításokat, alapvető táblákat, felhasználókat stb. amelyek/akik minden adatbázis részei.
    • template0: csupasz sablon, ezt az adatbázist soha ne módosítsuk az initdb lefuttatása után (hasznos lesz pl., ha pg_dump segítségével építünk fel újra régebbi adatbázist)
  • -h, --host: melyik hoston fut a szerver. Ha /-jellel kezdődik, akkor Unix-domain socketet adunk meg
  • -p, --port: melyik porton figyel a szerver
  • -U, --username: melyik felhasználóként csatlakozzon alapértelmezés szerint
  • -W, --password: kötelező legyen jelszó megadása
• Adatbázis törlése 'dropdb <adatbazisnev>' paranccsal
• Adatbázisba belépni a 'psql <adatbazisnev>' paranccsal lehet
  • Ha nem adjuk meg az adatbazis nevét, akkor a felhasználó alapértelmezett adatbázisába lép be. Ha ilyen nem létezik, hiba. (Itt felhasználó alatt az operációs rendszer felhasználóját értjük.)
  • Ha más felhasználó adatbázisába szeretnénk belépni, mint amelyik be van jelentkezve az operációs rendszerbe (pl. mert más a felhasználónevünk a kettőben - nem kell egyezniük, semmi közük egymáshoz), akkor az -U kapcsolót használhatjuk.
• A felsorolt programocskák (createuser, dropuser, createdb, dropdb) frontendet jelentenek bizonyos PostreSQL-utasításokhoz. Ugyanezek a műveletek elvégezhetőek tehát PostgreSQL parancssornál is, CREATE ROLE, DROP ROLE, CREATE DATABASE, illetve DROP DATABASE utasításokkal.
• Felhasználók tekintetében ROLE-okról beszél a PostgreSQL. Egy ROLE egy szerepkör, ami jogosultságokat tartalmaz, és vagy egyetelen felhasználóhoz, vagy pedig felhasználók egy csoportjához van társítva

1.1.2.3 Szerver paramétereinek beállítása

• Ami egy-egy adatbázis-klaszter paramétereit illeti, megadhatóak az initdb program futtatásakor
• Ami a szerverre vonatkozó egyéb paramétereket illeti, megadhatjuk őket:
  • Konfigurációs fájlokban
  • PostgreSQL-utasításként, SET parancs segítségével
• Konfigurációs fájlok:
  • Adatkönyvtárba (data directory) helyezkednek el, amit az initdb program hozott létre
  • config_file: alap konfigurációs fájl
  • hba_file: host-based authentication, hozzáféréseket írja le
  • ident_file: az operációs rendszer felhasználóihoz társít adatbázis-felhasználókat
  • external_pid_file: szerver-adminisztrációs programok számára külső pid-file. Tudjuk, hogy mennyire megbízhatóak versenyhelyzetek tekintetében....
  • Néhány fontosabb környezeti változó:
    • listen_addresses : mely TCP/IP címekről érkezhet kérés
    • port : melyik porton figyeljen a szerver
    • max_connections : alapértelmezés szerint 100
    • authentication-timeout: befejezetlen azonosítási protkollok terminálása
    • ssl: lehet-e ssl-ezni
    • password-encryption: új jelszavak létrehozásánál a jelszavak tiktosítottak legyenek-e
    • shared_buffers: hány darab osztott memóriát alkalmazzon a szerver folyamat. Alapértelmezett memóriaméret 8192 byte, de ez telepítéskor változtatható (BLCKSZ opció)
    • temp_buffers: egy adatbázis kapcsolat hány ideiglenes puffert hozhat létre
    • max_fsm_pages: fsm = free-space-map, térkép arról, hol vannak szabad helyek az adatbázisban

1.1.2.4 Karbantartás

1.1.2.4.1 Write-ahead logolás

Néhány, a logolást befolyásoló környezeti változó:

• fsync: bool érték, amely megmondja, Hasznos, de költséges (minden db művelet után flush a lemezre). Több módszer is használható a kiírásra, amelyeket a wal_sync_method paraméterrel állíthatunk be:
  • open_datasync – az open() opciója 0_DSYNC, ilyenkor csak a “rendes” adatok kerülnek szinkronizált átvitelre, a metaadatok nem
  • fdatasync – fdatasync() meghívása minden egyes kommittálás esetén, csak adatot mentünk, metaadatot nem
  • fsync – fsync() meghívása minden egyes kommittálás esetén, minden adatot mentünk
  • fsync_writethrough – ugyanolyan mint az fsync, csak a cache-elt adatok írásakor azonnal az eredeti példányt is frissíti
  • open_sync – az open() opciója 0_SYNC, a metaadatok is szinkronizált átvitelre kerülnek
• commit_delay: microszekundumokban, hogy egy commit után legkésőbb mikor kell kiírni diszkre az adatokat
• checkpoint_segments: hány megabyte adat gyűljön össze két checkpoint között
• checkpoint_timeout: mennyi idő után kell újabb checkpoint
• archive_command: parancs, amelyet lefuttat a DBMS amikor teleírta az összes lehetséges log file-t. Rendelkezésre áll az %f és %p változó, amelyek az aktuálisan kiírandó fájl nevét illetve elérési útját tartalmazzák. Példa:
  • archive_command = 'cp %p /mnt/server/archivedir/”%f”'

1.1.2.4.2 Vacuuming (porszívózás)

• A VACUUM parancsot időről időre célszerű lefuttatni a következő okok miatt:
  • Frissített vagy törölt sorok által elfoglalt lemezterület felszabadítása (pl. törölt sorokat nem törlünk fizikailag is azonnal, mert egy másik tranzakció még függhet tőle)
  • Adatstatisztikák frissítése, amelyeket pl. a Query Planner használ (az ANALYZE parancs segítségével külön összegyűjthetőek ezek az adatok)
  • Nagyon régi adatokat ne veszítsünk el (különben ha az ún. transaction id wraparound bekövetkezik, ez megtörténhet – transaction id wraparound azt jelenti, hogy túlcsordulás történt a tranzakciók számlálójában, és egy új tranzakció ugyanazt az azonosítót kapja, mint egy régebbi)
• Kétféle VACUUM parancs létezik:
  • Sima VACUUM – más néven “lusta” vacuum – paranccsal csak megjelöljük a törlendő helyeket, de azok az operációs rendszer számára nem szabadulnak fel.
  • VACUUM FULL parancs segítségével az operációs rendszer visszakapja a lemezterületet, azonban ilyenkor kizárólagos zárral kell rendelkezni a fájl felett.
• A PostgreSQL 8.1-es verziója óta létezik külön daemon folyamat, amely elvégzi az időszakos porszívózást (autovacuum opció, bool értékkel, a konfigurációs fájlban).

1.1.2.4.3 Arhiválás és visszaállítás

Alapvetően 3 backup technika létezik:

• SQL dump – ilyenkor olyan fájlt tárolunk, amely végrehajtható SQL-utasításokat tartalmaz, amelyeket végrehajtva visszakapjuk az adatbázist
  • pg_dump program, amely konzisztens és a másolás közbeni írásokat nem veszi figyelembe a másoláskor:
    • pg_dump dbnev > outfile
  • pg_dump utáni restore:
    • psql dbnev < infile
  • pipe-okkal is használhatóak, ezért közvetlenül új szerverre is átpakolható az adatbázis:
    • pg_dump -h host1 dbnev | psql -h host2 dbnev
  • Ha teljes adatbázis-klasztert szeretnénk backupolni:
    • pg_dumpall > outfile
    • psql -f infile postgres
      • postgres itt egy adatbazisnev, amellyel elkezdi a visszaállítást; bármilyen létező adatbázis nevét meg lehet adni, de postgres-t szokás, főleg amikor teljesen üres adatbázist töltünk fel
• Fájlrendszer-szintű backup
  • pl. tar -cf backup.tar /usr/local/data
  • Ilyenkor azonban ideiglenesen le kell állítni az adatbázist
• On-line backup
  • Alapötlet: write-ahead-logok folyamatosan eltárolásra kerülnek az adatkönyvtárban (data directory, amelyet az initdb segítségével lehet beállítani). Ezek felhasználhatóak a legfrissebb változások újra-lejátszására, még akkor is, ha nem áll rendelkezésre teljesen konzisztens backup file az adatbázisról. Tehát a két módszert kombinálja.

1.2 MySQL

1.2.1 Telepítés

• RedHat esetén rpm csomagkezelővel. Le kell tölteni az összes szükséges csomagot, majd le kell futtatni az 'rpm -ivh mysql-*' parancsot. A következő programcsomagokra lesz szükség:
  • mysql – alapprogramok
  • mysql-devel – alkalmazások fejlesztéséhez és fordításához. A kiszolgáló telepítéséhez feltétlenül szükséges.
  • mysql-server – amennyiben lokális hoston szeretnénk futtatni, és nem távoli szerverhez kapcsolódunk.
• Debian alapú rendszer esetén mindössze egyetlen parancsot kell kiadnunk:
  • apt-get install mysql-common mysql-server mysql-client
• Végül lehet forrásból telepíteni a szokások ./configure – make – make install parancsokkal. Erről bővebb információ a http://www.mysql.com címen olvasható.

• A rendszer indítása a 'mysql -u <felhasznalo> <adatbazis>' paranccsal történik.
• Ha jelszót is meg szeretnénk adni, akkor a parancs végéhez még hozzá kell fűznünk a -p kapcsolót
• Kezdetben egyetlen adatbázis létezik, melynek neve 'mysql', és a 'root' felhasználó (semmi köze az operációs rendszer rootjához) jelszó nélkül kapcsolódhat:
  • mysql -u root mysql
• Hasonló SQL-értelmező parancssort kapunk, mint PostgreSQL esetében a psql parancs futtatásakor