Questa pagina è stata tradotta dall'API Cloud Translation.

Trovare e correggere gli indici B-tree incoerenti

Possono verificarsi incoerenze negli indici dei database per diversi motivi, tra cui difetti software, problemi hardware o modifiche sottostanti nel comportamento quali modifiche all'ordinamento.

La community PostgreSQL ha creato strumenti per identificare e risolvere questi problemi. Sono inclusi strumenti come amcheck, consigliato dalla community PostgreSQL per identificare i problemi di coerenza, inclusi i problemi riscontrati nelle versioni precedenti di PostgreSQL 14.

Abbiamo scritto questo playbook come riferimento per gli utenti di Cloud SQL per PostgreSQL che riscontrano questi problemi. Ci auguriamo che questa pagina fornisca informazioni che possano essere utili anche ad altri utenti PostgreSQL per identificare e correggere gli indici b-tree incoerenti. Il nostro obiettivo è migliorare continuamente questo documento come risorsa per la più ampia community open source. Se hai feedback, usa Pulsante Invia feedback nella parte superiore e inferiore di questa pagina.

Per risolvere le incoerenze di un indice, segui questi passaggi:

Prima di iniziare.

Prima di iniziare la reindicizzazione, devi eseguire il backup del database, impostare le autorizzazioni corrette, verifica la versione del client psql e scarica amcheck.
Verifica la presenza di indici B-tree incoerenti.

Per identificare gli indici per i quali devi correggere le incoerenze, devi: identificare tutti gli indici B-tree con incoerenze e identificare tutti gli indici univoci e violazioni chiave primaria.
Correggi le incoerenze dell'indice.

La ricorsione di un indice corregge tutte le sue incoerenze. Puoi e devi regolare le impostazioni della memoria dell'istanza per migliorare le prestazioni.
Monitorare le operazioni di reindicizzazione.

Ti consigliamo di monitorare l'avanzamento dell'operazione di indicizzazione per verificare che stia procedendo e non sia bloccata.
Verifica che gli indici siano coerenti.

Dopo aver indicizzato correttamente l'indice, ti consigliamo di verificare che non contenga incoerenze.

Prima di iniziare

Esegui il backup del database

Per assicurarti che non vengano persi dati durante la reindicizzazione, ti consigliamo di eseguire il backup del database. Per ulteriori informazioni, vedi Creare un backup on demand.

Imposta l'autorizzazione `cloudsqlsuperuser`

Per completare i passaggi in questa pagina, devi avere cloudsqlsuperuser autorizzazioni aggiuntive. Per ulteriori informazioni, consulta session_replication_role.

Assicurati che la versione del client `psql` sia 9.6 o successiva

Per completare la procedura indicata in questa pagina, devi assicurarti che il cliente psql sia la versione 9.6 o successive. Esegui il comando psql --version per verificare la versione corrente del client psql.

Installa l'estensione amcheck

Per verificare la presenza di incoerenze nell'indice, devi attivare l'estensione amcheck.

PostgreSQL 9.6

Nota: devi assicurarti che POSTGRES_9_6_24.R20220710.01_12 sia installato prima di installare amcheck_next. Per maggiori informazioni informazioni sull'installazione di una nuova versione di manutenzione di destinazione, consulta Manutenzione self-service.

Per installare amcheck per PostgreSQL 9.6, esegui la seguente istruzione:

  CREATE EXTENSION amcheck_next;

Se viene visualizzato il messaggio di errore "Impossibile aprire il file di controllo dell'estensione…", verifica di utilizzare la versione di manutenzione target corretta (POSTGRES_9_6_24.R20220710.01_12).

PostgreSQL 10 e versioni successive

Per installare amcheck per PostgreSQL 10 e versioni successive, esegui la seguente dichiarazione:

  CREATE EXTENSION amcheck;

Verifica la presenza di indici B-tree incoerenti

Le seguenti sezioni descrivono come verificare la presenza di indici B-tree incoerenti in base a verificare la presenza di incongruenze di un indice e di violazioni chiave primaria e univoche.

Verificare la presenza di incoerenze

Esegui la seguente istruzione per verificare la presenza di incoerenze in tutti gli indici B-tree di ciascun database:

Esempio di codice

  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 $$;

Dovresti ricevere un output simile al seguente:

Output

  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"

Per saperne di più sulla visualizzazione dei log PostgreSQL, consulta Visualizza i log dell'istanza.

