Enlaces útiles de Postgres

Analizador de logs

http://michael.otacoo.com/postgresql-2/postgres-pgbadger-sneaking-in-log-files-for-you/#comment-10881

Estadisticas Postgres

Es interesante revisar las estadísticas de acceso a cada tabla de tu base de datos:

select * from pg_stat_database;

select * from pg_stat_user_tables;

select * from pg_stat_user_indexes;

Guarda esta información, antes de hacer optimizaciones, y luego reseteala y posteriormente compara los resultados:

select * from pg_stat_reset();

Para ver cuantas conexionest tienes establecidas y que están ejecutando:

select * from pg_stat_activity;

Y los bloqueos con:

 select * from pg_locks;

Si haces uso intensivo de procedimientos almacenados, también puedes consultar su rendimiento:

select * from pg_stat_user_functions

Si no tienes estadísticas calculadas, será porque no tienes activada la recopilación de las mismas,



Referencias:
http://www.postgresql.org/docs/9.1/static/monitoring-stats.html#MONITORING-STATS-SETUP

Reemplazar cadena en ficheros recursivamente

Busca a partir del directorio, recursivamente en todos los ficheros y cambia "lo_que_busco" por "lo_cambio_por_esto":

for i in `grep -l -R "lo_que_busco" ./directorio`; do sed 's/lo_que_busco/lo_cambio_por_esto/g' -i $i; done;

Trucos ssh

Conexión sin contraseña a un servidor ssh:


En el servidor origen de la conexión:


$ ssh-keygen -t rsa


El contenido del fichero resultante ~/.ssh/id_rsa.pub, hay que añadirlo al final del fichero ~/.ssh/authorized_keys, del usuario con el que quiero conectarme en el servidor destino donde quiero conectarme.

Copiar la llave id_rsa.pub al servidor remoto a traves de: ssh_copy_id id_rsa.pub usuario_remoto@servidor_remoto


Nota: en el servidor destino tiene que estar activados los siguientes parámetros del /etc/ssh/sshd_config


RSAAuthentication yes
PubkeyAuthentication yes




Copiar directorios


Copiar un directorio remoto vía ssh, con compresión y recursividad

scp -rC usuario@servidor.com:/origen ./destino

Creación de índices

Cuando en producción nos encontramos que tenemos que optimizar las consultas, nos encontraremos a veces que no podemos parar los servidores.

La optimización muchas veces pasa por crear un índice para alguna tabla, pero en Postgres esta operación bloquea la tabla.

Esto puede hacer caer el servidor de aplicaciones en el caso de aplicaciones web dinámicas, si el índice es muy grande.

Por fortuna Postgres nos facilita hacer esta operación de forma concurrente sin necesidad de bloquear la tabla, la operación será más lenta pero podremos hacerla en caliente:

CREATE INDEX CONCURRENTLY idx_salary ON employees(last_name, salary);

Ranking compuesto

Tenemos una tabla con resultados parciales:

  

Tabla Parcial

id
campo1
campo2
...
campo N

Tenemos una tabla con el ranking actual:

 

Tabla Ranking

id

posicion

posicion_anterior

Vamos a actualizar el ranking con la suma de los resultados parciales, guardando además la posición anterior a la actualización para saber cuanto se ha avanzado o retrocedido desde el último recalculo del ranking.

begin;

-- Insertamos los nuevos registros en el ranking
insert into ranking (id,posicion,ant_posicion) select id,999999,999999
from parcial where id not in (select id from ranking);-- Actualizamos la posición actual y guardamos la anterior
update ranking set posicion_anterior=ranking.posicion,posicion=temporal.posicion
from  (
select rank() over ( order by sum (campo1+campo2...campoN) desc) as posicion,
sum (campo1+campo2) as puntos,id
from parcial
group by id) as temporal
where ranking.id = temporal.id;

 

-- Borramos del ranking quien no tenga puntos parciales
delete from ranking where id not in (select id from parcial);
delete from ranking where id in
(select id from rankings.acumulado where campo1+campo2...campoN=0 );


end;

Tenemos una tabla con los puntos acumulados por equipos de diferentes regiones y divisiones.

 region_id
division
equipo
puntos

Como hacer un ranking de un plumazo: 
 select region_id,division,rank() over ( partition by region_id,division order by puntos desc) as posicion,id from
(select region_id,division,id,sum(puntos) as puntos
from rankings.diario group by region_id,division,id) as temporal;

Enlaces de Interés:

http://www.postgresql.org.es/node/376 (Funciones de ventana)

Corregir nicks de usuarios con caracteres no válidos

Aquí podeis observar como arreglar la típica tabla de usuarios en la que se se han introducidos nicks con caracteres extraños que luego dan problemas al hacer login. La tabla principal que almacena los usuarios es USUARIO La tabla aux_usuario almacena los usuarios con caracteres no admitidos La tabla aux_usuario_bueno almacena los usuarios que realizada la traducción de un caracter incorrecto a otro, no supone nick duplicado en la tabla principal
-- Borramos la tabla de apoyo si existe
drop table if exists aux_usuario;
-- Rellenamos la tabla con los usuarios problemáticos
create table aux_usuario as select * from usuario where nick <> translate(nick, 'abcdefghijklmnoprstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789_áéíóúÁÉÍÓÚçÇñÑüÜ;,.-{}[]¡!ªº"@#$%&/()=¿?^*+€´:àèìòùÀÈÌÒÙâêîôûÂÊÎÔÛ¨¬', 'abcdefghijklmnoprstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789_aeiouAEIOUcCnNuU____________________________________________________'); 
-- Actualizamos el nick substituyendo los caracteres problematicos  
update aux_usuario set nick=translate(nick,'abcdefghijklmnoprstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789_áéíóúÁÉÍÓÚçÇñÑüÜ;,.-{}[]¡!ªº"@#$%&/()=¿?^*+€´:àèìòùÀÈÌÒÙâêîôûÂÊÎÔÛ¨¬', 'abcdefghijklmnoprstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789_aeiouAEIOUcCnNuU____________________________________________________') where nick <> translate(nick, 'abcdefghijklmnoprstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789_áéíóúÁÉÍÓÚçÇñÑüÜ;,.-{}[]¡!ªº"@#$%&/()=¿?^*+€´:àèìòùÀÈÌÒÙâêîôûÂÊÎÔÛ¨¬', 'abcdefghijklmnoprstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789_aeiouAEIOUcCnNuU____________________________________________________') ;
-- Creamos una tabla excluyendo los duplicados
drop table if exists aux_usuario_bueno;
create table aux_usuario_bueno as select * from aux_usuario aux where aux.nick not in ( select nick from usuario) and aux.nick not in ( select nick from (
-- Actualizamos la tabla principal
update usuario set nick=aux_usuario_bueno.nick from aux_usuario_bueno where aux_usuario_bueno.id=usuario.id ; ===================================================================== --- PARA LOS DUPLICADOS
--- Los usuarios que no al ser traducido el nick colisiona con otro
--- existente le añadimos el identificador para hacerlo único.
--- En el caso de nombres mayores de 10 caracteres añadimos
--- el identificador por segunda vez. ===================================================================== -- Borramos la tabla de apoyo si existe  
drop table if exists aux_usuario2;
-- Rellenamos la tabla con los usuarios problemáticos create table aux_usuario2 as select * from usuario where nick <> translate(nick, 'abcdefghijklmnoprstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789_áéíóúÁÉÍÓÚçÇñÑüÜ;,.-{}[]¡!ªº"@#$%&/()=¿?^*+€´:àèìòùÀÈÌÒÙâêîôûÂÊÎÔÛ¨¬\\''', 'abcdefghijklmnoprstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789_aeiouAEIOUcCnNuU_______________________________________________________');
update aux_usuario2 set nick=translate(nick, 'abcdefghijklmnoprstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789_áéíóúÁÉÍÓÚçÇñÑüÜ;,.-{}[]¡!ªº"@#$%&/()=¿?^*+€´:àèìòùÀÈÌÒÙâêîôûÂÊÎÔÛ¨¬\\''', 'abcdefghijklmnoprstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789_aeiouAEIOUcCnNuU_______________________________________________________') where nick <> translate(nick, 'abcdefghijklmnoprstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789_áéíóúÁÉÍÓÚçÇñÑüÜ;,.-{}[]¡!ªº"@#$%&/()=¿?^*+€´:àèìòùÀÈÌÒÙâêîôûÂÊÎÔÛ¨¬\\''', 'abcdefghijklmnoprstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789_aeiouAEIOUcCnNuU_______________________________________________________');
update aux_usuario2 set nick=id::varchar where length(nick) >=10; update aux_usuario2 set nick=nick||id::varchar where length(nick) <10;
update usuario set nick=aux_usuario2.nick from aux_usuario2 where aux_usuario2.id=usuario.id ;