Administració d'un SGBD

En els diferents nivells i aplicacions de bases de dades hi ha la funció de l’administrador de la base de dades (DBA), encara que varia en complexitat. Aquesta és més senzilla quan es tracta d’una base de dades personal que quan es refereix a una base de dades de grups de treball, i aquesta al seu torn és més senzilla que en una base de dades organitzacional.

En una base de dades personal normalment l’usuari mateix és l’administrador de la base de dades, però les bases de dades de grups de treball requereixen una o dues persones que normalment no es dediquen a aquesta funció de temps complet, ja que tenen altres responsabilitats dins o fora de l’organització. En les bases de dades organitzacionals, que normalment permeten l’accés a desenes i fins i tot centenars d’usuaris, es requereix un administrador de base de dades de temps complet, a causa de l’alt volum de processos que han de desenvolupar, controlar i supervisar.

Un administrador de base de dades de temps complet normalment té aptituds tècniques per al maneig del sistema en qüestió i a més és desitjable que tingui nocions d’administració, maneig de personal i fins i tot un cert grau de diplomàcia. La característica més important que ha de tenir és un coneixement profund de les polítiques i normes de l’empresa i el criteri de l’empresa per aplicar-les en un moment donat.

La responsabilitat general del DBA és facilitar el desenvolupament i l’ús de la base de dades dins de les guies d’acció definides per l’administració de les dades.

El DBA és responsable primordialment del següent:

• Administrar l’estructura de la base de dades
• Administrar l’activitat de les dades
• Administrar el sistema gestor de base de dades
• Establir el diccionari de dades
• Assegurar la fiabilitat de la base de dades
• Confirmar la seguretat de la base de dades.

L’administració de l’estructura de la base de dades inclou participar en el disseny inicial i la posada en pràctica i també controlar i administrar-ne els requisits, i ajudar a avaluar alternatives, incloent-hi els SGBD per utilitzar i ajudar en el disseny general de BD. En els casos de grans aplicacions de tipus organitzacional, el DBA és un gerent que supervisa el treball del personal de disseny de la BD.

Les principals tasques de l’administrador de la base de dades són les següents:

• Definició dels esquemes conceptual i lògic: és tasca de l’administrador de dades decidir amb exactitud quina és la informació que s’ha de mantenir en la base de dades, és a dir, identificar les entitats que interessen a l’empresa i la informació que s’ha de registrar sobre aquestes entitats. Aquest procés en general es denomina disseny conceptual de la base de dades. Quan l’administrador de dades decideix el contingut de la base de dades en un nivell abstracte, el DBA crea a continuació el disseny lògic corresponent, utilitzant el DDL lògic. L’SGBD utilitzarà la versió objecte (compilada) d’aquest esquema per respondre les sol·licituds d’accés. La versió font sense compilar servirà com a document de referència per als usuaris del sistema.

• Definició de l’esquema intern: el DBA ha de decidir també com es representarà la informació a la base de dades emmagatzemada. Aquest procés se sol anomenar disseny físic de la base de dades. Un cop fet això el DBA haurà de crear la definició d’estructura d’emmagatzematge corresponent (és a dir, l’esquema intern) valent-se del DDL intern. A més, haurà de definir la correspondència pertinent entre els esquemes intern i conceptual. En la pràctica, ja sigui el DDL lògic o bé el DDL intern inclouen segurament els mitjans per definir aquesta correspondència, però les dues funcions (crear l’esquema i definir la correspondència) s’han de poder separar amb nitidesa. Igual que l’esquema lògic, l’esquema intern i la correspondència associada existiran tant en la versió font com en la versió objecte.

Una vegada dissenyada la BD, és posada en pràctica utilitzant productes de l’SGBD, i es procedeix llavors a la creació de les dades (captura inicial). El DBA participa en el desenvolupament de procediments i controls per assegurar la qualitat i l’alta integritat de la BD.

El DBA s’ha d’encarregar de la comunicació amb els usuaris, garantir la disponibilitat de les dades que requereixen i d’escriure –o ajudar els usuaris a escriure– els esquemes externs necessaris, emprant el DDL extern aplicable. A més, caldrà definir la correspondència entre qualsevol esquema extern i l’esquema conceptual. En la pràctica, el DDL extern inclourà amb tota probabilitat els mitjans per especificar la correspondència, però en aquest cas també l’esquema i la correspondència s’hauran de poder separar amb claredat. Cada esquema extern i la correspondència associada existiran en totes dues versions font i objecte. Altres aspectes de la funció d’enllaç amb els usuaris inclouen les consultes sobre disseny d’aplicacions, la interpretació de la documentació tècnica, l’ajuda en la localització i resolució de problemes, i altres serveis professionals similars relacionats amb el sistema.

Els requisits dels usuaris es van modificant, ja que aquests troben noves formes o mètodes per assolir els seus objectius. Per altra banda, la tecnologia de la BD es va modificant i els fabricants de l’SGBD actualitzen els seus productes. Això implica que totes les modificacions en les estructures o procediments de BD requereixen una administració acurada.

Les sol·licituds de modificació són inevitables una vegada que el sistema ha entrat en operació, poden aparèixer sol·licituds de nous requisits o poden resultar d’una comprensió inadequada d’aquests. En qualsevol cas, s’han d’efectuar modificacions en relació amb tota la comunitat de la BD, ja que l’impacte d’aquestes alteracions ressentirà més d’una aplicació. En alguns casos, es poden donar modificacions que presenten efectes negatius per a alguns usuaris, i aquests casos hauran de ser tractats esgrimint com a argument els beneficis globals que seran obtinguts d’aquestes alteracions.

Una administració eficaç de la BD ha d’incloure procediments i polítiques mitjançant les quals els usuaris puguin registrar les seves necessitats de modificacions, i així la comunitat podrà analitzar i discutir els impactes d’aquestes modificacions, i determinar llavors la posada o no en pràctica d’aquestes alteracions.

En raó de la mida i complexitat d’una BD i de les seves aplicacions, les modificacions poden tenir resultats inesperats. El DBA ha d’estar preparat per reparar la BD i reunir suficient informació per diagnosticar i corregir el problema provocat per la falla. Després d’un canvi la BD és més vulnerable a fallides.

La responsabilitat final d’un DBA en l’administració de l’estructura d’una BD és la documentació. És molt important saber quines modificacions han estat efectuades, com van ser fetes i quan van ser establertes. Una modificació sobre l’estructura de la BD pot ocasionar un error que no aparegui a curt termini, i una vegada que aquest surti, sense la documentació adequada sobre les modificacions fetes, el diagnòstic resultaria extremadament complicat. En aquests casos, es faria necessària una seqüència de reexecucions per intentar detectar el punt en conflicte, i el risc d’aquest procediment és que és possible afectar la informació continguda a la BD. Per identificar un canvi és molt important mantenir un registre dels formats de prova i de les execucions de les proves efectuades. Si s’utilitzen procediments de prova, formats de proves i mètodes de registre estandarditzats, el registre dels resultats de la prova no consumirà un temps excessiu.

Normalment el temps de la documentació és tediós i això fa que alguns DBA tendeixen a reduir o abreujar la informació que s’hi registra i fins i tot l’arriben a desatendre. Quan ocorre un sinistre, la documentació completa i organitzada pot ser la diferència entre resoldre o no un problema d’extrema importància en la majoria dels casos, i pot implicar costos molt grans a l’empresa.

La tasca de la documentació és cada vegada més lleugera i precisa quan s’utilitzen SGBD que integren eines CASE per a les tasques de disseny, manteniment i documentació. Aquestes eines CASE mateixes proporcionen en la majoria dels casos la facilitat de generar i mantenir de manera automàtica el diccionari de dades.

Una raó més per documentar consisteix en la necessitat de mantenir organitzades les dades històriques. Passa sovint que es vol fer una consulta sobre els suports per conèixer l’estat que tenia la informació en un període determinat que va transcórrer prèviament. Els registres de modificació existents en la documentació permetran resoldre problemes d’incompatibilitat entre les estructures que eren vigents en el període de suport i les que ho són ara, i permetran també el desenvolupament de mòduls d’ajust que facilitin la traducció de formats o escales per d’emmagatzematge.

En els casos de caigudes del sistema es presenta una situació semblant: les còpies de seguretat són requerides i s’ha de verificar l’estructura, format i escala per integrar-los en l’operació del sistema.

Encara que el DBA protegeix les dades, no les processa. El DBA no és usuari del sistema; en conseqüència, no administra valors de dades, sinó que administra l’activitat de les dades. Atès que la BD és un recurs compartit, el DBA ha de proporcionar estàndards, guies d’acció, procediments de control i la documentació necessària per garantir que els usuaris treballen de manera cooperativa i complementària en processar dades a la BD.

Com es pot suposar, hi ha una gran activitat a l’interior d’un SGBD. La concurrència de múltiples usuaris requereix estandarditzar els processos d’operació, el DBA és responsable d’aquestes especificacions i d’assegurar que aquestes arribin als qui en són afectats. Tot l’àmbit de la BD es regeix per estàndards, des de la manera com es captura la informació (tipus, longitud, format), fins a com és processada i presentada. El nivell d’estandardització arriba fins als aspectes més interns de la BD, com s’accedeix a un arxiu, com es determinen els índexs primaris i auxiliars, la foliació dels registres i d’altres.

S’ha de procurar sempre que els estàndards que s’han d’aplicar beneficiïn també els usuaris, privilegiant sempre l’optimització en l’operació de l’SGBD i l’afecció de les polítiques de l’empresa.

Una administració de BD efectiva ha de disposar sempre d’aquest tipus d’estàndards; entre les funcions del DBA es troba la de revisar-ho periòdicament per determinar-ne l’operativitat, i si escau ajustar-los, ampliar-los o cancel·lar-los. És també la seva responsabilitat que aquests es compleixin.

Quan es defineixen estàndards sobre l’estructura de la BD, aquests s’han de registrar en una secció del diccionari de dades a la qual tots aquells usuaris relacionats amb aquest tipus de procés poden accedir.

Un altre dels aspectes que l’administrador ha d’atendre és el de coordinar totes aquelles noves propostes de modificació dels drets d’accés a dades compartides i les propostes corresponents a noves aplicacions. Primerament, caldria que aquestes propostes fossin analitzades en conjunt amb els supervisors o directius de les àrees involucrades per determinar si cal fer aquestes modificacions, no fos cas que poguessin aparèixer problemes quan dos o més grups d’usuaris queden autoritzats per notificar les mateixes dades.

Un d’aquests conflictes és el de l’actualització perduda: es dóna quan el treball d’un usuari queda sobreescrit pel d’un segon usuari. El DBA queda responsabilitzat d’identificar la possible ocurrència d’aquests problemes, i també de crear normes i procediments per impedir-los. S’obtindran aquest tipus de garanties quan l’SGBD sigui capaç d’implementar les restriccions aplicables a l’accés concurrent, i aquest sigui utilitzat adequadament per programadors i usuaris, per evitar el que hem esmentat anteriorment. Per això es fa indispensable ajustar-se als estàndards corresponents al seguiment d’instructius i documentar mitjançant manuals totes aquells regles establertes per als diversos processaments i procediments que es duen a terme.

Entre les alternatives més utilitzades pel DBA per intentar resoldre o minimitzar aquest problema es troben les següents:

• Restringir l’accés als procediments per a certs usuaris.
• Restringir l’accés a les dades per a certs usuaris, procediments o dades.
• Evitar la coincidència d’horaris per a usuaris que comparteixen les mateixes dades.

Les tècniques de recuperació són una altra funció essencial del DBA en administrar l’activitat de dades.

Tot i que el SGBD porta a terme una part del procés de recuperació, els usuaris determinen de manera crítica l’operativitat d’aquests sistemes de protecció. El DBA ha d’anticipar fallides i definir procediments estàndard d’operació, els usuaris han de saber què han de fer quan el sistema està caigut i què és el primer que s’ha de fer quan el sistema està en marxa novament. El personal d’operació haurà de saber com iniciar el procés de recuperació de la BD, quines còpies de seguretat cal utilitzar, com cal programar la reexecució del temps perdut i de les tasques pendents. És important també establir un calendari per dur a terme aquestes activitats sense afectar altres sistemes dins de l’organització que facin ús dels mateixos recursos de còmput.

Destaquen per la seva importància en el procés de recuperació i al seu torn en l’atenció que presten a altres sectors de l’organització: els dispositius de comunicació remota, els sistemes d’interconnexió i altres accessoris d’ús compartit.

Els productes SGBD més populars que es troben en el mercat proporcionen eines per ajudar el DBA en l’administració de les dades i la seva activitat. Alguns sistemes registren automàticament els noms dels usuaris i de les aplicacions a les quals tenen accés i també altres objectes de la BD. Incorporen també utilitats que permetin definir en el diccionari de dades les restriccions perquè determinades aplicacions o mòduls d’aquestes només tinguin accés a segments específics de la BD.

A més d’administrar l’activitat de dades i l’estructura de la BD, el DBA ha d’administrar l’SGBD mateix. Haurà de compilar i analitzar estadístiques relatives al rendiment del sistema i identificar àrees potencials del problema. Atès que la BD està servint molts grups d’usuaris, el DBA ha d’investigar totes les queixes sobre el temps de resposta del sistema, la precisió de les dades i la facilitat d’ús. Si es requereixen canvis el DBA se’ls ha de planejar i posar-los en pràctica.

El DBA haurà de vigilar periòdicament i contínuament les activitats dels usuaris a la BD. Els productes SGBD inclouen tecnologies que reuneixen i publiquen estadístiques. Aquests informes poden indicar quins van ser els usuaris actius, quins arxius i quins elements de dades han estat utilitzats, i fins i tot el mètode d’accés que s’ha aplicat. Poden capturar i reportar les taxes d’error i els tipus d’errors. El DBA analitzarà aquestes dades per determinar si cal una modificació en el disseny de la BD per gestionar-ne el rendiment o per facilitar les tasques dels usuaris; en aquest cas, el DBA la durà a terme.

El DBA haurà d’analitzar les estadístiques de temps d’execució sobre l’activitat de la BD i el seu rendiment. Quan s’identifiqui un problema de rendiment, ja sigui mitjançant una queixa o un informe, el DBA ha de determinar si resulta apropiada una modificació en l’estructura de la BD o en el sistema.

Quan el fabricant de l’SGBD anunciï una nova versió del producte, s’ha de fer una anàlisi de les característiques que incorpora i analitzar-les segons les necessitats de la comunitat d’usuaris. Si es decideix l’adquisició del producte, els usuaris n’han de ser notificats, i també capacitats per utilitzar-lo. El DBA haurà d’administrar i controlar la migració, tant de les estructures, com de les dades i les aplicacions.

El programari de suport i altres característiques de maquinari poden implicar també modificacions de les quals el DBA és responsable ocasionalment; aquestes modificacions tenen com a conseqüència canvis en la configuració o en alguns paràmetres d’operació de l’SGBD.

Les opcions de l’SGBD són ajustades al principi, és a dir, en la posada en marxa del sistema es coneix molt poca informació sobre les característiques de funcionament i resposta que proporcionarà als grups d’usuaris. L’anàlisi de l’experiència operacional i el seu rendiment en un període determinat de temps poden revelar que es requereix un camp. Si el rendiment sembla acceptable, el DBA pot considerar modificar algunes opcions i observar-ne l’efecte sobre el sistema, tot buscant optimitzar-lo o afinar-lo.

Quan una empresa es decideix a utilitzar un sistema de base de dades, es torna dependent en grau màxim del funcionament correcte d’aquest sistema. En el cas que pateixi un dany qualsevol porció de la base de dades per causa d’un error humà o una fallida en l’equip o en el sistema que li dóna suport, és essencial poder reparar les dades implicades amb un mínim de retard i afectant el menys possible la resta del sistema. En teoria, per exemple la disponibilitat de les dades no danyades no s’hauria de veure afectada. El DBA ha de definir i posar en pràctica un pla de recuperació adequat que inclogui, per exemple una descàrrega o buidatge periòdic de la base de dades en un medi d’emmagatzematge de suport, i procediments per carregar una altra vegada la base de dades a partir del buidatge més recent quan sigui necessari.

Les coses es trenquen, és un fet. Quan tot és nou i ho acabem d’instal·lar tot rutlla segons el que està establert, però amb el temps es va afegint nou programari al sistema, noves aplicacions, nou maquinari, etc. Una de les feines més importants dels DBA és reaccionar davant els possibles errors que es produeixin en el nostre sistema. Aquests es poden classificar en tres grans grups:

