Le incoerenze negli indici del database possono verificarsi per vari motivi, tra cui difetti del software, problemi hardware o modifiche al comportamento sottostante, ad esempio 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 problemi di coerenza, inclusi quelli riscontrati nelle versioni precedenti di PostgreSQL 14.
Questo playbook è un riferimento per gli utenti di Cloud SQL che riscontrano questi problemi di coerenza. Il playbook fornisce informazioni che aiutano gli utenti di PostgreSQL a identificare e correggere gli indici incoerenti.
La risoluzione delle incoerenze di un indice prevede i seguenti passaggi:
-
Prima di iniziare la reindicizzazione, devi eseguire il backup del database, impostare le autorizzazioni corrette, verificare la versione del client
psql
e scaricare l'estensioneamcheck
. Controlla la presenza di indici incoerenti.
Un'istruzione identifica le violazioni di chiavi uniche e primarie, mentre un'altra istruzione rileva una serie di altre incongruenze.
Correggi le incongruenze dell'indice.
La reindicizzazione di un indice corregge tutte le relative incoerenze. Potresti dover modificare le impostazioni di memoria dell'istanza per migliorare le prestazioni.
Monitora le operazioni di reindicizzazione.
Ti consigliamo di monitorare l'avanzamento dell'operazione di reindicizzazione per assicurarti che stia procedendo e non sia bloccata.
Verifica che gli indici siano coerenti.
Dopo aver reindicizzato correttamente l'indice, ti consigliamo di verificare che non contenga incongruenze.
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 disporre delle autorizzazioni cloudsqlsuperuser
. Per maggiori informazioni, consulta session_replication_role.
Assicurati che la versione del client psql
sia 9.6 o successive.
Per completare i passaggi descritti in questa pagina, devi assicurarti che la versione del client psql
sia 9.6 o successive. Esegui il comando psql --version
per verificare la versione attuale del client psql
.
Installare l'estensione amcheck
Per verificare la presenza di incoerenze nell'indice, devi abilitare l'estensione amcheck
.
PostgreSQL 9.6
Per installare amcheck
per PostgreSQL 9.6, esegui la seguente istruzione:
CREATE EXTENSION amcheck_next;
Se viene visualizzato l'errore "Impossibile aprire il file di controllo dell'estensione…", verifica di eseguire la versione di manutenzione di destinazione 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 istruzione:
CREATE EXTENSION amcheck;
Verifica la presenza di indici incoerenti
Le sezioni seguenti descrivono come verificare la presenza di indici incoerenti controllando le incoerenze di un indice, nonché le violazioni di chiavi univoche e primarie.
Verificare la presenza di incongruenze
In ogni database, esegui la seguente istruzione per verificare la presenza di incoerenze:
Esempio di codice
DO $$ DECLARE r RECORD; version varchar(100); BEGIN RAISE NOTICE 'Started relhasindex validation on database: %', current_database(); FOR r IN SELECT indexrelid::regclass relname FROM pg_index WHERE indrelid NOT IN (SELECT oid FROM pg_class WHERE relhasindex) LOOP RAISE LOG 'Failed to check index %: %', r.relname, 'relhasindex is false, want true'; RAISE WARNING 'Failed to check index %: %', r.relname, 'relhasindex is false, want true'; END LOOP; RAISE NOTICE 'Finished relhasindex validation on database: %', current_database(); RAISE NOTICE 'Started b-tree 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 b-tree amcheck on database: %', current_database(); END $$;
Dovresti ricevere un output simile al seguente:
Output
WARNING: Failed to check index t_i_key: relhasindex is false, want true 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 di PostgreSQL, consulta Visualizzare i log delle istanze.
Identificare e correggere le violazioni di chiavi uniche e primarie
Questa sezione descrive come controllare l'indice per rilevare violazioni di chiavi univoche e primarie e, se presenti, come risolverle.
Identificare le violazioni univoche delle chiavi
Le violazioni della chiave univoca devono essere corrette prima di reindicizzare un indice. Per verificare tutte le violazioni di chiavi univoche, esegui questo comando 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
, significa che l'indice è danneggiato e potrebbe essere necessario
risolvere il problema. Le righe con BACKWARDS
indicano possibili valori duplicati che
potrebbero essere nascosti. Se vedi una di queste voci nella tabella, consulta
Correggere le violazioni delle chiavi duplicate.
Correggere le violazioni della chiave duplicata
Se hai identificato un indice univoco duplicato o se un'operazione di reindicizzazione non va a buon fine a causa di un errore di violazione della chiave duplicata, completa i seguenti passaggi per trovare e rimuovere le chiavi duplicate.
Estrai
key_columns
dall'intestazione della tabellaNOTICE
, 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. Utilizza
psql
per connetterti al tuo database ed esegui il seguente comando:Esempio di codice
Il valore nella colonna\dt TABLE_NAME
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 chiavi duplicate.
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;
Nell'istruzione precedente, KEY_COLUMNS sono una o più colonne coperte dall'indice univoco o dalla chiave primaria nella tabella che stai controllando. Questi sono stati identificati quando hai verificato le 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 una delle colonne in KEY_COLUMNS è nulla, puoi ignorarla perché i vincoli univoci non si applicano alle colonne NULL.
Se non vengono trovate chiavi duplicate, puoi passare a Correggere gli indici incoerenti.
(Facoltativo, ma consigliato) Crea un backup dei 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 parametro ((KEY_VALUES)) nell'istruzione
IN
con l'istruzioneSELECT
del passaggio 2 senza il parametrocount
. 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 i trigger:
Esempio di codice
ALTER USER CURRENT_USER with REPLICATION; SET session_replication_role = replica;
Esegui la seguente 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
Dove day e rnum sono 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;
L'esecuzione di questa istruzione mantiene una riga ed elimina le altre per ogni insieme di righe duplicate. Se vuoi controllare quale versione della riga viene eliminata, esegui il seguente filtro nell'istruzione di eliminazione:
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 previsto di righe senza errori:Esegui la seguente istruzione per identificare le righe in cui sono state modificate le tabelle:
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;
Se tutte le righe sono corrette, esegui il commit della transazione
DELETE
:Esempio di codice
END;
Se sono presenti errori, rollback le modifiche per correggerli:
Esempio di codice
ROLLBACK;
Dopo aver eliminato le chiavi duplicate, puoi reindicizzare l'indice.
Correggere gli indici incoerenti
Le sezioni seguenti descrivono come correggere le incoerenze dell'indice trovate nell'istanza.
A seconda della configurazione del database, potresti dover eseguire le seguenti operazioni per ogni indice identificato nei passaggi precedenti:
Se l'operazione di reindicizzazione non riesce a causa di violazioni di chiave esterna, devi trovare e correggere queste violazioni.
Esegui di nuovo l'operazione di reindicizzazione.
Prepararsi a reindicizzare l'indice
Trovare le dimensioni 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 reindicizzazione di database più grandi, puoi allocare più memoria e potenza di calcolo a queste operazioni. Si tratta di un passaggio importante nella pianificazione dell'operazione di reindicizzazione. Una volta nota la dimensione dell'indice, puoi impostare la dimensione della memoria utilizzata dall'operazione di reindicizzazione e impostare il numero di worker paralleli.
Esegui la seguente istruzione per trovare le dimensioni dell'indice, in kilobyte, dell'indice che vuoi 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 le dimensioni della memoria da utilizzare per la reindicizzazione
In base alle dimensioni dell'indice determinate nella sezione precedente, è importante impostare il valore appropriato per maintenance_work_mem
. Questo parametro
specifica la quantità di memoria da utilizzare per l'operazione di reindicizzazione. Ad esempio, se la dimensione dell'indice è superiore a 15 GB, ti consigliamo di regolare la memoria di manutenzione. Per saperne di più, consulta Impostare un flag di database.
L'indicizzazione di database più grandi richiede più tempo rispetto a quelli più piccoli. Per
migliorare la velocità delle operazioni di indicizzazione e reindicizzazione, ti consigliamo di impostare
maintenance_work_mem
su almeno il 2% della memoria dell'istanza per le istanze con
4 GB o più di memoria durante questa operazione di reindicizzazione.
Impostare il numero di worker paralleli
Puoi aumentare il numero di worker paralleli per la reindicizzazione 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 più alto per aumentare il numero di
worker per la reindicizzazione. Per le istanze con 8 o più core vCPU, consigliamo di impostare il valore del flag max_parallel_maintenance_workers
su 4.
Per saperne di più, vedi Impostare un flag di database.
Reindicizzare l'indice
Puoi reindicizzare un indice senza bloccare il carico di lavoro di produzione utilizzando l'utilità
pg_repack
. Questa utilità automatizza e semplifica il processo di reindicizzazione simultanea, consentendoti di eseguire la reindicizzazione senza tempi di inattività, in particolare per le versioni di PostgreSQL 11 e precedenti, che non dispongono dell'operazione REINDEX CONCURRENTLY
. Per questa procedura, utilizza pg_repack
versione 1.4.7.
Per reindicizzare l'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 binario eseguibile precompilato per la piattaforma Linux x86_64.
L'hash del checksum SHA256 del binario è il seguente:
ecfee54364a625d9365d86cb27940b458bfdb0d6ff63bb88063039256fbde96f
Per verificare che la tua versione di Linux sia Debian GNU/Linux 11, esegui il comando
hostnamectl
.Self compile
Scarica, compila e installa l'utilità
pg_repack
dalla paginapg_repack
.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
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 delle chiavi univoche e delle chiavi primarie, devi verificare la presenza dichiave esternai esterne e correggere quelle trovate.
Trovare e correggere le violazioni delle chiave esterna
Per informazioni su come trovare e correggere le violazioni dei chiave esterna, consulta Trovare e correggere le chiave esterna referenziale.
Monitorare le operazioni di reindicizzazione
A volte, l'operazione di reindicizzazione potrebbe essere bloccata da altre sessioni. Ti consigliamo di controllare ogni 4 ore. Se l'operazione di reindicizzazione è bloccata, puoi annullare la sessione di blocco in modo che l'operazione di reindicizzazione possa essere completata.
Completa i seguenti passaggi per identificare le sessioni di blocco e attesa e poi annullarle nell'operazione INDEX:
Per identificare le sessioni di blocco, esegui la seguente 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);
Verifica che gli indici siano coerenti
Devi continuare a verificare la presenza di incoerenze dell'indice per ogni indice incoerente. Dopo aver corretto tutti gli indici e le violazioni delle chiavi incoerenti dell'istanza, puoi verificare che non esistano problemi seguendo i passaggi descritti nelle sezioni precedenti: