Logs via SQL/MED
Con la versión 9.1 de PostgreSQL tenemos disponible una nueva funcionalidad llamada SQL/MED mediante la cual se puede acceder a datos externos a nuestra base de datos mediante comandos SQL.
En SQL/MED existen los llamados “Foreign Data Wrapper (FDW)” que es una especie de “driver” para acceder a un tipo de datos externos. Existen diferentes tipos y con la versión 9.1 existe uno en los modulos contrib que se llama file_fdw. Este FDW se puede utilizar para acceder ficheros en formato CSV.
Hace unos meses lei en las listas de ayuda de postgreSQL y en un artículo sobre como poder acceder al fichero de registros (Log) de un servidor PostgreSQL mediante comandos SQL y sin tener que acceder al sistema operativo. Esta semana he tenido que instalar esto en un servidor y aquí os dejo los pasos a seguir para que veais lo fácil que es.
Lo primero es configurar PostgreSQL para que grabe todos los registros en un fichero en formato CSV. Tenemos que actualizar el fichero postgresql.conf de nuestro servidor con los siguientes parámetros y arrancar PostgreSQL de nuevo:
log_destination='csvlog'
logging_collector='on'
log_filename='postgresql.csv'
Despues hay que instalar en nuestra base de datos el FDW que vayamos a utilizar :
postgres=# CREATE DATABASE test;
CREATE DATABASE
postgres=# \c test
test=# CREATE EXTENSION file_fdw;
CREATE EXTENSION
A continuación hay que definir un servidor foraneo (en este caso un fichero de datos en formato CSV):
test=# CREATE SERVER logserver FOREIGN DATA WRAPPER file_fdw;
CREATE SERVER
Y por último definir una tabla foranea en nuestro servidor foraneo. En nuestro caso, esta tabla tendrá que tener tantas columnas como atributos por fila tenga nuestro fichero CSV:
CREATE FOREIGN TABLE postgres_log
(
log_time timestamp(3) with time zone,
user_name text,
database_name text,
process_id integer,
connection_from text,
session_id text,
session_line_num bigint,
command_tag text,
session_start_time timestamp with time zone,
virtual_transaction_id text,
transaction_id bigint,
error_severity text,
sql_state_code text,
message text,
detail text,
hint text,
internal_query text,
internal_query_pos integer,
context text,
query text,
query_pos integer,
location text,
application_name text
)
SERVER logserver OPTIONS (filename 'pg_log/postgresql.csv', format 'csv');
Esto es todo. Ya podemos acceder a nuestro fichero de registros (Logs) via comandos SQL.
test=# SELECT log_time,database_name,process_id,error_severity,message FROM postgres_log ;
log_time | database_name | process_id | error_severity | message
----------------------------+---------------+------------+----------------+----------------------------------------------------------
2012-02-04 14:15:58.266+01 | | 3110 | LOG | database system was shut down at 2012-02-04 14:15:56 CET
2012-02-04 14:15:58.354+01 | | 3108 | LOG | database system is ready to accept connections
2012-02-04 14:15:58.354+01 | | 3113 | LOG | autovacuum launcher started
2012-02-04 14:17:01.26+01 | template1 | 3214 | FATAL | role "nagios" does not exist
2012-02-04 14:18:00.44+01 | | 3108 | LOG | received fast shutdown request
2012-02-04 14:18:00.44+01 | | 3108 | LOG | aborting any active transactions
2012-02-04 14:18:00.44+01 | | 3113 | LOG | autovacuum launcher shutting down
2012-02-04 14:18:00.441+01 | | 3111 | LOG | shutting down
2012-02-04 14:18:00.521+01 | | 3111 | LOG | database system is shut down
(9 rows)
test=# SELECT error_severity,count(*) as cnt FROM postgres_log GROUP BY error_severity;
error_severity | cnt
----------------+-----
FATAL | 1
LOG | 8
Por supuesto no podreis modificar nada en la tabla que habeis creado ya que en realidad estais accediendo a un fichero CSV.
test=# DELETE FROM postgres_log;
ERROR: cannot change foreign table "postgres_log"
No olvidar configurar la rotación de vuestro fichero postgresql.csv para reciclar y borrar las entradas antiguas.
Enlaces: