Lähtetekstist kompileeritud PostgreSQL kasutamine Debianiga

Sissejuhatus

PostgreSQL tarkvara kasutamiseks Debian Lenny'ga on kaks võimalust

• paigaldada tarkvara paketihaldusest, 20091102 on seal versioon 8.3.7
• paigaldada tarkvara paketihaldusest, backports.org repositooriumist, 20091102 on seal versioon 8.4.1
• kompileerida tarkvara aadressil http://www.postgresql.org/ jagatavast lähtetekstist

Käesolev tekst tegeleb juhtumiga, kus andmebaas ning olulised lisamoodulid on paigaldatud lähtetekstist, sellisel lähenemisel on sellised eelised

• paindlikkus - kasutaja saab kontrollida, milliste omaduste PostgreSQL täpselt tekib
• andmebaasi versioon - kasutaja saab valida, millist PostgreSQL versiooni kasutada

ja puudused

• automaatsed veaparandused - kasutajal endal on kohustus veaparanduste ja versiooniuuenduste puhul kompileerida PostgreSQL mida muidu võiks saada uuendada paketihalduse vahenditega

Kasutaja peab ise otsustama, millisel viisil ta PostgreSQL tarkvara kasutab, paketihaldusest tuleva PostgreSQL kasutamist on kirjeldatud palas http://kuutorvaja.eenet.ee/wiki/Paketihaldusest_paigaldatud_PostgreSQL_kasutamine_Debianiga

Ettevalmistuste tegemine

Tekitada operatsioonisüsteemi kasutaja:grupp postgres:postgres kodukataloogiga /home/postgres

 # groupadd -g 110 postgres
 # useradd -u 110 -g postgres -m -d /home/postgres postgres

Valmistame ette LVM abil failisüsteemid binari installi ning andmekluster (ingl. k. data cluster) kataloogide jaoks

• /usr/local - kuigi PostgreSQL ise hakkab asuma kataloogis /usr/local/postgresql, nagu allpool näeme paigutame lisamooduli Postgis kataloogi /usr/local/postgis ja mõnus on neid korraga käsitleda
• /data/postgresql - andmekluster
• /data/backup/postgresql/archive-logs - arhiveeritud WAL logid
• /var/log/postgresql - logid

Operatsioonisüsteemi peab olema paigaldatud pakett locales, paigaldatud lokaadid

 # locale -a
 C
 POSIX
 et_EE.utf8

kusjuures süsteemi vaikelokaat on C.

Tarkvara paigaldamine

Kasutame selleks GNU C kompilaatorit (gcc) ning muid abivahendeid, mida saab paigaldada öeldes

 # apt-get build-dep postgresql-8.3
 # apt-get install bison flex

Kusjuures, eksituste vältimiseks on parem kui paketihaldusest ei ole paigaldatud PostgreSQL tarkvara, ei teeke ega binaare, st mitte ühtegi neist pakettidest (see nimekiri pole lõplik)

 postgresql  
 postgresql-7.4
 postgresql-8.0
 postgresql-8.3
 postgresql-8.3-pljava
 ...

Kui kõnealuses süsteemis kasutatakse nt PHPd koos PostgreSQLiga, siis libpq5 pakett on vajalik.

Paigaldamiseks tuleb logida süsteemi sisse kasutajana postgres ning pakkida lahti PostgreSQLi lähtetekst

 bash$ mkdir 20100505 && cd 20100505
 bash$ wget ftp://ftp.eenet.ee/pub/postgresql/source/v8.4.3/postgresql-8.4.3.tar.bz2
 bash$ tar jxf postgresql-8.4.3.tar.bz2

Veendume, et protsessid ei käi ja failid ei ole kasutuses ning kui on asjakohane, liigutame vanad asjad eest ära

 bash# mv /usr/local/postgresql /usr/local/postgresql-20090329-ennem-843-paigaldust
 bash# mv /data/postgresql /data/postgresql-20090329-ennem-843-paigaldust