Identificare e correggere le violazioni delle chiavi univoche e primarie

Questa sezione descrive come controllare l'indice per verificare la presenza di violazioni delle chiavi univoche e principali e, se esistono, come correggerle.

Identifica violazioni delle chiavi univoche

Le violazioni delle chiavi univoche devono essere corrette prima di reindicizzare un indice. Per verificare tutte le violazioni delle chiavi univoche, esegui il comando seguente in ogni database:

Esempio di codice

  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

L'output dello script è simile al seguente:

Output

  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

In questo output, l'intestazione della tabella NOTICE mostra l'indice, la colonna e la tabella per i valori visualizzati sotto. Se l'output contiene righe che mostrano DUPLICATE o BACKWARDS, l'indice è danneggiato e potrebbe essere necessario da correggere. Le righe con BACKWARDS indicano possibili valori duplicati che potrebbe essere nascosto. Se nella tabella sono presenti una di queste voci, consulta Correggere le violazioni delle chiavi duplicate.

Correggi le violazioni delle chiavi duplicate

Se hai identificato un indice univoco duplicato o se un'operazione di reindicizzazione non riesce a causa di un errore di violazione della chiave duplicata, completa la procedura seguente per trovare e rimuovi le chiavi duplicate.

Estrai key_columns dall'intestazione della tabella NOTICE, come mostrato nell'output di esempio precedente. Nell'esempio seguente, la colonna chiave è email.
Esempio di codice
```
  NOTICE:  checking index=users_email_key on table=users key_columns="(email)"
```
Utilizza questi valori in KEY_COLUMNS nella query del passaggio 3.
Trova lo schema per la tua tabella. Usa psql per connetterti al tuo ed esegui questo comando:
Esempio di codice
```
\dt TABLE_NAME
```
Il valore nella colonna schema è il valore che utilizzi per SCHEMA_NAME nella query del passaggio 3.

Ad esempio, per la seguente query:
```
 \dt games
 
```
L'output è simile al seguente:
```
 List of relations
 Schema  | Name  | Type  | Owner
 --------+-------+-------+----------
 public  | games | table | postgres
 (1 row)
 
```
Esegui le seguenti istruzioni per forzare una scansione completa della tabella e ottenere duplicati chiave.
Esempio di codice
```
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;
```
Nota: se il tuo database è di grandi dimensioni, questa istruzione potrebbe richiedere molto tempo completato.

Nell'istruzione precedente, KEY_COLUMNS sono una o più colonne coperte dall'indice univoco o dalla chiave primaria nella tabella che stai controllando. che sono stati identificati quando hai verificato la presenza di violazioni delle chiavi univoche. L'istruzione restituisce le chiavi duplicate e un conteggio dei duplicati per ciascuna.

Ad esempio, per la seguente query:
```
  SELECT name,count(*)
    FROM public.TEST_NAMES
  GROUP BY name
  HAVING count(*) > 1;
  
```
L'output è simile al seguente:
```
  name                | count
  --------------------+-------
  Johnny              |     2
  Peter               |     2
  (2 rows)
  
```
In questo caso, vai al passaggio successivo per rimuovere le chiavi duplicate.

Se alcune colonne in KEY_COLUMNS sono null, puoi ignorare poiché i vincoli univoci non si applicano alle colonne NULL.

Se non vengono trovate chiavi duplicate, puoi passare a Correggi gli indici incoerenti.

(Facoltativo, ma consigliato) Crea un backup per i record contenenti chiavi duplicate. Esegui questa istruzione per creare record di backup:

Esempio di codice

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

In questa istruzione, KEY_VALUES è un elenco di valori copiati dal risultato del passaggio precedente. Ad esempio:

Esempio di codice

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

Per un numero elevato di righe, è più facile sostituire il valore ((KEY_VALUES)) nell'istruzione IN con SELECT dell'istruzione del passaggio 2 senza il parametro count. Ad esempio:

Esempio di codice

  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);

Aggiungi un ruolo di replica all'utente per disattivare gli attivatori:

Esempio di codice

  ALTER USER CURRENT_USER with REPLICATION;
  SET session_replication_role = replica;

Esegui questa istruzione per eliminare le chiavi duplicate:

Esempio di codice

  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;

Ad esempio, per KEY_COLUMNS a più colonne:

Esempio di codice

  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;

dove day e rnum sono KEY_COLUMNS.

