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 AlloyDB 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:
-
Antes de começar a reindexação, faça backup do seu banco de dados, defina as permissões corretas, verifique a versão do cliente
psql
e ative a extensãoamcheck
. 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.
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.
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.
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 dos dados do cluster do AlloyDB
Para garantir que nenhum dado seja perdido durante a reindexação, recomendamos fazer backup dos dados do cluster. Para ver mais informações, consulte Criar um backup sob demanda.
Definir a permissão alloydbsuperuser
Para concluir as etapas nesta página, você precisa ter permissões
alloydbsuperuser
. Para mais informações, consulte Funções predefinidas do PostgreSQL do AlloyDB.
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.
Ativar a extensão amcheck
Para verificar se há inconsistências de índice, ative a extensão amcheck
.
Exemplo de código
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 em cada um dos bancos de dados para verificar se há inconsistências em todos os índices de árvore B:
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; PERFORM bt_index_check(index => r.oid, heapallindexed => r.indisunique); 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.
Exemplo de código
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"
Para mais informações sobre como visualizar registros, consulte Ver registros usando o Explorador de registros.
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%2$I on table %1$I %4$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ê encontrar 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 se uma operação de reindexação falhar devido a um erro de violação de chave duplicada, siga as etapas abaixo para encontrar e remover as chaves duplicadas.
Extraia
key_columns
do cabeçalho da tabelaNOTICE
, conforme mostrado no exemplo de saída anterior. No exemplo a seguir, a coluna de chaves éemail
.Saída
NOTICE: checking index=users_email_key on table=users key_columns="(email)"
Use esses valores no KEY_COLUMNS na consulta da etapa 3.
Encontre o esquema da sua tabela. Use
psql
para se conectar ao banco de dados e execute o seguinte comando:Exemplo de código
O valor na coluna\dt TABLE_NAME
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)
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;
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.
Opcional, mas recomendado: crie um backup para os registros que contêm chaves duplicadas. Execute a seguinte instrução para criar registros de backup:
Nessa instrução, KEY_VALUES é uma lista de valores copiados do resultado da etapa anterior. Por exemplo:Exemplo de código
CREATE TABLE SCHEMA_NAME.TABLE_NAME_bak AS SELECT * FROM SCHEMA_NAME.TABLE_NAME WHERE (KEY_COLUMNS) IN (KEY_VALUES);
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çãoSELECT
da etapa 2 sem o parâmetrocount
. 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);
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;
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
Em que dia e rnum são KEY_COLUMNS.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;
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);
Conclua as etapas a seguir para verificar se o comando
DELETE
retornou o número esperado de linhas sem erros: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;
Se todas as linhas estiverem corretas, confirme a transação
DELETE
:Exemplo de código
END;
Se houver erros, reverta as alterações para corrigi-los:
Exemplo de código
ROLLBACK;
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.
Dependendo de como o banco de dados está configurado, pode ser necessário fazer o seguinte para cada índice identificado nas etapas anteriores:
Se a operação de reindexação falhar devido a violações de chave externa, será necessário encontrar e corrigir essas violações.
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 o
parâmetro de configuração
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.
O exemplo a seguir mostra como definir maintenance_work_mem
:
Exemplo de código
SET maintenance_work_mem TO "1GB";
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 de configuração max_parallel_maintenance_workers
no banco de dados. 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 4.
Confira a seguir como identificar os valores definidos para esses parâmetros:
Exemplo de código
SHOW max_parallel_maintenance_workers; SHOW max_worker_processes; SHOW max_parallel_workers;
O parâmetro max_parallel_maintenance_workers
é um subconjunto de
max_worker_processes
e limitado por
max_parallel_workers
. Se você precisar de mais workers em paralelo,
aumente o valor de max_worker_processes
e
max_parallel_workers
.
O exemplo a seguir mostra como definir max_parallel_maintenance_workers
:
Exemplo de código
SET max_parallel_maintenance_workers TO 4;
O parâmetro max_parallel_maintenance_workers
não garante a
alocação de workers. Para confirmar que você tem vários workers paralelos
iniciada pela reindexação, execute a consulta a seguir em outra sessão depois
de iniciar a reindexação:
Exemplo de código
SELECT leader.leader_pid,leader.pid "worker_pid",leader.query FROM pg_stat_activity leader, pg_stat_activity worker WHERE leader.leader_pid = worker.pid;
Reindexar o índice
É possível reindexar um índice sem bloquear a carga de trabalho de produção usando o
utilitário pg_repack
. Esse utilitário automatiza e simplifica o processo de reindexação
simultânea, permitindo a reindexação sem inatividade. Para este procedimento, use a
versão 1.4.7 de pg_repack
.
Conclua as etapas a seguir para indexar o índice novamente usando pg_repack
:
Faça o download, compile e instale o utilitário
pg_repack
na página pg_repack.Crie a extensão
pg_repack
:Exemplo de código
CREATE EXTENSION pg_repack;
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
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:
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;
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: