Corrigir Chaves Estrangeiras Quebradas Depois de Mensagens Perdidas na Fila, pela Linha de Comando

· 11 min de leitura · chave estrangeira · sql server para postgresql · fila de mensagens · comparar dados · linha de comando · api de jobs

Corrigir Chaves Estrangeiras Quebradas Depois de Mensagens Perdidas na Fila, pela Linha de Comando

Uma réplica PostgreSQL guarda notas (invoices) e os itens delas (invoice_lines, com chave estrangeira para invoices). Quem alimenta a réplica é um consumidor que lê uma fila, Kafka ou RabbitMQ: uma mensagem por nota, uma mensagem por item. Os dados vêm de um banco SQL Server, que continua sendo a referência.

Algumas mensagens se perderam. Agora a réplica tem dois tipos de buraco, e eles têm cara diferente:

  1. Mensagens de nota perdidas. A nota falta. Quando os itens dela chegaram, a chave estrangeira recusou, porque o pai não estava lá. Então os itens também faltam.
  2. Mensagens de item perdidas. A nota está lá, mas falta um dos itens. O pai existe, os filhos não estão todos.

Você quer achar os dois, completar sem recarregar as tabelas e provar que a réplica está completa. Por script, para rodar no cron ou no CI.

Este texto mostra como fazer, no Linux e no Windows, com o dbSlice by Thinwork. Todas as saídas abaixo vêm de execuções reais em laboratório.

Os dados de exemplo

Duas tabelas genéricas, sem dado real. Cada valor é calculado a partir dos ids.

  • SQL Server (origem): 10.000 notas com 3 itens cada, 30.000 itens. A chave do item é line_id = invoice_id * 10 + número do item, então a nota 7 tem os itens 71, 72 e 73. Veja 01-sqlserver-source.sql.
  • PostgreSQL (réplica): as mesmas tabelas, com a mesma chave estrangeira. Veja 02-postgres-target.sql.

No laboratório não havia broker. A perda das mensagens foi simulada direto no banco: o 02-postgres-target.sql monta a réplica do jeito que o consumidor a teria deixado.

  1. 25 mensagens de nota perdidas (invoice_id % 400 = 7). As 25 notas faltam, e os 75 itens delas também (recusados pela chave estrangeira quando chegaram).
  2. 40 mensagens de item perdidas (o item 2 de cada nota com invoice_id % 250 = 3). As 40 notas estão lá, cada uma com um item a menos.

Na réplica: 9.975 notas e 29.885 itens. 40 notas têm menos de 3 itens.

Todos os arquivos estão num download só: fix-broken-foreign-keys-after-lost-messages.zip. Ele traz os dois scripts SQL, os quatro arquivos de job e um script pronto para Linux (run-linux.sh) e para Windows (run-windows.ps1). Os comentários dos arquivos estão em inglês.

Os quatro arquivos de job

O job é um JSON pequeno com origem, destino, chave e tipo. Ele nunca leva senha: só o nome de um perfil de conexão que você salvou no dbSlice.

MASTER e Server-dbPostg são os nomes dos perfis do nosso laboratório. Troque pelos nomes dos seus perfis.

Dois jobs comparam e só leem, um por tabela. fk-invoices-check.json:

{
  "nome": "fk-invoices-check",
  "tipo": "status",
  "origem": {
    "environment": "article",
    "db": "MASTER",
    "type": "TABLE",
    "object": "invoices",
    "key": "invoice_id"
  },
  "destino": {
    "environment": "article",
    "db": "Server-dbPostg",
    "type": "TABLE",
    "object": "invoices",
    "key": "invoice_id"
  },
  "pageSize": 10000
}

O fk-lines-check.json é igual, com "object": "invoice_lines" e "key": "line_id" dos dois lados.

Dois jobs carregam, um por tabela: fk-invoices-load.json e fk-lines-load.json. Têm a mesma origem e o mesmo destino das conferências. Só muda o tipo:

{
  "nome": "fk-lines-load",
  "tipo": "insert",
  ...
}

O insert copia só as linhas cuja chave falta no destino. Não atualiza e não apaga. É o que este caso pede: a conferência vai mostrar que nada divergiu, só faltam linhas.

Nenhum deles tem where. As conferências leem a tabela inteira, então todo buraco aparece.

Como rodar: Linux e Windows

Linux (edição Docker, API de jobs)Windows (CLI)
Precisa de curl e jq. Crie um token na guia API da edição web, com os escopos jobs:run e runs:read, a lista de jobs permitidos e a validade. Salve em um arquivo. Copie os quatro arquivos de job para a pasta jobs/ do volume de dados da instância. A API roda o job pelo nome.Salve os dois perfis no dbSlice. Não há mais nada para configurar.
O token vai só no cabeçalho Authorization: Bearer.O job não leva token nem senha.

Linux, um job:

URL=http://127.0.0.1:8098
TOKEN="$(cat ./token)"   # dbs_xxxxxxxx_...
curl -sS -X POST "$URL/api/v1/jobs/fk-invoices-check/run" \
  -H "Authorization: Bearer $TOKEN" -H "Content-Type: application/json" \
  -d '{"runLabel": "after-lost-messages"}'

A API responde com um runId. Consulte a execução até o status sair de queued e running, e leia o log:

curl -sS -H "Authorization: Bearer $TOKEN" "$URL/api/v1/runs/<runId>"
curl -sS -H "Authorization: Bearer $TOKEN" "$URL/api/v1/runs/<runId>/log"

Windows, um job:

dbSlice.exe fk-invoices-check.json check-invoices.log

A CLI imprime o resultado e grava o log no arquivo informado.

A sequência: conferir as duas tabelas, carregar os itens primeiro (de propósito, para mostrar o que acontece), carregar as notas, carregar os itens, conferir as duas tabelas de novo.

Passo 1. Conferir as duas tabelas

Saída no Linux (a instância do laboratório roda em português), notas:

OK  Comparação concluída em 0,1s: 9.975 iguais, 25 faltando no destino, 0 faltando na origem. 0 com valores divergentes.
  Faltando no destino (25): 7, 407, 807, 1207, 1607, 2007, 2407, 2807, 3207, 3607, 4007, 4407, 4807, 5207, 5607, 6007, 6407, 6807, 7207, 7607, 8007, 8407, 8807, 9207, 9607

Itens:

OK  Comparação concluída em 0,6s: 29.885 iguais, 115 faltando no destino, 0 faltando na origem. 0 com valores divergentes.
  Faltando no destino (115): 32, 71, 72, 73, 2532, 4071, 4072, 4073, 5032, 7532, 8071, 8072, 8073, ...

Leia as chaves dos itens com a fórmula invoice_id * 10 + número do item:

  • 71, 72, 73: os três itens da nota 7, uma das 25 notas que faltam. São os itens que a chave estrangeira recusou. 25 notas × 3 itens = 75.
  • 32: o item 2 da nota 3. A nota 3 está na réplica. É uma mensagem de item perdida. São 40.
  • 75 + 40 = 115.
  • 0 faltando na origem e 0 com valores divergentes: nada existe só na réplica, e as linhas que estão lá estão certas. Só faltam linhas.

No Windows, o mesmo resultado (interface em inglês): 9,975 matched, 25 missing in destination e 29,885 matched, 115 missing in destination.

Passo 2. Carregar os itens primeiro (falha)

Carregamos os itens antes das notas, de propósito. Saída no Linux:

> GET /api/v1/runs/<runId>
{
  "job": "fk-lines-load",
  "status": "failed",
  ...
}
INFO destino: Server-dbPostg [PostgreSQL]  ·  invoice_lines  ·  modo: InsertNew
INFO impacto: grava no destino (só INSERT)
INFO FALHOU  23503: insert or update on table "invoice_lines" violates foreign key constraint "invoice_lines_invoice_id_fkey"

Dos 115 itens que faltam, 75 são de notas que ainda não estão na réplica. O PostgreSQL recusa com o erro 23503, violação de chave estrangeira. É a mesma coisa que aconteceu com o consumidor quando as mensagens de nota se perderam.

Três pontos:

  • Não gravou nada. No Windows, o resumo mostra inserted: 0. Nem os 40 itens cujas notas existem. A carga seguinte dos itens ainda encontrou os 115 faltando.
  • A API devolve "status": "failed" e, no Windows, o código de saída é diferente de zero. Um script ou o cron para no primeiro erro, em vez de seguir adiante.
  • O erro vem sem DETAIL. O log diz DETAIL: Detail redacted as it may contain sensitive data. Specify 'Include Error Detail' in the connection string to include this information. É o driver do PostgreSQL (Npgsql) escondendo os valores da linha por padrão. O nome da restrição, invoice_lines_invoice_id_fkey, já diz qual tabela-pai está com linhas faltando.