L'esecuzione di questa istruzione conserva una riga ed elimina le altre per ogni insieme di righe duplicate. Se vuoi controllare quale versione della riga ottiene elimina, esegui il filtro seguente nell'istruzione delete:

Esempio di codice

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

Completa i seguenti passaggi per verificare che il comando DELETE abbia restituito il numero di righe previsto senza errori:
1. Esegui la seguente istruzione per identificare le righe in cui le tabelle sono state modificate:
  Esempio di codice
  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 tutte le righe sono corrette, esegui il commit della transazione DELETE:
  Esempio di codice
  END;
3. In caso di errori, esegui il rollback delle modifiche per correggerli:
  Esempio di codice
  ROLLBACK;
Dopo aver eliminato le chiavi duplicate, puoi reindicizzare l'indice.

Correggi gli indici incoerenti

Le sezioni seguenti descrivono come correggere le incoerenze dell'indice rilevate nell'istanza.

A seconda della configurazione del database, potresti dover eseguire le seguenti operazioni per ogni indice identificato nei passaggi precedenti:

Preparati a reindicizzare l'indice.
Reindicizzazione dell'indice.
Se l'operazione di indicizzazione non riesce a causa di violazioni delle chiavi esterne, devi trovare e correggere queste violazioni.
Esegui di nuovo l'operazione di reindicizzazione.

Preparati a reindicizzare il tuo indice

Trovare la dimensione dell'indice

L'indicizzazione di database più grandi richiede più tempo rispetto a quelli più piccoli. Per migliorare la velocità delle operazioni di indicizzazione e ridefinizione dell'indice di database più grandi, puoi allocare più memoria e potenza della CPU a queste operazioni. Si tratta di un passaggio importante pianificare l'operazione di reindicizzazione. Dopo aver conosciuto le dimensioni dell'indice, puoi impostare le dimensioni della memoria utilizzata dall'operazione di indicizzazione e impostare il numero di worker in parallelo.

Esegui la seguente istruzione per trovare la dimensione dell'indice, in kilobyte, da correggere:

Esempio di codice

  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';

L'output di questa istruzione è simile al seguente:

Output

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

Imposta la dimensione della memoria da utilizzare per la ricorsione

In base alle dimensioni dell'indice determinate nella sezione precedente, è è importante impostare il valore appropriato maintenance_work_mem Questo parametro specifica la quantità di memoria da utilizzare per l'operazione di indicizzazione. Ad esempio, se la dimensione dell'indice è superiore a 15 GB, ti consigliamo di modificare la memoria di manutenzione. Per maggiori informazioni consulta Impostare un flag di database.

L'indicizzazione di database più grandi richiede più tempo rispetto a quella di database più piccoli. A migliorare la velocità delle operazioni di indicizzazione e indicizzazione, consigliamo di impostare da maintenance_work_mem ad almeno il 2% della memoria dell'istanza per le istanze con 4 GB o più di memoria durante l'operazione di reindicizzazione.

Imposta il numero di worker paralleli

Puoi aumentare il numero di worker paralleli per la ricorsione impostando il parametro max_parallel_maintenance_workers nei database che utilizzano PostgreSQL 11 o versioni successive. Il valore predefinito di questo parametro è 2, ma può essere impostato su un valore superiore per aumentare il numero di worker per la ricorsione. Per le istanze con 8 o più core vCPU, consigliamo di impostare il valore del flag max_parallel_maintenance_workers su 4.

Per ulteriori informazioni, consulta Impostare un flag del database.

Esegui nuovamente l'indicizzazione dell'indice

Puoi eseguire nuovamente l'indicizzazione di un indice senza bloccare il carico di lavoro di produzione utilizzando l'utilitàpg_repack. Questa utilità automatizza e semplifica la gestione processo di reindicizzazione, che consente di farlo senza tempi di inattività, in particolare per PostgreSQL 11 e versioni precedenti, che non includono Operazione REINDEX CONCURRENTLY. Per questa procedura, utilizza la pg_repack versione 1.4.7.

Completa i seguenti passaggi per reindicizzare il tuo indice utilizzando pg_repack:

Scarica, compila e installa l'utilità pg_repack dalla pagina pg_repack.

Debian GNU/Linux 11

Per comodità, consigliamo agli utenti di Debian Linux di scaricare e installare questo file eseguibile precompilato per la piattaforma Linux x86_64.

