Explotación

1. Configuración

El archivo de configuración propio de cada instancia de PostgreSQL se llama postgresql.conf y se sitúa generalmente en el directorio inicializado con la instancia, correspondiente a la opción -D del comando initdb. En los casos de los sistemas Debian, se sitúa en el directorio /etc/postgresql/, seguido de la versión principal de PostgreSQL y del nombre de la instancia elegida.

El archivo postgresql.conf puede contener las directivas include e include_dir, que pueden hacer referencia a otros archivos que contienen diversas directivas. Por defecto, estas dos directivas no se utilizan.

El archivo postgresql.auto.conf siempre se ubica en el directorio de los datos y contiene los argumentos modificados por el comando ALTER SYSTEM. Este archivo siempre se lee en último lugar, lo que hace que los argumentos presentes sobrecarguen los mismos argumentos presentes en el resto de los archivos de configuración.

Estos archivos se parecen a las directivas de configuración, que permiten adaptar el comportamiento del servidor al hardware sobre el que funciona, así como a las bases de datos utilizadas. Una vez que el servidor arranca, algunas de estas directivas se pueden sobrecargar con el comando SET o asociándolas a una base de datos o a un rol, con los comandos ALTER respectivos. Las directivas se pueden definir durante el arranque o durante la ejecución del servidor o bien con una sencilla recarga del archivo de configuración.

Las directivas que indican un valor de tamaño de memoria o de duración se pueden expresar utilizando una unidad, lo que simplifica la escritura y relectura del archivo de configuración.

Las unidades de tamaño son: KB, MB, GB. Las unidades de tiempo son: ms, s, min, h, d.

Los argumentos de configuración están dotados de valores por defecto, que se pueden revisar sobre todo en función del hardware realmente utilizado.

Cuando se añaden modificaciones al archivo de configuración, no se tienen en cuenta sin intervención del administrador. Es necesario que la instancia PostgreSQL vuelva a leer el archivo y algunas directivas necesitan un rearranque completo de la instancia, lo que implica una parada de las sesiones actuales y, por lo tanto, un corte de servicios. Cada argumento se puede modificar en un contexto concreto....

1. Pgpool

El administrador de conexiones Pgpool es una herramienta externa a PostgreSQL. No se proporciona por el grupo PostgreSQL, pero forma parte de los proyectos del ecosistema PostgreSQL.

El rol de Pgpool es insertarse entre el software cliente y los servidores PostgreSQL para reagrupar las conexiones, optimizando de esta manera el número de conexiones abiertas sobre un servidor PostgreSQL. Un cliente puede conectarse directamente a Pgpool exactamente como si se conectara a un servidor PostgreSQL. El software Pgpool conserva las conexiones abiertas, optimizando de esta manera el tiempo de apertura...

1. Copia de seguridad lógica con pg_dump y pg_dumpall

Las estrategias de copia de seguridad en caliente de las bases de datos obligatoriamente deben tener en cuenta la restauración de estos datos.

Existen dos herramientas para que la copia de seguridad lógica en caliente tenga éxito, cada una con sus particularidades y sus opciones. La elección de la herramienta correcta y las opciones correctas determinan la cualidad y la rapidez de la restauración. Por lo tanto, es importante haber planificado las copias de seguridad correctas, con los formatos correctos y conocer al avance de los métodos de restauración adaptados.

1. Análisis de una consulta con EXPLAIN

El comando EXPLAIN permite estudiar el comportamiento de una consulta y principalmente los diferentes métodos utilizados por PostgreSQL para acceder a los datos.

El plan de ejecución detalla el recorrido de las tablas y, por ejemplo, permite entender dónde añadir un índice en una tabla. También muestra los algoritmos utilizados para los joins y los costes estimados de ejecución, expresados en unidades de recuperación sobre las páginas en disco.

La opción ANALYZE ejecuta realmente la consulta y añade el tiempo real de ejecución y el número real de registros devueltos.

La sinopsis del comando es la siguiente:

EXPLAIN  <opciones> consulta; 

donde <opciones> puede ser:

( ANALYZE <booleano>, BUFFERS <booleano>, COSTS <booleano>,  
TIMING <booleano>,FORMAT { TEXT | XML | JSON | YAML } ) 

A continuación se muestra, por ejemplo, una sencilla consulta de lectura con un filtro sobre la clave primaria de la tabla prestaciones. Aquí, la tabla tiene muy pocas tuplas. Por lo tanto, el planificador de consultas no juzga necesario utilizar el índice de la clave primaria y realiza una lectura secuencial de la tabla:

clientes=# EXPLAIN SELECT * FROM prestaciones   
where prest_id = 32;   
                        QUERY PLAN   
——————————————————————————————   
Seq Scan on prestaciones  (cost=0.00..1.38...

1. Instalación

La herramienta se empaqueta en diferentes distribuciones GNU/Linux, pero se puede instalar siguiendo las instrucciones indicadas en su documentación, en la siguiente dirección: http://dalibo.github.io/pgbadger/

El procedimiento de instalación es extremadamente sencillo y solo necesita el comando perl, que ya está presente en un sistema GNU/Linux:

$ tar xf pgbadger-6.2.tar.gz   
$ cd pgbadger-6.2   
$ perl Makefile.PL   
$ make && sudo make install 

2. Análisis de los archivos de trazas

El archivo de trazas de actividad contiene un determinado número de información, según los ajustes realizados. La herramienta pgBadger puede entender el formato de los archivos de trazas y, por lo tanto, es sencillo de manipular, incluso si el tiempo de análisis de los archivos de trazas puede ser largo y consumir los recursos de la máquina en la que se ejecuta el análisis.

El siguiente comando permite lanzar el análisis y generar un archivo HTML que contiene el informe:

pgbadger /var/log/postgresql/postgresql-9.4-main.log...