Backup e Restauração de grandes bancos de dados PostgreSQL

Na última semana encontrei alguns problemas para fazer um backup e restaurar um grande banco de dados PostgreSQL. Eu estava usando pg_dump para gerar um único arquivo .sql para ser restaurado com o psql. Ao tentar fazer a restauração ocorria um erro de: "out of memory".

PRIMEIRA TENTATIVA

Após algumas pesquisas vi que como o arquivo SQL estava muito grande, ao usar ele como input do psql acontecia o "out of memory" porque eram muitos dados na entrada para serem processados. Uma das soluções a esse problema seria fazer um split do arquivo conforme o pg_dump fosse gerando o SQL. Isso estava de acordo com a documentação do próprio PostgreSQL, que você pode ver aqui. Então tentei algo mais ou menos assim:

pg_dump NOME_BANCO -v | split -b 1000m - BACKUP_ARQUIVOS.dump

Então conforme o pg_dump ia gerando o arquivo .sql ele fazia o split em vários arquivos de aproximadamente 1GB. Parecia ser uma boa alternativa, mas no fim acabou dando o mesmo erro de "out of memory". Então o que fazer? Mais pesquisas!

AGORA VAI!

Logo após essa tentativa que parecia que resolveria o problema, pensei comigo: "deve ser alguma configuração do postgres". Então comecei a pesquisar sobre o assunto e realmente, existiam algumas configurações que precisavam ser feitas para melhorar a performance do PostgreSQL para conseguir fazer o restore de um grande banco de dados. Vou listar abaixo quais modifiquei:

WORK_MEM

Defini a work_mem como 32MB e isso seria o suficiente para a operação. Esse valor define a quantidade máxima base de memória a ser usada por uma operação de consulta (como uma tabela de classificação ou hash) antes de gravar em arquivos temporários do disco.

SHARED_BUFFERS

Defini shared_buffers como 4GB, esse aumentei um pouco a mais do que deveria (tava com raiva já, queria que funcionasse de qualquer jeito). Esse valor define a quantidade de memória que o servidor de banco de dados usa para buffers de memória compartilhada. A documentação recomenda que seja 25% do total de memória da máquina onde o PostgreSQL está rodando.

MAINTENANCE_WORK_MEM

Defini maintenance_work_mem como 2GB, essa configuração foi a mais justamente pelo seu papel durante a importação. A documentação diz o seguinte sobre essa configuração: "Specifies the maximum amount of memory to be used by maintenance operations, such as VACUUM, CREATE INDEX, and ALTER TABLE ADD FOREIGN KEY". Durante o processo de restauração esses seriam os processos que mais demorariam e consumiriam memória da máquina para serem executados, então o aumento foi necessário.

FULL_PAGE_WRITES, AUTOVACUUM e WAL_BUFFERS

Defini full_page_writes como off, autovacuum também como off e wal_buffers como -1. Todas essas configurações estão ligadas uma a outra e não seria necessário estar ativo essas opções porque se algo acontecesse no meio do caminho, eu não queria garantir a atomicidade das transações, se desse erro, eu deveria apenas ver o erro, corrigir e recomeçar o processo com a base limpa e isso me garantiria não usar o WAL desnecessariamente, de certa forma usando memória que não seria necessário.

UM DETALHE QUE PASSOU DESPERCEBIDO

CUSTOM FORMAT

Quando estava lendo a documentação do PostgreSQL sobre backup de grandes bancos de dados deixei escapar uma opção muito importante.

pg_dump -Fc dbname > filename

Conseguiu perceber o que não vi? O custom format, a opção -Fc. Mas que raios isso iria influenciar? Bom o pg_dump e pg_restore trabalham em conjunto, ao usar o custom format da própria ferramenta para gerar o backup o pg_restore entende melhor esse formato a ponto de conseguir definir melhor como restaurar o banco de dados para garantir os dados e usar menos memória possível, é um formato que através da ferramenta ganhamos performance! Era tudo o que eu queria, então utilizei essa opção para gerar o backup com o pg_dump.

TAMANHO DO BACKUP

Tinha percebido que os arquivos de backups estavam grandes, então algo que ajudou foi usar a compressão de dados da própria ferramenta usando o parâmetro -Z 9 que indicava o nível de compressão que queria, com isso os arquivos de backup passaram a pesar poucos gigas.

TUDO PRONTO, LET'S GO!

E com tudo pronto fui rodar o pg_dump para gerar o arquivo de backup, e em poucos minutos o arquivo ficou pronto.

pg_dump -Fc -Z 9 dbname -v > filename.dump

Fiz o download para o meu PC de trabalho, e rodei o pg_restore para restaurar o banco de dados.

pg_restore -Fc -j 8 -d NOME_BANCO filename.dump

O banco de dados importou sem nenhum problema e muito rapidamente, menos de 5 minutos!

Faltou explicar uma opção ali do pg_restore a opção -j 8. Essa opção faz com que execute as partes mais demoradas do pg_restore — aquelas que carregam dados, criam índices ou criam restrições — usando vários jobs simultâneos. Ela ajuda a reduzir drasticamente o tempo de restauração do banco de dados que é grande!

CONSIDERAÇÕES FINAIS

Algumas das configurações feitas precisam ser diminuídas após o processo de restauração porque podem acabar prejudicando a performance da sua máquina, já que definimos que algumas configurações usariam mais memória.

A importância de ler a documentação com cautela e atenção é evidente nesse artigo, procurem no stackoverflow tranquilamente, mas aprendam a ler a documentação, sem ela apenas copiamos e colamos do tio Google.

Esse foi meu primeiro artigo aqui no TabNews, estou aberto a correções, críticas construtivas e elogios. Decidi escrever o artigo porque pouco se encontra em Português sobre o assunto e quando se encontra é apenas a resolução do problema sem a explicação. Espero que tenha ajudado vocês e até a próxima!