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.
Tööpõhimõte
- commit kirjutab muudatuse wal logifaili
- checkpoint kirjutab muudatused andmefailidesse
Vastaku ühe tabeli mingitele ridadele kolm 8k suurust page't base kataloogi all mingis failis; kui parasjagu baas nendega töötab, sh muudab, siis võiks nii öelda, et neist on olemas kolm eksemplari, kõik tõenäoliselt vähemalt mingitel hetkedel mingis osas erinevad üksteisest
- base kataloogi alla nn data failides
- shared buffers mälus
- wal logi kataloogis failides
Andmebaasi mootori üks vastutus seisneb selles, et ta oma dirty, commit, checkpoint jt vahenditega töötab andmetega ja sedasi, et mis iganes hetkel võib juhtuda crash, ikkagi pärast seda kõige hullem, mis juhtub, et keritakse mingi hulk muudatusi tagasi, kuid andmed on siiski kooskõlalised.
Väited
- autovacuum - tabeli ridadega toimunud update ja delete muudatused tekitavad tabelisse 'füüsiliselt' uusi sissekandeid, mille olemasolu on seotud PostgreSQL MVCC süsteemiga, st kui sama reaga tegeles üks kasutaja ja ütles delete, ning samal ajal teine kasutaja luges seda, siis peab baas suutma neid mõlemat kasutust teenindada; sellel põhjusel kogunevad ühest samast tabeli reast mitmed versioonid, autovacuum tegeleb vanade kasutute versioonide all oleva salvestusressursi uuesti kasutatavaks märkimisega; seejuurus autovacuum ei anna failisüsteemis mahtu tagasi v.a. siis kui tema vabastatavad read asuvad data failide lõpus
- vacuum full - tabel kopeeritakse sisuliselt ümber ja ruum vabastatakse; ümber kopeerimiseks on vaja sama palju ruumi kui nö mustas tabelis on (st mitte pole vaja lisaks ainult seda ruumi, mis on reaalselt kasutuses); vacuum full saab öelda create database kaupa ja ka tabeli kaupa; vacuum full haldab automaatselt ära indeksid, constraintid jms
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.*
ja koos suurustega lisada +
SQL> \dt+ priiduskeem.*
Väljundi faili salvestamiseks tuleb ette öelda
SQL> \o /tmp/psql-valjund.txt
Andmebaasid
Vaikimisi on kõigil PostgreSQL kasutajatel õigus ühenduda andmebaasiga, kusjuures sealt edasi andmebaasis sisalduvate objektide (skeemid, tabelid, vaated je) kasutamist piiratakse privileegidega. Kui andmebaas on ühe kasutaja oma ja skeem ning skeemis sisalduv teise kasutaja oma, siis esimene kasutaja ei pruugi saada teist skeemi kasutada vaatamata sellele, et ta on vastava andmebaasi omanik.
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
Skeemidele antud privileegide esitamiseks sobib öelda
baasinimi=# \dn+
List of schemas
Name | Owner | Access privileges | Description
--------------------+----------+----------------------+----------------------------------
information_schema | postgres | postgres=UC/postgres |
: =U/postgres
pg_catalog | postgres | postgres=UC/postgres | system catalog schema
: =U/postgres
pg_toast | postgres | | reserved schema for TOAST tables
pg_toast_temp_1 | postgres | |
public | postgres | postgres=UC/postgres | standard public schema
: =U/postgres
kus
- public skeemi omanik on kasutaja postgres ja tal on selle skeemi suhtes usage ja create õigused
- kõigil teistel kasutajatel on public skeemi suhtes usage õigus; üldiselt võib öelda
kasutaja_kellele_privileeg_antakse=privileeg/kõnealuse_objekt_omaniku_nimi
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 kasutajad;
ja rolli eemaldamiseks
SQL> drop role kasutajad;
LOGIN rolli moodustamiseks sh parooli seadmiseks sobib öelda
SQL> create role priit login password 'parool' NOSUPERUSER INHERIT NOCREATEDB NOCREATEROLE;
Andmebaasi administraator saab muuta kõigi kasutajate paroole ja kasutaja saab muuta ise enda parooli öeldes
SQL> alter role priit with password 'uusparool';
Ilmutatud kujul parooli esitamisel on selline puudus, et see võib jääda kuhugi alles, nt logisse või .psql_history faili. Alternatiiviks on arvutada selliselt kokku ühendatud parool + kasutajanimi md5 summa
SQL> select md5('uusparoolpriit');
"fdd04c2f594e548b77c66781c8f5a85a"
ning seejärel, nb! hash algusse on lisatud 'md5'
SQL> alter role priit with encrypted password 'md5fdd04c2f594e548b77c66781c8f5a85a';
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;
Käesolevat rolli saab küsida
SQL> show role;
Kasutajale st login rollile grupi rolliga seotud privileegide lisamiseks
SQL> grant grupiroll to loginroll;
Rollidele andmebaasiobjektide kasutamise lubamine andmebaasi sisemise acl abil
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); üldiselt kehtib
kasutaja_kellele_privileeg_antakse=privileeg/kõnealuse_objekti_omaniku_nimi
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';
Kõigi skeemi tabelitele rolli ligipääsu tekitamine, vastuseks saab hulga sql lauseid, mis tuleb seejärel käivitada, nt (\o /tmp/failinimi.sql salvestab vastuse failisüsteemi, et seda sealt \i /tmp/failinimi.sql abil seejärel kasutada)
SQL> \o /tmp/failinimi.sql SQL> select 'GRANT SELECT, INSERT, UPDATE, DELETE ON ' || schemaname || '.' || tablename || ' TO rollinimi;' \ from pg_tables where tableowner='omanikunimi' and schemaname != 'pg_catalog' and schemaname != 'information_schema';
Kusjuures, selleks, et roll saaks skeemi kasutada peab olema skeemile antud nt USAGE privileeg
SQL> GRANT USAGE ON SCHEMA skeeminimi TO rollinimi;
Tabelile ligipääs
SQL> SELECT 'GRANT SELECT ON skeeminimi.' || table_name || ' TO rakendus_ro;' FROM information_schema.tables WHERE table_schema = 'skeeminimi' \ and table_type = 'BASE TABLE'; -------------------------------------------------------- GRANT SELECT ON skeeminimi.vahendaja TO rakendus_ro; GRANT SELECT ON skeeminimi.oigus_antud TO rakendus_ro; GRANT SELECT ON skeeminimi.dokumendi_fail TO rakendus_ro; GRANT SELECT ON skeeminimi.vastuvotja_staatus TO rakendus_ro; ..
Sequentsidele ligipääs
SQL> select 'grant usage, select, update on rakendus.' || sequence_name || ' to rakendus_rw' FROM information_schema.sequences \
where sequence_schema = 'skeeminimi';
?column?
------------------------------------------------------------------------
grant usage, select, update on skeeminimi.sq_transport_id to rakendus_rw;
grant usage, select, update on skeeminimi.sq_vastuvotja_id to rakendus_rw;
grant usage, select, update on skeeminimi.sq_ametikoht_id to rakendus_rw;
grant usage, select, update on skeeminimi.sq_ametikoht_taitmine_id to rakendus_rw;
..
Ligipääsu eemaldamine
SQL> SELECT 'REVOKE SELECT ON skeeminimi.' || table_name || ' FROM rakendus_rw;' FROM information_schema.tables WHERE table_schema = 'skeeminimi' \ and table_type = 'BASE TABLE';
Funktsioon
ALTER FUNCTION vklog.log_dokument(vk.dokument, vk.dokument, character varying) OWNER TO vk_admin;
Trigger funktsioon
GRANT EXECUTE ON FUNCTION dkk.tr_allkiri_log() TO dkk_rw;
Rollidele andmebaasiobjektide kasutamise lubamine pg_hba.conf seadistusfaili abil
Seadistusfail /etc/postgresql/8.4/main/pg_hba.conf kontrollib ligipääsu sellise komplekti andmete alusel
- ühenduse tüüp - üle tcp või unix soketi (st kas üle võrgu või unix soket abil)
- baasi nimi
- kasutaja nimi
- ip aadress (tcp ühenduse puhul)
- autentimise meetod - parooliga, kasutaja sertifikaadiga; unix soketi puhul ident
ident
Nn unixi postgre kasutaja saab ligi kõigile baasidele üle unixi soketi (/var/run/postgresql/.s.PGSQL.5432)
local all postgres ident
ja kasutamiseks tuleb öelda nt
# su - postgres $ psql -U postgres
map
mappinguid juhitakse failiga pg_ident.conf http://www.postgresql.org/docs/8.4/static/auth-username-maps.html
# cat pg_ident.conf .. # MAPNAME SYSTEM-USERNAME PG-USERNAME mapping_yks www-data pg_kasutaja_1 mapping_kaks www-data pg_kasutaja_2
kus
- MAPNAME - teisenduse nimi, mida pg_hba.conf failis kasutatakse
- SYSTEM-USERNAME - ident nimi (kohalikust arvutist tehtud pöördumiste puhul praktiliselt andmebaasi poole pöörduva protsessi UID)
- PG-USERNAME - andmebaasi kasutaja nimi, millena on vastaval SYSTEM-USERNAME'il lubatud andmebaasi kasutada
ja (kusjuures nagu ikka, oluline on kus ridade järjekorra mõttes antud rida paikneb nt tema ees ei tohi olla 'local all all reject')
# pg_hba.conf local all all ident map=mapping_yks
siis töötab nt selline kasutus
# su - www-data $ psql -U pg_kasutaja_1 baasinimi .. baasinimi =>
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
Kui andmebaasis on kasutatud contrib mooduleid, mille esmakordsel paigaldamisel tuli tõenäoliselt käivitavad vastav mooduli paigaldusskript (mis tekitas baasi funktsioone jms), siis taastel ei ole reeglina seda vaja kasutada, sest vastavad funktsioonid on baasis olemas ja taaste käigus nad tekitatakse.
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.
Kui esitada alltoodud käsk kujul, toimub skripti täitmine ühe transaktsioonina, mis praktiliselt tähendab seda, et kui skripti täitmine tehnilises mõttes ebaõnnestub, töötab andmebaas edasi muudatusele eelnenud kujul
$ psql -1 -U postgres -h localhost -f baasinimi-20090802.sql baasinimi
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
- Mitme protsessoriga arvutil saab -j võtme abil näidata milliselt määral kasutatakse paralleelset tööd andmete sisselugemisel, nt
$ pg_restore -j 16 -d baasinimi 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. Ainult rollide andmete kopeerimiseks sobib öelda
$ pg_dumpall -r -h localhost -U postgres > roles.sql
SQL dump/restore kasutamine arvestades objektide omanikke
Lähtepunkt
- töötab üks PostgreSQL protsesside komplekt
- on olemas üks andmebaasi tarkvara sisene kasutaja
- on olemas create database andmebaas (kõik objektid kuuluvad mainitud kasutajale)
Sihtpunkt
- töötab üks PostgreSQL protsesside komplekt
- lähtepunktiks olnud kasutaja ja baas säilivad algsel kujul
- on tekitatud juurde teine andmebaasi tarkvara sisene kasutaja
- on tekitatud juurde teine create database andmebaas; kõik objektid on seal teise kasutaja omanduses
- teise andmebaasi sisuks on esimese andmebaasi sisu
- mõlemad create database andmebaasid on tavalised, üksteisest sõltumatud andmebaasid
Nt leiab selline ülesanne kasutust töötavast lahendusest teha koopia kõrvale mingite testimiste jaoks.
pg_dump -O ja psql
Protseduuri eeliseks on, et andmete sisselaadimine uude andmebaasi tõimub privilegeerimata kasutajana. pg_dump kasutamisel sql skript moodustamisel tuleb omanike käsud dump failist välja jätta kohe alguses
pg_dump -O ...
sisselaadimiseks
TODO
pg_dump -Fc ja pg_restore
Protseduuri eeliseks on, et andmete sisselaadimine uude andmebaasi tõimub privilegeerimata kasutajana. Moodustada -Fc dump
$ /usr/lib/postgresql/9.4/bin/pg_dump -Fc -f baasinimi-20180922-94.dump baasinimi
uue kasutaja moodustamine
postgres=# create role kasutajanimi login password 'fakeparool' NOSUPERUSER INHERIT NOCREATEDB NOCREATEROLE;
uue baasi moodustamine (template = template0 LC_COLLATE = 'C.UTF-8' LC_CTYPE = 'C.UTF-8' on näiteks)
postgres=# create database baasinimi owner = kasutajanimi template = template0 LC_COLLATE = 'C.UTF-8' LC_CTYPE = 'C.UTF-8';
Sisselaadimiseks sobib öelda
$ /usr/lib/postgresql/9.4/bin/pg_restore -d baasinimi -O -h 127.0.0.1 -U kasutajanimi baasinimi-20180922-94.dump
Password:
pg_restore: [archiver (db)] Error while PROCESSING TOC:
pg_restore: [archiver (db)] Error from TOC entry 8746; 0 0 COMMENT EXTENSION plpgsql
pg_restore: [archiver (db)] could not execute query: ERROR: must be owner of extension plpgsql
Command was: COMMENT ON EXTENSION plpgsql IS 'PL/pgSQL procedural language';
pg_restore: WARNING: no privileges could be revoked for "public"
pg_restore: WARNING: no privileges could be revoked for "public"
pg_restore: WARNING: no privileges were granted for "public"
pg_restore: WARNING: no privileges were granted for "public"
WARNING: errors ignored on restore: 1
kus
- -O kasutada ilma owner käskudeta
- - tõneäoliselt saab mõned praktiliselt ebaolulised vead/hoiatused
reassing
Protseduuri puhul toimub andmete sisselaadimine uude andmebaasi privilegeerimata kasutajana. Moodustada -Fc dump
$ /usr/lib/postgresql/9.4/bin/pg_dump -Fc -f baasinimi-20180922-94.dump baasinimi
uue kasutaja moodustamine
postgres=# create role uuskasutajanimi login password 'fakeparool' NOSUPERUSER INHERIT NOCREATEDB NOCREATEROLE;
uue baasi moodustamine
postgres=# create database baasinimi owner = uuskasutajanimi;
andmete sisselaadimine baasi ülikasutajana (postgres)
postgres$ /usr/lib/postgresql/9.4/bin/pg_restore -d baasinimi baasinimi-20180922-94.dump
Tundun, et see reassing mõjub ühe create database andmebaasi piires; selles, millega on parasjagu ühendus
sql> reassign owned by vanakasutajanimi to uuskautajanimi;
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.100.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.
Kui nö tavaline vacuum ei saavuta soovitud tulemus, sobib proovida vacuum full
sql> \c andmebaas sql> vacuum full;
seejuures tuleb arvestada, et
- parem kui vacuum full ajal rakendused baasi ei kasuta
- vacuum full võtab palju aega ja tekitab tugeva IO koormuse
- vacuum full kasutab ajutisel sama palju ruumi kui andmebaasi ise (õieti kõige suurem tabel)
Autovacuum
Autovacuum võib olla sisselülitatud või väljalülitatud kusjuures autovacuum juhtub igal juhul automaatsel kui mõne tabeli pg_class.relfrozenxid suurus ületab seadistusfailis näidatud väärtused (vaikimisi 200 M).
autovacuum_freeze_max_age (integer) Specifies the maximum age (in transactions) that a table's pg_class.relfrozenxid field can attain before a VACUUM operation is forced to prevent transaction ID wraparound within the table. Note that the system will launch autovacuum processes to prevent wraparound even when autovacuum is otherwise disabled. The default is 200 million transactions. This parameter can only be set at server start, but the setting can be reduced for individual tables by entries in pg_autovacuum.
Autovacuum toimimist iseloomustavad tabeli vastavad tulbad
SQL> select * from pg_stat_user_tables;
Andmete varundamine ja taaste WAL logide abil
PostgreSQL abil saab andmeid varundada nn Full, Differential ja Incremental viisil
- full - varundatud andmetest piisab varundamise hetkel töötanud andmebaasi seisu taastamiseks
- differential - varundatud andmetest ja ning viimasest full backupist piisab viimase differential varundamise hetkel töötanud andmebaasi seisu taastamiseks; iga differential backupi tegemisel varundatakse kõik viimasest fullile järgnenud muudatused
- incremental - varundatud andmetest, kõigist viimase full backupist alatest tehtud muudest incrementalitest ja full backupist piisab viimase differential varundamise hetkel töötanud andmebaasi seisu taastamiseks; iga incremental backupi teegmisel varundatakse kõik viimati tehtud incrementalile järgnenud muudatused
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. Tundub, et wal logide järgnevuse kasutamist ei sega, kui selle logi tekkimise ajal on andmebaas crashinud.
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 ..
Andmeklastri failisüsteemi täiskoopia salvestamine
Andmebaasi töö käigus andmeklastri 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 andmeklastri sisu varundusarvutisse, nt rsync abil
- jätkame andmeklastrisse 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 andmeklastrist 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, vähemalt kuni 'select pg_stop_backup();' ütlemiseni
- 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, uuemal ajal võib ka selle kustutada, moodustatakse automaatselt tagasi, v 8.4 ja 9.0 puhul)
$ find /srv/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
- 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
- Andmeklustri kataloogis peab olema fail backup_label, millest saab süsteem taasteks kasutatava esimese wal logi faili nime teada, tegevuse käigus nimetatakse see ümber backup_label.old'iks
- Ajutiselt baasi seadistusfailist WAL logide arhiveerimist välja lülitada ei ole vaja
- 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
Korrektsel juhutumil hakatakse kasutama logisid alates sellest failis mis on backup_label failis kirjas stop wal location juures.
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. Kui 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, vähemalt kuni 'select pg_stop_backup();' ütlemiseni
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 {} \;
- Andmeklustri kataloogis peab olema fail backup_label, millest saab süsteem taasteks kasutatava esimese wal logi faili nime teada, tegevuse käigus nimetatakse see ümber backup_label.old'iks
- 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
Ajahetkeni taastamisel peab arvestama, et see aega saab olla peale pg_stop_backup ütlemise ajahetke ja arusaadaval ei saa see olla hilisem kui on kasutada wal logisid.
Varundamise monitooring Nagiose passiivse kontrolliga
Varundamise skript lõppu tuleb lisada
if [ $ec_psql -eq 0 ] && [ $ec_rsync -eq 0 -o $ec_rsync -eq 24 ]; then echo -e "db.loomaaed\tbackup\t0\tbackup korras" | send_nsca -c /etc/send_nsca.cfg -H mon.loomaaed -p 5667 else echo -e "db.loomaaed\tbackup\t2\tbackup katki" | send_nsca -c /etc/send_nsca.cfg -H mon.loomaaed -p 5667 fi
kus
- juhtumil kui psql või rsync käsud on õnnestunud saadatetakse korras nagiosele signaal
- juhtumil kui psql või rsync käsud on õnnestunud saadatetakse katki nagiosele signaal
ning Nagioses kirjeldada passiivne kontroll
define service {
host_name www-1.auul
service_description backup
use passive-generic-service
}
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
- v. 8.4 - failinime järgi tabeli leidmine, kui failisüsteemis on suured failid ja tekib küsimus, millise tabeliga on nad seotud, sobib öelda nii
$ du -sk /data/postgresql/8.4/main/base/16400/* | sort -n | tail -n 4 1049604 /data/postgresql/8.4/main/base/16400/195106.6 1049604 /data/postgresql/8.4/main/base/16400/195106.7 1049604 /data/postgresql/8.4/main/base/16400/195106.8 1049604 /data/postgresql/8.4/main/base/16400/195106.9
$ /usr/lib/postgresql/8.4/bin/oid2name -H 127.0.0.1 -U postgres -d loomaaed -f 195106 -x From database "loomaaed": Filenode Table Name Oid Schema Tablespace --------------------------------------------------------- 195106 pg_toast_195103 195106 pg_toast pg_default
$ /usr/lib/postgresql/8.4/bin/oid2name -H 127.0.0.1 -U postgres -d loomaaed -f 195103 -x From database "loomaaed": Filenode Table Name Oid Schema Tablespace ------------------------------------------------------------ 195103 tabelinimi 74224 wizard pg_default
Tulumusena on teada, et 195106.6 fail on seotud wizard.tabelinimi tabeliga.
- v. 9.4 - failinime järgi tabeli leidmine, kui failisüsteemis on suured failid ja tekib küsimus, millise tabeliga on nad seotud, sobib öelda nii
# \c baasinimi
baasinimi=# select pg_filenode_relation(0,233328054);
pg_filenode_relation
----------------------
baasinimi
(1 row)
baasinimi=# select pg_relation_filepath('tabelinimi');
pg_relation_filepath
----------------------
base/16424/233328054
(1 row)
Andmebaasi krahh
Andmebaasi krahh (ingl. k. crash) on selline olukord kus andmebaasi töö on lõppenud ilma nö normaalselt andmebaasi protsesse seisates, nt öeldes
# pg_ctlcluster 9.2 main stop
Krahh võivad tekkida nt järgmistel juhtudel
- seoses operatsioonisüsteemi krahhiga (nt arvutil kaob toide)
- andmefailide failisüsteem saab täis
- andmebaasi ise või mõni moodul kasutab mälu valesti ja kogu baasi töö lõpetatakse (andmebaasi teeb endale ise restardi)
Anmdmebaasi käivitamine peale krahhi
Üldiselt PostgreSQL andmebaas ei lähe krahhi tulemusena katki, andmebaas kasutab failisüsteemi sellisel viisil, et järgmisel käivitamisel jõuab baas viimase töötava seisuni (transaktsioonini) ja jätkab sealt. Midagi erilist pole teha vaja lisaks tavalisele baasi käivitamisel, logisse kirjutatakse seejuures
TODO
Krahh PITR varundamise ajal
Kui enamusel juhtudel peale krahhi toimumist piisab andmebaas tavalisel moel käivitada ja ta jätkab tööd, siis PITR backupi (st pg_start_backup/pg_stop_backup) tegemise ajal toimunud krahhi puhul on andmeklastri kataloogis ees fail, mis segab käivitumist
/data/postgresql/backup_label
Samal ajal öeldakse logisse
2013-03-20 12:10:25 EET 15966 51498b11.3e5e 1 2013-03-20 12:10:25 EET 0 LOG: database system was interrupted; last known up at 2013-03-20 12:10:09 EET 2013-03-20 12:10:25 EET 15966 51498b11.3e5e 2 2013-03-20 12:10:25 EET 0 LOG: could not open file "pg_xlog/00000001000005E600000008" (log file 1510, segment 8): \ No such file or directory 2013-03-20 12:10:25 EET 15966 51498b11.3e5e 3 2013-03-20 12:10:25 EET 0 LOG: invalid checkpoint record 2013-03-20 12:10:25 EET 15966 51498b11.3e5e 4 2013-03-20 12:10:25 EET 0 PANIC: could not locate required checkpoint record 2013-03-20 12:10:25 EET 15966 51498b11.3e5e 5 2013-03-20 12:10:25 EET 0 HINT: If you are not restoring from a backup, try removing the file "/data/postgresql/backup_label". 2013-03-20 12:10:25 EET batchmanager portaal 127.0.0.1(50593) 15969 51498b11.3e61 1 2013-03-20 12:10:25 EET 0 FATAL: the database system is in recovery mode 2013-03-20 12:10:25 EET eit1 xportal 10.100.6.75(55188) 15970 51498b11.3e62 1 2013-03-20 12:10:25 EET 0 FATAL: the database system is in recovery mode 2013-03-20 12:10:25 EET 21687 513baab9.54b7 37 2013-03-09 23:33:45 EET 0 LOG: startup process (PID 15966) was terminated by signal 6: Aborted 2013-03-20 12:10:25 EET 21687 513baab9.54b7 38 2013-03-09 23:33:45 EET 0 LOG: aborting startup due to startup process failure
kus
- on näha, et baasi käivitamisega on probleem
- HINT annab teada, mida teha, tuleb eemaldada pg_start_backup() ütlemisega tekitatud fail backup_label
$ rm /data/postgresql/backup_label
Seejärel võib baasi tavapärasel viisil edasi kasutada
Krahhi 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 relname, age(relfrozenxid) FROM pg_class WHERE relkind = 'r'; 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
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
Vaikimisi toimub andmebaasi kasutaja autentimine selliselt, et
- baasi saab üle võrgu sisse logida baasis kirjeldatud kasutaja oma baasis kirjeldatud parooliga
- baasi saab lokaalselt sisse logida kasutaja, kelle unix kasutajanimi klapib baasis kirjeldatud kasutajanimega
Kasutaja autentimine /etc/passwd abil
Andmebaasi kasutaja autentimiseks nö süsteemi lokaalse kasutajana sobib kasutada pg_hba.conf failis nt rida
host all all 0.0.0.0/0 pam
ning muuta /etc/shadow faili loabit, seejuures tuleb ise hinnata, kas see muudatus on aktsepteeritav
# chmod o+r /etc/shadow
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"
- kasutada pg_hba.conf failis sarnast rida
# TYPE DATABASE USER CIDR-ADDRESS METHOD ... host all all 192.168.10.205/32 gss
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
Samuti toetab PgAdmin3 GSSAPI kasutamist.
Kasutaja autentimine PAM + LDAP kataloogiga
Eeldusel, et süsteemis on PAM LDAP backend kasutamine ettevalmistatud sobib PostgreSQL kasutajate LDAP kataloogi vastu autentimiseks
- PostgreSQL pg_hba.conf seadistusfailis kasutada nt rida
host all all 127.0.0.1/32 pam pamservice=postgresql
- /etc/pam.d/postgresql failis kasutada ridu (pamservice parameeter näitab PAM teenuse nime)
auth sufficient pam_ldap.so account sufficient pam_ldap.so
Oluline on tähele panna, et antud asjakorralduse puhul peab PostgreSQL andmebaasis olema kõnealune kasutaja olemas ja tema privileegid kirjeldatud, ainult autentimine toimub PAM+LDAP abil.
Kasutaja autentimine Active Directory vastu
TODO
Andmebaasi tuunimine
Aadressil http://wiki.postgresql.org/wiki/Performance_Optimization on toodud mitmeid viiteid tekstidele, kuidas admebaasi seadistusfaili sobivate seadistuste valimisel suurendada andmebaasi jõudlust.
- shared_buffers - kui arvutis ei tööta muid olulisi protsesse peale andmebaasi, siis võiks selle parameetri väärtus olla 1/4 operatsioonisüsteemi mälust (RAM); seejuures tuleb tõenäoliselt suurendada ka kernel.shmmax parameetri väärtust, nt pooleli ram'ist, nt kui ram on 4g, siis sobib öelda
# sysctl -w kernel.shmmax=2147483648
Tavaliselt on kernel.shmall väärtus piisavalt suur, see väljendab kogu jagatud mälu suurust süsteemis page ühikutes (4 kB x86 raual); nt 2097152 = 8G
PostgeSQL enda manuaalis on vastavad juhised aadressil http://www.postgresql.org/docs/8.3/interactive/kernel-resources.html.
- max_connections = 750
- superuser_reserved_connections = 8
- work_mem -
- maintainance_work_mem -
- effective_cache_size - pool arvuti mälu suurusest
shmall ja shmmax arvutamiseks sobib nt selline skript, http://www.postgresql.org/message-id/4D3B1F75.8040405@2ndquadrant.com
page_size=`getconf PAGE_SIZE` phys_pages=`getconf _PHYS_PAGES` if [ -z "$page_size" ]; then echo Error: cannot determine page size exit 1 fi if [ -z "$phys_pages" ]; then echo Error: cannot determine number of memory pages exit 2 fi shmall=`expr $phys_pages / 2` shmmax=`expr $shmall \* $page_size` echo \# Maximum shared segment size in bytes echo kernel.shmmax = $shmmax echo \# Maximum number of shared memory segments in pages echo kernel.shmall = $shmall
256 GB mäluga arvutis annab ta sellise tulemuse
# sh shmsetup # Maximum shared segment size in bytes kernel.shmmax = 135512178688 # Maximum number of shared memory segments in pages kernel.shmall = 33084028
Debian Wheezy, Ubuntu 12.04 jt keskkondade ja PGDG andmebaasi puhul paigutatakse need parameetrid /etc/sysctl.d/30-postgresql-shm.conf seadistusfaili.
Kui work_mem väärtus on liig väike, siis kasutatakse tmp faile, nende kasutamisest aitab aimu saada log_temp_files parameeter
#log_temp_files = -1 # log temporary files equal or larger
# than the specified size in kilobytes;
# -1 disables, 0 logs all temp files
Kasulikud lisamaterjalid
Logimine
- Andmebaasi tehtud ühenduste logimiseks sobib kasutada ridu
log_connections = on log_disconnections = on
- kasutaja päringute logimiseks
SQL> ALTER USER gateway SET log_statement = 'all';
kuna see täidab tõenäoliselt hästi failisüsteemi, tuleb peale kasutamist logimine uuesti välja lülitada
SQL> ALTER USER gateway SET log_statement = 'none';
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;
- kasutaja search_path seadmine
SQL> dvk=# ALTER USER dkk_admin SET search_path = dkk, public;
- 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".
- Kõigile baasi tabelitele, mis võivad asuda erinevates skeemides grantide ütlemine
$ psql -h 127.0.0.1 -U kasutaja baas -c '\dt' | egrep "data|public|wizard|live" | awk '{ print $1 "." $3}' > baas.tabelid
$ for i in `cat baas.tabelid`; do psql -h 127.0.0.1 -U kasutaja baas -c "GRANT SELECT ON TABLE $i TO grupinimi;"; done
- sequence viimase väärtuse küsimune, last_value asemele võib kirjutada ka *
baasinimi=# select last_value from skeeminimi.sq_tabelinimi_id; last_value ------------ 1009492 (1 row)
- Kui ühenduste arv (max_connections) on täis, siis logitakse
2016-07-22 13:49:35 EEST rakendus rakendus 10.100.7.168(38576) 12756 startup 5791fa3f.31d4 startup 1 \ 2016-07-22 13:49:35 EEST 0 FATAL: remaining connection slots are reserved for non-replication superuser connections
Ligipääsude massiline haldamine
Skeemid
Tõenäoliselt saab nii nö päris schemade nimekirja
SQL> select * from information_schema.schemata where schema_owner != 'postgres';
Kõigile skeemidele portaal_ro non-login rollile USAGE privileegi andmine
SQL> select 'GRANT USAGE ON ' || schema_name || ' TO portaal_ro;' from information_schema.schemata where schema_owner != 'postgres';
Tabelid
TODO
20171017 täiendus
Skeemi public kõigile tabelitele lugemise ligipääsu andmine
SQL> create role wiki_ro; SQL> \c baasinimi SQL> GRANT SELECT ON ALL TABLES IN SCHEMA public TO wiki_ro; SQL> GRANT SELECT ON ALL SEQUENCES IN SCHEMA public TO wiki_ro; SQL> create role wiki_bi login; SQL> grant wiki_ro to wiki_bi;
Kasulikud lisamaterjalid
PostgreSQL SSL toe kasutamine
TODO
Extensionite kasutamine
Nimekirja küsimine
sql> select * from pg_available_extensions order by name;
pg_buffercache
pg_buffefrcache on contrib moodul, kasutamiseks tuleb laadida baasi abivahendid
$ psql -p 5432 -U postgres -d pgbench -f /usr/share/postgresql/9.0/contrib/pg_buffercache.sql
Baasi kustutamine
Reeglina ei saa kustuta create database andmebaasi kui seal on kasutajad küljes, selle vastu võiks aidata selline skript
# cat kustuta-test-baas.sh /bin/su - postgres -c "psql -U postgres -c \"update pg_database set datallowconn = 'false' where datname = 'test'; \"" /bin/su - postgres -c "psql -U postgres -c \"select pg_terminate_backend(procpid) from pg_stat_activity where datname = 'test'; \"" dropdb -U testija test 1>/dev/null
pg_dump skriptiga töötamine
Tabelinimede küsimine
$ sed '/^CREATE TABLE/!d' baas.sql
Esitada tabeli loomised ridade numbritega
$ sed -n '/^CREATE TABLE/{=;p}' baas.sql
COPY vahemiku otsimine
sed -n '/^COPY/{=;p}' baas.sql > vahemik.sql
Ridade vahemiku esitamine
$ sed -n '15994,16041p' baas.sql > vahemik.sql
Rea eraldamine failist
$ grep ^622087 vahemik.sql > baas-tabel-copy-622087.sql
Märkused
- Baasis parasjagu toimuvate tegevuste nimekirja esitamine
postgres=# select datname,usename,procpid,client_addr,waiting,query_start,current_query FROM pg_stat_activity;
datname | usename | procpid | client_addr | waiting | query_start | current_query
----------+----------+---------+---------------+---------+-------------------------------+------------------------------------------------------------------------------------------------------
postgres | postgres | 2424 | | f | 2013-03-08 10:37:24.551676+02 | select datname,usename,procpid,client_addr,waiting,query_start,current_query FROM pg_stat_activity;
prtaal | postgres | 1673 | 172.19.10.142 | t | 2013-03-08 09:49:11.287969+02 | SELECT count(*) AS rows FROM ONLY pw.logi
prtaal | postgres | 21303 | | f | 2013-03-08 09:22:40.516297+02 | ALTER TABLE ONLY logi
: ADD CONSTRAINT logi_pkey PRIMARY KEY (log_id);
- Suuremate tabelite nimekirja esitamine, koos vastavate indexite ja toastidega
SELECT nspname || '.' || relname AS "relation",
pg_size_pretty(pg_total_relation_size(C.oid)) AS "size"
FROM pg_class C
LEFT JOIN pg_namespace N ON (N.oid = C.relnamespace)
WHERE nspname NOT IN ('pg_catalog', 'information_schema') and relkind='r'
ORDER BY pg_total_relation_size(C.oid) DESC
LIMIT 20;
- pg_controldata programmiga saab küsida infot andmeklustri kohta, protsessid võiksid sel ajal pigem mitte töötada
$ /usr/lib/postgresql/9.2/bin/pg_controldata /var/lib/postgresql/9.2/main pg_control version number: 922 Catalog version number: 201204301 Database system identifier: 6004185537552842495 Database cluster state: in production pg_control last modified: Tue Oct 7 15:01:55 2014 Latest checkpoint location: 1CC/83B86EE0 Prior checkpoint location: 1CC/83AD1C30 Latest checkpoint's REDO location: 1CC/83B7B2C8 Latest checkpoint's TimeLineID: 2 Latest checkpoint's full_page_writes: on Latest checkpoint's NextXID: 0/160131529 Latest checkpoint's NextOID: 98092825 Latest checkpoint's NextMultiXactId: 13515 Latest checkpoint's NextMultiOffset: 27409 Latest checkpoint's oldestXID: 675 Latest checkpoint's oldestXID's DB: 1 Latest checkpoint's oldestActiveXID: 0 Time of latest checkpoint: Tue Oct 7 15:01:50 2014 ...
- defaultist erineva collate ja ctype väärtustega create database baasi tekitamine, eelduseks on, et kõik baasid on UTF-8 kodeeringus
postgres=# create database db_jira_c template = template0 lc_collate = 'C.UTF-8' lc_ctype='C.UTF-8';
tulemusena on
postgres=# \l
List of databases
Name | Owner | Encoding | Collate | Ctype | Access privileges
--------------+------------+----------+-------------+-------------+----------------------
..
db_jira | jira | UTF8 | et_EE.UTF-8 | et_EE.UTF-8 |
db_jira_c | postgres | UTF8 | C.UTF-8 | C.UTF-8 |
template0 | postgres | UTF8 | et_EE.UTF-8 | et_EE.UTF-8 | =c/postgres +
| | | | | postgres=CTc/postgres
template1 | postgres | UTF8 | et_EE.UTF-8 | et_EE.UTF-8 | =c/postgres +
| | | | | postgres=CTc/postgres
Rakendus kasutab andmebaasi
PostgreSQL andmebaasis vaikimis olemas oleva kasutaja nimi on postgres ja seal on olemas create database postgres. Neid kumbagi ei tohiks kasutada rakenduse andmete jaoks. Seoses rakendusega on andmebaasis juurutatud
- kasutaja rakendusenimi_admin - sellele kasutajale kuuluvad rakendusega seotud andmebaasi objektid (skeemid, tabelid, funktsioonid jne); kasutaja ei ole PostgreSQL superuser privileegiga
- kasutaja rakendusenimi_user - selle kasutajana pöördub baasi poole rakendus, kasutajale on antud grant lausete abil õigus kuhu vaja teha select, update jms; kasutaja ei ole PostgreSQL superuser privileegiga
- create database andmebaas rakenduse_baas - selle ja kõigi seal sisalduvate objektide omanik on rakendusenimi_admin, kusjuures rakendus ei tohiks kasutada public skeemi vaid spetsiifiliste nimedega skeeme
Selline tulemus tekib nt sellise järgnevuse tulemusena
- ühendutakse uude tühja PostgreSQL andmebaasi kasutajana postgres ning tekitatakse kaks mitte-superuser kasutajat
- ühendutakse uude PostgreSQL andmebaasi kasutajana postgres ning tekitatakse rakenduse jaoks create database rakenduse_baas näidates omaniku
sql> create database rakenduse_baas owner = rakendusenimi_admin;
- ühendutakse baasi kasutajana rakendusenimi_admin ja tekitatakse andmebaasiobjektid (skeemid, tabelid jne)
- ühendutakse baasi kasutajana rakendusenimi_admin ja lisatakse vajalikud grantid rakendusenimi_user jaoks
Oluline on seejuures, et andmebaasi rakenduse_baas süsteemseid objekte ei muudeta ära postgres kasutaja omandusest (nt create database rakendusenimi_baas skeemid information_schema või pg_catalog).
Kui selliselt moodustatud baasist (või mõnest osast, nt skeem, tabel) teha postgres või rakendusenimi_admin kasutajana dump, siis on seal automaatselt sees vajalikud alter ja grant laused. Kui mõnes teises PostgreSQL andmebaasiserveris on olemas kasutajad rakendusenimi_admin ja rakendusenimi_user, siis saab sinna mainitud dumpi laadida sisse postgres või rakendusenimi_admin kasutajana ning tekivad sobivate omanikega ja grantidega objektid.
LVM, NFS ja PostgreSQL kasutamine andmebaasi hooldustöös
Toetudes sellistele asjaoludele, saab kasutada kiireid ja mugavaid haldusprotseduure
- Debiani ja Ubuntu paketihalduse PostgreSQL sisaldab võimalust hästi hõlpsasti käivatada ühes arvutis st operatsioonisüsteemis mitut andmebaasi eksemplari (st postgres protsesside komplekti)
- LVM plokkseadme haldusvahendid
- NFS serveri ja kliendi võimalused
LVM kasutamine PostgreSQL andmebaasiga
Käesolevas punktis juhitakse tähelepanu kuidas PostgreSQL töös kasutada ära LVM võimalusi. Põhiliselt seisneb see LVM snapshot kasutamisel. Eelduseks on , et operatsioonisüsteemis on LVM tugi olemas (reeglina on see nii paratamatult) ning seda kasutatakse andmebaasi failisüsteem all, nt nii
# df -h Filesystem Size Used Avail Use% Mounted on .. /dev/mapper/pgdata-data_postgresql 158G 135G 16G 90% /var/lib/postgresql /dev/mapper/pgdata-data_backup 36G 25G 9.2G 73% /data/backup
LVM snapshot kasutamine annab võimaluse kasutada ajahetkel vastavat baasi seisu nö lühiajaliseks katseks (ajalise kestuse võimaluse määrab LVM snapshotile eraldatud mahu ressursi suurus; nb! kui näidatud maht saab täis jääb baas seisma ja enam andmetele vähemalt kergesti ligi ei pääse)
- peatada baasi protsessid
- ühendada lahti /var/lib/postgresql failisüsteem
- tekitada snapshot
# lvcreate -L 40G -s -n data_postgresql_20150326_tootanud /dev/pgdata/data_postgresql
- ühendada snapshot failisüsteem /var/lib/postgresql alla
# mount /dev/pgdata/data_postgresql_20150326_tootanud /var/lib/postgresql
- käitada baasi protsessid
Peale katse lõppu
- peatada protsessid
- ühendada snapshoti failisüsteem lahti
- kustutada snapshot
# lvremove /dev/pgdata/data_postgresql_20150326_tootanud
- ühedada algne failisüsteem külge
- käivitada protsessid
Lisaks on võimalus käivitada kaks PostgreSQL eksemplari paraleelselt, selleks tuleb tekida snapshot nagu kirjeldatud, käivitada tagasi originaal ning valmistada ette teise (st snapshotilt töötava) andmebaasi eksemplari seadistused
# cp -a /etc/postgresql/9.4/main /etc/postgresql/9.4/test
ning kohendada postgresql.conf seadistusfailis parameetreid
- data kataloog
- pid fail
- archive log välja lülitada
- logi kataloog
- port - nt 5433
- ressursikasutus üle vaadata (shared_buffers, max_connections)
nt selliselt
# diff /etc/postgresql/9.4/main/postgresql.conf /etc/postgresql/9.4/test/postgresql.conf < data_directory = '/var/lib/postgresql/9.4/main' --- > data_directory = '/var/lib/postgresql/9.4/test' < hba_file = '/etc/postgresql/9.4/main/pg_hba.conf' --- > hba_file = '/etc/postgresql/9.4/test/pg_hba.conf' < ident_file = '/etc/postgresql/9.4/main/pg_ident.conf' --- > ident_file = '/etc/postgresql/9.4/test/pg_ident.conf' < external_pid_file = '/var/run/postgresql/9.4-main.pid' --- > external_pid_file = '/var/run/postgresql/9.4-test.pid' < port = 5432 < max_connections = 100 --- > port = 5433 > max_connections = 30 < shared_buffers = 2GB --- > shared_buffers = 384MB < archive_mode = off --- > # archive_mode = off < stats_temp_directory = '/var/run/postgresql/9.4-main.pg_stat_tmp' --- > stats_temp_directory = '/var/run/postgresql/9.4-test.pg_stat_tmp'
ühendada külge sobivasse punkti failisüsteemis, nt
# mkdir /var/lib/postgresql/9.2/test # mount /dev/pgdata/data_postgresql_20150326_tootanud /var/lib/postgresql/9.2/test
ning käivitada teine eksemplar
# pg_ctlcluster 9.4 test start
Tulemusena on kaks eksemplari
# pg_lsclusters Ver Cluster Port Status Owner Data directory Log file 9.2 main 5432 online postgres /var/lib/postgresql/9.2/main /var/log/postgresql/postgresql-%Y-%m-%d_%H%M%S.log 9.2 test 5433 online postgres /var/lib/postgresql/9.2/test /var/log/postgresql-test/postgresql-%Y-%m-%d_%H%M%S.log
Kui LVM snapshot tehti töötavast andmeklastri kataloogist, siis saab käivitamisel sellise veateate
# pg_ctlcluster 9.2 test start Cluster is already running.
Teda segab selline fail, mis tuleb eemaldada
# cat /var/lib/postgresql/9.2/test/postmaster.pid 2025 /var/lib/postgresql/9.2/main 1473969248 5432 /var/run/postgresql
NFS kasutamine PostgreSQL andmebaasiga
PostgreSQL töötab põhimõtteliselt ilusti üle NFS külge ühendatud failisüsteemiga. Seejuures tuleb muidugi arvestada, et NFS ressursi jõudlus ei ole tingimata selliste omadustega nagu lokaalne või FC ressurss. NFS ja LVM kombineerimine eemaldab eelmises punktis kirjeldatud tegevustelt samas arvutis töötamise piirangu. St võimalik on LVM snapshotilt käivitada andmebaas teises arvutis. Selleks tuleb
- moodustada LVM snaphost
- snapshot failisüsteem ühendada külge nagu kirjeldatud eelmises punktis (NB! mitte käivitada lokaalselt sealt protsesse, vastasel juhul tekib konflikt kuna samu andmebaasi faile kasutatakse kahest arvutist sõltumatult)
- Seejärel tuleb jagada snapshoti failisüsteem välja NFS serveris (10.100.13.159 on NFS klient)
# cat /etc/exports /var/lib/postgresql/9.2/test 10.100.13.159(rw,sync,no_subtree_check)
ning öelda
# /etc/init.d/nfs-kernel-server reload
- kliendi arvutis külge ühendada (10.100.13.174 on NFS server)
# mount 10.100.13.174:/var/lib/postgresql/9.2/test /var/lib/postgresql/9.2/test
- edasi kasutada sarnaselt kui eelmises punktis teist eksemplari
LVM ja NFS kasutamine PostgreSQL andmebaasiga
Kahe eelmise punkti üks variantsioon on võimalus mugavalt läbi proovida andmebaasi versiooni uuendamist pg_upgrade utiliidi abil. St teises arvutis on siis olemas
- snapshotilt tulev vana baasi failisüsteem üle NFS
- peab olema vana baasi versioonile vastav tarkvara paigaldatud lokaalselt (nt v. 8.4)
- uue baasi versiooni tarkvara paigaldatud lokaalselt (nt v. 9.4)
Ja tulemusena peab saama öelda nö
$ /usr/lib/postgresql/9.4/bin/pg_upgrade -b /usr/lib/postgresql/8.4/bin -B /usr/lib/postgresql/9.4/bin -d /var/lib/postgresql/8.4/test \ -D /var/lib/postgresql/9.4/test -p 5432 -P 5433 -o ' -D /etc/postgresql/8.4/test' -O ' -D /etc/postgresql/9.4/test' -c
Andmebaasi vigade parandamine
XXX viga
Probleemiks on baasi dump käigus tekkiv viga
$ pg_dump -p 5435 -Fc -f baasinimi.dump baasinimi pg_dump: Dumping the contents of table "failid" failed: PQgetResult() failed. pg_dump: Error message from server: ERROR: unexpected chunk number 570 (expected 568) for toast value 241844880 in pg_toast_62025918 pg_dump: The command was: COPY public.failid (faili_id, yhistu_id, perioodi_id, arve_id, dokumendi_id, ...) TO stdout;
Tööpõhimõte
TODO
failid2 tabeli tekitamiseks sobib öelda (nb! ei tekitata constrainitisid jms)
baasinimi=# select * into failid2 from failid limit 1; baasinimi=# delete from failid2;
Vigaste kirjete leidmine
-- FUNCTION: public.imre10(integer, integer)
-- DROP FUNCTION public.imre10(integer, integer);
CREATE OR REPLACE FUNCTION public.imre10(
integer,
integer)
RETURNS character varying
LANGUAGE 'plpgsql'
COST 100
VOLATILE
AS $BODY$
DECLARE
badid INT;
vfaili_id integer;
vyhistu_id integer;
vperioodi_id integer;
varve_id integer;
vdokumendi_id integer;
...
loc varchar;
BEGIN
FOR badid IN SELECT faili_id FROM failid LOOP
BEGIN
select faili_id into vfaili_id FROM failid where faili_id = badid;
select yhistu_id into vyhistu_id FROM failid where faili_id = badid;
select perioodi_id into vperioodi_id FROM failid where faili_id = badid;
select arve_id into varve_id FROM failid where faili_id = badid;
select dokumendi_id into vdokumendi_id from failid where faili_id = badid;
...
insert into failid2 (faili_id, yhistu_id, arve_id, dokumendi_id, ...) values (vfaili_id, vyhistu_id, varve_id, vdokumendi_id, ...);
-- RAISE NOTICE 'Hello World %', badid::int;
EXCEPTION
WHEN OTHERS THEN
RAISE NOTICE 'Data for ID % is corrupt', badid;
CONTINUE;
END;
END LOOP;
return 'tere';
END;
$BODY$;
ALTER FUNCTION public.imre10(integer, integer)
OWNER TO imre;
käivitamiseks
baasinimi=# select imre10(1, 1); NOTICE: Data for ID 2594124 is corrupt NOTICE: Data for ID 2594125 is corrupt imre10 -------- tere (1 row)
Vigaste kirjete kustutamiseks
baasinimi=# select * from failid where faili_id = '2594124'; ERROR: unexpected chunk number 570 (expected 568) for toast value 241844880 in pg_toast_62025918 baasinimi=# select * from failid where faili_id = '2594125'; ERROR: unexpected chunk number 62 (expected 60) for toast value 241844882 in pg_toast_62025918 baasinimi=# delete from failid where faili_id = '2594124'; DELETE 1 baasinimi=# delete from failid where faili_id = '2594125'; DELETE 1
Kasulikud lisamaterjalid
Misc
postgres=# table pg_hba_file_rules;
line_number | type | database | user_name | address | netmask | auth_method | options | error
-------------+-------+---------------+------------+-----------+-----------------------------------------+-------------+---------+-------
85 | local | {all} | {postgres} | | | peer | |
90 | local | {all} | {all} | | | peer | |
92 | host | {all} | {all} | 127.0.0.1 | 255.255.255.255 | md5 | |
94 | host | {all} | {all} | 0.0.0.0 | 0.0.0.0 | md5 | |
97 | local | {replication} | {all} | | | peer | |
98 | host | {replication} | {all} | 127.0.0.1 | 255.255.255.255 | md5 | |
99 | host | {replication} | {all} | ::1 | ffff:ffff:ffff:ffff:ffff:ffff:ffff:ffff | md5 | |
(7 rows)
