MANTENIMIENTO

PostgreSQL, como cualquier otro software de base de datos, requiere que ciertas tareas se realicen
regularmente para lograr un rendimiento ptimo. Las tareas descritas aqu son necesarias, pero son
de naturaleza repetitiva y se pueden automatizar fcilmente con herramientas estndar, como las
secuencias de comandos de cron o de tareas de Windows Scheduler. Sin embargo, es
responsabilidad del administrador de la base de datos de crear secuencias de comandos adecuados,
y comprobar que se ejecutan con xito.
Una de las tareas de mantenimiento evidente es la creacin de copias de seguridad de los datos en
un horario regular. Sin una copia de seguridad reciente, usted no tiene ninguna posibilidad de
recuperacin despus de una catstrofe (fallo de disco, incendio, por error borrar una tabla
importante, etc.).
La otra categora principal de tareas de mantenimiento es el peridico "VACUUM" de la base de
datos. Muy relacionado con esto es la actualizacin de las estadsticas que sern utilizadas por el
planificador en las consultas.
Otra tarea que podra necesitar atencin peridica es la gestin de archivos de registro.
check_postgres.pl est disponible para la vigilancia de la salud de base de datos y presentacin de
informes bajo condiciones inusuales. check_postgres.pl viene integrado con Nagios y MRTG, pero
pueden ejecutarse por separado tambin.
PostgreSQL es de bajo mantenimiento en comparacin con algunos otros sistemas de gestin de
bases de datos. Sin embargo, la debida atencin a estas tareas van mucho ms all para garantizar
una experiencia agradable y productiva con el sistema.
CONTENIDO
1. Ejecutar tareas VACUUM
Bases de VACUUM
Recuperacin de espacio en disco
Actualizacin de la Planificacin de Estadsticas
Autovacuum daemom
2. Log File Maintenance

EJECUTAR TAREAS VACUUM

Las bases de datos PostgreSQL requieren un mantenimiento peridico conocido como VACUUM.
Para muchas instalaciones, VACUUM se realizar por el demonio Autovacuum. Puede que tenga
que ajustar los parmetros autovacuuming descritos para obtener los mejores resultados para su
situacin. Algunos administradores de base de datos quieren complementar o sustituir las
actividades de los demonios con el gestionado manual del comando de VACUUM, que
normalmente se realiza de acuerdo a un calendario de tareas de cron o Programador de secuencias
de comandos. Para configurar manualmente el gestionado VACUUM adecuadamente, es esencial
comprender los temas tratados en las secciones siguientes.

Bases de VACUUM
Comando de PostgreSQL VACUUM tiene que procesar cada tabla de forma regular por varias
razones:
1. Recuperar o reutilizar el espacio en disco ocupado por actualizacin o filas eliminadas.
2. Estadsticas de la actualizacin de datos utilizados por el planificador de consultas de
PostgreSQL.
3. Proteger contra la prdida de datos muy antiguos, debido a la operacin de transaction ID
wraparound.
Cada una de estas razones recomienda realizar operaciones de VACUUM variando de frecuencia y
mbito de aplicacin, como se explica en los apartados siguientes.
Hay dos variantes de VACUUM: estndar y VACUUM FULL. VACUUM FULL puede reclamar ms espacio
en disco, pero se ejecuta mucho ms lentamente. Adems, la forma estndar de VACUUM puede correr en
paralelo con las operaciones de base de datos de produccin. (Comandos tales como SELECT, INSERT,
UPDATE y DELETE seguir funcionando con normalidad, aunque usted no ser capaz de modificar la
definicin de una tabla con comandos como ALTER TABLE mientras se est en VACUUM.) VACUUM FULL
requiere bloqueo exclusivo sobre la tabla en que est trabajando, y por lo tanto no puede hacerse en paralelo
con el uso de la tabla. Otra desventaja de VACUUM FULL es que, si bien reduce el tamao de la tabla, no
reduce el tamao de ndice proporcionalmente, de hecho puede hacer los ndices ms grandes. Por lo general,
por lo tanto, los administradores deben esforzarse por utilizar el vaco estndar y evitar el vaco completo.

VACUUM crea una cantidad considerable de trfico I / O, que puede causar un mal rendimiento
para otras sesiones activas. Hay parmetros de configuracin que se puede ajustar para reducir el
impacto en el rendimiento de VACUUM de fondo.
Recuperacin de espacio en disco
En PostgreSQL, un UPDATE o DELETE de una fila no retira de inmediato la versin anterior de la fila.
Este enfoque es necesario para obtener los beneficios del control de concurrencia multiversin. la versin

de fila no debe ser borrada si bien todava es potencialmente visible para otras transacciones. Pero
con el tiempo, una versin obsoleta de fila o eliminado ya no es de inters para cualquier
transaccin. A continuacin, el espacio que ocupa debe ser recuperado para la reutilizacin de los nuevos
registros, para evitar el crecimiento infinito de las necesidades de espacio en disco. Esto se hace mediante
la ejecucin de VACUUM.
La forma estndar de VACUUM elimina las versiones de fila muertos en las tablas e ndices, y marca el
espacio disponible para la reutilizacin futura. Sin embargo, no se devolver el espacio para el sistema
operativo, salvo en el caso concreto de una o ms pginas al final de un cuadro pasa a ser totalmente
libre y un bloqueo de tabla exclusivo puede ser fcilmente obtenido. Por el contrario, VACUUM
FULL compacta activamente tablas moviendo versiones de filas a pginas anteriores. Por tanto, es capaz
de forzar a las pginas al extremo de la tabla para ser totalmente libre, con lo cual se volver al sistema
operativo. Sin embargo, si muchas filas deben ser movidas, esto puede tomar mucho tiempo. Adems,
requiere mover una fila de forma transitoria tomada de entradas de ndice por duplicado (la entrada que
apunta a su nueva ubicacin debe hacerse antes de la entrada antigua que puede ser eliminada), de modo
que mover un montn de registros de esta manera causa un aumento severo del ndice.
El objetivo de realizar tareas VACUUM peridicamente es el de evitar tener que realizar un VACUUM
FULL. El demonio Autovacuum intenta trabajar de esta manera, y de hecho nunca realiza un VACUUM
FULL. La idea no es mantener las tablas en su tamao mnimo, sino mantener estable el uso de espacio
en disco: cada tabla ocupa un espacio equivalente a su tamao mnimo, ms el espacio que se
necesita entre VACUUMS. Aunque el VACUUM FULL se puede utilizar para reducir el tamao de una
tabla de nuevo a su tamao mnimo y devolver el espacio en disco para el sistema operativo, no hay
mucho a hacer si la tabla vuelve a crecer en el futuro. Por lo tanto, VACUUMS estndar moderadamente

frecuentes son ms recomendables que VACUUM FULL de vez en cuando para realizar un
mantenimiento de tablas muy actualizado.

Algunos administradores prefieren programar VACUUM ellos mismos, por ejemplo, haciendo todo
el trabajo por la noche, cuando la carga es baja. La dificultad de hacer pasar VACUUM de acuerdo a
un horario fijo es que si una tabla tiene un pico inesperado en la actividad de actualizacin, se puede
llegar a un aumento del tamao hasta el punto de tener que usar FULL VACUUM que es realmente
necesario en este caso para recuperar el espacio. Usar el demonio Autovacuum reduce este problema,
ya que los horarios de demonio VACUUM varan dinmicamente en respuesta a la actualizacin de la
actividad. No es prudente deshabilitar el demonio por completo a menos que tenga una carga de
trabajo extremadamente predecible. Una posible solucin de compromiso es establecer los parmetros
de los demonios de forma que slo reaccionen a una actividad de actualizacin inusualmente fuertes,
manteniendo as las cosas fuera de control, mientras que los VACUUM programados se esperan a hacer
la mayor parte del trabajo cuando la carga es tpico.

Para aquellos que no utilizan Autovacuum, un mtodo tpico consiste en programar VACUUM en
una base de datos amplia una vez al da durante un perodo de bajo uso, completada con una
limpieza ms frecuente de las tablas fuertemente actualizadas segn sea necesario. (Algunas
instalaciones presentan una actualizacin de las tasas extremadamente altas de VACUUM de sus
tablas ms activas tan a menudo como una vez cada pocos minutos.) Si tiene varias bases de datos en
un clster, no se olvide de pasar VACUUM a cada una; el programa vacuumdb podra ser til.
Consejo: Ninguna forma de VACUUM es del todo satisfactoria cuando una tabla contiene un gran nmero
de versiones de fila muertas como resultado de un UPDATE masivo o DELETE Si usted tiene por ejemplo
una tabla y es necesario recuperar el exceso de espacio en disco que ocupa, la mejor manera es utilizar clster
o una de las variantes expuestas en la reescritura de ALTER TABLE. Estos comandos reescriben una copia
totalmente nueva de la tabla y construyen nuevos ndices para ello. Al igual que el vaco completo, requieren
bloqueo exclusivo. Tenga en cuenta que ellos tambin ocupan temporalmente el uso del espacio de disco
adicional, ya que las copias antiguas de la tabla y los ndices no puede ser liberados hasta que los nuevos
estn completos. En el peor de los casos en que el disco est casi lleno, FULL VACUUM puede ser la nica
alternativa viable.
Consejo: Si tiene una tabla cuyo contenido completo se elimina de forma peridica, considere la posibilidad de
hacerlo con TRUNCATE en lugar de usar DELETE seguida por VACUUM. TRUNCATE elimina todo el
contenido de la tabla de inmediato, sin necesidad de un VACUUM posterior o FULL VACUUM para
recuperar el espacio en el disco, ahora no utilizados. La desventaja es que se viola la semntica MVCC.

Actualizacin de la Planificacin de Estadsticas

El planificador de consultas PostgreSQL se basa en la informacin estadstica sobre el contenido de las
tablas a fin de generar buenos planes para las consultas. Estas estadsticas son recogidas por el comando
ANALYZE, que puede ser invocado por s mismo o como un paso opcional en VACUUM. Es importante

contar con estadsticas razonablemente precisas, de lo contrario la pobreza de los planes de consulta
podra degradar el rendimiento de base de datos.
El demonio Autovacuum, si est activado, emitir automticamente comandos de ANALYZE siempre que
el contenido de una tabla haya cambiado lo suficiente. Sin embargo, los administradores prefieren

realizar en las operaciones de ANALYZE manualmente, sobre todo si se sabe que la actividad de
actualizacin en una tabla no afectarn a las estadsticas de columnas "interesantes". Los horarios en
que el demonio lanza ANALYZE son estrictamente en funcin del nmero de filas insertadas o
actualizadas, que no tienen conocimiento en si que conduzca a cambios estadsticos significativos.
Al igual que con VACUUM para la recuperacin del espacio, las frecuentes actualizaciones de las

estadsticas son ms tiles para tablas fuertemente actualizadas que para las que rara vez son
actualizadas. Pero incluso para una tabla muy actualizada, podra no haber necesidad de actualizar las
estadsticas, si la distribucin estadstica de los datos no est cambiando mucho. Una simple regla de oro

es pensar en la cantidad de los valores mnimos y mximos de las columnas en el cambio de la

tabla. Por ejemplo, una columna timestamp que contiene la hora de actualizar una fila tendr un
valor mximo en constante aumento como filas se aaden y actualizacin; como una columna
probablemente necesitar actualizaciones ms frecuentes de las estadsticas, por ejemplo, una
columna que contiene las direcciones URL de pginas visitadas en un sitio web. La columna de
direccin puede recibir cambios de la misma frecuencia, pero en la distribucin estadstica de sus
valores, probablemente los cambios se den de forma relativamente lenta.
Es posible ejecutar ANALYZE en tablas y columnas aunque slo sea especfico de una tabla, por lo que
existe la flexibilidad para actualizar algunas estadsticas con ms frecuencia que otras, si su aplicacin
requiere. En la prctica, sin embargo, generalmente es mejor simplemente analizar la base de datos,
porque es una operacin rpida. ANALYZE utiliza un muestreo aleatorio de Estadstica de las filas de
una tabla en lugar de leer cada fila.
Consejo: Si bien no podra ser muy productivo ajustar la columna de la frecuencia de analizar, es posible
que descubra que vale la pena hacer el ajuste para la columna del nivel de detalle de las estadsticas
recopiladas por ANALYZE. Las columnas que son muy utilizadas en las clusulas WHERE y tienen
distribucin muy irregular de los datos podran requerir un volumen ms fino de datos de histograma de otras
columnas. Ver las estadsticas SET de ALTER TABLE, o cambiar la base de datos por defecto para todo el
uso del parmetro de configuracin default_statistics_target

Autovacuum daemom
Ver la documentacin PostgreSQL.
LOG FILE MAINTENANCE
Es una buena idea guardar la salida de log del servidor de base de datos en alguna parte. La salida de
registro tiene un valor incalculable a la hora de diagnosticar problemas. Sin embargo, el resultado de
registro tiende a ser voluminoso (especialmente en los niveles ms altos de depuracin) y no se desea
guardar de forma indefinida. Se necesita "rotar" los archivos de registro para que los archivos de registro
se generen de nuevo y los antiguos se eliminen despus de un perodo razonable de tiempo.
Si simplemente se dirige el stderr de postgres a un archivo, usted tendr los resultados del registro, pero la
nica manera de truncar el archivo de registro es detener y reiniciar el servidor. Esto podra estar bien si
se est utilizando PostgreSQL en un entorno de desarrollo. No en servidores de produccin.

Un mejor enfoque es enviar la salida stderr del servidor a algn tipo de programa de registro por
rotacin. Hay una incorporado en el programa de rotacin de los registros, que se puede utilizar
mediante el establecimiento de la logging_collector parmetro de configuracin en postgresql.conf.
Los parmetros de control para este programa se describen en el documento. Tambin puede
utilizar este enfoque para capturar los datos de registro en la mquina de formato CSV legible.
Alternativamente, es posible que prefiera utilizar un programa externo de rotacin de los registros,
si usted tiene uno que ya est utilizando con otro software de servidor. Por ejemplo, la herramienta
rotatelogs incluido en la distribucin de Apache se puede utilizar con PostgreSQL. Si inicia el
servidor con pg_ctl, entonces stderr ya est redirigida a la salida estndar, por lo que slo necesita
un comando de tuberas, por ejemplo:
pg_ctl start | rotatelogs /var/log/pgsql_log 86400

LINK

http://kb.deister.net/index.php/Mantenimiento

VACUUM EN POSTGRESQL
NOMBRE
VACUUM -- limpia y opcionalmente analiza una base de datos.
SINOPSIS
VACUUM [ FULL ] [ FREEZE ] [ VERBOSE ] [ table_name ];
VACUUM [ FULL ] [ FREEZE ] [ VERBOSE ] ANALYZE table_name [ (column_name [, ...] ) ]
DESCRIPCIN
VACUUM reclama el almacenamiento ocupado por tuplas muertas. En la operacin normal de
PostgreSQL, las tuplas que son eliminadas u obsoletas por una actualizacin no son fsicamente
removidas de su tabla; permanecen presentes hasta que se hace un VACUUM. Por lo tanto, es
necesario hacer un VACUUM peridicamente, especialmente sobre tablas actualizadas
frecuentemente.
Sin parmetros, VACUUM procesa cada tabla en la base de datos actual de la cual el usuario tiene
permisos de vaciado. Con un parmetro, VACUUM procesa solo la tabla especificada.
VACUUM ANALYZE realiza un VACUUM y luego un ANALYZE para cada tabla seleccionada.
ANALYZE.- Colecta estadsticas sobre los contenidos de las tablas en la base de datos, y almacena los
resultados en el catlogo del sistema. Posteriormente, el planeador de consultas usa estas estadsticas
para ayudar a determinar el plan de ejecucin ms eficiente para las consultas.
Sin parmetros, ANALYZE examina cada tabla en la base de datos actual. Con un parmetro, ANALYZE
examina solo una tabla. Adems, se puede dar una lista de columna en cuyo caso solo las estadsticas
para dichas columnas son colectadas.

Un VACUUM plano (sin FULL) simplemente reclama el espacio y lo hace disponible para su reuso.
Esta forma del comando puede operar en paralelo con lecturas y escrituras normales a la tabla, ya
que no se obtiene un bloqueo exclusivo. VACUUM FULL hace un procesamiento ms extensivo,
incluyendo mover las tuplas entre bloques para tratar de compactar la tabla a un nmero mnimo
de bloques de disco. Esta forma es mucho ms lenta y requiere un bloqueo exclusivo en cada tabla
que esta siendo procesada.
PARMETROS
FULL.- Opcional. Selecciona el vaciamiento "completo", el cual reclama ms espacio,
pero toma mucho ms tiempo y bloquea exclusivamente la tabla.
FREEZE.- Opcional. Selecciona el "congelamiento" agresivo de tuplas. La opcin
FREEZE esta depreciada y ser removida en liberaciones futuras.
VERBOSE.- Opcional. Imprime un reporte detallado de la actividad de vaciamiento para
cada tabla.
ANALYZE.- Opcional. Actualiza las estadsticas usadas por el planeador para determinar
la forma ms eficiente de ejecutar una consulta.
nombre_tabla.- Opcional. Si se especifica, el vacuum slo se
realizara a la tabla especificada. Si no se especifica, el
vacuum se realizara para todas las tablas de la base de datos.

 nombe_columna_1, nombre_columna_n.- Opcional. Si se especifica,

stas son las columnas que sern analizadas. Si no se
especifican, se analizaran todas las columnas.
SALIDA
Cuando se especifica VERBOSE, la consulta VACUUM emite mensajes de progreso para indicar que
tabla esta siendo actualmente procesada. Tambin se imprimen varias estadsticas sobre las tablas.
NOTAS
Cada vez que se realiza una actualizacin o eliminacin de un registro de una tabla, el registro
original se guarda en la base de datos. Un VACUUM eliminar estos viejos registros (es decir:
tuplas) y reducir el tamao de la base de datos PostgreSQL.
No se puede ejecutar un comando VACUUM dentro de una transaccin.
Si no se especifica una tabla, el comando VACUUM se ejecutara sobre la base de datos abierta.
Para ejecutar un VACUUM sobre una tabla, se necesita ser el propietario de dicha tabla o tener
permisos de superusuario. Sin embargo, el propietario de la base de datos puede ejecutar un
VACUUM a todas las tablas de su base de datos, a excepcin de los catlogos compartidos.
VACUUM FULL no reduce el tamao de los ndices; se recomienda una reindexacin peridica.
De hecho, a menudo es ms rpido eliminar todo los ndices (DROP INDEX), luego compactar
la Base de Datos (VACUUM FULL) y finalmente volver a crear los ndices (CREATE INDEX).
RECOMENDACIONES
Razones por las que se debe ejecutar en cada tabla el comando VACUUM, de forma regular.
Para recuperar el espacio en el disco ocupado por filas actualizadas o suprimidas.
Para actualizar las estadsticas de datos utilizados por el planificador de consultas de
PostgreSQL.
Para protegerse contra la prdida de datos muy antiguo debido a envolvente ID de
transaccin.
VACUUM FULL se recomienda para los casos en que usted sabe que se haya eliminado la
mayora de las filas de una tabla, de modo que el tamao de estado estacionario de la tabla se
puede reducir sustancialmente con el enfoque ms agresivo VACUUM FULL. No usar VACUUM
FULL para la recuperacin rutinaria del espacio.
Despus de una considerable cantidad de modificaciones sobre una tabla, se recomienda emitir
un comando VACUUM ANALYZE para la tabla afectada. Esto actualizar los catlogos del
sistema con los resultados de todos los cambios recientes, y permitir que el planificador de
consultas de PostgreSQL a tomar mejores decisiones en las consultas de planificacin.
CONCLUSIONES
La frecuencia y el alcance del VACUUM realizado puede variar dependiendo de las necesidades
de cada sitio. Por lo tanto, los administradores de bases deben entender estos temas y desarrollar
una estrategia de mantenimiento apropiado.
Es recomendable realizar VACUUM ANALYZE despus de una considerable cantidad de
modificaciones sobre una tabla y realizar VACUUM FULL de forma peridica cuando no exista
utilizacin sobre la base de datos, por ejemplo en horas nocturnas fuera del horario de trabajo.
LINKS
http://www.postgresql.org.ar/trac/wiki/sql-vacuum.html
http://www.techonthenet.com/postgresql/vacuum.php

COMPACTAR BASE DE DATOS POSTGRESQL