Konfigureerimine, kompileerime ning paigaldame

 bash$ ./configure --with-perl --with-libxml --with-libxslt --with-openssl --with-pam --prefix=/usr/local/postgresql 1> ../configure.1.log 2>../configure.2.log
 bash$ make 1> ../make.1.log 2> ../make.2.log
 bash$ make install 1> ../make-install.1.log 2> ../make-install.2.log

contrib moodulid

Kompileerimise õnnestumiseks tuleb PostgreSQL kompileerida võtmega --with-libxslt

 $ cd contrib/xml2
 $ make
 $ make install

Kui andmebaas juba töötab, tuleb laadida skript

 postgresql-8.3.7/contrib/xml2/pgxml.sql

pgcrypto mooduli paigaldamiseks tuleb öelda

 $ cd contrib/pgcrypto
 $ make
 $ make install

pldebuggeri paigaldamiseks tuleb tarkvara kopeerida aadressilt http://pgfoundry.org/projects/edb-debugger/ ning pakkida lahti contrib kataloogis

 $ tar zxf edb-debugger-0.93.tgz
 $ cp -a pldebugger postgresql-8.3.9/contrib
 $ make
 $ make install

Lisatakse failid

/usr/local/postgresql/share/contrib
/usr/local/postgresql/share/contrib/pldbgapi.sql
/usr/local/postgresql/lib
/usr/local/postgresql/lib/pldbgapi.so
/usr/local/postgresql/lib/plugins
/usr/local/postgresql/lib/plugins/plugin_debugger.so
/usr/local/postgresql/lib/targetinfo.so

PL/Java paigaldamine

PL/Java võimaldab kasutada java't andmebaasist. PL/Java kasutamiseks peab olema süsteemis 2010 aasta kevade seisuga JDK 1.6 ning tarkvara kodulehekülge asub aadressil http://wiki.tada.se/index.php/PL/Java

$ tar zxf pljava-src-1.4.0.tar.gz 
$ export JAVA_HOME=/usr/local/java
$ export PATH=/usr/local/java/bin:$PATH
$ make 1>../pljava-make.1.log 2>../pljava-make.2.log
$ make install 1>../pljava-make-install.1.log 2>../pljava-make-install.2.log

Kui andmebaas juba töötab, tuleb laadida skript

 pljava/pljava-1.4.0/src/sql/install.sql

PL/Proxy

PL/Proxy https://developer.skype.com/SkypeGarage/DbProjects/PlProxy on PL/Proxy.

Tarkvara paigaldamine toimub sellise järgnevusena

 $ wget http://pgfoundry.org/frs/download.php/2447/plproxy-2.0.9.tar.gz
 $ tar plproxy-2.0.9.tar.gz
 $ make
 $ make install
 /bin/sh /usr/local/postgresql/lib/pgxs/src/makefiles/../../config/install-sh -c \
   -m 644 plproxy.sql '/usr/local/postgresql/share/contrib'
 /bin/sh /usr/local/postgresql/lib/pgxs/src/makefiles/../../config/install-sh -c \
   -m 755  libplproxy.so.0.0 '/usr/local/postgresql/lib/plproxy.so'

ja tulemusena lisanduvad süsteemi kaks faili

 /usr/local/postgresql/share/contrib/plproxy.sql
 /usr/local/postgresql/lib/plproxy.so

Seejärel tuleb baasi keele lisamiseks täita plproxy.sql skript, nt öeldes

 $ psql -U postgres -h localhost portaal < /usr/local/postgresql/share/contrib/plproxy.sql

Iseenesest see skript sisaldab lauseid

 -- handler function
 CREATE FUNCTION plproxy_call_handler ()
 RETURNS language_handler AS '$libdir/plproxy' LANGUAGE C;
 
 -- language
 CREATE LANGUAGE plproxy HANDLER plproxy_call_handler;

Postgis

Postgis http://geos.refractions.net/ on PostgreSQL andmebaasi laiendus, mis võimaldab kasutada andmebaasi GIS (Geographic Information Systems) objektide jaoks. Tavaliselt paigaldatakse lisaks ka Proj4 ja GEOS teegid.

Proj4 teekide paigaldamine

Proj4 teekide lähtetekstid saab kopeerida aadressilt http://trac.osgeo.org/proj/ või paigaldada Debian Lenny paketihaldusest, paigaldame paketihaldusest (versioon 4.6.0-2)

 # apt-get install proj

GEOS teekide paigaldamine

GEOS teekide lähtetekstid saab kopeerida aadressilt http://geos.refractions.net/ ja paigaldada Debian Lenny paketihaldusest, paigaldame paketihaldusest (versioon 3.0.0)

 # apt-get install libgeos-3.0.0 libgeos-dev

Postgis laienduse paigaldamine

Postgis tuleb paigaldada peale seda kui on paigadatud Proj4 ja GEOS teegid ning PostgreSQL andmebaas.

 $ wget http://postgis.refractions.net/download/postgis-1.3.5.tar.gz
 $ cd postgis-1.3.5
 $ ./configure --with-geos --with-proj --prefix=/usr/local/postgis 1>../postgis-configure-1.log 2>../postgis-configure-2.log

kusjuures postgis-configure-1.log peab ütleme midagi sellist lõpus

  HOST_OS: linux-gnu

  PGSQL: /usr/local/postgresql/bin/pg_config
   GEOS: /usr/bin/geos-config (with C-API)
         (ldflags: -L/usr/lib)
   PROJ: prefix=/usr libdir=/usr/lib
  ICONV: 1 

PORTNAME: linux
  PREFIX: /usr/local/postgis
 EPREFIX: ${prefix}
     DOC: ${prefix}/share/doc
    DATA: ${datarootdir}
     MAN: ${datarootdir}/man
     BIN: ${exec_prefix}/bin
     EXT: ${exec_prefix}/lib (${exec_prefix}/lib)

Kompileerimiseks ja paigaldamiseks tuleb öelda

 $ make 1> ../postgis-make-1.log 2>../postgis-make-2.log
 $ make install 1> ../postgis-make-install-1.log 2>../postgis-make-install-2.log

Andmebaasi moodustamine ja käivitamine

Andmebaasi moodustamiseks tuleb esmalt initsialiseerime andmebaasi, näidates sobiva lokaadi

 bash$ /usr/local/postgresql/bin/initdb -E UNICODE --locale et_EE.UTF-8 -D /data/postgresql
 The files belonging to this database system will be owned by user "postgres".
 This user must also own the server process.
 
 The database cluster will be initialized with locale et_EE.UTF-8.
 initdb: could not find suitable text search configuration for locale et_EE.UTF-8
 The default text search configuration will be set to "simple".
 
 fixing permissions on existing directory /data/postgresql ... ok
 creating subdirectories ... ok
 selecting default max_connections ... 100
 selecting default shared_buffers/max_fsm_pages ... 32MB/204800
 creating configuration files ... ok
 creating template1 database in /data/postgresql/base/1 ... ok
 initializing pg_authid ... ok
 initializing dependencies ... ok
 creating system views ... ok
 loading system objects' descriptions ... ok
 creating conversions ... ok
 creating dictionaries ... ok
 setting privileges on built-in objects ... ok
 creating information schema ... ok
 vacuuming database template1 ... ok
 copying template1 to template0 ... ok
 copying template1 to postgres ... ok
 
 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:
 
     /usr/local/postgresql/bin/postgres -D /data/postgresql
 or
     /usr/local/postgresql/bin/pg_ctl -D /data/postgresql -l logfile start

Edasise anmebaasi kasutamisel tuleks kohendada seadistusfaili ja keskkonnamuutujaid kasutuses oleva lokaadi osas

nt et_EE.UTF-8 kodeeringu puhul sobivad sellised muudatused seadistufailis

  # These settings are initialized by initdb, but they can be changed.
 lc_messages = 'et_EE.UTF-8'                     # locale for system error message
                                                 # strings
 lc_monetary = 'et_EE.UTF-8'                     # locale for monetary formatting
 lc_numeric = 'et_EE.UTF-8'                      # locale for number formatting
 lc_time = 'et_EE.UTF-8'                         # locale for time formatting