1. Problemes al sistema. Inclouen problemes del sistema operatiu sobre el qual està instal·lat l’SGBD, problemes interns de l’SGBD o problemes relacionats amb algun programari.
2. Problemes d’aplicació o transaccions. Es donen quan hi ha una execució de programes o procediments fora d’un temps establert o les entrades del programa no són les correctes. Aquestes fallades poden crear dades incorrectes que haurem de detectar i corregir.
3. Problemes de maquinari. Aquest tipus de problemes vénen donats quan fallen els medis on desem les dades (disc dur, cintes, etc.). Aquests problemes cada vegada són menys freqüents i, si es donen, s’autocorregeixen si utilitzem mesures de seguretat com el RAID.

És feina del DBA establir una planificació per tal de fer les còpies de seguretat de les dades adequades en el moment adequat.

Per saber amb quina freqüència hem de fer una còpia de desament de dades ens fixarem amb quant de temps és necessari per tal de restaurar-les, i per fer això ens fixarem en els factors següents:

• El nombre de registres que s’han de restaurar.
• El temps que es triga a muntar i desmuntar les cintes pertinents.
• El temps que es triga a processar els canvis dels registres una vegada han estat restaurats.

A més de fer això hem de tenir clar quan hem de fer les còpies de seguretat i en quina quantitat. Tot seguit teniu unes possibles i més que recomanables pautes a seguir en aquest procés:

• Fer, com a mínim, dues còpies per evitar les possibles errades del medi on s’emmagatzemen les còpies.
• Fer còpies locals i en la mesura que es pugui fer-les en medis fora del servidor (altres servidors, altres medis).
• Conservar dues rondes de còpies sempre. És a dir, si fem una còpia integral del sistema cada dilluns, mantenir les còpies dels dos dilluns anteriors.
• Ens hem d’assegurar d’incloure el catàleg del sistema, ja que si ha canviat el DDL (data definition language, llenguatge de definició de dades) tindríem error fins i tot havent restaurat el sistema.
• Comprovar, una vegada acabat el procés de desament, que la còpia és correcta.

Lògicament, les còpies incrementals ocupen menys espai físic i tarden menys a fer-se.

Hem de fer una valoració de quin és el millor tipus de còpia de seguretat que podem utilitzar a cada moment o sistema gestor. Per tant, és imprescindible que, com a DBA, sapiguem quina quantitat de registres hem de desar i aproximadament quant de temps es triga a fer una còpia integral i incremental.

Hem de tenir present que el temps que ens estalviem fent còpies de seguretat incremental només és útil si el nombre de registres que canvien entre còpies de seguretat és reduït.

És aconsellable fer còpies de seguretat utilitzant les eines que proporciona el mateix SGBD. Tot i així, hi ha diverses formes de fer còpies de seguretat alternatives, que només s’han d’utilitzar en moments molt concrets.

Fins ara hem estat parlant de còpies de tot el sistema, però de vegades aquest tipus de còpies de seguretat més que ajudar en la restauració ens poden donar problemes segons les dades que s’hagin perdut.

Veurem ara una forma de fer una còpia de seguretat lògica (ens fixarem en les dades emmagatzemades, no tant en el tipus de dades). La instrucció EXPORT o UNLOAD, segons l’SGBD, ens permetrà fer un bolcat de totes les dades indicades i així tenir una còpia lògica de les dades. Veiem en quins casos ens pot arribar a ser útil:

• Restauració d’una fila o objecte en especial. Si per error algú esborra només unes quantes files o dades d’una taula, pot arribar a ser molt complicat només restaurar aquesta part des d’una còpia de seguretat física.
• Actualització de l’SGBD. Si ens trobem davant una actualització del sistema gestor i no volem convertir totes les estructures, també pot ser útil fer una introducció massiva de les dades des d’una còpia lògica.
• Migració de dades entre SGBD heterogenis. Si volem transportar dades d’un sistema gestor a un altre que disposa d’estructures físiques ben diferenciades, també optarem per restaurar una còpia lògica al sistema destinatari de les dades.
• Moviment de dades. Per traslladar les dades entre aplicacions o fer-ho per exemple en fitxers de text, seria l’única manera d’aconseguir-ho.

Com que la còpia de seguretat lògica es fa amb el sistema en funcionament, l’únic problema de rendiment pot venir donat per l’accés concurrent a les dades.

La restauració de dades no és una tasca senzilla. És més que dur a terme una restauració d’una imatge prèviament feta. Hem de tractar de deixar el sistema tal com requereixi l’aplicació en qüestió, incorporant-hi tots els possibles canvis que hagin succeït a les bases de dades des del moment de la còpia de seguretat fins al moment exacte que nosaltres volem.

El primer que hem de fer davant una fallada del sistema és tenir ben present de quins recursos disposem per fer la restauració, és a dir, de quines còpies disposem i on es troben. El pas següent és descobrir exactament què ha produït la fallada del sistema. Hi ha una sèrie de preguntes que ens poden ajudar a enfocar els nostres esforços:

• Com s’ha apagat el sistema gestor? De manera fortuïta o per la nostra ordre?
• Té a veure amb el sistema operatiu?
• S’ha reiniciat el servidor?
• Hi ha errors en els fitxers de LOG del sistema operatiu o del servidor de bases de dades?
• Quina és la importància de les dades que s’han perdut?
• S’ha intentat algun tipus de restauració? Si és així, en quin punt ens trobem de la restauració?
• Què es necessita restaurar? La base de dades, una taula o un altre element?
• Tenim còpies lògiques recents?
• Es pot accedir als objectes de la base de dades?

Una vegada disposem de les respostes, estarem preparats per poder iniciar la restauració de les dades perdudes. Els passos que cal seguir per fer-la són els següents:

1. Identificar l’error que provoca la fallada del sistema: les aplicacions o els usuaris han de proporcionar el tipus d’error que els indica l’aplicació que accedeix a les dades.
2. Analitzar la situació: esbrinar fins on arriba exactament l’error i quines parts s’han vist realment compromeses.
3. Determinar què s’ha de restaurar exactament: mirar quines dades necessiten una restauració (pot portar molt de temps fer-ho).
4. Identificar dependències entre objectes de la base de dades: possibles restauracions de certs objectes poden comportar restaurar-ne altres com a efecte col·lateral.
5. Localitzar les còpies que s’han d’utilitzar i veure si són operatives: localitzar els dispositius i comprovar que s’hi pot accedir (no falla el medi d’emmagatzematge).
6. Restaurar les còpies de seguretat.
7. Retornar el sistema al punt exacte de funcionament desitjat: utilitzar els fitxers de moviment per deixar el sistema exactament al moment que necessitem.

El primer tipus de restauració que veurem és, potser, un dels més comuns. Es tracta de la recuperació fins al moment on va fallar. Se sol donar a causa de problemes amb els medis d’emmagatzematge (discos durs per exemple).

Es necessita una còpia integral del sistema per començar la restauració, ja que serà el punt de partida. Una vegada restaurada aquesta imatge, hem d’utilitzar els fitxers LOG de moviments per deixar el sistema en el mateix estat que estava abans de la fallada. Si per qualsevol motiu no trobem una còpia integral actual, hem de recórrer a una còpia antiga i anar restaurant també les còpies incrementals successives fins a arribar al punt on podríem utilitzar els fitxers LOG de moviments.

Un altre tipus de restauració és aquell en què portem les bases de dades a un estat determinat en el temps. Per exemple, si sabem que el nostre sistema va fallar a les 13.43 del migdia, intentarem portar el nostre sistema fins a com estava a les 13.42, sigui l’hora que sigui. Aquest tipus de restauració se sol fer només per a una part del sistema gestor. Generalment no afecta tot el conjunt de dades del sistema. Hem de tenir clar com actuarem amb les transaccions que s’han efectuat al sistema des de la fallada fins al moment de fer la restauració. Es tracta, doncs, de fer una restauració del sistema aplicant els canvis correctes i rebutjant els canvis que no interessen i que han produït aquest error del sistema.

Hi ha dues maneres de fer aquest tipus de restauració, i l’aproximació per la qual optem vindrà donada pels mecanismes de què disposi el nostre SGBD i la quantitat de dades que s’han de restaurar. Podrem optar, doncs, per:

• Restaurar una còpia i aplicar els canvis que ens interessin dels fitxers LOG fins a arribar al punt que vulguem.
• No restaurar cap còpia. Desfarem els canvis erronis fixant-nos en els fitxers LOG fins a deixar el sistema en un punt consistent.

Si podem optar per totes dues solucions, escollirem sempre la que faci que el sistema estigui aturat menys temps.

Per finalitzar la tipificació de les possibles restauracions que ens podem trobar, ens queda per veure la restauració de transaccions. Les transaccions vénen definides per les aplicacions dels usuaris. Tenint en compte aquest fet, nosaltres, com a DBA, no podrem fer gaire cosa, ja que serà necessària l’ajuda d’un programari extern per reparar els possibles errors que s’hi hagin produït. Aquests errors sempre estan relacionats amb la pèrdua de certa informació important.

Diem que cal una aplicació externa a l’SGBD per fer la reparació perquè només l’usuari sap què esperava fer en la transacció i com ha quedat la informació de la transacció. Haurà de tornar enrere desfent totes les modificacions que ha fet i tornar a intentar l’operació una altra vegada. Per tirar enrere “sobre els seus propis passos”, ho podrà fer amb les ordres REDO i UNDO.

Hi ha altres maneres de protegir l’SGBD a part de les còpies de seguretat. Vegem-ne unes possibles alternatives que, per descomptat, no ens eximeixen en cap cas de tenir còpies de seguretat:

• Replicació. Es tracta de mantenir unes còpies redundants exactes de les bases de dades en discos i suports diferents. Com més còpies redundants tinguem més segurs estarem, davant possibles fallades del sistema, que podrem restaurar-lo en poc temps.
• Discos mirall. Es tracta d’aconseguir en el mercat el maquinari que proporciona aquest tipus de tecnologia, en què es mantenen diversos discos amb la mateixa informació replicada de maneres diferents (RAID) i amb una certa seguretat de dades incorporada (CRC). Aquest tipus de seguretat ens estalviaria la recuperació necessària en tenir una fallada d’algun dels discos.

Els fitxers en què el PostgreSQL emmagatzema la BD són fitxers físics en disc des del punt de vista del sistema operatiu, i per això a mesura que s’incorpora informació, aquests van augmentant de mida.

Juntament amb la informació útil que es desa en els fitxers de la BD, s’emmagatzemen dades relatives a l’ordre d’aquesta, al tipus de dades, i altres necessàries per a l’accés i el funcionament correcte de l’SGBD. Això fa que de manera estimada, els fitxers esmentats ocupin almenys el doble d’espai que les dades que emmagatzemen.

Per assegurar la durabilitat de totes aquestes dades, cal que l’administrador de la BD faci còpies de seguretat periòdiques, ja que malgrat que el PostgreSQL és molt estable, i fins i tot els discos durs tinguin redundància, res no permetria recuperar les dades en cas de robatori físic de l’equip, d’incendi, o d’altres accidents.

Les còpies de seguretat “totals” es poden fer d’almenys tres maneres:

• Còpia dels fitxers de la BD en fred. Per fer una còpia en fred, s’ha d’aturar l’SGBD i desar els fitxers on PostgreSQL emmagatzema les taules i la resta d’informació. Aquest mètode és poc recomanable, ja que implica no poder utilitzar el sistema durant el temps en què s’executa aquest procés. La restauració posteriorment també pot ser complicada, ja que només es podrà recuperar la còpia de manera total, i no serà possible fer recuperacions parcials.
• Abocament des de rutines pròpies. L’opció d’utilitzar rutines pròpies per fer l’abocament de les dades tampoc no és gaire recomanable, ja que pot passar que no es tinguin permisos d’accés a totes les taules, o que en fer l’abocament, les dades resultants no tinguin integritat referencial, per haver accedit a les taules de manera seqüencial.
• Còpia en calent. L’opció de fer una còpia de seguretat en calent, amb la utilitat pg_dump que el PostgreSQL mateix incorpora, sembla la més bona. Aquest programa serveix per fer un abocament total del contingut de la BD pel canal estàndard de sortida del sistema operatiu.

Si fem una canalització a un fitxer, aquest després es podrà tornar a utilitzar per fer la càrrega de dades. Si tenim en funcionament dues bases de dades, també es podran utilitzar canalitzacions pipe-out i pipe-in, per fer un traspàs directe de la informació entre aquestes. Aquesta última opció és especialment útil en fer migracions de versió, o en haver de substituir el maquinari, ja que els SGBD es poden estar executant en diferents ordinadors.

En primer lloc localitzarem l’executable pg_dump o pg_dumpall (en sistemes Windows) en el directori ../bin d’allà on és instal·lat l’SGBD.

Tot seguit es pot utilitzar la instrucció amb els paràmetres:

pg_dump --host=\nomServidor --port=5432 –username=postgres  --password >backup.txt

Les clàusules host i port són opcionals si s’executa des del mateix ordinador on hi ha la base de dades i si el port de comunicacions és l’estàndard.

En executar l’abocament, ens demanarà la contrasenya de l’usuari postgres.

El fitxer backup.txt contindrà l’abocament de les dades, i totes les instruccions necessàries per refer les taules, procediments emmagatzemats, usuaris i tots aquells paràmetres necessaris per aconseguir que es pugui reconstruir la BD original, en restaurar aquest fitxer sobre una BD buida.

A grans trets, l’ordre i contingut del fitxer backup.txt és (per blocs):

-- PostgreSQL database cluster dump'' Connexió a la tablespace de la qual es fa l'abocament.

-- Users Instruccions de creació dels usuaris existents.

-- Database creation Instruccions per a la creació de la BD.

-- Users Paràmetres addicionals dels usuaris de la BD.

-- PostgreSQL database dump Paràmetres de la BD (valors dels ‘SET’).

-- Name: DATABASE [nomTablespace]; Type: COMMENT; Schema: -; Owner: Comentaris DATABASE

-- Name: SCHEMA public; Type: COMMENT; [nomUsuari] Comentaris dels SCHEMA existents.

-- Name: [nomFuncio](); Type: FUNCTION; Schema: public; Owner: [nomUsuari] Codi plpgsql de les funcions existents.

-- Name: [nomLlenguatge]; Type: PROCEDURAL LANGUAGE; public; Owner:'' Instruccions de creació dels llenguatges instal·lats.

-- Name: [nomTipus]; [nomUsuari] Creació dels tipus.

-- Name: nom_taula; Type: [nomUsuari]; Tablespace: Creació de taules.

-- Data for Name: test; Type: TABLE DATA; Schema: public; Owner: [nomUsuari] Instruccions de l'abocament de les dades de la taula.

-- Name: [nomVista]; [nomUsuari] Creació de vistes.

-- Data for Name: [nomTaula]; Type: TABLE DATA; Schema: public; Owner: [nomUsuari] Instrucció d'abocament de dades de les taules, i les dades d’aquestes.

-- Name: [nomResticcio]; Type: (PK/FK) CONSTRAINT; Schema: public; Owner: [nomUsuari]; Tablespace: Modificació de taules per afegir les CONSTRAINT (claus primàries, índexs, etc).

-- Name: [nomDisparador]; Type: TRIGGER; Schema: public; Owner: [nomUsuari] Definició de disparadors

-- Name: public; Type: ACL; Schema: -; Owner: [nomUsuari] Assignació de privilegis d’accés dels usuaris sobre les dades.

-- PostgreSQL database dump complete Indicació de fi d'abocament de la BD.

L’avantatge d’aquest mètode de còpia de seguretat és que es pot editar el fitxer de còpia per cercar informació concreta. En aquest trobarem tant les dades contingudes en les taules, com les sentències de creació d’aquestes (amb les claus primàries i foranes), i fins i tot el codi plpgsql de les funcions i disparadors.

El contingut d’una còpia de seguretat es pot restaurar totalment o parcialment.

En el cas de voler una restauració parcial, haurem d’editar el fitxer on s’ha emmagatzemat la còpia, i traspassar el que volem editar a un nou fitxer. Utilitzarem aquest últim per fer una restauració parcial.

La importació es farà utilitzant una canonada del sistema operatiu mateix:

pgsql NomBaseDades < backup.txt

Per restaurar tot el contingut d’una BD, primer l’esborraríem per assegurar no quedessin dades, després la crearíem, per finalitzar amb la restauració pròpiament dita.

