Lehekülje PostgreSQL haldamine lähteteksti vaatamine

===Sissejuhatus===

PostgreSQL andmebaasi haldamise eesmärgiks on tagada korrektselt tootav andmebaasiteenus, mida saavad kasutajad ja rakendused kasutada. PostgreSQL andmebaasi haldamine sisaldab sisaldab üldiselt selliseid tegevusi

* Andmebaasi tarkvara paigaldamine süsteemi
* Süsteemi paigaldatud andmebaasi tarkvara veaparanduste rakendamine
* Süsteemi paigaldatud andmebaasi tarkvara versiooniuuenduste sooritamine
* Andmabaasile kasutajate ja rakenduse jaoks ligipääsude tegemine
* Andmebaasi vakumeerimine
* Andmebaasist või selle osadest arhiivifaili moodustamine ja sellise arhiivifaili kasutamine
* Andmebaasi varundamine ja taaste
* Andmebaasi tarkvara eemaldamine süsteemist

Osa neist tegevustest on Kuutõrvajas käsitletetud eraldi tekstides.

Andmebaasi haldamise alla ei kuulu t andmebaasi kasutamisega seotud küsimused, kuigi tehniliselt võib saada kasutada samu instrumente nii kasutamise kui haldusprotseduuride soovitamiseks, nt programm psql.

Lisaks haldusprotseduuride endile kirjeldatakse käesolevas tekstis ka nt PostgreSQL andmebaasi objektide hierariat ja privileegisüsteemi, samuti, kuidas saada vastuseid haldamise seisukohalt olulistele küsimustele, nt milline on tabeli või andmebaasi suurus MBaitides.

===Haldustarkvara===

Andmebaasi haldamiseks sobib kasutada nt selliseid programme

* psql - nt sisaldub paketis postgresql-client-8.3
* PgAdmin3

===psql utiliidi kasutamine===

psql on andmebaasi interaktiivne terminaliprogramm, mida saab üldiselt kasutada nii andmebaasi pärigute esitamiseks kui haldusprotseduuridele iseloomulike tegevuste sooritamiseks. Järgnevas kirjeldatakse psql kasutamist aktsendiga haldusele.

* Skeemi tabelite nimekirja esitamine

 SQL> \dt priiduskeem.*

===Skeemid===

* Võimaldada mitmetele kasutajatel kasutada sama andmebaasi säilitades kasutajate privaatsuse.
* Korraldada andmebaasi objektid loogilistesse gruppidesse selleks, et andmebaas oleks paremini hallatav
* Erinevates skeemides saab kasutada samu objektide nimesid

Skeemi moodustamiseks sobib öelda, tavaliselt tekitab nii süsteemi administraator oma kasutajatele skeeme

 CREATE SCHEMA priiduskeem AUTHORIZATION priit;

kus

* priiduskeem - tekitatava skeemi nimi
* priit - andmebaasi kasutaja (üldisemalt roll)

Skeemide moodustamisel peab nimevalikul arvestama, et pg_ algusega nimede on reserveeritud kasutamiseks pg_catalog skeemis ning kuigi kasutajad saavad neid nimesid ka ise kasutada, kasutab andmebaas vaikimisi süsteemseid objekte kui sellise nimega pöörduda.

Kui andmebaasiga töötades skeemi mitte näidata, siis vaikimisi toimub tegevus

* kasutajanimelises skeemis
* kasutajanimelise skeemi puudumisel skeemis 'public'; see skeem moodustatakse andmebaasi loomisel automaatselt.

 test=> show search_path;
   search_path   
 ----------------
  "$user",public
 (1 row)

Skeemirada töötab sarnaselt tabelile ka muude skeemis sisalduvate objektidega, nt funktsioonid.

Skeemi suhtes on kasutajatel kahe sorti privileege

* usage - skeemis sisalduvate objektide kasutamine
* create - skeemi objektide tekitamine

Vaikimisi saavad kõik andmebaasi kasutajad töötada public skeemis, st seda kasutada ja sinna tekitada objekte.

Alati sisaldub andmebaasis nö süsteemne skeem pg_catalog ja seda kasutatakse enne skeemirajalt objektide otsimist.

public skeem on iseenesest tavaline skeem nagu iga teinega, nt võib selle vajadusel ka kustutada.

Skeemiraja seadistamiseks sobib öelda

 set search_path to skeeminimi1, skeeminimi2

===PostgreSQL privileegisüsteem===

PostgreSQL privileegisüsteem kontrollib, mida saavad erinevad kasutajad andmebaasis teha. Üldiselt on andmebaasi ligipääsule võimalik piiranguid seada kolmel tasemel

* võrguühenduse tase - nt andmebaasi ees töötavas tulemüüris võrguühendusi src ipi aadressi täpsusega piirates
* pg_hba.conf failis
* andmebaasi sisemiste vahenditega

Andmebaasi sisemiste vahenditega saab ligipääse piirata kõige täpsemalt kusjuures seadistatud ligipääse hoib andmebaas selleks ettenähtud andmebaasi tabelites.

===Kasutajad ja grupid - rollid===

Üldiselt öeldakse, et PostgreSQLi andmebaasi kasutajad tegutsevad andmebaasi kasutades mingites rollides

* andmebaasi objektide, nt tablitele juures on kirjas, millise rolliga kasutaja saab kõnealuse objektida millist tegevust sooritada, nt tabelit sisu select käsuga lugeda
* andmebaasi sisselogimiseks on kasutajal vaja teada mõnda andmebaasis kirjeldatud LOGIN omadusega rolli ligipääse (sh parooli)
* rollid võivad moodustada hierarhiaid, mida võib praktiliselt ette kujutada grupikuuluvustena; praktiliselt tähendab see nt seda, et andmebaasis kirjeldatakse roll ja sellega seostatakse mingid ligipääsud erinevatele objektidele ning selleks, et konkreetsed sisselogivad kasutajad saaksid kõnealustele objektidega tegevusi sooritada määratakse nad kirjeldatud rolli nö pärijateks