Porque necesito compactar una base de datos PostgreSQL?
PostgreSQL como la mayora de los servidores de bases de datos no elimina los registros
fsicamente de las tablas sino que los marca para ser eliminado posteriormente por razones de
rendimiento del comando DELETE.
Que consecuencias tiene no compactar la base de datos?
Probablemente hayas llegado a este blog buscando la razn por la que la base de datos tiene un
tamao muy exagerado comparado con la cantidad de registros que tienes y el rendimiento de la
base de datos en las instrucciones UPDATE, SELECT e INSERT se estn tardando demasiado en
ejecutarse.
Cuales son y porque existen diferentes tipos de compactacin en PostgreSQL?
La compactacin en PostgreSQL se lleva a cabo usando el comando SQL VACUUM, sin embargo,
este tiene varios modificadores o argumentos dependiendo de la necesidad o la ocasin, estas son
las mas importantes:
VACUUM ANALIZE Actualiza las estadsticas de almacenamiento de las tablas que son
usadas por el motor de bases de datos para determinar la forma mas veloz de ejecutar una
Consulta, sin embargo, este no bloquea la tabla de forma exclusiva y solo necesita un Cursor de
Solo-Lectura, es decir, solo actualiza las estadsticas para mejorar los queries.
VACUUM FULL Realiza una coleccin de registros eliminados completa reclamando espacio
en la base de datos ocupado por registros marcados para ser eliminados. Este comando bloquea
las tabla e impide que otras conexiones realicen operaciones sobre la tabla o las tablas
afectadas.
Nota: para ambos comandos, el nombre de la tabla es opcional. Si no es especifica el nombre de la
tabla, la operacin se realiza sobre la base de datos a la cual estamos conectados al momento de
ejecutar el comando VACUUM.
Cuando ejecutar cada tipo VACUUM?
Es recomendable realizar VACUUM ANALYZE despus de una considerable cantidad de
modificaciones sobre una tabla y realizar VACUUM FULL cuando no exista utilizacin sobre la
base de datos, por ejemplo en horas nocturnas fuera del horario de trabajo. pgAgent es un excelente
ayudante para este tipo de trabajos programados.
Como ejecutar VACUUM en linea de comandos en el servidor?
Usando psql
Para esta tarea podemos usar la utilidad de terminal psql usando el usuario postgres, en ubuntu
o debian podemos hacer esto usando los siguientes comandos:
$sudo su postgres

$psql
$VACUUM;
NOTA: Para ver el progreso del comando puede utilizar el modificador VERBOSE
Para salir de la utilidad psql utilice \q
Usando pgAdminIII
Abre la conexin con el servidor de bases de datos usando pgAdminIII y selecciona en el rbol
izquierdo el nombre de la base de datos que se compactara: Clic Derecho y Seleccione
"Mantenimiento" el cual le mostrara una ventana como la siguiente:

LINKS
http://johansoft.blogspot.pe/2007/08/compactar-base-de-datos-postgresql.html
http://www.postgresql.org/docs/8.2/interactive/sql-vacuum.html
http://www.pgadmin.org/docs/1.4/pgagent.html
/*Libera el espacio dentro de cada tabla y deja el espacio disponible para ser utilizado por la misma tabla.
No devuelve el espacio para el sistema operativo, por lo tanto, el tamao del archivo de base de datos no se
reduce.*/
VACUUM; --Recupera el espacio para ser utilizado por la misma tabla.
/*vacuum full podra reescribir todas las tablas en un archivo nuevo, por lo que requiere un bloqueo exclusivo en
cada tabla.
El archivo de base de datos se minimiza como todo el espacio no utilizado, se recupera el espacio para el sistema
operativo.*/
VACUUM FULL; --Recuperar el espacio y reduce el tamao de la base de datos.
/*Libera el espacio dentro de la tabla productos y deja el espacio disponible para ser utilizado slo por la tabla
productos.
El tamao del archivo de base de datos no se reducira.*/
VACUUM products; --Recuperar el espacio en una tabla especifica, en lugar de toda la base de datos.
/*No slo libera el espacio no utilizado en la tabla de productos, sino que tambin permite que el sistema
operativo
recupere el espacio, adems se reduce el tamao de la base de datos.*/
VACUUM FULL products; --Recupera el espacio de una tabla especifica y reduce el tamao de la base de datos.
/*Se puede aadir la opcin VERBOSE al comando VACUUM para mostrar un informe de actividades del

proceso de vaco.*/
VACUUM FULL VERBOSE products; --Reporta las actividades del proceso de vaco.