Zerando Large Objects numa base PostgreSQL

Se tem uma coisa que eu realmente não recomendo é sair guardando zilhões de imagens dentro do banco de dados. Uma vez ou outra vá lá… se as imagens forem poucas e pequenas. Bom, mas sabe aquela coisa desenvolvida há décadas, com zilhões de otimizações, pesquisas e intermináveis discussões para se chegar num produto ótimo  chamado Sistema de Arquivos? Então, por algum motivo bizarro algumas pessoas realmente acham que o seu banco de dados é um lugar melhor que o Sistema de arquivos para guardar…. arquivos!

Bom, estes dias eu precisei lidar com um destes sistemas “inteligentes”, desenvolvido por pessoas realmente “inteligentes” que guarda milhões de imagens no pobre do PostgreSQL. Não se engane, se fosse no Oracle ou no SQL Server, você estaria no sal do mesmo jeito. Em determinado momento, o sistema que estava em fase de rollout precisou parar tudo e começar de novo. Na verdade eu estou adiantando um pouco a história. Houveram capítulos de terror antes. Eles utilizam os Large Objects e excluem as linhas das tabelas e esquecem de apagar os arquivos da tabela pg_largeobjects. A solução seria rodar o vacuumlo, que faz este tipo de limpeza para nós. Veja a situação:

  • A base tem uns 150GB;
  • 1GB de dados em tabelas normais;
  • Aproximadamente 75 milhões de arquivos na pg_largeobjects em 149GB;
  • Previsão de excluir 80% dos arquivos, ou seja: 60 milhões de arquivos em 119GB.

Resultado: rodamos o vacuumlo por 12 horas e…. continuou rodando….

Bom, como todo mundo tem chefe a nova solicitação seria zerar TODOS os arquivos, e rápido. Claro, um TRUNCATE é muito mais fácil que um DELETE. Ops, Large Object não faz DELETE. Para remover um arquivo do tipo Large Object você tem de fazer um lo_unlink.

Ok, você poderia pensar que agora é só dar um simples TRUNCATE na tabela pg_largeobjects e tudo estaria resolvido, certo? Então veja isso:

Ah… claro, não é possível sair truncando tabelas de sistema assim numa boa. Bom, existe um jeitinho de se fazer isso. Você pode inicializar o postgres com o parâmetro allow_system_table_mods = TRUE no postgresql.conf. Claro, você tem de reiniciar o postgres para fazer isso.

Depois disso, melhor você remover o parâmetro do seu postgresql.conf e reiniciar o servidor novamente. Mas…. um requisito importante apareceu. Eu não poderia reiniciar o Postgres. Outra base no mesmo cluster estava em produção… e a próxima janela para reiniciar o Postgres estava muito longe, isto poderia atrasar todo o projeto em uma semana. Qual foi a solução? Usar o velho e bom pg_dump. A questão é que se você especificar tabelas e/ou esquemas no seu dump (--schema ou --table), o nosso amigo pg_dump NÃO traz os dados da pg_largeobjects. Como os dados representam apenas 1GB, a solução foi considerada rápida o suficiente para nós:

Encontrando as tabelas que referenciam Large Objects

Isto realmente funcionou bem. Agora faltava truncar as tabelas que usam OID ou LO, que são as colunas que apontam para os nossos Large Objects. Para descobrir quais são estas tabelas, este SELECT pode lhe ajudar:

Segue um exemplo do que você pode encontrar. Primeiro vamos adicionar algumas tabelas contendo OID e LO:

Depois vemos o resultado da consulta:

Com isso em mãos, fizemos um TRUNCATE nas tabelas restantes e zeramos finalmente todas os arquivos binários e suas referências na base.

Deixe uma resposta

O seu endereço de e-mail não será publicado. Campos obrigatórios são marcados com *