Encontrar e corrigir índices de árvore B inconsistentes

Inconsistências nos índices de banco de dados podem ocorrer por vários motivos, incluindo defeitos de software, problemas de hardware ou mudanças no comportamento, como mudanças na ordem de classificação.

A comunidade do PostgreSQL criou ferramentas para identificar e corrigir esses problemas. Isso inclui ferramentas como o amcheck, recomendado pela comunidade do PostgreSQL para identificar problemas de consistência, incluindo problemas apresentados pelas versões anteriores do PostgreSQL 14.

Escrevemos este manual como uma referência para usuários do Cloud SQL para PostgreSQL que enfrentam esses problemas. Esperamos que esta página tenha informações que também possam ajudar outros usuários do PostgreSQL a identificar e corrigir índices inconsistentes de árvore B. Nosso objetivo é melhorar continuamente este documento como um recurso para a comunidade de código aberto mais ampla. Para fazer comentários, use o botão "Enviar feedback" na parte de cima e de baixo da página.

Para resolver as inconsistências de um índice, siga estas etapas:

1. Antes de começar.

   Antes de começar a reindexar, faça backup do seu banco de dados, defina as permissões corretas, verifique a versão do psql do cliente e faça o download da extensão amcheck.

2. Verifique se há índices inconsistentes de árvore B.

   Para identificar os índices em que você precisa corrigir inconsistências, é necessário identificar todos os índices de árvore B com inconsistências e todas as violações de chaves únicas e primárias.

3. Corrija as inconsistências do índice.

   A reindexação de um índice corrige todas as inconsistências dele. Talvez seja necessário ajustar as configurações de memória da instância para melhorar o desempenho.

4. Monitore as operações de reindexação.

   Recomendamos monitorar o progresso da operação de reindexação para garantir que ela esteja em andamento e não seja bloqueada.

5. Verifique se os índices são consistentes.

   Após reindexar com êxito o índice, recomendamos que você verifique se não há inconsistências nele.

Antes de começar

Fazer backup do banco de dados

Para garantir que nenhum dado seja perdido durante a reindexação, recomendamos fazer backup do banco de dados. Para ver mais informações, consulte Criar um backup sob demanda.

Cuidado: um backup feito em um banco de dados que já tem inconsistências de índice ainda terá inconsistências quando for restaurado.

Definir a permissão cloudsqlsuperuser

Para concluir as etapas nesta página, você precisa ter permissões cloudsqlsuperuser. Para saber mais, consulte session_replication_role.

Verifique se a versão do psql do cliente é 9.6 ou mais recente

Para concluir as etapas nesta página, verifique se a versão do psql do cliente é 9.6 ou mais recente. Execute o comando psql --version para verificar a versão atual do psql do cliente.

Instalar a extensão amcheck

Para verificar se há inconsistências de índice, ative a extensão amcheck.

PostgreSQL 9.6

Observação: é necessário garantir que POSTGRES_9_6_24.R20220710.01_12 esteja instalado antes de instalar o amcheck_next. Para mais informações sobre como instalar uma nova versão de manutenção de destino, consulte Manutenção de autoatendimento.

Para instalar o amcheck para PostgreSQL 9.6, execute a seguinte instrução:

  CREATE EXTENSION amcheck_next;
  

Se ocorrer um erro "Não foi possível abrir o arquivo de controle da extensão..." verifique se você está executando a versão de manutenção de destino correta (POSTGRES_9_6_24.R20220710.01_12).

PostgreSQL 10 e versões mais recentes

Para instalar o amcheck para PostgreSQL 10 e versões posteriores, execute a seguinte instrução:

  CREATE EXTENSION amcheck;
  

Verificar se há índices inconsistentes de árvore B

As seções a seguir descrevem como verificar índices de árvore B inconsistentes verificando as inconsistências de um índice e as violações únicas e primárias de chave.

Conferir se há inconsistências

Execute a seguinte instrução para verificar se há inconsistências em todos os índices de árvore B em cada um dos bancos de dados:

