Gostaria de compartilhar com vocês minha primeira experiência bem-sucedida de restauração de um banco de dados Postgres para funcionalidade total. Conheci o SGBD Postgres há meio ano, antes não tinha nenhuma experiência em administração de banco de dados.
Trabalho como engenheiro semi-DevOps em uma grande empresa de TI. Nossa empresa desenvolve software para serviços de alta carga e sou responsável pelo desempenho, manutenção e implantação. Recebi uma tarefa padrão: atualizar um aplicativo em um servidor. A aplicação é escrita em Django, durante a atualização são realizadas migrações (alterações na estrutura do banco de dados), e antes desse processo fazemos um dump completo do banco de dados através do programa pg_dump padrão, só para garantir.
Ocorreu um erro inesperado ao fazer um dump (Postgres versão 9.5):
pg_dump: Oumping the contents of table “ws_log_smevlog” failed: PQgetResult() failed.
pg_dump: Error message from server: ERROR: invalid page in block 4123007 of relatton base/16490/21396989
pg_dump: The command was: COPY public.ws_log_smevlog [...]
pg_dunp: [parallel archtver] a worker process dled unexpectedly
Bicho "página inválida no bloco" fala de problemas no nível do sistema de arquivos, o que é muito ruim. Em vários fóruns foi sugerido fazer VÁCUO COMPLETO com opção zero_damaged_pages para resolver este problema. Bem, vamos tentar...
Preparando-se para a recuperação
ATENÇÃO! Certifique-se de fazer um backup do Postgres antes de qualquer tentativa de restaurar seu banco de dados. Se você tiver uma máquina virtual, pare o banco de dados e tire um snapshot. Se não for possível tirar um snapshot, pare o banco de dados e copie o conteúdo do diretório Postgres (incluindo arquivos wal) para um local seguro. O principal no nosso negócio é não piorar as coisas. Ler .
Como o banco de dados geralmente funcionava para mim, limitei-me a um despejo regular do banco de dados, mas excluí a tabela com dados danificados (opção -T, --exclude-tabela = TABELA em pg_dump).
O servidor era físico, era impossível tirar um snapshot. O backup foi removido, vamos em frente.
Verificação do sistema de arquivos
Antes de tentar restaurar o banco de dados, precisamos ter certeza de que tudo está em ordem com o próprio sistema de arquivos. E em caso de erros, corrija-os, porque senão só piorará as coisas.
No meu caso, o sistema de arquivos com o banco de dados foi montado em "/srv" e o tipo era ext4.
Parando o banco de dados: parada do systemctl [email protegido] e verifique se o sistema de arquivos não está em uso por ninguém e pode ser desmontado usando o comando lsof:
lsof +D /srv
Também tive que parar o banco de dados redis, pois ele também estava usando "/srv". Em seguida eu desmontei / srv (quantidade).
O sistema de arquivos foi verificado usando o utilitário e2fsck com a opção -f (Forçar a verificação mesmo se o sistema de arquivos estiver marcado como limpo):
A seguir, usando o utilitário dumpe2fs (sudo dumpe2fs /dev/mapper/gu2—sys-srv | grep verificado) você pode verificar se a verificação foi realmente realizada:
e2fsck diz que nenhum problema foi encontrado no nível do sistema de arquivos ext4, o que significa que você pode continuar tentando restaurar o banco de dados, ou melhor, retornar para vácuo cheio (claro, você precisa montar o sistema de arquivos novamente e iniciar o banco de dados).
Se você possui um servidor físico, verifique o status dos discos (via smartctl -a /dev/XXX) ou controlador RAID para garantir que o problema não esteja no nível do hardware. No meu caso, o RAID acabou sendo “hardware”, então pedi ao administrador local para verificar o status do RAID (o servidor estava a várias centenas de quilômetros de mim). Ele disse que não houve erros, o que significa que podemos definitivamente iniciar a restauração.
Tentativa 1: zero_damaged_pages
Nós nos conectamos ao banco de dados via psql com uma conta que possui direitos de superusuário. Precisamos de um superusuário, porque... opção zero_damaged_pages só ele pode mudar. No meu caso é postgres:
psql -h 127.0.0.1 -U postgres -s [nome_do_banco_de_dados]
Opção zero_damaged_pages necessário para ignorar erros de leitura (do site postgrespro):
Quando o PostgreSQL detecta um cabeçalho de página corrompido, normalmente relata um erro e aborta a transação atual. Se zero_damaged_pages estiver habilitado, o sistema emite um aviso, zera a página danificada na memória e continua o processamento. Este comportamento destrói dados, nomeadamente todas as linhas da página danificada.
Habilitamos a opção e tentamos fazer um vácuo completo nas mesas:
VACUUM FULL VERBOSE
Infelizmente, azar.
Encontramos um erro semelhante:
INFO: vacuuming "“public.ws_log_smevlog”
WARNING: invalid page in block 4123007 of relation base/16400/21396989; zeroing out page
ERROR: unexpected chunk number 573 (expected 565) for toast value 21648541 in pg_toast_106070– um mecanismo para armazenar “dados longos” no Poetgres caso não caibam em uma página (8kb por padrão).
Tentativa 2: reindexar
O primeiro conselho do Google não ajudou. Depois de alguns minutos de pesquisa, encontrei a segunda dica - fazer reindexar mesa danificada. Vi esse conselho em muitos lugares, mas não inspirou confiança. Vamos reindexar:
reindex table ws_log_smevlog
reindexar concluído sem problemas.
No entanto, isso não ajudou, VÁCUO CHEIO travou com um erro semelhante. Como estou acostumado com os fracassos, comecei a procurar mais conselhos na Internet e me deparei com um bastante interessante .
Tentativa 3: SELECIONAR, LIMITE, DESLOCAMENTO
O artigo acima sugeriu examinar a tabela linha por linha e remover dados problemáticos. Primeiro precisávamos olhar todas as linhas:
for ((i=0; i<"Number_of_rows_in_nodes"; i++ )); do psql -U "Username" "Database Name" -c "SELECT * FROM nodes LIMIT 1 offset $i" >/dev/null || echo $i; doneNo meu caso, a tabela continha 1 628 991 linhas! Era preciso cuidar bem , mas este é um tópico para uma discussão separada. Era sábado, rodei esse comando no tmux e fui dormir:
for ((i=0; i<1628991; i++ )); do psql -U my_user -d my_database -c "SELECT * FROM ws_log_smevlog LIMIT 1 offset $i" >/dev/null || echo $i; donePela manhã resolvi verificar como estavam as coisas. Para minha surpresa, descobri que após 20 horas, apenas 2% dos dados tinham sido digitalizados! Eu não queria esperar 50 dias. Outro fracasso total.
Mas eu não desisti. Eu me perguntei por que a digitalização demorou tanto. Na documentação (novamente no postgrespro) descobri:
OFFSET especifica para pular o número especificado de linhas antes de iniciar a saída de linhas.
Se OFFSET e LIMIT forem especificados, o sistema primeiro ignora as linhas OFFSET e depois começa a contar as linhas para a restrição LIMIT.Ao usar LIMIT, é importante usar também uma cláusula ORDER BY para que as linhas de resultados sejam retornadas em uma ordem específica. Caso contrário, serão retornados subconjuntos de linhas imprevisíveis.
Obviamente, o comando acima estava errado: em primeiro lugar, não havia ordenar por, o resultado pode estar errado. Em segundo lugar, o Postgres primeiro teve que varrer e pular linhas OFFSET, e com o aumento DESVIO a produtividade diminuiria ainda mais.
Tentativa 4: despejar em formato de texto
Então, uma ideia aparentemente brilhante me veio à mente: despejar o texto e analisar a última linha gravada.
Mas primeiro, vamos dar uma olhada na estrutura da tabela. ws_log_smevlog:
No nosso caso temos uma coluna "Identidade", que continha o identificador exclusivo (contador) da linha. O plano era assim:
- Começamos a despejar em formato de texto (na forma de comandos sql)
- Em determinado momento, o dump seria interrompido devido a um erro, mas o arquivo de texto ainda seria salvo no disco
- Olhamos o final do arquivo de texto, assim encontramos o identificador (id) da última linha que foi removida com sucesso
Comecei a despejar em formato de texto:
pg_dump -U my_user -d my_database -F p -t ws_log_smevlog -f ./my_dump.dumpO dump, como esperado, foi interrompido com o mesmo erro:
pg_dump: Error message from server: ERROR: invalid page in block 4123007 of relatton base/16490/21396989
Mais adiante cauda Eu olhei para o final do lixão (cauda -5 ./my_dump.dump) descobriu que o dump foi interrompido na linha com id 186 525. “Então o problema está na linha com o id 186 526, está quebrado e precisa ser deletado!” - Eu pensei. Mas, fazendo uma consulta ao banco de dados:
«selecione * de ws_log_smevlog onde id=186529"Acontece que estava tudo bem com esta linha... Linhas com índices 186 - 530 também funcionaram sem problemas. Outra “ideia brilhante” falhou. Mais tarde entendi porque isso acontecia: ao excluir e alterar dados de uma tabela, eles não são excluídos fisicamente, mas são marcados como “tuplas mortas”, então vem vácuo automático e marca essas linhas como excluídas e permite que elas sejam reutilizadas. Para entender, se os dados da tabela forem alterados e o vácuo automático estiver ativado, eles não serão armazenados sequencialmente.
Tentativa 5: SELECT, FROM, WHERE id=
As falhas nos tornam mais fortes. Você nunca deve desistir, você precisa ir até o fim e acreditar em si mesmo e nas suas capacidades. Então decidi tentar outra opção: basta examinar todos os registros do banco de dados, um por um. Conhecendo a estrutura da minha tabela (veja acima), temos um campo id que é único (chave primária). Temos 1 linhas na tabela e id estão em ordem, o que significa que podemos examiná-los um por um:
for ((i=1; i<1628991; i=$((i+1)) )); do psql -U my_user -d my_database -c "SELECT * FROM ws_log_smevlog where id=$i" >/dev/null || echo $i; doneSe alguém não entender, o comando funciona da seguinte forma: ele varre a tabela linha por linha e envia stdout para / dev / null, mas se o comando SELECT falhar, o texto do erro será impresso (stderr é enviado para o console) e uma linha contendo o erro será impressa (graças a ||, o que significa que o select teve problemas (o código de retorno do comando não é 0)).
Tive sorte, tive índices criados no campo id:
Isso significa que encontrar uma linha com o ID desejado não deve demorar muito. Em teoria deveria funcionar. Bem, vamos executar o comando em tmux e vamos para a cama.
Pela manhã, descobri que cerca de 90 entradas foram visualizadas, o que representa pouco mais de 000%. Um excelente resultado quando comparado com o método anterior (5%)! Mas eu não queria esperar 2 dias...
Tentativa 6: SELECT, FROM, WHERE id >= e id
O cliente contava com um excelente servidor dedicado ao banco de dados: processador duplo Intel Xeon E5-2697 v2, havia até 48 tópicos em nossa localização! A carga no servidor foi média, conseguimos baixar cerca de 20 threads sem problemas. Também havia RAM suficiente: até 384 gigabytes!
Portanto, o comando precisava ser paralelizado:
for ((i=1; i<1628991; i=$((i+1)) )); do psql -U my_user -d my_database -c "SELECT * FROM ws_log_smevlog where id=$i" >/dev/null || echo $i; doneAqui foi possível escrever um script bonito e elegante, mas escolhi o método de paralelização mais rápido: dividir manualmente o intervalo 0-1628991 em intervalos de 100 registros e executar separadamente 000 comandos no formato:
for ((i=N; i<M; i=$((i+1)) )); do psql -U my_user -d my_database -c "SELECT * FROM ws_log_smevlog where id=$i" >/dev/null || echo $i; doneMas isso não é tudo. Em teoria, conectar-se a um banco de dados também exige algum tempo e recursos do sistema. Conectar 1 não foi muito inteligente, você deve concordar. Portanto, vamos recuperar 628 linhas em vez de uma conexão uma a uma. Como resultado, a equipe se transformou nisso:
for ((i=N; i<M; i=$((i+1000)) )); do psql -U my_user -d my_database -c "SELECT * FROM ws_log_smevlog where id>=$i and id<$((i+1000))" >/dev/null || echo $i; doneAbra 16 janelas em uma sessão tmux e execute os comandos:
1) for ((i=0; i<100000; i=$((i+1000)) )); do psql -U my_user -d my_database -c "SELECT * FROM ws_log_smevlog where id>=$i and id<$((i+1000))" >/dev/null || echo $i; done 2) for ((i=100000; i<200000; i=$((i+1000)) )); do psql -U my_user -d my_database -c "SELECT * FROM ws_log_smevlog where id>=$i and id<$((i+1000))" >/dev/null || echo $i; done … 15) for ((i=1400000; i<1500000; i=$((i+1000)) )); do psql -U my_user -d my_database -c "SELECT * FROM ws_log_smevlog where id>=$i and id<$((i+1000))" >/dev/null || echo $i; done 16) for ((i=1500000; i<1628991; i=$((i+1000)) )); do psql -U my_user -d my_database -c "SELECT * FROM ws_log_smevlog where id>=$i and id<$((i+1000))" >/dev/null || echo $i; done
Um dia depois recebi os primeiros resultados! A saber (os valores XXX e ZZZ não são mais preservados):
ERROR: missing chunk number 0 for toast value 37837571 in pg_toast_106070
829000
ERROR: missing chunk number 0 for toast value XXX in pg_toast_106070
829000
ERROR: missing chunk number 0 for toast value ZZZ in pg_toast_106070
146000Isso significa que três linhas contêm um erro. Os ids do primeiro e do segundo registros problemáticos estavam entre 829 e 000, os ids do terceiro estavam entre 830 e 000. Em seguida, simplesmente precisávamos encontrar o valor exato do id dos registros problemáticos. Para fazer isso, examinamos nosso intervalo de registros problemáticos com passo 146 e identificamos o id:
for ((i=829000; i<830000; i=$((i+1)) )); do psql -U my_user -d my_database -c "SELECT * FROM ws_log_smevlog where id=$i" >/dev/null || echo $i; done 829417 ERROR: unexpected chunk number 2 (expected 0) for toast value 37837843 in pg_toast_106070 829449 for ((i=146000; i<147000; i=$((i+1)) )); do psql -U my_user -d my_database -c "SELECT * FROM ws_log_smevlog where id=$i" >/dev/null || echo $i; done 829417 ERROR: unexpected chunk number ZZZ (expected 0) for toast value XXX in pg_toast_106070 146911
Final feliz
Encontramos as linhas problemáticas. Entramos no banco de dados via psql e tentamos excluí-los:
my_database=# delete from ws_log_smevlog where id=829417;
DELETE 1
my_database=# delete from ws_log_smevlog where id=829449;
DELETE 1
my_database=# delete from ws_log_smevlog where id=146911;
DELETE 1Para minha surpresa, as entradas foram excluídas sem problemas, mesmo sem a opção zero_damaged_pages.
Aí me conectei ao banco de dados, fiz VÁCUO CHEIO (acho que não foi necessário fazer isso) e finalmente removi o backup com sucesso usando pg_dump. O dump foi feito sem erros! O problema foi resolvido de uma forma tão estúpida. A alegria não teve limites, depois de tantos fracassos conseguimos encontrar uma solução!
Agradecimentos e Conclusão
Foi assim que aconteceu minha primeira experiência de restauração de um banco de dados Postgres real. Vou me lembrar dessa experiência por muito tempo.
E, finalmente, gostaria de agradecer ao PostgresPro por traduzir a documentação para o russo e por , o que ajudou muito durante a análise do problema.
Fonte: habr.com