Igual que en l’exemple anterior, utilitzarem una canonada del sistema operatiu per fer la importació de les dades:

dropdb NomBaseDades

createdb -E UTF-8 NomBaseDades

psql NomBaseDades < backup.txt

Hi ha una sèrie d’activitats que l’administrador d’un sistema gestor de bases de dades ha de tenir presents constantment i que haurà de fer periòdicament. En el cas del PostgreSQL, aquestes es limiten a un manteniment i neteja dels identificadors interns i de les estadístiques de planificació de les consultes, a una reindexació periòdica de les taules i al tractament dels fitxers de registre.

Tal com s’ha exposat al llarg del present mòdul, l’SGBD PostgreSQL gairebé no requereix manteniment. No obstant això, per assegurar-ne el bon rendiment, caldrà executar periòdicament els processos següents:

1. Eliminació dels registres marcats com a obsolets (generats a causa de l’ús de l’MVCC).
2. Regeneració dels clústers creats.
3. Regeneració de la informació estadística de les taules.

Així, caldrà automatitzar respectivament l’execució de la seqüència d’ordres següents:

1. VACUUM FULL;
2. CLUSTER;
3. ANALYZE;

Si incloem aquestes instruccions en un fitxer (amb nom sql.txt, per exemple), es podria automatitzar l’execució d’un script des del sistema operatiu de l’ordinador, perquè executés la línia:

..\rutaBinDelPostgreSQL\psql -U postgres -d nom_db -f sql.txt <password.txt

En què plsql és la instrucció que permet executar les ordres, nom_db és la base de dades, postgres és el nom d’usuari que ha d’executar les instruccions que hi ha al fitxer sql.txt, considerant que en l’interior del fitxer password.txt es troba la contrasenya d’aquest usuari.

Quan s’esborra una fila d’una taula, per qüestions de rendiment, la fila no s’esborra realment del fitxer que conté la taula, sinó que senzillament es desindexa dels índexs que l’afectin i es marca com a esborrada. Les noves insercions tampoc no faran servir aquest espai, sinó que s’afegiran sempre al final del fitxer.

Això fa que les operacions d’inserció i esborrament siguin molt més ràpides del que ho serien si haguessin d’anar movent dades d’aquí cap allà per ajustar la mida de cada fila amb la següent. Però també implica que si tenim un volum important d’operacions d’esborrament, l’espai utilitzat al disc creix molt més del realment necessari per a les dades. A més, la dispersió més gran de les dades dins la taula tampoc no afavoreix el temps de resposta en les consultes.

El procés que fa la neteja de la base de dades en PostgreSQL es diu VACUUM. L’operació VACUUM compacta els fitxers de les taules i eliminen definitivament les fileres esborrades i manté, lògicament, la correlació dels índexs. La necessitat de dur a terme processos de VACUUM periòdicament es justifica pels motius següents:

• Recuperar l’espai de disc perdut en esborraments i actualitzacions de dades.
• Actualitzar les estadístiques de dades utilitzades pel planificador de consultes SQL.
• Protegir-se davant de la pèrdua de dades per reutilització d’identificadors de transacció.

Per dur a terme un VACUUM, haurem d’executar periòdicament les sentències VACUUM i ANALYZE. A partir de la versió 8.1 del PostgreSQL, la base de dades mateixa es preocupa, per defecte, de fer els vacuums quan ho estima necessari. Tot i que, si ens interessa, podem deshabilitar aquesta funcionalitat si preferim encarregar-nos nosaltres de programar els vacuums com més ens interessi.

Per exemple, passant-li com a paràmetre el nom d’una o més taules, podem fer vacuum exclusivament d’aquelles taules i evitar així perdre temps amb altres taules que sabem que tenen un índex de fragmentació molt més baix.

En cas que hi hagi algun problema o acció addicional per fer, el sistema ens ho indicarà.

Quan es fa l’operació CLUSTER sobre una taula aquesta es reordena físicament segons la informació d’un índex especificat definit sobre aquesta taula. Si la taula s’actualitza posteriorment, de mica en mica aquesta ordenació es va perdent, és a dir, no s’intenten desar files noves o actualitzades d’acord amb el seu ordre de l’índex. Per tant, periòdicament caldrà emprar l’ordre CLUSTER de nou perquè es faci aquesta reestructuració.

La instrucció CLUSTER sense cap paràmetre reorganitza totes les taules prèviament agrupades. Quan una taula s’agrupa, aquesta adquireix bloqueig d’accés exclusiu. Això evita que es faci qualsevol operació de modificació sobre la taula fins que l’acció de la instrucció CLUSTER hagi finalitzat.

A l’hora de planificar una consulta, el PostgreSQL té en compte molts factors, com ara els índexs de què disposa, la mida de la taula, i altres dades estadístiques sobre les dades emmagatzemades a la taula.

Obtenir tota aquesta informació a cada consulta costaria molt més que l’avantatge que ens reporta, i per això no es fa automàticament, sinó que es calcula un primer cop quan creem la taula i prou.

A mesura que es van fent insercions a la taula o es van modificant les dades, aquesta informació esdevé desactualitzada i pot fer que les decisions que prengui el planificador a l’hora d’avaluar les consultes siguin més dolentes.

Executant la sentència ANALYZE, fem que el PostgreSQL recalculi tota aquesta informació estadística, i aconseguim així que l’analitzador faci molt millor la seva feina. Amb una base de dades petita, pot ser que la diferencia no sigui gaire grossa, però quan es comencen a tenir taules d’uns pocs milions de registres, la diferència pot estar entre diversos segons i unes poques centèsimes d’execució per a la mateixa consulta.