Exemplo de código

  DO $$
  DECLARE
    r RECORD;
    version varchar(100);
  BEGIN
    RAISE NOTICE 'Started amcheck on database: %', current_database();
    SHOW server_version into version;
    SELECT split_part(version, '.', 1) into version;
    FOR r IN
      SELECT c.oid, c.oid::regclass relname, i.indisunique
        FROM pg_index i
        JOIN pg_opclass op ON i.indclass[0] = op.oid
        JOIN pg_am am ON op.opcmethod = am.oid
        JOIN pg_class c ON i.indexrelid = c.oid
        JOIN pg_namespace n ON c.relnamespace = n.oid
      WHERE am.amname = 'btree'
        AND c.relpersistence != 't'
        AND c.relkind = 'i'
        AND i.indisready AND i.indisvalid LOOP
      BEGIN
        RAISE NOTICE 'Checking index %:', r.relname;
        IF version = '10' THEN
          PERFORM bt_index_check(index => r.oid);
        ELSE
          PERFORM bt_index_check(index => r.oid, heapallindexed => r.indisunique);
        END IF;
      EXCEPTION
        WHEN undefined_function THEN
          RAISE EXCEPTION 'Failed to find the amcheck extension';
        WHEN OTHERS THEN
          RAISE LOG 'Failed to check index %: %', r.relname, sqlerrm;
          RAISE WARNING 'Failed to check index %: %', r.relname, sqlerrm;
      END;
    END LOOP;
    RAISE NOTICE 'Finished amcheck on database: %', current_database();
  END $$;
  

Será exibida uma saída semelhante à exibida a seguir.

Saída

  NOTICE:  Checking index t_pkey:
  NOTICE:  Checking index t_i_key:
  WARNING:  Failed to check index t_i_key: item order invariant violated for index "t_i_key"
  NOTICE:  Checking index t_j_key:
  WARNING:  Failed to check index t_j_key: item order invariant violated for index "t_j_key"
  NOTICE:  Checking index ij:
  WARNING:  Failed to check index ij: item order invariant violated for index "ij"
  

Aviso: se a saída não tiver avisos WARNING, talvez ainda haja violações de chaves exclusivas ou primárias. Para verificar violações, execute os procedimentos em Identificar e corrigir violações de chaves exclusivas e primárias.

Para mais informações sobre como visualizar registros do PostgreSQL, consulte Ver registros de instância.

Identificar e corrigir violações de chaves exclusivas e principais

Nesta seção, descrevemos como verificar se há violações de chave primária e única no índice e como corrigir as ocorrências encontradas.

Identificar violações de chaves exclusivas

As violações de chave únicas precisam ser corrigidas antes de reindexar um índice. Para verificar se há violações de chave única, execute o seguinte comando em cada banco de dados:

Exemplo de código

  WITH q AS (
      /* this gets info for all UNIQUE indexes */
      SELECT indexrelid::regclass as idxname,
            indrelid::regclass as tblname,
            indcollation,
            pg_get_indexdef(indexrelid),
            format('(%s)',(select string_agg(quote_ident(attname), ', ')
                from pg_attribute a
                join unnest(indkey) ia(nr) on ia.nr = a.attnum
              where attrelid = indrelid)) as idxfields,
            COALESCE(substring(pg_get_indexdef(indexrelid) FROM '[)] (WHERE .*)$'), '') as whereclause
        FROM pg_index
      WHERE indisunique
      /* next line excludes indexes not affected by collation changes */
        AND trim(replace(indcollation::text, '0', '')) != ''
  )
  SELECT
  /* the format constructs the query to execute for each index */
  format(
  $sql$
  DO $$ BEGIN RAISE NOTICE 'checking index=%3$I    on   table=%1$I      key_columns=%2$I '; END;$$;
  SELECT this,
        prev,
        /* we detect both reversed ordering or just not unique */
        (CASE WHEN this = prev THEN 'DUPLICATE' ELSE 'BACKWARDS' END) as violation_type
    FROM (SELECT %2$s AS this,
                lag(%2$s) OVER (ORDER BY %2$s) AS prev
            FROM %1$s %4$s
          ) s
  WHERE this <= prev and this IS NOT NULL and prev IS NOT NULL; /* change to just '<' if looking for reverse order in index */
  $sql$, tblname, idxfields, idxname, whereclause
  )
    FROM q
  -- LIMIT 20 /* may use limit for testing */
  -- the next line tells psql to executes this query and then execute each returned line separately
  \gexec
  

A saída deste script é semelhante a:

Saída

  NOTICE:  checking index=users_email_key on table=users key_columns="(email)"
  NOTICE:  checking index=games_title_key on table=games  key_columns="(title)"
          this        |        prev        | violation_type
  --------------------+--------------------+----------------
  Game #16 $soccer 2  | Game #16 $soccer 2 | DUPLICATE
  Game #18 $soccer 2  | Game #18 $soccer 2 | DUPLICATE
  Game #2 $soccer 2   | Game #2 $soccer 2  | DUPLICATE
  Game #5 $soccer 2   | Game #5 $soccer 2  | DUPLICATE
  

