Manutenção de Banco de Dados PostgreSQL

Introdução

Neste laboratório, você aprenderá tarefas essenciais de manutenção de banco de dados PostgreSQL. O objetivo é entender como monitorar os tamanhos dos objetos do banco de dados, realizar manutenção rotineira para otimizar o desempenho e solucionar problemas potenciais verificando conexões e logs.

Você começará conectando-se a um banco de dados PostgreSQL e consultando os tamanhos de tabelas e índices. Em seguida, aprenderá como executar ANALYZE para atualizar as estatísticas do banco de dados e VACUUM para recuperar espaço de armazenamento. Finalmente, você cobrirá como listar conexões ativas de clientes e verificar logs do servidor em busca de erros para diagnosticar e resolver problemas de banco de dados.

Consultando Tamanhos de Objetos do Banco de Dados

Nesta etapa, você se conectará ao banco de dados PostgreSQL e consultará os tamanhos de uma tabela e seu índice. Compreender o tamanho dos seus objetos de banco de dados é crucial para o ajuste de desempenho e planejamento de capacidade.

Primeiro, abra um terminal. Para se conectar ao servidor PostgreSQL como o usuário postgres e acessar o banco de dados mydatabase, execute o seguinte comando:

sudo -u postgres psql mydatabase

Você deverá ver o prompt do psql (mydatabase=#), indicando que você está conectado ao banco de dados mydatabase. Todos os comandos SQL subsequentes neste laboratório devem ser executados neste shell psql, a menos que especificado de outra forma.

Agora, vamos determinar o tamanho de mytable. Usaremos as funções pg_size_pretty e pg_relation_size. pg_relation_size retorna o tamanho de uma tabela em bytes, e pg_size_pretty o formata em um formato legível por humanos (por exemplo, KB, MB).

Execute a seguinte consulta SQL para obter o tamanho de mytable:

SELECT pg_size_pretty(pg_relation_size('mytable'));

Você deverá ver uma saída semelhante a esta, mostrando o espaço em disco utilizado pelos dados da tabela:

 pg_size_pretty
----------------
 56 kB
(1 row)

Em seguida, verifique o tamanho do índice idx_mytable_name que foi criado na coluna name:

SELECT pg_size_pretty(pg_relation_size('idx_mytable_name'));

A saída mostrará o espaço consumido pelo índice:

 pg_size_pretty
----------------
 48 kB
(1 row)

Finalmente, para obter o tamanho total da tabela, incluindo todos os seus índices, use a função pg_total_relation_size:

SELECT pg_size_pretty(pg_total_relation_size('mytable'));

Esta saída mostra o tamanho combinado da tabela e seu índice:

 pg_size_pretty
----------------
 176 kB
(1 row)

Otimizando com ANALYZE

Nesta etapa, você aprenderá sobre o comando ANALYZE, que é essencial para manter um bom desempenho de consulta.

Entendendo ANALYZE

O comando ANALYZE coleta estatísticas sobre o conteúdo das tabelas no banco de dados. O planejador de consultas do PostgreSQL usa essas estatísticas para escolher os planos de execução mais eficientes para as consultas. Sem estatísticas precisas, o planejador pode fazer escolhas ruins, levando a um desempenho lento das consultas. É uma boa prática executar ANALYZE periodicamente, especialmente após alterações significativas nos dados da tabela.

Ainda no shell psql, execute ANALYZE na tabela mytable:

ANALYZE mytable;

Este comando analisa mytable e atualiza suas estatísticas. Você verá a seguinte saída, confirmando que o comando foi bem-sucedido:

ANALYZE

Embora o comando simplesmente retorne ANALYZE, ele atualizou as estatísticas internas para mytable em segundo plano.

Recuperando Espaço com VACUUM

Nesta etapa, você usará o comando VACUUM para realizar outra tarefa crítica de manutenção: recuperar armazenamento.

Entendendo VACUUM

No PostgreSQL, quando uma linha é atualizada ou excluída, a versão antiga da linha (uma "tupla morta") não é removida imediatamente do disco. VACUUM recupera o armazenamento ocupado por essas tuplas mortas, tornando o espaço disponível para reutilização. Ele também atualiza informações de visibilidade de dados, o que ajuda a melhorar o desempenho das consultas.

Vamos executar VACUUM na tabela mytable. No shell psql, execute:

VACUUM mytable;

Este comando processará a tabela e retornará uma confirmação:

VACUUM

Você também pode combinar VACUUM e ANALYZE em um único comando eficiente. Esta é uma prática de manutenção comum.

VACUUM ANALYZE mytable;

Este comando primeiro recupera o armazenamento e depois atualiza as estatísticas da tabela, retornando VACUUM ao ser concluído.

Nota: Existe também o comando VACUUM FULL, que recupera espaço de forma mais agressiva e encolhe o arquivo em disco. No entanto, ele bloqueia a tabela inteira, impedindo quaisquer leituras ou gravações durante sua operação, portanto, deve ser usado com cautela e apenas quando necessário.

Monitorando Conexões e Logs

Nesta etapa final, você aprenderá como monitorar conexões ativas ao banco de dados e verificar os logs do servidor em busca de erros, que são habilidades-chave para solução de problemas.

Primeiro, ainda no shell psql, você pode consultar a view pg_stat_activity para ver todas as conexões ativas ao servidor.

Execute a seguinte consulta SQL:

SELECT datname, usename, client_addr, state FROM pg_stat_activity;

Esta consulta mostra o nome do banco de dados (datname), o nome do usuário (usename), o endereço IP do cliente (client_addr) e o estado atual da conexão (state). Você verá sua própria conexão listada na saída, semelhante a isto:

  datname  | usename  | client_addr | state
-----------+----------+-------------+--------
 mydatabase| postgres |             | active
 postgres  | postgres |             |
           | labex    |             |
(3 rows)

Em seguida, você verificará os logs do servidor. Para fazer isso, você deve primeiro sair do shell psql. Digite \q e pressione Enter:

\q

Agora você está de volta ao prompt padrão do terminal Linux. Os logs do PostgreSQL estão localizados no diretório /var/log/postgresql/. Neste sistema, o arquivo de log é postgresql-14-main.log.

Use o comando grep para procurar por quaisquer linhas que contenham "ERROR" no arquivo de log:

grep ERROR /var/log/postgresql/postgresql-14-main.log

Se não houver erros, este comando não produzirá nenhuma saída. Este é o resultado esperado para este laboratório, pois não realizamos nenhuma ação que pudesse causar um erro. Examinar os logs do servidor é um passo fundamental para diagnosticar problemas no banco de dados.

Resumo

Neste laboratório, você aprendeu a realizar tarefas essenciais de manutenção do banco de dados PostgreSQL. Agora você entende como monitorar o tamanho de tabelas e índices, executar ANALYZE para otimizar o desempenho de consultas e usar VACUUM para recuperar espaço de armazenamento. Você também praticou a listagem de conexões ativas de clientes e a verificação dos logs do servidor em busca de erros. Essas habilidades são cruciais para garantir a saúde, o desempenho e a confiabilidade do seu banco de dados PostgreSQL.