PostgreSQL haldamine

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'.

Järgnevate rollide moodustamiseks sobib öelda

create role priit

Rolli eemaldamiseks sobib öelda

drop role priit

Rollide kasutamine

Käesoleva rollinime küsimiseks sobib öelda

select user;

Uue rolli omandamiseks sobib öelda

set role uusrollinimi

Vaikimisi rolli tagasilülitamiseks

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.

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;

Kasulikud lisamaterjalid

• PostgreSQL