Nesta saída, o cabeçalho da tabela NOTICE mostra abaixo de si o índice, a coluna e a tabela dos valores. Se a saída contiver linhas que exibem DUPLICATE ou BACKWARDS, haverá corrupção no índice e ela poderá precisar ser corrigida. As linhas com BACKWARDS indicam possíveis valores duplicados que podem estar ocultos. Se você vir uma dessas entradas na tabela, consulte Corrigir violações de chaves duplicadas.

Corrigir violações de chaves duplicadas

Se você tiver identificado um índice único duplicado ou ocorrer uma falha em uma operação de reindexação devido a um erro de violação de chave duplicada, siga as seguintes etapas para localizar e remover as chaves duplicadas.

Observação: é preciso repetir as etapas a seguir para cada índice que tiver violações de chave.

1. Extraia key_columns do cabeçalho da tabela NOTICE, conforme mostrado no exemplo de saída anterior. No exemplo a seguir, a coluna de chaves é email.

   Exemplo de código

     NOTICE:  checking index=users_email_key on table=users key_columns="(email)"

   Use esses valores no KEY_COLUMNS na consulta da etapa 3.

2. Encontre o esquema da sua tabela. Use psql para se conectar ao banco de dados e execute o seguinte comando:

   Exemplo de código

   \dt TABLE_NAME

   O valor na coluna schema é o valor usado para SCHEMA_NAME na consulta da etapa 3.

   Por exemplo, para a seguinte consulta:

    \dt games
    

   O resultado será assim:

    List of relations
    Schema  | Name  | Type  | Owner
    --------+-------+-------+----------
    public  | games | table | postgres
    (1 row)
    

3. Execute as instruções a seguir para forçar uma verificação completa da tabela e receber chaves duplicadas.

   Exemplo de código

   SET enable_indexscan = off;
   SET enable_bitmapscan = off;
   SET enable_indexonlyscan = off;
   
   SELECT KEY_COLUMNS, count(*)
     FROM SCHEMA_NAME.TABLE_NAME
   GROUP BY KEY_COLUMNS
   HAVING count(*) > 1;

   Observação: se o banco de dados for grande, essa instrução poderá levar muito tempo para ser concluída.

   Na instrução acima, KEY_COLUMNS são uma ou mais colunas cobertas pelo índice único ou pela chave primária na tabela que você está verificando. Elas foram identificadas quando você verificou violações de chaves únicas. A instrução retorna as chaves duplicadas e uma contagem das cópias para cada uma.

   Por exemplo, para a seguinte consulta:

     SELECT name,count(*)
       FROM public.TEST_NAMES
     GROUP BY name
     HAVING count(*) > 1;
     

   O resultado será assim:

     name                | count
     --------------------+-------
     Johnny              |     2
     Peter               |     2
     (2 rows)
     

   Nesse caso, siga para a próxima etapa e remova as chaves duplicadas.

   Se alguma das colunas em KEY_COLUMNS for nula, ignore-a porque as restrições únicas não se aplicam a colunas NULL.

   Se nenhuma chave duplicada for encontrada, acesse Corrigir índices inconsistentes.

4. Opcional, mas recomendado: crie um backup para os registros que contêm chaves duplicadas. Execute a seguinte instrução para criar registros de backup:

   Exemplo de código

     CREATE TABLE SCHEMA_NAME.TABLE_NAME_bak
     AS SELECT * FROM SCHEMA_NAME.TABLE_NAME
     WHERE (KEY_COLUMNS)
     IN ((KEY_VALUES));
     

   Nessa instrução, KEY_VALUES é uma lista de valores copiados do resultado da etapa anterior. Exemplo:

   Exemplo de código

     CREATE TABLE public.TEST_NAMES_bak
     AS SELECT * FROM public.TEST_NAMES
     WHERE (name) IN (('Johnny'),('Peter'))
     

   Para um grande número de linhas, é mais fácil substituir o parâmetro ((KEY_VALUES)) na instrução IN com a instrução SELECT da etapa 2 sem o parâmetro count. Por exemplo:

   Exemplo de código

     CREATE TABLE SCHEMA_NAME.TABLE_NAME_bak
     AS SELECT * FROM SCHEMA_NAME.TABLE_NAME
     WHERE (KEY_COLUMNS)
     IN ( SELECT (KEY_COLUMNS)
     FROM SCHEMA_NAME.TABLE_NAME
     GROUP BY (KEY_COLUMNS)
     HAVING count(*) > 1);
     

