Formatando dados no PostgreSQL

É muito comum fazermos alguns selects e querermos que os dados retornem formadados, como por exemplo a data. Vou exemplificar aqui alguns exemplos mais comuns de formatação que usamos no nosso dia-a-dia.

Vou sempre colocar o exemplo do dado original seguido do exemplo do dado formatado.

Pegando apenas a data de um tipo timestamp.

postgres=# SELECT current_timestamp, to_char(current_timestamp, 'DD/MM/YYYY') ;
 now              |  to_char
-------------------------------+------------
 2010-05-05 15:05:49.981577-04 | 05/05/2010
(1 row)

Formatando hora

postgres=# SELECT current_timestamp, to_char(current_timestamp, 'HH24:MI:SS') ;
 now              | to_char
-------------------------------+----------
 2010-05-05 15:10:22.454159-04 | 15:10:22
(1 row)

Formatando data e hora

postgres=# SELECT current_timestamp, to_char(current_timestamp, 'DD/MM/YYYY HH24:MI:SS') ;
 now              |       to_char
-------------------------------+---------------------
 2010-05-05 15:11:09.345058-04 | 05/05/2010 15:11:09
(1 row)

Formatando numero.

postgres=# SELECT to_char(125.8, '999D99');
 to_char
---------
 125,80
(1 row)

Backup de vários bancos PostgreSQL no Linux

Não tem coisa mais chata do que ficar fazendo backup. Mas o Linux nos dá um poder muito grande com seus scripts e agendamento de tarefas através do cron. No meu ambiente de trabalho, tenho um servidor de banco de dados que tem vários bancos. Se eu usar o pd_dumpall vou estar fazendo o backup de todos os bancos, mas não terei como restaurar apenas um banco caso eu precise. Para isso, criei um script que faz o backup individual dos bancos.

Para compactar, estou utilizando o bzip2. Apesar de levar mais tempo para compactar, o banco de dados compactado com gzip que fica com 1,7GB, passou para 1,2GB com bzip2. Se o backup puder rodar na madrugada como é o meu caso, então não terá problemas de perda de desempenho do servidor, pois ele consome mais processamento do que disco.

Vou colocar todo o código e vou explicando linha a linha para facilitar o entendimento. Coloque as linhas abaixo em um arquivo e torne-o executável (chmod 700 seuarquivo.sh).

### Data do backup
data=`date +%Y_%m_%d`

### Apaga os mais antigos, vamos apagar o que tiver mais de 15 dias,
### mantendo o espaço do disco
TIME="+15"
find $dire -type f -mtime $TIME | xargs rm -rf

### Lista de banco de dados para backup individual. Deve ser separado por espaço
BANCO="financeiro contabil vendas"

### Loop para fazer o backup individual
for banco in `echo $BANCO`
do

    ### Caminho completo e Nome do arquivo de backup
   arq="$dire$banco$data.sql"

    ### Fazendo o backup do banco
   /usr/local/pgsql/bin/pg_dump "$banco" > $arq

   ### Compacta o arquivo com tar e bzip2 para obter compactacao maxima
   tar -cf $arq.tar $arq
   bzip2 $arq.tar

   ### A compactacao nao apaga o original, vamos apagar manualmente
   rm -f $arq

done

### finaliza
exit 0

Feito isso, basta agendar no cron e ser feliz. Apenas acompanhe para ter certeza que seu disco não irá encher, nesse caso, diminua a quantidade de dias de backup que você irá guardar alterando a variável TIME.

Log de alterações de registros - PostgreSQL.

Muitas vezes nos deparamos com o sumiço de certos registros importantes e pra piorar a situação esse sumiço nunca tem culpado. Dependendo da situação, precisamos cadastrar o registro novamente e isso pode acabar trazendo consequencias pois um novo registro virá com um novo ID e normalmente não virá com todos os dados originais. Há também o caso do fulano que simplesmente apagou o conteúdo do campo e ninguém sabe o que tinha lá. Uma boa solução para isso é criarmos um log com a cópia do registro antes da atualização ser realizada, quer seja update ou delete.Podemos fazer isso de forma bem simples com uma trigger. O primeiro passo é criarmos uma tabela com a estrutura idêntica a tabela que queremos preservar os dados, vou citar como exemplo o cadastro de clientes.

No cadastro de clientes tenho os campos ID, nome, email e telefone. Na tabela que criarei para guardar os registros de alteração, além de ter esses campos, colocarei também o campo data_hora que armazenará quando o registro foi alterado ou excluido e também o campo operacao que irá informar se foi uma operação de exclusão ou alteração.

Precisamos criar uma função que pegará o registro atualizado da tabela de cliente (update ou delete) e copie para a tabela de log.

Agora que a função existe, vamos criar a trigger que irá disparar a execução da função logo após as operações de insert e delete.

Pronto. Agora, sempre que um usuário alterar ou excluir um registro da tabela cliente, a cópia desse registro irá para a tabela log_cliente. Com isso teremos uma cópia fiel do registro antes da cagad. digo, ação indevida do usuário ter sido executada. Vamos ao teste.

Primeiro eu fiz um select na tabela log_cliente, como eu acabei de criar, ela não tem nada.

Agora eu listei os registros da tabela cliente. Há dois registros.

Vamos apagar o registro cujo ID seja igual a 1, ou seja, o cliente Cleberson. Se olharmos na tabela cliente novamente, veremos que o registro foi realmente apagado.

Agora vamos ver na tabela log. Além de termos uma cópia do registro, temos a data e hora que a ação foi executada e também qual a ação foi executada, que nesse caso foi um DELETE.

A grande vantagem é termos o registro pronto para ser restaurado caso seja necessário. A desvantagem é que se a tabela sofre updates com muita frequencia, então a tabela log_cliente irá crescer rapidamente. Como você tem data e hora que foi executado, periodicamente você poderá fazer uma limpeza na tabela de log, deixando as alterações dos ultimos 6 meses ou 1 ano por exemplo.

ESSA DICA FOI RETIRADA DO BLOG DO CLEBERSONSILVA: http://www.clebersonsilva.com.br/

Listando processos em execução no PostgreSQL

No PostgreSQL você poderá através de uma simples query saber o que o servidor está executando no momento.

Exemplo:

SELECT datname, procpid, usename, current_query, query_start FROM pg_stat_activity;

Definições da colunas:

datname: é o banco de dados que está sendo executado a query;
procpid: é o id do processo no sistema operacional;
usename: é o usuário utilizado para conectar no banco de dados;
current_query: é a query que está sendo executada;
query_start: é a data e hora que a query foi iniciada.

Se a query não aparecer na coluna current_query, então você deve fazer uma alteração no arquivo postgresql.conf ativando o parametro stats_command_string conforme abaixo
stats_command_string = on

Essa alteração no arquivo de configuração requer que o banco de dados seja reiniciado.