Rollid on kirjeldatud kogu andmeklustri ehk PostgreSQL installatsiooni eksemplari kohta, mitte iga andmebaasi kohta. Tuleb arvestada, et sama nö ühenduse sees saab kasutada ainult ühte andmebaasi, st nt ei saa sooritada päringut, mis tegelab erinevate baaside tabelitega.

===Rollide moodustamine===

Andmebaasi paigaldamisel tekitatakse andmebaasi selle kasutaja nimeline LOGIN omadusega roll, millena andmebaas paigaldati, traditsiooniliselt on selle rolli nimeks 'postgres'.

Group ehk mitte-LOGIN rolli moodustamiseks sobib öelda nt

 SQL> create role priit;

ja rolli eemaldamiseks

 SQL> drop role priit;

===Rollide kasutamine===

Käesoleva rollinime küsimiseks sobib öelda

 SQL> select user;

Uue rolli omandamiseks sobib öelda

 SQL> set role uusrollinimi;

Vaikerollile lülitumiseks

 SQL> reset role;

===Rollidele andmebaasiobjektide kasutamise lubamine===

Andmebaasi objektidel on privileegisüsteemi seisukohast kaks olulist omadust

* objekti omanik (ingl. k. owner) - objekti moodustanud roll, kellel on objekti suhtes kõikvõimalikud õigused
* pääsunimekiri (ingl. k. access control list, ACL) - millised omanikud saavad kõnealuse objektida milliseid tegevusi sooritada

====Tabel====

Olgu andmebaasi administraator tekitanud kaks rolli priit ning mart ning moodustanud kummagi kasutaja jaoks andmebaasi nendenimelise skeemi. Sellises olukorras saavad mõlemad kasutajad oma skeemis toimetada kuid üksteise tabelitele sisule ligi ei pääse.

 test=> select * from priit.priidutabel;
 ERROR:  permission denied for schema priit
 LINE 1: select * from priit.priidutabel;

Selleks, et kasutaja mart pääseks lugema kasutaja priit tabeli priit.priidutabel sisu peab priit kõnealusele tabelile andma kasutajale mart select pääsu

 GRANT SELECT ON TABLE priit.priidutabel TO mart;

Tabeli ligipääsude kohta saab küsida öeldes

 test=> \z priit.priidutabel 
                                Access privileges
  Schema |    Name     | Type  |  Access privileges  | Column access privileges 
 --------+-------------+-------+---------------------+--------------------------
  priit  | priidutabel | table | priit=arwdDxt/priit | 
                              : mart=r/priit          
 (1 row)

kust on näha

* priit=arwdDxt/priit - tabel kuulub kasutajale priit ning tal on omanikuna kõik privileegid
* mart=r/priit - tabel kuulub kasutajale priit ning kasutajal mart on sellele r privileeg (select)

Tabelilt ligipääsu eemaldamiseks sobib öelda

 REVOKE select ON priit.priidutabel TO mart;

Tabelile saab ligipääse anda ka tulba täpsusega, nt

 GRANT SELECT(nimi) ON priit.priidutabel TO mart;

kusjuures siis on võimalik küsida selectiga ainult vastava tulba andmeid.

Kasutaja parooli muutmine

 ALTER USER kasutajanimi WITH PASSWORD 'parool';

===SQL arhiivifaili moodustamine===

pg_dump programmi abil saab teha PostgreSQL andmebaasi (mitte kogu andmeklustrist, st kõigist andmebaasidest ühekorraga) andmetest erinevatel tasemetel koopiaid - andmebaasist, skeemist, tabelist, ja määrata seejuures täpsustusi, nt kas kopeeritakse ainult struktuur või struktuur koos andmetega. pg_dump kasutamise tulemusena moodustatakse arhiivifail.

Programmi kasumisel saab valida kolme formaadi vahel, milles väljund tekitatakse

* tekst (ingl. k. plain) - tekstikujul SQL skript, vaikeformaat
* kostümiseeritud arhiiv (ingl. k. custom) - binaarne ahriiv, vaikimisi pakitud, võimaldab kõige paindlikumalt pärast arhiivifailis olevaid andmeid kasutada
* tar - TODO

pg_dump moodustab tänu MVCC tehnika kasutamisele andmetest kooskõlalise väljundi, nii nagu andmete seis oli pg_dump käivitamisel. Kuig pg_dump kasutamise ajal on nö tavapärane andmebaasi kasutamine võimalik, kasutab ta lukke selliselt, et nt samal ajal ei saab tabelite struktuuri muuta.

====SQL skripti moodustamine====

SQL skripti kujul esineva arhiivifaili moodustamiseks sobib öelda nt

  $ export PGPASSWORD=parool
  $ pg_dump -U postgres -h localhost baasinimi -f baasinimi-20090802.sql 
    1> baasinimi-20090802-1.log 2> baasinimi-20090802-2.log

Skripti tekitamisel saab määrata nt, kas skripti lisatakse käsud andmebaasi, skeemi või tabeli moodustamise kohta või mitte.

====Kostümiseeritud arhiivifaili moodustamine====

Nt andbaasi portaal skeemi data tabelist documents kostümiseeritud arhiivi moodustamiseks tuleb öelda

  $ export PGPASSWORD=parool
  $ pg_dump -Fc -h localhost -U postgres -t data.documents -f portaal.data.documents.fcdump portaal

===SQL arhiivifaili kasutamine===

Andmete taastamiseks on kaks programmi

* psql - SQL skript antakse programmi sisendisse, psql täidab järjekorras seal esitatud laused kuni esimese veani või skripti lõpuni
* pg_restore - programmi argumendina näidatakse kostümiseeritud SQL arhiivifail, väljundisse kirjutatakse lausete täitmisel esinevad vead, kuid töötatakse kuni arhiivifaili lõpuni

pg_restore võimaldab

* taastada arhiivist andmeid objekti, nt tableli täpsusega
* valida, millised järjekorras andmed arhiivist taastatakse

====SQL skripti kasutamine====

Kasutamiseks tuleb psql utiliidiga laadida skript andmebaasi, nt

  $ psql -U postgres -h localhost baasinimi < baasinimi-20090802.sql

Tekstikujul esituse puuduseks on suhteliselt mahtukas tulemus ja aeganõudev protseduuri kestus. Eeliseks võib pidada asjaolu, et andmed on inimesele hõlpsasti loetaval ja muudetaval kujul.

Kasutades psql järel võtit '-1' toimub skripti täitmine ühe transaktsioonina.

====Kostümiseeritud arhiivifaili kasutamine====

Andmete kopeerimiseks kostümiseeritud arhiivifailist otse andmebaasi sobib öelda nt

  $ pg_restore -d portaal -h localhost -U postgres portaal.data.documents.fcdump

Arhiivifailis sisalduvate objektide nimekirja esitamiseks tuleb kasutada -l võtit

  $ pg_restore -l /data/backup/dumps/portaal.data.dokuments.fcdump
  ;
  ; Archive created at Thu Sep 10 14:54:27 2009
  ;     dbname: portaal
  ;     TOC Entries: 11
  ;     Compression: -1
  ;     Dump Version: 1.10-0
  ;     Format: CUSTOM
  ;     Integer: 4 bytes
  ;     Offset: 8 bytes
  ;     Dumped from database version: 8.3.5
  ;     Dumped by pg_dump version: 8.3.5
  ;
  ;
  ; Selected TOC Entries:
  ;
  8462; 1259 40169 TABLE data documents sysadm
  9384; 0 0 ACL data documents sysadm
  8463; 1259 40175 SEQUENCE data documents_doc_id_seq sysadm
  9385; 0 0 SEQUENCE OWNED BY data documents_doc_id_seq sysadm
  9386; 0 0 SEQUENCE SET data documents_doc_id_seq sysadm
  9378; 2604 42053 DEFAULT data doc_id sysadm
  9381; 0 40169 TABLE DATA data documents sysadm
  9379; 1259 48086585 INDEX data documents_doc_id sysadm
  9380; 2620 48087091 TRIGGER data timestamp sysadm

kus

* TOC (Table of Contents) - sisukord
* rea alguses on objekti id väärtus
* kõik semikoolonist paremale jääv on kommentaar

Nõuanded pg_restore kasutamiseks

* Arhiivifaili sisu teisendamiseks SQL skripti kujule tuleb jätta ära -d võti

 $ pg_restore arhiivifail-20100506.fcdump > arhiivifail-20100506.sql

* Arhiivifaili ühte tabeli kirjelduse esitamiseks tuleb näidata skeemi ning tabeli nimi ja võti -s

 $ pg_restore -s -n priiduskeem -t inimene arhiivifail-20100506.fcdump

* Arhiivifailist ühe tabeli taastamiseks otse andmebaasi

 $ pg_restore -d baasinimi -n priiduskeem -t inimene arhiivifail-20100506.fcdump

* Arhiivifailist nimekirja alusel objektide taastamiseks tuleb esmalt -l väljundi alusel moodustada sobiva sisuga tekstifail ja seda redigeerida ning siis seda kasutada, seejuures võib ka objektide järjekorda muuta

 $ pg_restore -l arhiivifail-20100506.fcdump > arhiivifail-20100506.fcdump.list
 $ pg_restore -d baasinimi -h localhost -U postgres -L arhiivifail-20100506.fcdump.list arhiivifail-20100506.fcdump

====Globals====

Globalsiks nimetatakse neid andmebaasi andmeid, mis ei sisaldu otseselt üheski baasis, vaid kuuluvad andmebaasi kui terviku juurde, nt rollid. Nende salvestamiseks sobib öelda

 $ pg_dumpall -g -h localhost -U postgres > globals.sql

Moodustatud skripti sobib kasutada nagu nö tavalist sql skripti.

===Andmebaasi vakuumimine===

Töötavat PostgreSQL andmebaasi tuleb regulaarselt vakuumida (ingl. k. vacuum) nt sellistel põhjustel

* päringud muutuvad kiiremaks kuna planner saab kasutada otsuste tegemisel tegelikkusele enam vastavat infot
* andmebaasi andmekluster võtab failisüsteemis vähem ruumi

Vakuumimiseks on kaks võimalust

* käsitsi vakuumimine - nt crontab abiga käivitatakse vakuumimist sooritav sql lause
* autovacuum - andmebaas nö sisemiste vahendite abil otsustab kunas ja mida vakuumida

====Käsitsi vakuumimine====

Käsitsi vakuumimine käib põhimõtteliselt öeldes tabeli või baasi kohta (mis tähendab, et tegeldakse baasi kõigi tabelitega)

  $ psql -h localhost portaal -U postgres -c "vacuum verbose analyze"

Nt võiks kutsuda crontabist välja sellist skripti kord ööpäevas

 #!/bin/bash
 renice 19 -p $$ 1>/dev/null
 export PGPASSWORD=parool
 nyyd=`date +%Y%m%d`
 export HOSTNAME=10.0.6.88
 export PGPORT=5432
 export PGUSER=postgres
 
 mkdir /srv/backup/postgresql/vacuum-logs/$nyyd
 
 psql_baasid=`/usr/local/postgresql/bin/psql -h $HOSTNAME -p$PGPORT template1 -U $PGUSER -c "\l" \
 | sed -e '1,3d' | sed -e '$d' | sed -e '$d' | awk {'print $1'}`
 
 for i in $psql_baasid
   do
     if test "$i" != "template0" && test "$i" != "template1" && test "$i" != "postgres"; then
       /usr/local/postgresql/bin/psql -h $HOSTNAME -p$PGPORT $i -U $PGUSER -c "vacuum verbose analyze" \
         1>/data/backup/postgresql/vacuum-logs/$nyyd/$HOSTNAME-$nyyd-pg_$i-vacuum-analyze-5432.txt 2>&1;
         echo "exit code: $?" >> /data/backup/postgresql/vacuum-logs/$nyyd/$HOSTNAME-$nyyd-pg_$i-vacuum-analyze-5432.txt
     fi
   done

