Postgresql

Sissejuhatus

Postgresql on Berkeleys Ülikoolis arendatud postgres andmebaasi vabavaraline edasiarendus

PostgreSQL on relatsiooniline andmebaasihaldur mõningase objektorienteeritud kallakuga ning tehniliselt ja funktsionaalselt tõsine konkurent parimatele kommertsanaloogidele. Samuti oli ta üks esimesi andmebaase, mis pakkus MVCC’d vastukaaluks realukustusele või tabelilukustusele ning see parandab oluliselt süsteemi tõhusust andmebaasi ühiskasutuse korral.

Esimese pooleteise tunni jooksul tähelepandud erinevused võrreldes MySQL'iga:

• - kaasas on hea dokumentatsioon ja tutorid (ka PostScript'is) mida MySQL'il peaaegu polegi
• - erinevatele kasutajaltele erinevate õiguste andmine on keerulisem (sh. passwordid); siiski on lihtne anda lihtsaid õigusi
• - andmebaasi struktuurist kõneldes kasutatakse (tõenäoliselt põhjusega) mõisteid 'class', 'instance' jms mis on tuntud kui lähenetakse asjadele 'objekt- orienteeritud' seisukohast. Nt. MySQL'i andmeväli ei saa koosneda mitmest alamosast. Küll aga PostgrSQL'i puhul võib.

Paigaldus

Eesti peegel asub EENet serveris: ftp://ftp.ee.postgresql.org/pub/postgresql/

Installime postgres serveri vastavalt enda operatsioonisüsteemile

Seejärel on vajalik initsialiseerida andmebaas selleks anname järgnevad käsud.

Kaustaks kus andmebaasid asuvad hakkab olema /home/pgsql

mkdir /home/pgsql

Anname kausta postgres kasutaja õigustesse

chown -R postgres:postgres /home/pgsql

Lähme postgres kasutaja õigustesse. NB! vale kasutaja alt default baaside loomine lõpeb sellega ,et pole võimalik luua esimesi kasutajaid või muuta paroole.

su postgres

ning seejärel loome default keskkonna

initdb -D /home/pgsql

Kui kasutusel FreeBSD siis on postgres kasutajaks hoopis pgsql

Edasi võime juba operatsioonisüsteemi enda vahenditega (/etc/init.d/postgres, /usr/local/etc/rc.d/postgres.sh) abil serveri startida

Baasi loomine

Käivitage postgreSQL prooviks: (logige uuesti sisse kasutaja postgres alt, et lisatud PATH'i take'eksid efffect'i :)

Kõik järgnevad käsud tuleb anda postgres kasutaja alt, su - postgres

looge andmebaas

$ createdb mhh 

votke andmebaasiga ühendust

$ psql 

käsivitage lihtne päring

postgres=> SELECT datetime 'now'; 

(kas tulemus on näha?)

väljuge andmebaasi kliendist

postgres=> \q 

eemaldage andmebaas

$ destroydb mhh 
 

Kui see kõik läks libedalt siis on peamiselt kõik korras; tavis vaid ivake kasutajete õigusi sättida ja kõik.

Kasutajad-õigused

Kasutajate lisamine

createuser -P root

Enter password for new role:
Enter it again:
Shall the new role be a superuser? (y/n) y

Peale selle peab failis

• /usr/local/pgsql/data/pg_hba.conf

local          all                                         md5
#local        all                                          trust 
#host         all         127.0.0.1     255.255.255.255   trust 
#host         all         192.168.1.1   255.255.255.255   trust  

antud juhul on kolm alumist rida välja kommenteeritud ja töötav rida ütleb, et lase sisse localhost'i pealt kõik kes on nõus end autentima. (st. alustama psql -u -d database) Kui kuidagi ei taha sisse lasta, siis kasautage ülevalt teist rida :)

Upgrade

Postgres upgrade puhul oleks soovitatav dumpida kogu baas ja hiljem restoreda

pg_dumpall -U root > pgdump
/etc/init.d/postgresql stop

Ning vana database kausta võime näiteks kustutada või varundada, antudhetkel on selleks /home/pgsql ja kustutada ning luua uue ja anda selle chown käsuga postgres omandusse.

seejärel teeme vastavalt os'ile vajaliku ,et vahetuks postgres versioon

gentoos näiteks käsuga

emerge postgresql

Tasub peale baasi taas initsialiseerimist lubaca localhostist pääseda postgresile ligi ilma paroolideta (admin parool pole veel taastatud) Seda saame teha failist nano pg_hda ja seal võime lahtikommenteerida rea

local   all         all                               trust

Seejärel võime anda käsud

mkdir /home/pgsql
chown -R postgres:postgres /home/pgsql
su postgres
initdb -D /home/pgsql
postgres -D /home/pgsql
psql -d postgres -f pgdump

PostgreSQLi andmebaasi kasutamine

Enne andmebaasiga suhtlemist peab kasutaja nö andmebaasi sisse logima näitates oma kasutajanime ja parooli, so sama kasutajanime, mida ta kasutab veebiserverisse sisselogimisel ning vastavat PostgreSQL-i andmebaasi parooli, mis edastatakse talle andmebaasi loomisel.

Andmebaasiga suhtlemine on võimalik kahel viisil:

• PostgreSQLi kliendiga psql käsurealt
• veebi vahendusel

Järgnevas kirjeldame kuidas seda teha käsurealt kasutades SQL termineid, mis vastavad Protgresi objekt-orienteeritud traditsioonile selliselt

andmetabel - class 
kirje - instance 
tulp - attribute 
Andmebaasi sisselogimine

Andmebaasiga tegelemiseks peab kõigepealt näitama PostgreSQLile andmebaasiserveri nime, oma kasutajanime, parooli ja andmebaasi nime

bash$ psql -h www.zoo.edu.ee -U zoo zoo
Password: 
Welcome to psql, the PostgreSQL interactive terminal.

Type:  \copyright for distribution terms
       \h for help with SQL commands
       \? for help on internal slash commands
       \g or terminate with semicolon to execute query
       \q to quit

zoo=> 

kus on kasutatud järgmisi võtmeid:

-h - PostgreSQLi serveri nimi, alati teie veebiserveri nimi 
-U - PostgreSQLi serveri kasutajanimi, alati teie nW serveri kasutajanimi 

viimane zoo - PostgreSQLi andmebaasi nimi, see on alati veebiserveri omaniku-nimeline; kui see argument jätta kirjutamata, siis kasutatakse kasutajanime nimelist andmebaasi

Seejärel küsitakse kasutajalt parooli.

Märgake, et tulemusena avanes andmebaasi nime sisaldav PostgreSQLi viip 'zoo=>', mille järele saab sisestada kahte tüüpi käske: PostgreSQLi põge-käsud (ingl. k. internal slash commands) - juhivad andmebaasikliendi tööd; need algavad tagurpidi kaldkriipsuga (\) SQL käsud - SQL päringute tegemiseks ja nad peavad lõppema semikooloniga (;)

Näiteks PostgreSQList väljumiseks ja operatsioonisüsteemi viiba juurde tagasipöördumiseks tuleb anda käsk '\q'

zoo=> \q

Andmetabeli loomine

Enne järgneva tegevuse juurde asumist logige PostgreSQLi serverisse ja võtke kasutusele oma andmebaas

bash$ psql -h www.zoo.edu.ee -U zoo zoo
Password:
Welcome to psql, the PostgreSQL interactive terminal. 

Type:  \copyright for distribution terms 
       \h for help with SQL commands
       \? for help on internal slash commands
       \g or terminate with semicolon to execute query
       \q to quit

zoo=>

Kirjeldame kolme väljaga andmetabeli inimesed:

• nimi - tekstiline andmeväli, kuni 15 tähemärki
• vanus - täisarvuline andmeväli
• elukoht - tekstiline andmeväli, kuni 18 tähemärki

Selleks tuleb anda korraldus

zoo=> create table inimesed (nimi varchar(15), vanus int, elukoht varchar(18));

PostgreSQLi SQL käsk lõppeb semikooloniga (;). Seejärel sisetame andmetabelisse kolm kirjet

nimi      vanus     elukoht
Mart      16        Narva
Priit     17        Tallinn
Laa       12        Kuressaare

Selleks tuleb anda korraldused:

zoo=> INSERT INTO inimesed VALUES ('Mart', '16', 'Narva');
zoo=> INSERT INTO inimesed VALUES ('Priit', '17', 'Tallinn');
zoo=> INSERT INTO inimesed VALUES ('Laa', '12', 'Kuressaare');

PostgreSQLi kliendi käsureal saab kursorit liigutada näiteks vasakule ja paremale nooleklahvide abil ning olemasolevat teksti redigeerida. Sarnaselt saab varemantud käskude vahel valida neid sirvides üles ja alla nooleklahvide abil.

Otse käsurealt on tõenäoliselt tülikas sisestada suurt hulka andmeid, sellepärast selgitame allpool kuidas seda teha 'batch' rezhiimis.

Olles lõpetanud andmete sisestamise sulgeda PostgreSQLi klient käsuga '\q'.

Päringu sooritamine

Päringu sooritamine käsurealt eeldab, et teil on olemas andmed.

Esmalt logige enda PostgreSQLi serverisse ja võtke kasutusele oma andmebaas.

bash$ psql -h www.zoo.edu.ee -U zoo zoo
zoo=> 

Esitame näiteks päringu, mis näitab andmetabeli inimesed kõikide kirjete kõiki välju

zoo=> select * from inimesed;
 nimi  | vanus | elukoht 
-------+-------+---------
 Mart  |    16 | Narva
 Priit |    17 | Tallinn
 Laa   |    12 | Kuressaare

Vahel on abiks näha andmetabeli struktuuri, selleks andke korraldus '\d'

zoo=> \d inimesed
         Table "inimesed"
  Attribute |    Type     | Modifier 
-----------+-------------+----------
 nimi      | varchar(15) | 
 vanus     | integer     | 
 elukoht   | varchar(18) | 

Olles lõpetanud päringud väljuge PostgreSQLi kliendist käsuga '\q'.

Andmetabeli kustutamine

Olemasoleva andmetabeli kustutamiseks andmebaasist logige end PostgreSQLi serverisse ja võtke kasutausele oma andmebaas. Näiteks kustutame andmetabeli inimesed

zoo=> drop table inimesed;

Arvestage, et nii kustutatakse kõik andmed, mis sisalduvad kõnealuses andmetabelis ja te olete neist jäädavalt ilma.

Batch rezhiimi kasutamine

Mahukamate sisestuste puhul ei ole otstarbekas anda PostgreSQLile käske käsitsi, vaid lasta neid lugeda selleks ettevalmistatud teksifailist. Sel puhul täidetakse käske ükshaaval, kuid psql ei oota neid klaviatuurilt.

Näiteks lisame batch rezhiimis andmebaasi inimesed kirjed

Leida   18   Elva
Maie    45   Põlva
Väino   39   Haapsalu

Selleks tekitage järgmise sisuga tekstifail andmelisa.psql-batch

INSERT INTO inimesed VALUES ('Leida', '18', 'Elva');
INSERT INTO inimesed VALUES ('Maie', '45', 'Põlva');
INSERT INTO inimesed VALUES ('Väino', '39', 'Haapsalu');

Ning andmete lisamiseks andke psqli viibast käsk

zoo=> \i andmelisa.psql-batch

Tulemusena lisati andmetabelisse zoo andmed Leida, Maie ja Väino kohta.

Kui SQL käsud genereerivad väljundi, siis saab selle suunata tekstifaili.

Selleks tuleb anda korraldus '\o failinimi', mille järel kõik väljund suunatakse edasi näidatud faili. Näiteks suuname andmetabeli inimesed kõigi väljada sisu faili koik.inimesed

zoo=> \o koik.inimesed

ning esitame päringu

zoo=> select * from inimesed;

Tulemus suunatakse faili koik.inimesed, kus on kolm tulpa eraldatud sobiva arvu tühikutega

 nimi | vanus | elukoht
------+-------+---------
Mart  | 16    | Narva
Priit | 17    | Tallinn
Laa   | 12    | Kuressaare
Leida | 18    | Elva
Maie  | 45    | Pulva
Vdino | 39    | Haapsalu

Soovides näha taas väljundit terminalil, andke korraldus '\o'

zoo=> \o

Soovides koopeerida andmebaasi sisu ning määrata andmevälja eraldaja, andke käsk

zoo=> COPY inimesed TO stdout USING DELIMITERS ',';

Kasutajale ei ole ette nähtud teha failioperatsioone ning seega peab anmed faili suuname läbi väljundi (stdout) kasutades käsku '\o'.

Lingid

http://gentoo-wiki.com/HOWTO_Configure_Postgresql

http://www.postgresql.org/docs/8.1/interactive/install-upgrading.html

http://www.freebsddiary.org/postgresql.php

Eelnevate versioonide võrlustabel

http://www.postgresql.org/about/featurematrix

PGCluster is a multi-master and synchronous replication system that supports load balancing of PostgreSQL.

http://www.csra.co.jp/~mitani/jpug/pgcluster/