5. Adicione um papel de replicação ao usuário para desativar os gatilhos:

   Exemplo de código

     ALTER USER CURRENT_USER with REPLICATION;
     SET session_replication_role = replica;
     

6. Execute a seguinte instrução para excluir as chaves duplicadas:

   Exemplo de código

     BEGIN;
   
     DELETE FROM  SCHEMA_NAME.TABLE_NAME a
         USING  (
                 SELECT   min(ctid) AS ctid,
                         KEY_COLUMNS
                 FROM     SCHEMA_NAME.TABLE_NAME
                 GROUP BY KEY_COLUMNS
                         HAVING count(*) > 1 ) b
         WHERE a.KEY_COLUMNS = b.KEY_COLUMNS
         AND   a.ctid <> b.ctid;
     

   Por exemplo, para KEY_COLUMNS de várias colunas:

   Exemplo de código

     DELETE FROM public.test_random a
         USING (
                SELECT min(ctid) AS ctid,
                day, rnum
         FROM public.test_random
         GROUP BY day, rnum
                HAVING count(*) > 1 ) b
         WHERE a.day=b.day and a.rnum = b.rnum
         AND a.ctid <> b.ctid;
     

   Em que dia e rnum são KEY_COLUMNS.

   A execução dessa instrução mantém uma linha e exclui outras para cada conjunto de linhas duplicadas. Para controlar qual versão da linha é excluída, execute o seguinte filtro na instrução de exclusão:

   Exemplo de código

     DELETE FROM  SCHEMA_NAME.TABLE_NAME
     WHERE ( KEY_COLUMNS, ctid) = (KEY_VALUES, CTID_VALUE);
     

   Aviso: a confirmação dessa transação altera o banco de dados e remove as chaves duplicadas. Não exclua os dados que ainda forem necessários. A restauração de um backup pode restaurar os índices inconsistentes.

7. Conclua as etapas a seguir para verificar se o comando DELETE retornou o número esperado de linhas sem erros:

   1. Execute a seguinte instrução para identificar as linhas em que as tabelas foram alteradas:

      Exemplo de código

        SELECT schemaname, relname, n_tup_del, n_tup_upd
          FROM pg_stat_xact_all_tables
        WHERE n_tup_del+n_tup_upd > 0;
        

   2. Se todas as linhas estiverem corretas, confirme a transação DELETE:

      Exemplo de código

        END;
        

   3. Se houver erros, reverta as alterações para corrigi-los:

      Exemplo de código

        ROLLBACK;
        

8. Após a exclusão das chaves duplicadas, será possível reindexar o índice.

Corrigir índices inconsistentes

Veja nas seções a seguir como corrigir as inconsistências de índice encontradas na instância.

Observação: se o índice tiver violações de chaves primárias e únicas, corrija-as antes da reindexação.

Dependendo de como o banco de dados está configurado, pode ser necessário fazer o seguinte para cada índice identificado nas etapas anteriores:

1. Preparar-se para reindexar o índice.

2. Reindexar o índice.

3. Se a operação de reindexação falhar devido a violações de chave externa, será necessário encontrar e corrigir essas violações.

4. Executar a operação de reindexação novamente.

Preparar-se para indexar novamente o índice

Encontrar o tamanho do índice

Os bancos de dados maiores exigem mais tempo do que os menores para a indexação. Para melhorar a velocidade da indexação e da reindexação das operações de bancos de dados maiores, aloque mais memória e CPU para essas operações. Essa é uma etapa importante no planejamento da operação de reindexação. Depois de saber o tamanho do índice, é possível definir o tamanho da memória usado pela operação de reindexação e definir o número de workers paralelos.

Execute a seguinte instrução para encontrar o tamanho, em kilobytes, do índice que você quer corrigir:

Exemplo de código

  SELECT i.relname                                      AS index_name,
        pg_size_pretty(pg_relation_size(x.indexrelid)) AS index_size
  FROM   pg_index x
        JOIN pg_class i
          ON i.oid = x.indexrelid
  WHERE  i.relname = 'INDEX_NAME';
  

A saída desta instrução é semelhante a:

Saída

  index_name  | index_size
  ------------+------------
  my_index    | 16 kB
  (1 row)
  

Definir o tamanho da memória a ser usado para reindexação

Com base no tamanho do índice, conforme determinado na seção anterior, é importante definir o valor apropriado para maintenance_work_mem. Esse parâmetro especifica a quantidade de memória a ser usada para a operação de reindexação. Por exemplo, se o tamanho do índice for maior que 15 GB, ajuste a memória de manutenção. Para mais informações, consulte Definir uma sinalização de banco de dados.

