Erinevus lehekülje "Postgresql" redaktsioonide vahel
(→Andmebaasi sisselogimine) |
(→Baasi loomine) |
||
74. rida: | 74. rida: | ||
DROP DATABASE testikas | DROP DATABASE testikas | ||
− | + | PostgreSQList väljumiseks ja operatsioonisüsteemi viiba juurde tagasipöördumiseks tuleb anda käsk '\q' | |
− | + | zoo=> \q | |
Kui see kõik läks libedalt siis on peamiselt kõik korras; tarvis vaid ivake kasutajate õigusi sättida ja valmis. | Kui see kõik läks libedalt siis on peamiselt kõik korras; tarvis vaid ivake kasutajate õigusi sättida ja valmis. |
Redaktsioon: 28. september 2011, kell 13:05
Sisukord
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.
Alates versioonist 8.3 on olemas ka fulltext otsing ja seda ei pea eraldi patchina lisama.
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
Mõningate lisavahendite näiteks tsearch2 http://www.postgresql.org/docs/8.3/interactive/tsearch2.html mooduli kasutamiseks on vajalik postgresql-contrib paketi paigaldamine
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
Võtame postgresiga ühendust:
# psql -U postgres -h localhost
Loome kasutaja
CREATE USER kasutaja WITH PASSWORD 'parool';
Loome andmebaasi ja seome kasutajaga:
CREATE DATABASE testikas OWNER kasutaja;
Privileege saab vajadusel anda ka hiljem näiteks sedasi
GRANT ALL PRIVILEGES ON DATABASE testikas to kasutaja;
Andmebaasi eemaldamiseks:
DROP DATABASE testikas
PostgreSQList väljumiseks ja operatsioonisüsteemi viiba juurde tagasipöördumiseks tuleb anda käsk '\q'
zoo=> \q
Kui see kõik läks libedalt siis on peamiselt kõik korras; tarvis vaid ivake kasutajate õigusi sättida ja valmis.
Kasutajad-õigused
Administraatori kasutaja 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 tegema mõningased muudatused
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 ligi kõik kes on nõus end autentima. (st. alustama psql -u -d database)
Kasutajaid näeb postgres käsurealt järgnevalt
postgresql > # \du List of roles Role name | Attributes | Member of ------------+-------------+----------- mart | | {} ants | | {} kalle | Superuser | {} : Create DB
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 lubada, et localhostist pääseks 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 (;)
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 logmimist võimaldavad andmed.
Esmalt logige enda PostgreSQLi serverisse ja võtke kasutusele oma andmebaas.
bash$ psql -h www.zoo.edu.ee -U zoo zoo zoo=>
SQL käskudes ei loe tähe suurus, st võib kasutada nii suuri kui väikesi tähti.
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) |
Päringu tulemuse kopeerimine uude andmetabelisse
Sooritades päringut on võimalik sellest luua uus andmetabel. Näiteks tekitame Haapsalust pärit inimeste tabeli
CREATE TABLE haapsalu AS SELECT nimi, vanus FROM inimesed WHERE elukoht='Haapsalu';
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.
Andmetabeli struktuuri muutmine
Andmetablit saab muuta käsuga ALTER. Näiteks lisame olemasolevasse andmetabelisse andmevälja sugu
ALTER TABLE inimesed ADD COLUMN sugu VARCHAR(30);
Muudame olemasoleva tabeli nime inimesed uueks nimega kodanikud
ALTER TABLE inimesed RENAME TO kodanikud;
Muudame olemasoleva tulba nime elukohast aadressiks
ALTER TABLE inimesed RENAME COLUMN elukoht TO aadress;
Tulba eemaldamiseks kopeerige sobivad tulbad uueks andmetabeliks ning kasutage seda!
Andmetabeli struktuuri saab näha käsuga '\d tabelinimi'.
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'.
Veebihaldus PhpPgAdmin
TODO
http://phppgadmin.sourceforge.net/
Postgresi koormuse testimine
Toimub käsuga
# pgbench -i -s 100 test
või
# pgbench -c 16 -t 1000 test
Probleemid
Kui postgresql ei saa ühendust pordiga tasub vaadata postgresql.confis ega pole seatud tcpip_socket = false see tuleb sellisel juhul ümber muuta trueks.
Lingid
http://www.postgresql.org/docs/8.1/interactive/install-upgrading.html
http://www.freebsddiary.org/postgresql.php
Eelnevate versioonide võrdlusmaatriks
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/
http://www.revsys.com/writings/postgresql/errors.html#cannot-connect-remote levinud veateated kasutamisel