seejuures tuleb tähele panna, et

* ps auxe esitab vakumeerimise ajal protsesside keskkonnamuutujate väärtused ning kui süsteemis saavad anda käske asjassepuutumatud kasutajad, siis on neil võimalik saada teada see parool

Vakuumimisega seotud ressurssikasutust saab kontrollida /srv/postgresql/postgresql.conf seadistusfaili parameetritega, http://www.postgresql.org/docs/8.3/static/runtime-config-resource.html#RUNTIME-CONFIG-RESOURCE-VACUUM-COST

  # - Cost-Based Vacuum Delay -
  
  vacuum_cost_delay = 10                  # 0-1000 milliseconds
  #vacuum_cost_page_hit = 1               # 0-10000 credits
  #vacuum_cost_page_miss = 10             # 0-10000 credits
  #vacuum_cost_page_dirty = 20            # 0-10000 credits
  #vacuum_cost_limit = 200                # 1-10000 credits

kus 

* vacuum_cost_delay = 10 - vähendab plokkseadme I/O kasutust

Vahel võib vakuumi logist leida huvitavaid teateid, nt selline, siis tuleks postgresql.conf failis max_fsm_pages parameetri väärtust varuga vastavalt suurendada

  NOTICE:  number of page slots needed (1875152) exceeds max_fsm_pages (1400000)
  HINT:  Consider increasing the configuration parameter "max_fsm_pages" to a value over 1875152.

====Autovacuum====

TODO

===Andmebaasi tuunimine===

Selleks, et kontrollida, kui palju süsteemi ressurssi andmebaasi protsessid kasutavad maksab üle vaadata /srv/postgresql/postgresql.conf faili sellised parameetrid, nt olgu arvutil 64 GB mälu ning praktiliselt ainuke teenus on PostgreSQL andmebaas

  max_connections = 750
  shared_buffers = 16GB
  maintenance_work_mem = 8GB

ning lisaks kasutada nt selliseid kernel.shm* parameetreid

  kernel.shmall = 64424509440
  kernel.shmmax = 64424509440

===Andmete varundamine ja taaste WAL logide abil===

Et võimalikest crash-situatsioonidest paremini välja tulla, kirjutab PostgreSQL kõik andmebaasi suhtes sooritatud tegevused andmeklustri alamkataloogi pg_xlog spetsiaalses formaadis logifailidesse, nn WAL (ingl. k. write ahead log) logidesse. Töötamise ajal võiks see kataloog välja näha nt selline

  $ find /data/postgresql/pg_xlog/ -ls
  7913473    4 drwx------   3 postgres postgres     4096 Apr  5 15:21 /data/postgresql/pg_xlog/
  7913480 16404 -rw-------   1 postgres postgres 16777216 Apr  5 15:14 /data/postgresql/pg_xlog/000000040000004600000081
  7913476 16404 -rw-------   1 postgres postgres 16777216 Apr  5 14:08 /data/postgresql/pg_xlog/00000004000000460000007E
  7913478 16404 -rw-------   1 postgres postgres 16777216 Apr  5 13:49 /data/postgresql/pg_xlog/00000004000000460000007D
  7913485 16404 -rw-------   1 postgres postgres 16777216 Apr  5 13:23 /data/postgresql/pg_xlog/00000004000000460000007C
  7913474    4 drwx------   2 postgres postgres     4096 Apr  5 15:21 /data/postgresql/pg_xlog/archive_status
  7913487    0 -rw-------   1 postgres postgres        0 Apr  5 00:23 /data/postgresql/pg_xlog/archive_status/00000004.history.done
  7913475    0 -rw-------   1 postgres postgres        0 Apr  5 06:03 /data/postgresql/pg_xlog/archive_status/000000040000004600000066.00557058.backup.done
  7913477 16404 -rw-------   1 postgres postgres 16777216 Apr  5 15:34 /data/postgresql/pg_xlog/00000004000000460000007A
  7913483    4 -rw-------   1 postgres postgres      243 Apr  5 06:03 /data/postgresql/pg_xlog/000000040000004600000066.00557058.backup
  7913486    4 -rw-------   1 postgres postgres       74 Apr  5 00:23 /data/postgresql/pg_xlog/00000004.history
  7913488 16404 -rw-------   1 postgres postgres 16777216 Apr  5 14:52 /data/postgresql/pg_xlog/000000040000004600000080
  7913479 16404 -rw-------   1 postgres postgres 16777216 Apr  5 14:30 /data/postgresql/pg_xlog/00000004000000460000007F
  7913481 16404 -rw-------   1 postgres postgres 16777216 Apr  5 12:57 /data/postgresql/pg_xlog/00000004000000460000007B

Selleks, et vähendada andmekadu, loetakse crash-situatsioonist väljudes viimasele checkpoint'ile otsa andmed WAL logist.

Kuigi tavaliselt pg_xlog kataloogis olevaid faile roteeritakse vastavalt checkpointide juhtumisele, siis on võimalik ka enne roteerimist vanemad WAL logid kopeerida eraldi kataloogi selleks, et neid vajadusel kasutada mõnele mineviku ajahetkele vastava andmebaasi seisu taastamiseks. Seda tegevust nimetatakse WAL logide arhiveerimiseks.

Et WAL logide abil saaks andmebaasi taastada tuleb teha sellised ettevalmistused

* käivitada andmebaas WAL logisid arhiveerivas režiimis
* kopeerida arhiveeritud WAL logid varundusse (st kuhugi teise andmete varundamisega tegelevasse arvutisse)
* salvestada andmebaasi nn täiskoopia varundusse

WAL logide abil andmebaasi taastamine toimub sellise järgnevusena

* andmebaasi protsesside töö on lõpetatud
* varundusest kopeeritakse andmeklustri kataloogi varem salvestatud nn täiskoopia
* andmebaasile tehakse kättesaadavaks arhiveeritud WAL logid, nt ühendades andmebaasi arvutile külge üle NFS'i
* andmebaasi käivitatakse WAL logisid peale lugevas režiimis

Lisaks taastamisele sobib see tehnika nt töötava andmebaasi kõrvale teise arvutisse andmebaasi mingi seisu moodustamiseks.

====WAL logide arhiveerimine====

WAL logide arhiveerimise sisselülitamiseks tuleb tuleb näidata seadistusfailis ära kahe parameetri väärtused nt selliselt ning muudatuse kehtestamiseks andmebaas seisata ja käivitada

  archive_mode = on               # allows archiving to be done
  archive_command = 'test ! -f /data/backup/postgresql/archive-logs/%f && cp %p /data/backup/postgresql/archive-logs/%f'
  # archive_timeout = 60

* arhive_mode - lülitab sisse andmebaasi tavalise töö taustal wal logide kirjutamise
* archive_command - näitab kuhu wal logid kopeerida
* archive_timeout - näitab millise ajalise intervalliga järmine WAL logi fail tekitatakse; kui parameetrit ei kasutata, siis moodustatakse järgmine andmete kogunemise mahu alusel

Tulemusena tekivad /data/backup/postgresql/archive-logs kataloogi sellised failid

  ...
  -rw------- 1 postgres postgres 16777216 Apr  5 07:23 000000040000004600000067
  -rw------- 1 postgres postgres 16777216 Apr  5 08:12 000000040000004600000068
  -rw------- 1 postgres postgres 16777216 Apr  5 08:53 000000040000004600000069
  -rw------- 1 postgres postgres 16777216 Apr  5 09:23 00000004000000460000006A
  -rw------- 1 postgres postgres 16777216 Apr  5 09:53 00000004000000460000006B
  -rw------- 1 postgres postgres 16777216 Apr  5 10:20 00000004000000460000006C
  -rw------- 1 postgres postgres 16777216 Apr  5 10:40 00000004000000460000006D
  ..

====Andmeklustri failisüsteemi täiskoopia salvestamine====

Andmebaasi töö käigus andmeklustri failisüsteemist koopiat tehes tuleb see failisüsteem viia nö kooskõlalisse olekusse. Selleks sobib kasutada sellist järgnevust
 
* peatame andmeklustrisse muudatuste kirjutamise

  $ psql -U postgres postgres -c "select pg_start_backup('backup');"

* kopeerida andmeklustri sisu varundusarvutisse, nt rsync abil
* jätkame andmeklustrisse muudatuste kirjutamist

  $ psql -U postgres postgres -c "select pg_stop_backup();"

See ei ole eriti oluline kui kaua süsteem on peatatud muudatuste kirjutamise olekus, praktiliselt ei ole hullu kui ta on seal ka mitu tundi. Alternatiiv on 

* öelda pg_start_backup
* moodustada andmeklustrist LVM snapshot
* öelda pg_stop_backup
* ühendada snapshot külge ja kopeerida andmed varundusarvutisse
* ühendada snapshot lahti ja kustutada snapshot

====Andmebaasi taastamine WAL logide abil kuni võimalikult kaugele====

Olgu andmebaasi arvutis juhtunud mingi selline sündmus, mille tulemusena tuleb varundusest andmebaas taastada võimalikult kaugele, st võimalikult väikese andmekaoga.

Lähtepunktiks on tarvis nelja asja

* Samal arhitektuuril ja versiooniga andmebaasi nn binari install kui see, mida varundati
* andmeklustri failisüsteemi täiskoopia
* peale täiskoopia moodustamist kogunenud WAL logid
* veel arhiveerimata WAL logid kataloogist pg_xlog (kui need on olemas ja võimalusel kopeerida suletud andmebaasi tingimustes)

Andmebaasi saab taastada sellise tegevuste järgnevuse abil

* Andmebaasi mootor ei käi
* Kopeerida andmebaasi andmeklustri failisüsteemi (nt /srv/postgresql) varundamise käigus moodustatud täiskoopia
* Kustutada /srv/postgresql/pg_xlog kataloogist failid (alamkaataloog archive_status alles jätta)

  $ find /data/postgresql/pg_xlog -type f -exec rm {} \;

* Kui on võimaik, kopeerida viimati töötanud andmebaasi failisüsteemist /srv/postgresql/pg_xlog kataloogist failid taastatava arvuti vastavasse kataloogi
* Kustutada fail /srv/postgresql/backup_label
* Tekitada nt sellise sisuga fail /srv/postgresql/recovery.conf andmeklusteri kataloogi, fail peab olema postgres kasutaja oma, sest peale protseduuri lõppu nimetatakse see fail ringi recovery.done nimega; restore_command näitab, kus WAL logid asuvad

  restore_command = 'cp /srv/backup/postgresql/archive-logs/%f %p'

* Paigutada WAL logid kataloogi /srv/backup/postgresql/archive-logs
* Lülitada ajutiselt baasi seadistusfailis WAL logide arhiveerimine välja
* Käivitada andmebaas tavapäraselt viisil

Käivitamisel tekivad sellised sissekanded andmebaasi logisse

  2009-04-04 23:53:33 EEST    7318  49d7c8cd.1c96  1 2009-04-04 23:53:33 EEST 0 LOG:  database system was interrupted; last known up at 2009-04-04 22:13:52 EEST
  2009-04-04 23:53:33 EEST    7318  49d7c8cd.1c96  2 2009-04-04 23:53:33 EEST 0 LOG:  starting archive recovery
  2009-04-04 23:53:33 EEST    7318  49d7c8cd.1c96  3 2009-04-04 23:53:33 EEST 0 LOG:  restore_command = 'cp /data/backup/postgresql/archive-logs/%f %p'
  cp: stat `/data/backup/postgresql/archive-logs/00000001.history' ei Ãµnnestu: No such file or directory
  2009-04-04 23:53:34 EEST    7318  49d7c8cd.1c96  4 2009-04-04 23:53:33 EEST 0 LOG:  restored log file "000000010000004600000059" from archive
  2009-04-04 23:53:34 EEST    7318  49d7c8cd.1c96  5 2009-04-04 23:53:33 EEST 0 LOG:  automatic recovery in progress
  2009-04-04 23:53:34 EEST    7318  49d7c8cd.1c96  6 2009-04-04 23:53:33 EEST 0 LOG:  redo starts at 46/59DB60E0
  2009-04-04 23:53:34 EEST    7318  49d7c8cd.1c96  7 2009-04-04 23:53:33 EEST 0 LOG:  restored log file "00000001000000460000005A" from archive
  2009-04-04 23:53:35 EEST    7318  49d7c8cd.1c96  8 2009-04-04 23:53:33 EEST 0 LOG:  restored log file "00000001000000460000005B" from archive
  2009-04-04 23:53:35 EEST    7318  49d7c8cd.1c96  9 2009-04-04 23:53:33 EEST 0 LOG:  restored log file "00000001000000460000005C" from archive
  2009-04-04 23:53:36 EEST    7318  49d7c8cd.1c96  10 2009-04-04 23:53:33 EEST 0 LOG:  restored log file "00000001000000460000005D" from archive
  cp: stat `/data/backup/postgresql/archive-logs/00000001000000460000005E' ei Ãµnnestu: No such file or directory
  2009-04-04 23:53:37 EEST    7318  49d7c8cd.1c96  11 2009-04-04 23:53:33 EEST 0 LOG:  record with zero length at 46/5E2CC3A0
  2009-04-04 23:53:37 EEST    7318  49d7c8cd.1c96  12 2009-04-04 23:53:33 EEST 0 LOG:  redo done at 46/5E2CC358
  2009-04-04 23:53:37 EEST    7318  49d7c8cd.1c96  13 2009-04-04 23:53:33 EEST 0 LOG:  last completed transaction was at log time 2009-04-04 23:47:30.504329+03
  cp: stat `/data/backup/postgresql/archive-logs/00000001000000460000005E' ei Ãµnnestu: No such file or directory
  2009-04-04 23:53:37 EEST    7318  49d7c8cd.1c96  14 2009-04-04 23:53:33 EEST 0 LOG:  restored log file "00000002.history" from archive
  cp: stat `/data/backup/postgresql/archive-logs/00000003.history' ei Ãµnnestu: No such file or directory
  2009-04-04 23:53:37 EEST    7318  49d7c8cd.1c96  15 2009-04-04 23:53:33 EEST 0 LOG:  selected new timeline ID: 3
  cp: stat `/data/backup/postgresql/archive-logs/00000001.history' ei Ãµnnestu: No such file or directory
  2009-04-04 23:53:38 EEST    7318  49d7c8cd.1c96  16 2009-04-04 23:53:33 EEST 0 LOG:  archive recovery complete
  2009-04-04 23:53:52 EEST    7316  49d7c8cd.1c94  2 2009-04-04 23:53:33 EEST 0 LOG:  database system is ready to accept connections

Ehk on ka logide sisselugemisel huvitav jälgida millised postgres kasutaja protsessid samal aja töötavad

  $ ps aux | grep postgres
  ...
  postgres  2080 13.0 12.7 1125532 1073016 ?     Ss   21:48   1:30 postgres: startup process   waiting for 00000001000000460000005B
  postgres  2468  9.0  0.0   9420   732 ?        D    22:00   0:00 cp /mnt/archive-logs/00000001000000460000005B pg_xlog/RECOVERYXLOG

Peale logide sisselugemist jääb andmebaas käima nö kasutataval kujul. Kuna protseduuri käigus oli WAL logide arhiveerimine välja lülitatud, tuleks baas siiski korraks kinni panna, logimine sisse lülitada ning uuesti käivitada.

====Andmebaasi taastamine WAL logide abil kuni ettenähtud ajahetkeni====

Olgu andmebaasi arvutis juhtunud mingi selline sündmus, mille tulemusena tuleb varundusest andmebaas taastada ettenähtud ajahetkeni (ingl. k. PITR - Point in Time Recovery).

Lähtepunktiks on tarvis kolme asja

* Samal arhitektuuril ja versiooniga andmebaasi nn binari install kui see, mida varundati
* andmeklustri failisüsteemi täiskoopia
* peale täiskoopia moodustamist kogunenud WAL logid

Andmebaasi saab taastada sellise tegevuste järgnevuse abil

* Andmebaasi mootor ei käi
* Kopeerida andmebaasi andmeklustri failisüsteemi (nt /srv/postgresql) varundamise käigus moodustatud täiskoopia
* Kustutada /srv/postgresql/pg_xlog kataloogist failid (alamkaataloog archive_status alles jätta)

  $ find /data/postgresql/pg_xlog -type f -exec rm {} \;

* Kustutada fail /srv/postgresql/backup_label
* Tekitada nt sellise sisuga fail /srv/postgresql/recovery.conf andmeklusteri kataloogi, fail peab olema postgres kasutaja oma, sest peale protseduuri lõppu nimetatakse see fail ringi recovery.done nimega; restore_command näitab, kus WAL logid asuvad (http://www.postgresql.org/docs/8.3/static/datetime-keywords.html)

  restore_command = 'cp /srv/backup/postgresql/archive-logs/%f %p'
  recovery_target_time = '2009-04-20 22:39:00 EETDST'

* Paigutada WAL logid kataloogi /srv/backup/postgresql/archive-logs
* Lülitada ajutiselt baasi seadistusfailis WAL logide arhiveerimine välja
* Käivitada andmebaas tavapäraselt viisil

Peale logide sisselugemist jääb andmebaas käima nö kasutataval kujul. Kuna protseduuri käigus oli WAL logide arhiveerimine välja lülitatud, tuleks baas siiski korraks kinni panna, logimine sisse lülitada ning uuesti käivitada.

Ajahetkeni taastamisel tekib selline log, muuhulgas öeldakse kuhu maani taastati

  2009-04-24 22:15:14 EEST    2655  49f20e37.a5f  150 2009-04-24 22:08:39 EEST 0 LOG:  restored log file "000000040000004C0000004C" from archive
  2009-04-24 22:15:15 EEST    2655  49f20e37.a5f  151 2009-04-24 22:08:39 EEST 0 LOG:  restored log file "000000040000004C0000004D" from archive
  2009-04-24 22:15:16 EEST    2655  49f20e37.a5f  152 2009-04-24 22:08:39 EEST 0 LOG:  recovery stopping before commit of transaction 95521631, time 2009-04-20 22:39:00.001938+03
  2009-04-24 22:15:16 EEST    2655  49f20e37.a5f  153 2009-04-24 22:08:39 EEST 0 LOG:  redo done at 4C/4D8E6BD0
  2009-04-24 22:15:16 EEST    2655  49f20e37.a5f  154 2009-04-24 22:08:39 EEST 0 LOG:  last completed transaction was at log time 2009-04-20 22:38:59.779467+03
  cp: stat `/data/backup/postgresql/archive-logs/00000005.history' ei Ãµnnestu: No such file or directory
  2009-04-24 22:15:16 EEST    2655  49f20e37.a5f  155 2009-04-24 22:08:39 EEST 0 LOG:  selected new timeline ID: 5
  2009-04-24 22:15:16 EEST    2655  49f20e37.a5f  156 2009-04-24 22:08:39 EEST 0 LOG:  restored log file "00000004.history" from archive
  2009-04-24 22:15:17 EEST    2655  49f20e37.a5f  157 2009-04-24 22:08:39 EEST 0 LOG:  archive recovery complete
  2009-04-24 22:15:46 EEST    2653  49f20e36.a5d  2 2009-04-24 22:08:38 EEST 0 LOG:  database system is ready to accept connections