Observação: o valor de maintenance_work_mem é definido para cada sessão. Você não precisa reiniciar a instância depois de alterar o valor. No entanto, se você se conectar ao banco de dados novamente em uma nova sessão, o valor da configuração anterior não será salvo.

Os bancos de dados maiores exigem mais tempo do que os menores para a indexação. Para aumentar a velocidade das operações de indexação e reindexação, defina maintenance_work_mem como pelo menos 2% da memória para instâncias com 4 GB ou mais de memória durante essa operação de reindexação.

Definir o número de workers paralelos

É possível aumentar o número de workers paralelos para reindexação definindo o parâmetro max_parallel_maintenance_workers nos bancos de dados usando o PostgreSQL 11 ou mais recente. O valor padrão desse parâmetro é 2, mas ele pode ser definido mais alto para aumentar o número de workers relacionados à reindexação. Para instâncias com oito ou mais núcleos de vCPU, recomendamos definir o valor da sinalização max_parallel_maintenance_workers como quatro.

Para mais informações, consulte Definir uma sinalização de banco de dados.

Reindexar o índice

É possível reindexar um índice sem bloquear a carga de trabalho de produção usando o utilitário pg_repack. Ele automatiza e simplifica o processo de reindexação simultânea, permitindo a reindexação sem inatividade, especialmente para o PostgreSQL 11 e versões anteriores, que não têm a operação REINDEX CONCURRENTLY. Para esse procedimento, use a versão 1.4.7 de pg_repack.

Conclua as etapas a seguir para indexar o índice novamente usando pg_repack:

1. Faça o download, compile e instale o utilitário pg_repack na página pg_repack.

   Debian GNU/Linux 11

   Por conveniência, recomendamos aos usuários do Debian Linux fazer o download e instalar este binário executável pré-criado para a plataforma Linux x86_64.

   O hash da soma de verificação sha256 do binário é o seguinte:

   ecfee54364a625d9365d86cb27940b458bfdb0d6ff63bb88063039256fbde96f

   Para verificar se a versão do Linux é Debian GNU/Linux 11, execute o comando hostnamectl.

   Autocompilação

   Observação: para este procedimento, execute o comando no terminal do Linux ou Unix com um conjunto de ferramentas GNU funcional.

   Faça o download, compile e instale o utilitário pg_repack na página pg_repack.

2. Crie a extensão pg_repack:

   Exemplo de código

     CREATE EXTENSION pg_repack;
     

3. Execute o comando a seguir para indexar o índice simultaneamente:

   Exemplo de código

     pg_repack -h HOSTIP -p 5432 -U USERNAME -d "DATABASE_NAME" -i "INDEX_NAME" --no-superuser-check --no-kill-backend --wait-timeout=3600
     

   Observação: pg_repack não permite reindexar um índice em uma visualização materializada.

   A saída do comando é parecida com o seguinte:

   Saída

     INFO: repacking index "public.t_i_key"
     

   Se ocorreram erros ao executar pg_repack, corrija-os e tente novamente. Depois de corrigir todos os índices de chaves únicas e primárias, verifique se há violações de chaves externas e corrija-as, se encontradas.

Encontrar e corrigir violações de chaves externas

Para informações sobre como encontrar e corrigir violações de chaves externas, consulte Encontrar e corrigir violações de chaves externas.

Monitorar operações de reindexação

De vez em quando, a operação de reindexação pode ser bloqueada por outras sessões. Recomendamos verificar isso a cada quatro horas. Se a operação de reindexação for bloqueada, cancele a sessão de bloqueio para que ela seja concluída.

Conclua as etapas a seguir para identificar sessões de bloqueio e espera e cancelá-las na operação INDEX:

1. Para identificar sessões de bloqueio, execute a seguinte consulta:

   Exemplo de código

     SELECT pid,
           usename,
           pg_blocking_pids(pid) AS blocked_by,
           query                 AS blocked_query
     FROM   pg_stat_activity
     WHERE  cardinality(pg_blocking_pids(pid)) > 0;
     

2. Para cancelar uma sessão, execute a seguinte consulta usando o PID da sessão de bloqueio da consulta anterior:

   Exemplo de código

     SELECT pg_cancel_backend(PID);
     

Verificar se os índices são consistentes

Você precisa continuar a verificar as inconsistências de cada índice inconsistente. Depois de corrigir todos os índices inconsistentes e as violações de chave da instância, verifique se não há problemas seguindo as etapas nas seções anteriores:

• Verificar as inconsistências de um índice

• Verificar se há violações de chaves únicas