La reindexació completa de la base de dades no és una tasca gaire habitual, però pot millorar de manera substancial la velocitat de les consultes complexes en taules amb molta activitat.

demo=# reindex database demo;

La sentència EXPLAIN ens mostra, desglossada, la manera com el PostgreSQL planificaria una consulta i el cost aproximat que el planificador preveu que tindrà.

Això, per una banda, ens serveix per comparar i avaluar les diferents possibilitats que tenim a l’hora d’implementar una consulta per obtenir una informació determinada i així poder triar l’opció que resulti més eficient.

Per altra banda, si tenim una consulta molt pesada que volem optimitzar, com veurem més endavant, ens donarà informació molt útil per determinar quines són les parts de la consulta que li resulten més pesades i que més ens convé esforçar-nos a millorar.

Així doncs, la sentència EXPLAIN executada sobre una consulta ens mostra el següent:

• Aquesta instrucció mostra el pla d’execució que el planificador del PostgreSQL genera per a la consulta donada.
• El pla d’execució mostra la manera com seran escanejades les taules referenciades, ja sigui escaneig seqüencial pla, escaneig per índex, etc.
• En el cas que es referenciïn diverses taules, els algoritmes d’unió que seran utilitzats per agrupar els tuples requerits de cada taula d’entrada.
• L’opció VERBOSE emet la representació interna completa de l’arbre del pla, en comptes d’un resum (i també l’envia a l’arxiu log del postmaster). Normalment aquesta opció és únicament útil per a la correcció d’errors (depuració) del PostgreSQL.

Podem veure els valors de sortida següents:

• Cost inici estimat. Temps inicial que triga a retornar el primer tuple.
• Cost total estimat. Temps total que triguen a retornar tots els tuples: per exemple, si es limita el nombre de tuples per retornar amb una clàusula LIMIT, el planificador fa una interpolació apropiada entre els dos costos finals per estimar quin dels plans és realment el menys costós.
• Nombre estimat de files escanejades. Es compleix solament si l’execució de la consulta és completa.
• Mida estimada de les files de sortida. Es compleix solament si l’execució de la consulta és completa i indica la mida mitjana de les files expressada en bytes.

postgres=#CREATE TABLE TEST1  ( id int4 primary key, text varchar );
 
NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "test1_pkey" for table "test1"
CREATE TABLE
 
postgres=#EXPLAIN SELECT * FROM TEST1;
                        QUERY PLAN                        
- - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
 Seq Scan on test1  (cost=0.00..22.30 rows=1230 width=36)
(1 row)

postgres=#EXPLAIN SELECT * FROM TEST1 WHERE id=4;
                               QUERY PLAN                                
- - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
 Index Scan using test1_pkey on test1  (cost=0.00..8.27 rows=1 width=36)
   Index Cond: (id = 4)
(2 rows)

postgres=#EXPLAIN SELECT sum(id) FROM TEST1 WHERE id=4;
                                  QUERY PLAN                                  
- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
Aggregate  (cost=8.27..8.28 rows=1 width=4)
->  Index Scan using test1_pkey on test1  (cost=0.00..8.27 rows=1 width=4)
         Index Cond: (id = 4)
(3 rows)

És una bona pràctica mantenir arxius de registre de l’activitat del servidor. Almenys, dels errors que origina. Durant el desenvolupament d’aplicacions pot ser molt útil disposar també d’un registre de les consultes efectuades, encara que en bases de dades de molta activitat, disminueix el rendiment del gestor i no és de gaire utilitat.

En qualsevol cas, és convenient disposar de mecanismes de rotació dels fitxers de registre; és a dir, que cada cert temps (12 hores, un dia, una setmana…), es faci una còpia d’aquests fitxers i se’n comencin de nous, cosa que ens permetrà mantenir un historial (tants com fitxers puguem emmagatzemar segons la mida que tinguin i les nostres limitacions d’espai en disc).

El PostgreSQL no proporciona directament utilitats per fer aquesta rotació, però en la majoria de sistemes Unix s’inclouen utilitats.

A diferència d’altres SGBD, el PostgreSQL no reserva espai per als fitxers on emmagatzema les dades. L’agrupació d’aquests fitxers formen el que s’anomena clúster (no s’ha de confondre amb el clúster d’agrupació de dades de les taules), que és l’equivalent a les tablespaces de l’Oracle, o als dbspace de l’Informix.

Pel fet que cada taula és físicament un o més fitxers, no cal supervisar l’ocupació d’aquestes zones (no hi ha la possibilitat d’exhaurir l’espai intern del clúster), ja que els fitxers augmentaran de mida a mesura que s’hi insereixin dades.

Malgrat aquesta diferència de concepte amb altres SGBD, hi ha la possibilitat d’indicar la ubicació física dels fitxers, a l’efecte d’optimitzar la concurrència en l’accés als disc on s’emmagatzemen les dades.

Una de les tasques més importants d’un administrador de bases de dades és monitorar els sistemes a càrrec seu per saber com estan funcionant i planejar modificacions i actualitzacions futures.

En el nostre cas, monitorar significa vigilar el funcionament d’un sistema, servei o activitat. Hi ha dos tipus de monitoratge:

• Ad hoc. Monitoratge específic en cas de problemes o proves. S’utilitza generalment per investigar una situació puntual en què intentem trobar una explicació a un succés, canvi o problema.
• Preventiu. Detecta interrupcions de serveis, alerta sobre possibles problemes i crea gràfics amb tendències i dades històriques sobre els nostres sistemes. Aquest tipus de monitoratge està automatitzat i ens ajuda a descobrir canvis en els nostres sistemes que provoquen o poden provocar problemes en un futur proper.