===Andmebaasi UTF-8 kodeeringuvigade parandamine===

Järnev skript kontrollib sisendisse antud sql dump faili vastavust utf8 kodeeringule ning väljastab vigaste kirjete kohta andmed

 #!/usr/bin/python
 import sys, time
 
 rownr = 0
 t0 = t1 = time.time()
 next = sys.stdin.readline
 
 errors = open('utf8errors.log','w')
 
 while 1:
     row = next()
     if not row:
         break
     try:
         urow = row.decode('utf8')
     except:
         print row
         errors.write(row)
     rownr += 1
     if rownr % 1000000 == 0:
         t2 = time.time()
         print rownr, t2 - t1, t2 - t0
         t1 = t2
 
 print 'Done!'
 t2 = time.time()
 print rownr, t2 - t1, t2 - t0

Kasutamine toimub öeldes

  $ cat dump.sql | ./utf8check.py

===Misc===

* show <tab> - esitab nimekirja võimalikest küsimustest, nt

  show max_connections;
  max_connections 
 -----------------
  100
 (1 row)

* Andmebaasi poolt failisüsteemis kasutatud mahu küsimine, sisaldab indekseid

 sql> SELECT pg_size_pretty(pg_database_size('andmebaasinimi')) As fulldbsize;

* Tabeli suuruse küsimine

 sql> SELECT pg_size_pretty(pg_total_relation_size('skeeminimi.tabelinimi')) As fulltblsize, \
   pg_size_pretty(pg_relation_size('skeeminimi.tabelinimi')) As justthetblsize;

* Kui andmebaasi vacuumil esineb anomaaliaid, siis võib olla abiks baasi vacuumimine tabeli kaupa. Tabelite nimekirja saab küsida andmebaasilt öeldes

 $ psql -U postgres -h hostname -c "select n.nspname||'.'||c.relname from pg_class c join pg_namespace n on \
   (c.relnamespace=n.oid) where relkind='r' order by 1;" baasinimi > vacuum-tabelid.txt

ning saadud nimekirja abil toimub vakumeerimine öeldes

 $ for i in `cat vacuum-tabelid.txt`; do psql -c "vacuum verbose analyze $i" -U postgres -h hostname -p5432 baasinimi \
   1> $i-1.log 2> $i-2.log; sleep 5; done

===Andmebaasi crashide uurimine===

* Kui nt PL keeles tehtud protseduur kasutab mõnda teeki, mis omakorda kasutab mõnda teeki, mida aga failisüsteemis ei ole, siis tavaliselt andmebaas crashib teatega

 2010-05-24 16:10:09 EEST    21463  4bf28ed8.53d7  3 2010-05-18 15:58:00 EEST 0 LOG:  server process (PID 32188) exited with exit code 12
 2010-05-24 16:10:09 EEST    21463  4bf28ed8.53d7  4 2010-05-18 15:58:00 EEST 0 LOG:  terminating any other active server processes

Põhjusele võib aidata jälile jõuda mainitud PID väärtust eestpoolt logist otsides, nt antud juhtumil

 2010-05-24 16:01:19 EEST gateway portaal 127.0.0.1(57375) 32188 SELECT 4bfa789f.7dbc SELECT 1 2010-05-24 16:01:19 EEST 0 \
   WARNING:  error from Perl function "sqlora": install_driver(Oracle) failed: Can't load
   '/usr/local/lib/perl/5.8.8/auto/DBD/Oracle/Oracle.so' for module DBD::Oracle: libclntsh.so.10.1: cannot open shared object file: \
   No such file or directory at /usr/lib/perl/5.8/DynaLoader.pm line 225.
         at line 5
        Compilation failed in require at line 3.
        Perhaps a required shared library or dll isn't installed where expected
         at line 11
 2010-05-24 16:01:19 EEST gateway portaal 127.0.0.1(57375) 32188 SELECT 4bfa789f.7dbc SELECT 2 2010-05-24 16:01:19 EEST 0 \
   CONTEXT:  PL/pgSQL function "low" line 2 at RETURN
        PL/pgSQL function "field" line 335 at assignment
        PL/pgSQL function "sisu" line 502 at assignment
        PL/pgSQL function "show" line 56 at assignment
 Out of memory!