ja sellised keskkonnamuutujad

 export LC_CTYPE=et_EE.UTF-8
 export LC_COLLATE=et_EE.UTF-8

Andmebaasi seiskamiseks ja käivitamise võiks sobida nt selline start-skript

 # cat /etc/init.d/postgresql
 #!/bin/bash
 
 case "$1" in
   start)
     echo "Starting PostgreSQL server"
     /bin/su - postgres -c ". /home/postgres/pg-start.env && /usr/local/postgresql/bin/pg_ctl \
        start -D /data/postgresql -l /var/log/postgresql/pg_ctl.log"        
     ;;
 
   stop)
     echo "Stopping PostgreSQL server"
     /bin/su - postgres -c ". /home/postgres/pg-start.env && /usr/local/postgresql/bin/pg_ctl \
       stop -D /data/postgresql -m fast -l /var/log/postgresql/pg_ctl.log"         
     ;;
 
   *)
     echo "Usage: /etc/init.d/postgresql {start|stop}"
     exit 1
 esac
 
 exit 0

Kus /home/postgres/pg-start.env failis on kirjeldatud keskkonnamuutujad nt selliselt

 # cat /home/postgres/pg-start.env
 JAVA_HOME=/usr/local/java
 UL=/usr/local
 
 export PATH=/usr/local/sbin:/usr/local/bin:/usr/sbin:/usr/bin:/sbin:/bin:/usr/bin/X11:\
   /usr/local/java/bin:/opt/oracle/database/bin
 export JAVA_HOME=/usr/local/java
 export ORACLE_HOME=/opt/oracle/database
 
 export LD_LIBRARY_PATH=$JAVA_HOME/jre/lib/amd64:$JAVA_HOME/jre/lib/amd64/server:$UL/proj/lib:\
   $UL/geos/lib:$UL/postgis/lib:/opt/oracle/database/lib:/opt/oracle/database/lib32
 export CLASSPATH=$UL/postgresql/lib/pljava.jar:$UL/xt/xerces.jar:$UL/xt/sax2.jar:$UL/xt/xt20051206.jar:\
   $UL/xmlsec/commons-logging-api.jar:$UL/xmlsec/serializer.jar:$UL/xmlsec/xercesImpl.jar:\
   $UL/xmlsec/xmlsec-1.4.2.jar:$UL/xmlsec/xmlsecTests-1.4.2.jar:$UL/xmlsec/commons-logging.jar:\
   $UL/xmlsec/xalan.jar:$UL/xmlsec/xml-apis.jar:$UL/xmlsec/xmlsecSamples-1.4.2.jar
 
 export LC_CTYPE=et_EE.UTF-8
 export LC_COLLATE=et_EE.UTF-8

Tarkvara uuendamine

• Kui muutub PostgreSQLi nn minoorne versiooninumber, st tagant poolt kolmas, nt 8.3.6 -> 8.3.7, siis võib sama andmeklustri kataloogi kasutada.
• Kui muutub PostgreSQLi nn mažoorsem versioonumber, nt tagant poolt teine, nt 8.2.x -> 8.3.y, siis tuleb andmed esmalt kopeerida pg_dumpall käsu abil töötavast vanast baasist ära, samal ajal peaks pg_hba.conf failist kasutajate ja programmide ligipääs baasile olema keelatud

 $ /usr/local/postgresql/bin/pg_dumpall -h 127.0.0.1 -U postgres > pg_dumpall-20090329-8.2.sql

ning seejärel uude initsialiseeritud baasi sisse lugeda, öeldes nt

 $ /usr/local/postgresql/bin/psql -h 127.0.0.1 -U postgres template1 < pg_dumpall-20090329-8.2.sql \
 1>pg_dumpall-20090329-8.3-in-1.log 2>pg_dumpall-20090329-8.3-in-2.log

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

Andmete varundamine ja taaste pg_dump ja pg_restore programmide abil

pg_dump programmi abil saab PostgreSQL andmeklustri andmetest erinevatel tasemetel teha koopiaid - andmebaasist, skeemist, tabelist, ja määrata seejuures täpsustusi, nt kas kopeeritakse ainult struktuur või struktuur koos andmetega.

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 paindlikumat kasutust
• tar - TODO

Andmete taastamiseks sobib esimesel juhul kasutada psql programmi ning kahel viimasel spetsiaalset taasteks mõeldud programmi pg_restore, viimane võimaldab taastamisel suhteliselt lihtsasti suuremat paindlikkust, nt valida

• millised andmed arhiivist taastada (nt tableli täpsusega)
• millised järjekorras andmed arhiivist taastada

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.

Tekstikujul SQL skript

Tekstikujul SQL skripti dumpi moodustamiseks tuleb öelda

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

Skipti tekitamisel saab määrata nt, kas kasutamisel skript moodustab andmebaasi, skeemi või tabeli või mitte.

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 binaarne arhiiv

Nt andbaasi portaal skeemi data tabelist documents binaarse arhiivi moodustamiseks tuleb öelda

 $ export PGPASSWORD=parool
 $ /pg_dump -Fc -h localhost -U postgres -t data.documents portaal > portaal.data.documents.bin

Ja taastamiseks

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

Lisaks taastamisele saab küsida pg_restore abil arhiivi kohta andmeid

 $ pg_restore -l /data/backup/dumps/portaal.data.dokuments.bin 
 ;
 ; 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

pgpool

TODO

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

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

Andmebaasi kodeering

 portaal=# show server_encoding;
 server_encoding 
 -----------------
  UTF8
 (1 row)
 
 portaal=# show client_encoding;
 client_encoding 
 -----------------
  UTF8

Märkused

• Selleks, et psql, pg_dump jm käsud ei küsiks parooli sobib seadistada keskkonnamuutuja, kuid kasutaja peab ise otsustama, kas selline lahendus on talle sobiv

 $ export PGPASSWORD=parool

• WAL logide kasutamisel tuleb arvestada, et 8.3 versiooni puhul ei kandu nii üle hash indeksid, vt täpsemalt http://www.postgresql.org/docs/8.3/interactive/continuous-archiving.html#CONTINUOUS-ARCHIVING-CAVEATS

• Kui WAL logide kataloog saab täis, siis tekib logisse igal minutiel selliseid sissekandeid

 2009-06-17 23:59:01 EEST    3624  4a1ba0db.e28  43405 2009-05-26 10:57:15 EEST 0 LOG:  archive command failed with exit code 1
 2009-06-17 23:59:01 EEST    3624  4a1ba0db.e28  43406 2009-05-26 10:57:15 EEST 0 DETAIL:  The failed archive command was: test \
   ! -f /data/backup/postgresql/archive-logs/0000000100000018000000CD \
   && cp pg_xlog/0000000100000018000000CD /data/backup/postgresql/archive-logs/0000000100000018000000CD
 2009-06-17 23:59:01 EEST    3624  4a1ba0db.e28  43407 2009-05-26 10:57:15 EEST 0 WARNING:  transaction log file \
   "0000000100000018000000CD" could not be archived: too many failures

Tavaliselt aitab lihtsalt kataloogi ruumi juurde tegemine, nt vanemate logide kustutamise teel ning kuna viimane mida kopeerida ei saada on tõenäoliselt poolik fail, tuleb see kustutada WAL logide kataloogist. Seejärel jätkab süsteem normaalset tööd.

PostgreSQL arendusversioon

Aadressil http://developer.postgresql.org/pgdocs/postgres/anoncvs.html saab tutvuda juhatustega, kuidas kopeerida PostgreSQL lähtetekstid versioonihaldusest ning neid kasutama asuda.

Kasulikud lisamaterjalid

• http://www.postgresql.org/docs/8.3/interactive/index.html
• WalMgr_kasutamine
• PgAdmin3
• PostgreSQL_tarkvara_kasutamine_Solarisega
• Paketihaldusest_paigaldatud_PostgreSQL_kasutamine_Debianiga
• Postgresql
• Andmebaasi haldus-ja arendustarkvara SQL Studio for PostgreSQL