Passo 3. Pais primeiro, depois os filhos

Carregar as notas:

OK  Concluído: 10.000 lidos, 25 inseridos, 0 upsert/update em 0,2s

Depois os itens:

OK  Concluído: 30.000 lidos, 115 inseridos, 0 upsert/update em 0,5s

O job insert lê a origem inteira e copia só as chaves que faltam na réplica. Aqui, 25 de 10.000 notas e 115 de 30.000 itens. 0 upsert/update: não regravou as linhas que já estavam lá.

Por isso dá para rodar de novo sem medo. Ele nunca insere uma chave que já existe, então uma segunda execução não duplica nada. Só copia o que ainda falta.

Passo 4. A prova: conferir de novo

Notas:

OK  Comparação concluída em 0,1s: 10.000 iguais, 0 faltando no destino, 0 faltando na origem. 0 com valores divergentes.

Itens:

OK  Comparação concluída em 0,4s: 30.000 iguais, 0 faltando no destino, 0 faltando na origem. 0 com valores divergentes.

O Windows deu as mesmas contagens em todos os passos.

Também contamos dos dois lados, antes e depois:

before  SQL Server invoices / lines: 10000 / 30000   PostgreSQL invoices / lines / invoices with a line missing: 9975 / 29885 / 40
after   SQL Server invoices / lines: 10000 / 30000   PostgreSQL invoices / lines / invoices with a line missing: 10000 / 30000 / 0

"Notas com item faltando" é uma consulta simples na réplica:

SELECT count(*) FROM invoices i
WHERE (SELECT count(*) FROM invoice_lines l WHERE l.invoice_id = i.invoice_id) < 3;

De 40 para 0. Quem prova que acabou é a conferência, não os contadores da carga.

O script

No Linux, os quatro arquivos de job rodam de um script shell com set -euo pipefail. Cada execução espera o resultado e devolve código diferente de zero quando o status não é succeeded. No nosso script de teste os itens são carregados primeiro de propósito, então essa falha é esperada e tratada:

run_job fk-invoices-check
run_job fk-lines-check
run_job fk-lines-load || echo "(failed as expected: the parents are not there yet)"
run_job fk-invoices-load
run_job fk-lines-load
run_job fk-invoices-check
run_job fk-lines-check

No seu script, tire o fk-lines-load antecipado e o || echo. Mantenha a ordem: notas, depois itens, depois as conferências. Aí qualquer falha para o script, e o cron percebe.

No Windows, ponha a mesma sequência num .ps1 e confira o $LASTEXITCODE depois de cada chamada do dbSlice.exe. O Agendador de Tarefas lê o código de saída, então uma execução com falha aparece como tarefa com falha.

A série

"dbSlice CLI no dia a dia", quatro casos pela linha de comando:

  1. Conferir e Completar uma Migração Grande do SQL Server para o PostgreSQL pela Linha de Comando
  2. Colocar em Dia uma Réplica PostgreSQL Depois de uma Queda de Rede, pela Linha de Comando
  3. Corrigir Chaves Estrangeiras Quebradas Depois de Mensagens Perdidas na Fila, pela Linha de Comando (este texto)
  4. Monte uma Base de QA Mascarada a partir de uma Amostra da Produção, pela Linha de Comando

Também pela linha de comando: Copiar Só as Linhas que Faltam do SQL Server para o PostgreSQL pela Linha de Comando.

Fechamento

Mensagens perdidas deixam dois tipos de buraco numa réplica pai/filho: pais que faltam com todos os filhos, e pais com filhos faltando. Uma conferência por tabela mostra os dois, e as chaves dos filhos dizem qual é qual. Depois:

  • Pais antes dos filhos. Carregue os itens primeiro e a chave estrangeira recusa, e nada é gravado.
  • O job insert copia só as chaves que faltam. Rodar de novo não duplica.
  • O código de saída diferente de zero deixa um script ou o cron parar no primeiro erro.
  • A segunda conferência, com zero em tudo, é a prova.

O dbSlice é produto da nossa empresa, então leia com isso em mente. Para testar:

Experimente grátis na Microsoft Store ↗ Ver preços

← Voltar ao blog