L'hash di checksum sha256 del file binario è il seguente:

ecfee54364a625d9365d86cb27940b458bfdb0d6ff63bb88063039256fbde96f

Per verificare che la tua versione Linux sia Debian GNU/Linux 11, esegui il comando hostnamectl.

Autocompilazione

Nota: per questa procedura, devi eseguire il comando nel terminale su Linux o Unix con una toolchain GNU funzionante.

Scarica, compila e installa l'utilità pg_repack dal pg_repack alla pagina di destinazione.
Crea l'estensione pg_repack:
Esempio di codice
```
  CREATE EXTENSION pg_repack;
  
```
Esegui questo comando per reindicizzare l'indice contemporaneamente:
Esempio di codice
```
  pg_repack -h HOSTIP -p 5432 -U USERNAME -d "DATABASE_NAME" -i "INDEX_NAME" --no-superuser-check --no-kill-backend --wait-timeout=3600
  
```
Nota: pg_repack non consente di reindicizzare un indice su una vista materializzata.

Questo comando ha un output simile al seguente:
Output
```
  INFO: repacking index "public.t_i_key"
  
```
Se si sono verificati errori durante l'esecuzione di pg_repack, puoi correggerli e riprovare. Dopo aver corretto tutti gli indici di chiavi univoci indici di chiave primaria, devi verificare la presenza di chiave esterna esterne e correggi gli errori rilevati.

Trova e correggi le violazioni delle chiave esterna

Per informazioni su come trovare e correggere le violazioni delle chiave esterna, consulta Individua e correggi le chiave esterna esterne.

Monitora le operazioni di indicizzazione nuovamente

A volte, l'operazione di reindicizzazione potrebbe essere bloccata da altre sessioni. Me ti consigliamo di controllare ogni 4 ore. Se l'operazione di reindicizzazione bloccato, puoi annullare la sessione di blocco in modo che l'operazione di reindicizzazione possa completato.

Completa i seguenti passaggi per identificare le sessioni di blocco e di attesa, dopodiché annullale nell'operazione INDEX:

Per identificare le sessioni di blocco, esegui questa query:

Esempio di codice

  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;

Per annullare una sessione, esegui la seguente query utilizzando il PID della sessione di blocco della query precedente:
Esempio di codice
```
  SELECT pg_cancel_backend(PID);
  
```

Verificare la coerenza degli indici

Devi continuare a verificare la presenza di incoerenze dell'indice per ogni indice incoerente. Dopo aver corretto tutti gli indici e la chiave incoerenti dell'istanza violazioni, puoi verificare che non ci siano problemi seguendo la procedura sezioni precedenti:

Verificare la presenza di incoerenze di un indice
Verifica la presenza di violazioni delle chiavi univoche

Trovare e correggere gli indici B-tree incoerenti

Prima di iniziare

Esegui il backup del database

Imposta l'autorizzazione cloudsqlsuperuser

Assicurati che la versione del client psql sia 9.6 o successiva

Installa l'estensione amcheck

PostgreSQL 9.6

PostgreSQL 10 e versioni successive

Verifica la presenza di indici B-tree incoerenti

Verificare la presenza di incoerenze

Esempio di codice

Output

Identificare e correggere le violazioni delle chiavi univoche e primarie

Identifica violazioni delle chiavi univoche

Esempio di codice

Output

Correggi le violazioni delle chiavi duplicate

Esempio di codice

Esempio di codice

Esempio di codice

Esempio di codice

Esempio di codice

Esempio di codice

Esempio di codice

Esempio di codice

Esempio di codice

Esempio di codice

Esempio di codice

Esempio di codice

Esempio di codice

Correggi gli indici incoerenti

Preparati a reindicizzare il tuo indice

Trovare la dimensione dell'indice

Esempio di codice

Output

Imposta la dimensione della memoria da utilizzare per la ricorsione

Imposta il numero di worker paralleli

Esegui nuovamente l'indicizzazione dell'indice

Debian GNU/Linux 11

Autocompilazione

Esempio di codice

Esempio di codice

Output

Trova e correggi le violazioni delle chiave esterna

Monitora le operazioni di indicizzazione nuovamente

Esempio di codice

Esempio di codice

Verificare la coerenza degli indici

Imposta l'autorizzazione `cloudsqlsuperuser`

Assicurati che la versione del client `psql` sia 9.6 o successiva