Erinevus lehekülje "Postgresql" redaktsioonide vahel

Allikas: Kuutõrvaja
(Andmebaaside tihendamine)
 
(ei näidata sama kasutaja 45 vahepealset redaktsiooni)
14. rida: 14. rida:
  
 
Alates versioonist 8.3 on olemas ka fulltext otsing ja seda ei pea eraldi patchina lisama.
 
Alates versioonist 8.3 on olemas ka fulltext otsing ja seda ei pea eraldi patchina lisama.
 +
 +
Üks asi mida kohe tasub silmas pidada on see, et kasutusel on mysqliga võrreldes eraldi schemad andmebaaside liigitamiseks
 +
 +
db
 +
|-schema
 +
    |- tabel
  
 
===Paigaldus===
 
===Paigaldus===
25. rida: 31. rida:
 
Kaustaks kus andmebaasid asuvad hakkab olema /home/pgsql
 
Kaustaks kus andmebaasid asuvad hakkab olema /home/pgsql
  
  mkdir /home/pgsql
+
  # mkdir /home/pgsql
  
 
Anname kausta postgres kasutaja õigustesse
 
Anname kausta postgres kasutaja õigustesse
  
  chown -R postgres:postgres /home/pgsql
+
  # chown -R postgres:postgres /home/pgsql
 +
 
 +
Konfiguratsooni lisame
 +
 
 +
listen_addresses = '*'          # what IP address(es) to listen on;
  
 
Lähme postgres kasutaja õigustesse. NB! vale kasutaja alt default baaside loomine
 
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.
 
lõpeb sellega ,et pole võimalik luua esimesi kasutajaid või muuta paroole.
  
  su postgres
+
  # su postgres
  
 
ning seejärel loome default keskkonna
 
ning seejärel loome default keskkonna
  
  initdb -D /home/pgsql
+
  # initdb -D /home/pgsql
 +
.. 
 +
WARNING: enabling "trust" authentication for local connections
 +
You can change this by editing pg_hba.conf or using the -A option the
 +
next time you run initdb.
 +
 +
Success. You can now start the database server using:
 +
 +
    postgres -D /home/pgsql
 +
or
 +
    pg_ctl -D /home/pgsql -l logfile start
  
 
Kui kasutusel FreeBSD siis on postgres kasutajaks hoopis pgsql
 
Kui kasutusel FreeBSD siis on postgres kasutajaks hoopis pgsql
 +
 +
Meie tingimustes võime käivitada nt käsu järgnevate kodeeringu võtmetega
 +
 +
$ initdb --encoding=UTF-8 --lc-collate=et_EE.UTF-8 --lc-ctype=et_EE.UTF-8 /home/pgsql
  
 
Edasi võime juba operatsioonisüsteemi enda vahenditega (/etc/init.d/postgres, /usr/local/etc/rc.d/postgres.sh) abil serveri startida
 
Edasi võime juba operatsioonisüsteemi enda vahenditega (/etc/init.d/postgres, /usr/local/etc/rc.d/postgres.sh) abil serveri startida
46. rida: 70. rida:
 
Mõningate lisavahendite näiteks tsearch2 http://www.postgresql.org/docs/8.3/interactive/tsearch2.html mooduli
 
Mõningate lisavahendite näiteks tsearch2 http://www.postgresql.org/docs/8.3/interactive/tsearch2.html mooduli
 
kasutamiseks on vajalik postgresql-contrib paketi paigaldamine
 
kasutamiseks on vajalik postgresql-contrib paketi paigaldamine
 +
 +
Lähme pgsql kasutaja õigustesse (ehk postgres kasutaja õigustesse)
 +
 +
# su - pgsql
 +
 +
Käivitame postgresql serveri
 +
 +
$ pg_ctl -l logfile start -D /home/pgsql/
 +
server starting
 +
 +
Loome administraator õiguses kasutaja
 +
 +
$ createuser --pwprompt
 +
Enter name of role to add: admin
 +
Enter password for new role:
 +
Enter it again:
 +
Shall the new role be allowed to create more new roles? (y/n) y
  
 
===Baasi loomine===
 
===Baasi loomine===
54. rida: 95. rida:
 
Kõik järgnevad käsud tuleb anda postgres kasutaja alt, su - postgres
 
Kõik järgnevad käsud tuleb anda postgres kasutaja alt, su - postgres
  
looge andmebaas
+
Võtame postgresiga ühendust:
  
  $ createdb mhh
+
  # psql -U admin -h localhost
  
votke andmebaasiga ühendust
+
Loome kasutaja
  
  $ psql
+
  CREATE USER kasutaja WITH PASSWORD 'parool';
  
käsivitage lihtne päring
+
Loome andmebaasi ja seome kasutajaga:
  
  postgres=> SELECT datetime 'now';  
+
  CREATE DATABASE testikas OWNER kasutaja;
  
(kas tulemus on näha?)
+
Privileege saab vajadusel anda ka hiljem näiteks sedasi
  
väljuge andmebaasi kliendist
+
GRANT ALL PRIVILEGES ON DATABASE testikas to kasutaja;
  
postgres=> \q
+
Andmebaasi eemaldamiseks:
  
eemaldage andmebaas
+
DROP DATABASE testikas
  
  $ destroydb mhh
+
Loodud baasi (või baaside nägemiseks)
 
+
 
 +
  \list
 +
 
 +
PostgreSQList väljumiseks ja operatsioonisüsteemi viiba juurde tagasipöördumiseks tuleb anda käsk '\q'
 +
 
 +
\q
 +
 
 +
Sama tegevust on võimalik teha ka käsureal postgres kasutaja õigustes spets utiliitide abil, ilma psql utiliiti kasutamata
 +
 
 +
root> su - postgres
 +
postgres> createuser –s –P kasutaja
 +
postgres> createdb andmebaas
 +
postgres> dropdb andmebaas
  
Kui see kõik läks libedalt siis on peamiselt kõik korras; tavis vaid ivake kasutajete õigusi sättida ja kõik.
+
Kui see kõik läks libedalt siis on peamiselt kõik korras; tarvis vaid ivake kasutajate õigusi sättida ja valmis.
  
 
===Kasutajad-õigused===
 
===Kasutajad-õigused===
  
Kasutajate lisamine  
+
Administraatori kasutaja lisamine  
 
 
createuser -P root
 
  
 +
'''createuser -P root'''
 
  Enter password for new role:
 
  Enter password for new role:
 
  Enter it again:
 
  Enter it again:
 
  Shall the new role be a superuser? (y/n) y
 
  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
  
Peale selle peab failis
+
  local         all                                        md5
 
+
  #local        all                                         trust  
*/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        127.0.0.1    255.255.255.255  trust  
 
  #host        all        192.168.1.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.  
+
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)  
 
(st. alustama psql -u -d database)  
Kui kuidagi ei taha sisse lasta, siis kasautage ülevalt teist rida :)
+
 
 +
Kasutajaid näeb postgres käsurealt järgnevalt
 +
 
 +
postgresql > # \du
 +
            List of roles
 +
Role name  | Attributes  | Member of
 +
------------+-------------+-----------
 +
  mart      |            | {}
 +
  ants      |            | {}
 +
  kalle      | Superuser  | {}
 +
            : Create DB
 +
 
 +
Kasutaja parooli saab muuta käsuga
 +
 
 +
ALTER USER mart WITH PASSWORD 'uus';
  
 
===Upgrade===
 
===Upgrade===
 +
Postgres upgrade puhul oleks soovitatav esmalt dumpida kogu baas ja hiljem restoreda. Selleks on kaks võimalust.
  
Postgres upgrade puhul oleks soovitatav
+
Esimene on plaintext. Plaintext dump+restore on VÄGA aeglased. Peaaegu olenematud sellest, mis konfiga on sul server
dumpida kogu baas ja hiljem restoreda
 
  
 
  pg_dumpall -U root > pgdump
 
  pg_dumpall -U root > pgdump
118. rida: 181. rida:
 
gentoos näiteks käsuga
 
gentoos näiteks käsuga
  
  emerge postgresql
+
  # emerge postgresql
  
Tasub peale baasi taas initsialiseerimist lubaca
+
Tasub peale baasi taas initsialiseerimist lubada, et
localhostist pääseda postgresile ligi ilma paroolideta (admin parool pole veel taastatud)
+
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
 
Seda saame teha failist '''nano pg_hda''' ja seal võime lahtikommenteerida rea
  
131. rida: 194. rida:
 
  chown -R postgres:postgres /home/pgsql
 
  chown -R postgres:postgres /home/pgsql
 
  su postgres
 
  su postgres
  initdb -D /home/pgsql
+
  initdb --locale=et_EE.utf8 -E UNICODE -D '/srv/postgres/data'
 
  postgres -D /home/pgsql
 
  postgres -D /home/pgsql
  psql -d postgres -f pgdump
+
  psql -f /srv/postgres/varukoopia postgres
 +
 
 +
Teine lahendus on directory format. Directory format dump+restore on väga kiire ja suudab teha mitme tuumaga
 +
 
 +
Tee rooduna
 +
 
 +
su -l -c "pg_dump -j **PROTSESSIDEARV** -Fd **ANDMEBAASINIMI** -f /KAUST/KUHU/PANNAKSE-dump-ANDMEBAASINIMI" postgres
 +
 
 +
Uuenda Postgres
  
 +
Tee rooduna
 +
 +
su -l -c "pg_restore -j **PROTSESSIDEARV** /KAUST/KUST/VÕETAKSE/DUMP/" postgres
 +
 +
Soovitatavalt tee kõigepealt "postgres" andmebaas ja seejärel ülejäänud
 +
 +
Et kõiki andmebaase postgresis näha:
 +
 +
su -l -c 'psql -t -c "SELECT datname FROM pg_database WHERE datistemplate = false"
 +
 +
PROTSESSIDEARV pane enamvähem sama, mis serveril käes. :) See määrab ära, mitu paralleelset protsessi käima hakkavad.
 +
 +
Peamine miks mitte seda eelistada on probleemid liiga suurte tabelite korral.
  
 
===PostgreSQLi andmebaasi kasutamine===
 
===PostgreSQLi andmebaasi kasutamine===
157. rida: 241. rida:
 
  Password:  
 
  Password:  
 
  Welcome to psql, the PostgreSQL interactive terminal.
 
  Welcome to psql, the PostgreSQL interactive terminal.
 
+
 
  Type:  \copyright for distribution terms
 
  Type:  \copyright for distribution terms
 
         \h for help with SQL commands
 
         \h for help with SQL commands
167. rida: 251. rida:
  
 
kus on kasutatud järgmisi võtmeid:  
 
kus on kasutatud järgmisi võtmeid:  
-h - PostgreSQLi serveri nimi, alati teie veebiserveri nimi  
+
*-h - PostgreSQLi serveri nimi, alati teie veebiserveri nimi  
-U - PostgreSQLi serveri kasutajanimi, alati teie nW serveri kasutajanimi  
+
*-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  
 
viimane zoo - PostgreSQLi andmebaasi nimi, see on alati veebiserveri omaniku-nimeline; kui see argument jätta kirjutamata, siis kasutatakse kasutajanime nimelist andmebaasi  
  
175. rida: 259. rida:
 
Märgake, et tulemusena avanes andmebaasi nime sisaldav PostgreSQLi viip 'zoo=>', mille järele saab sisestada kahte tüüpi käske:  
 
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 (\)  
 
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 (;)  
+
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====
 
====Andmetabeli loomine====
184. rida: 265. rida:
 
Enne järgneva tegevuse juurde asumist logige PostgreSQLi serverisse ja võtke kasutusele oma andmebaas  
 
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
 
  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:  
 
Kirjeldame kolme väljaga andmetabeli inimesed:  
218. rida: 289. rida:
 
Otse käsurealt on tõenäoliselt tülikas sisestada suurt hulka andmeid, sellepärast selgitame allpool kuidas seda teha 'batch' rezhiimis.  
 
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'.  
+
Olles lõpetanud andmete sisestamise sulgeda PostgreSQLi klient käsuga '\q'.
  
 
====Päringu sooritamine====
 
====Päringu sooritamine====
229. rida: 300. rida:
  
 
SQL käskudes ei loe tähe suurus, st võib kasutada nii suuri kui väikesi tähti.  
 
SQL käskudes ei loe tähe suurus, st võib kasutada nii suuri kui väikesi tähti.  
 +
 +
Kõigi tabelite vaatamiseks andmebaasis
 +
 +
\dt
  
 
Esitame näiteks päringu, mis näitab andmetabeli inimesed kõikide kirjete kõiki välju  
 
Esitame näiteks päringu, mis näitab andmetabeli inimesed kõikide kirjete kõiki välju  
263. rida: 338. rida:
  
 
Arvestage, et nii kustutatakse kõik andmed, mis sisalduvad kõnealuses andmetabelis ja te olete neist jäädavalt ilma.
 
Arvestage, et nii kustutatakse kõik andmed, mis sisalduvad kõnealuses andmetabelis ja te olete neist jäädavalt ilma.
 
  
 
====Andmetabeli struktuuri muutmine====
 
====Andmetabeli struktuuri muutmine====
329. rida: 403. rida:
  
 
===Veebihaldus PhpPgAdmin===
 
===Veebihaldus PhpPgAdmin===
 +
 +
[[Pilt:Posgrephp.png]]
  
 
TODO
 
TODO
334. rida: 410. rida:
 
http://phppgadmin.sourceforge.net/
 
http://phppgadmin.sourceforge.net/
  
=== Postgresi koormuse test ===
+
=== Postgresi koormuse testimine ===
 +
 
 +
Toimub käsuga
  
 
  # pgbench -i -s 100 test
 
  # pgbench -i -s 100 test
 +
 +
või
  
 
  # pgbench -c 16 -t 1000 test
 
  # pgbench -c 16 -t 1000 test
 +
 +
Postgresi shared buf soovitus on veerand kasutada olevast mälust. Suurendada võib ka üheaegsete ühenduste arvu
 +
 +
shared_buffers = 4GB
 +
max_connections = 600
 +
 +
===Andmebaaside tihendamine===
 +
 +
Postgresi andmebaasi suuruse vähendamiseks on olemas eraldi mehanism nimega vacuum. Vaikimisi
 +
töötab postgresis kohe autovacuum, mis eemaldab täielikult tabelitest kustutatud read ja vabastab
 +
selle käigus ohtralt ruumi. Samas suure koormusega süsteemides ei pruugi see töötada kõige
 +
ideaalsemalt ja sellisel juhul on tarvis käivitada vacuum full.
 +
 +
Peamine probleem vacuum fulli puhul on see, et tabelid lukustatakse ära, mis tähendab omakorda downtime.
 +
Lisaks tekitab vacuum full koopia tabelist kus tühjad osad elimineeritud
 +
 +
===Tablespace===
 +
 +
Võimalik on seadistada osasid tabeleid erinevatele ketastele
 +
 +
CREATE TABLESPACE fastspace LOCATION '/ssd1/postgresql/data
 +
ALTER TABLE <table_name> SET TABLESPACE <new_tablespace_name>
  
 
===Probleemid===
 
===Probleemid===
  
 
Kui postgresql ei saa ühendust pordiga tasub vaadata postgresql.confis ega pole seatud tcpip_socket = false see tuleb sellisel juhul ümber muuta trueks.
 
Kui postgresql ei saa ühendust pordiga tasub vaadata postgresql.confis ega pole seatud tcpip_socket = false see tuleb sellisel juhul ümber muuta trueks.
 +
 +
Kui postgres teatab andmebaasi luues:
 +
 +
ERROR:  new encoding (UTF8) is incompatible with the encoding of the template database (SQL_ASCII)
 +
 +
Tuleks see luua nt käsurea kliendiga selliselt
 +
 +
CREATE DATABASE myDB TEMPLATE = template0 ENCODING = 'UTF8';
  
 
===Lingid===
 
===Lingid===
360. rida: 470. rida:
  
 
http://www.revsys.com/writings/postgresql/errors.html#cannot-connect-remote levinud veateated kasutamisel
 
http://www.revsys.com/writings/postgresql/errors.html#cannot-connect-remote levinud veateated kasutamisel
 +
 +
https://wiki.itcollege.ee/index.php/PostgreSQL

Viimane redaktsioon: 1. august 2019, kell 15:54

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.

Üks asi mida kohe tasub silmas pidada on see, et kasutusel on mysqliga võrreldes eraldi schemad andmebaaside liigitamiseks

db
|-schema
   |- tabel

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

Konfiguratsooni lisame

listen_addresses = '*'          # what IP address(es) to listen on;

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
..  
WARNING: enabling "trust" authentication for local connections
You can change this by editing pg_hba.conf or using the -A option the
next time you run initdb.

Success. You can now start the database server using:

    postgres -D /home/pgsql
or
    pg_ctl -D /home/pgsql -l logfile start

Kui kasutusel FreeBSD siis on postgres kasutajaks hoopis pgsql

Meie tingimustes võime käivitada nt käsu järgnevate kodeeringu võtmetega

$ initdb --encoding=UTF-8 --lc-collate=et_EE.UTF-8 --lc-ctype=et_EE.UTF-8 /home/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

Lähme pgsql kasutaja õigustesse (ehk postgres kasutaja õigustesse)

# su - pgsql

Käivitame postgresql serveri

$ pg_ctl -l logfile start -D /home/pgsql/
server starting

Loome administraator õiguses kasutaja

$ createuser --pwprompt
Enter name of role to add: admin
Enter password for new role:
Enter it again:
Shall the new role be allowed to create more new roles? (y/n) y

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 admin -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

Loodud baasi (või baaside nägemiseks)

\list

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

\q

Sama tegevust on võimalik teha ka käsureal postgres kasutaja õigustes spets utiliitide abil, ilma psql utiliiti kasutamata

root> su - postgres
postgres> createuser –s –P kasutaja
postgres> createdb andmebaas
postgres> dropdb andmebaas

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

Kasutaja parooli saab muuta käsuga

ALTER USER mart WITH PASSWORD 'uus';

Upgrade

Postgres upgrade puhul oleks soovitatav esmalt dumpida kogu baas ja hiljem restoreda. Selleks on kaks võimalust.

Esimene on plaintext. Plaintext dump+restore on VÄGA aeglased. Peaaegu olenematud sellest, mis konfiga on sul server

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 --locale=et_EE.utf8 -E UNICODE -D '/srv/postgres/data' 
postgres -D /home/pgsql
psql -f /srv/postgres/varukoopia postgres

Teine lahendus on directory format. Directory format dump+restore on väga kiire ja suudab teha mitme tuumaga

Tee rooduna

su -l -c "pg_dump -j **PROTSESSIDEARV** -Fd **ANDMEBAASINIMI** -f /KAUST/KUHU/PANNAKSE-dump-ANDMEBAASINIMI" postgres

Uuenda Postgres

Tee rooduna

su -l -c "pg_restore -j **PROTSESSIDEARV** /KAUST/KUST/VÕETAKSE/DUMP/" postgres

Soovitatavalt tee kõigepealt "postgres" andmebaas ja seejärel ülejäänud

Et kõiki andmebaase postgresis näha:

su -l -c 'psql -t -c "SELECT datname FROM pg_database WHERE datistemplate = false"

PROTSESSIDEARV pane enamvähem sama, mis serveril käes. :) See määrab ära, mitu paralleelset protsessi käima hakkavad.

Peamine miks mitte seda eelistada on probleemid liiga suurte tabelite korral.

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

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.

Kõigi tabelite vaatamiseks andmebaasis

\dt

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

Posgrephp.png

TODO

http://phppgadmin.sourceforge.net/

Postgresi koormuse testimine

Toimub käsuga

# pgbench -i -s 100 test

või

# pgbench -c 16 -t 1000 test

Postgresi shared buf soovitus on veerand kasutada olevast mälust. Suurendada võib ka üheaegsete ühenduste arvu

shared_buffers = 4GB
max_connections = 600

Andmebaaside tihendamine

Postgresi andmebaasi suuruse vähendamiseks on olemas eraldi mehanism nimega vacuum. Vaikimisi töötab postgresis kohe autovacuum, mis eemaldab täielikult tabelitest kustutatud read ja vabastab selle käigus ohtralt ruumi. Samas suure koormusega süsteemides ei pruugi see töötada kõige ideaalsemalt ja sellisel juhul on tarvis käivitada vacuum full.

Peamine probleem vacuum fulli puhul on see, et tabelid lukustatakse ära, mis tähendab omakorda downtime. Lisaks tekitab vacuum full koopia tabelist kus tühjad osad elimineeritud

Tablespace

Võimalik on seadistada osasid tabeleid erinevatele ketastele

CREATE TABLESPACE fastspace LOCATION '/ssd1/postgresql/data
ALTER TABLE <table_name> SET TABLESPACE <new_tablespace_name>

Probleemid

Kui postgresql ei saa ühendust pordiga tasub vaadata postgresql.confis ega pole seatud tcpip_socket = false see tuleb sellisel juhul ümber muuta trueks.

Kui postgres teatab andmebaasi luues:

ERROR:  new encoding (UTF8) is incompatible with the encoding of the template database (SQL_ASCII)

Tuleks see luua nt käsurea kliendiga selliselt

CREATE DATABASE myDB TEMPLATE = template0 ENCODING = 'UTF8';

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

https://wiki.itcollege.ee/index.php/PostgreSQL