===Andmebaasi monitooring===

* Üheks andmebaasi oluliseks andmebaasi seisundit iseloomustavaks näitajaks on kui kaugel ta on nn wraparound ajast, arv peab olema alati alla 2g ja seda hoiab väiksena korrapärane andmebaasi vakumeerimine

  SQL> SELECT datname, age(datfrozenxid) FROM pg_database;

* Andmebaasi sisemisi näitajaid sobib kasutada Cactiga graafikute joonistamiseks või Nagiosegaga alarmi saatmiseks, nt http://bucardo.org/wiki/Check_postgres.

Kasulikud materjalid

* http://kuutorvaja.eenet.ee/wiki/Nagiose_kasutamine_Debian_Lenniga#PostgreSQL

===Failisüsteemi kasutamine===

Tekst http://www.postgresql.org/docs/9.0/static/storage-file-layout.html kirjeldab kuidas PostgreSQL andmebaas kasutab failisüsteemi

 faili-või kataooginimi	  Kirjeldus
 PG_VERSION	          A file containing the major version number of PostgreSQL
 base	                  Subdirectory containing per-database subdirectories
 global	                  Subdirectory containing cluster-wide tables, such as pg_database
 pg_clog	          Subdirectory containing transaction commit status data
 pg_multixact	          Subdirectory containing multitransaction status data (used for shared row locks)
 pg_notify	          Subdirectory containing LISTEN/NOTIFY status data
 pg_stat_tmp	          Subdirectory containing temporary files for the statistics subsystem
 pg_subtrans	          Subdirectory containing subtransaction status data
 pg_tblspc	          Subdirectory containing symbolic links to tablespaces
 pg_twophase         	  Subdirectory containing state files for prepared transactions
 pg_xlog	          Subdirectory containing WAL (Write Ahead Log) files
 postmaster.opts	  A file recording the command-line options the server was last started with
 postmaster.pid	          A lock file recording the current server PID and shared memory segment ID (not present after server shutdown

Andmebaaside OID väärtusi kasutadakse failisüteemis kataloogis base asuvate andmebaasidele vastavate kataloogide nimedena

 SQL> SELECT datname, oid FROM pg_database;

Andmebaasi kataloogis asub selliseid faile

* tabelitele ja indeksitele vastavad failid, mille nimedena kasutatakse üldiselt vastavad OID väärtusi
* tabeli ja indeksi failiga kaasnevad vastavad *_fsm ja *_vm failid, mis on vastavalt free space map ning visibility map
* kui tabeli 8kB suurusse pagesse ei mahu tabeli rida ära, siis kasutatakse vastavate andmete hoidmiseks TOAST (The Oversized-Attribute Storage Technique) faili
* kui tabeli on üle 1 GB suur, siis hoitakse vastavaid andmeid OID.1, OID.2 jne nimelistes failides

Tabeli nimi, oid väärtuse, kasutatud 8kB lehted ja tabelis sisalduvate korteežide arvu kohta saab küsida selliselt

 SQL> select relname, oid, relpages, reltuples from pg_class limit 10;
          relname          |  oid  | relpages | reltuples 
 --------------------------+-------+----------+-----------
  priidutabel              |  1247 |        7 |       283
  marditabel               | 11550 |        0 |         0
  ...

contrib utiliit oid2name abil saab küsida oid väärtusele vastavat tabelinime

 $ /usr/lib/postgresql/8.4/bin/oid2name -H 127.0.0.1 -U postgres -d rsyslog -f 2755 -x 
 From database "loomdb":
   Filenode             Table Name   Oid      Schema  Tablespace
 ---------------------------------------------------------------
       2755            priidutabel  2755       priit  pg_default

===Kasutaja autentimine Kerberosega===

Kerberose kasutaja autentimiseks Kerberosega peab olema kasutada Kerberose infrastruktuur, nt selline nagu kirjeldatud tekstis [[:MIT Kerberose kasutamine Debianiga]]. Lisaks tuleb

* moodustada andmebaasiarvuti PostgreSQL teenusele vastav Kerberose osapool öeldes andmebaasi arvutis kasutajana postgres

 $ kadmin -p root/admin -q "addprinc postgres/postgresql.loomaaed"
 $ kadmin -p root/admin -q "ktadd -k /etc/postgresql-common/krb5.keytab postgres/postgresql.loomaaed"

Eeldusel, et kerberose kasutaja nimi langeb kokku andmebaasi kasutaja nimega saab seejärel kasutaja andmebaasi sisse logida öeldes tavalisel moel

 $ psql -h postgresql.loomaaed -U priit priit

===Märkused===

* pg_dump ei salvesta andmebaasi search_path'i, see peale baasi taastamist tuleb sobiv alter lause eraldi öelda, nt

 SQL> ALTER DATABASE loomaaed SET search_path=loom, lind;

* Kui andmebaasi arhiivifaili sisselugemisel keelega seotud teegi asukoht muutub, siis sobib teha kas sobib link, nt

 # ln -s /usr/lib/postgresql/8.3/lib/plpgsql.so /usr/local/postgresql/lib/plpgsql.so

või moodustada nö käsitsi vastav keel enne arhiivifaili kasutamist

 SQL> CREATE LANGUAGE plpgsql;

* Suuremate nö bulk-insertide puhul on normaalne, et logisse ilmuvad sellised teated

 2010-06-14 13:40:17 EEST    3260  4c07e97c.cbc  135 2010-06-03 20:42:20 EEST 0 LOG:  checkpoints are occurring too frequently \
   (21 seconds apart)
 2010-06-14 13:40:17 EEST    3260  4c07e97c.cbc  136 2010-06-03 20:42:20 EEST 0 HINT:  Consider increasing the configuration \
   parameter "checkpoint_segments".

===Kasulikud lisamaterjalid===

* [[:PostgreSQL]]
* [[:Nagiose kasutamine Debian Lenniga]]