Inkonsistensi dalam indeks database dapat terjadi karena berbagai alasan, termasuk kerusakan software, masalah hardware, atau perubahan perilaku yang mendasar seperti perubahan tata urutan.
Komunitas PostgreSQL telah membangun alat untuk mengidentifikasi dan mengatasi masalah tersebut.
Alat ini mencakup alat seperti amcheck
,
yang direkomendasikan oleh komunitas PostgreSQL untuk mengidentifikasi masalah konsistensi,
termasuk masalah yang ditampilkan dalam versi PostgreSQL 14 sebelumnya.
Kami telah menulis playbook ini sebagai referensi untuk AlloyDB untuk PostgreSQL yang mengalami masalah tersebut. Kami harap halaman ini memberikan informasi yang juga dapat membantu pengguna PostgreSQL lainnya dalam mengidentifikasi dan memperbaiki indeks B-tree yang inkonsisten. Tujuan kami adalah terus meningkatkan kualitas dokumen ini sebagai resource untuk komunitas open source yang lebih luas. Jika Anda memiliki masukan, gunakan tombol Kirim masukan di bagian atas dan bawah halaman ini.
Menyelesaikan inkonsistensi indeks melibatkan langkah-langkah berikut:
-
Sebelum memulai pengindeksan ulang, Anda harus mencadangkan database, menetapkan izin yang benar, memverifikasi versi klien
psql
, dan mengaktifkan ekstensiamcheck
. Periksa indeks B-tree yang inkonsisten.
Untuk mengidentifikasi indeks yang inkonsistensinya perlu diperbaiki, Anda harus mengidentifikasi semua indeks B-tree yang inkonsisten serta mengidentifikasi semua pelanggaran kunci utama dan unik.
Perbaiki inkonsistensi indeks.
Mengindeks ulang sebuah indeks dapat memperbaiki semua inkonsistensi. Anda mungkin perlu menyesuaikan setelan memori instance untuk meningkatkan performa.
Pantau operasi pengindeksan ulang.
Sebaiknya pantau progres operasi pengindeksan ulang untuk memastikan bahwa operasi sedang berlangsung dan tidak diblokir.
Verifikasi bahwa indeks tersebut konsisten.
Setelah berhasil mengindeks ulang, sebaiknya Anda memverifikasi bahwa indeks Anda tidak berisi inkonsistensi.
Sebelum memulai
Mencadangkan data cluster AlloyDB
Untuk memastikan tidak ada data yang hilang selama pengindeksan ulang, sebaiknya cadangkan data cluster Anda. Untuk mengetahui informasi selengkapnya, lihat Membuat cadangan sesuai permintaan.
Tetapkan izin alloydbsuperuser
Untuk menyelesaikan langkah-langkah di halaman ini, Anda harus memiliki izin
alloydbsuperuser
. Untuk informasi selengkapnya, lihat Peran PostgreSQL bawaan AlloyDB.
Pastikan versi klien psql
adalah 9.6 atau yang lebih tinggi
Untuk menyelesaikan langkah-langkah di halaman ini, Anda harus memastikan bahwa versi klien psql
Anda
adalah 9.6 atau yang lebih tinggi. Jalankan perintah psql --version
untuk memverifikasi
versi klien psql
Anda saat ini.
Mengaktifkan ekstensi amcheck
Untuk memeriksa inkonsistensi indeks, Anda harus mengaktifkan ekstensi amcheck
.
Contoh Kode
CREATE EXTENSION amcheck;
Memeriksa indeks B-tree yang tidak konsisten
Bagian berikut menjelaskan cara memeriksa indeks B-tree yang tidak konsisten dengan memeriksa inkonsistensi indeks serta pelanggaran kunci utama dan unik.
Memeriksa inkonsistensi
Jalankan pernyataan berikut di setiap database Anda untuk memeriksa inkonsistensi di semua indeks B-tree:
Contoh Kode
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 $$;
Anda akan melihat output yang mirip dengan hal berikut ini:
Contoh Kode
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"
Untuk mengetahui informasi selengkapnya tentang cara melihat log, lihat Melihat log menggunakan Logs Explorer.
Mengidentifikasi dan memperbaiki pelanggaran kunci utama dan unik
Bagian ini menjelaskan cara memeriksa indeks Anda untuk menemukan pelanggaran kunci utama dan unik, dan jika ada, cara memperbaikinya.
Mengidentifikasi pelanggaran kunci unik
Pelanggaran kunci unik harus diperbaiki sebelum Anda mengindeks ulang indeks. Untuk memeriksa semua pelanggaran kunci unik, jalankan perintah berikut di setiap database:
Contoh Kode
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
Output skrip mirip dengan berikut ini:
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
Dalam output ini, header tabel NOTICE
menunjukkan indeks, kolom, dan tabel
untuk nilai yang ditampilkan di bawahnya. Jika output Anda berisi baris yang menampilkan
DUPLICATE
atau BACKWARDS
, hal ini menunjukkan kerusakan dalam indeks dan mungkin perlu
diperbaiki. Baris dengan BACKWARDS
menunjukkan kemungkinan nilai duplikat yang
mungkin disembunyikan. Jika Anda melihat salah satu entri ini dalam tabel, lihat Memperbaiki pelanggaran kunci duplikat.
Memperbaiki pelanggaran kunci duplikat
Jika Anda telah mengidentifikasi indeks unik duplikat atau jika operasi pengindeksan ulang gagal karena error pelanggaran kunci duplikat, selesaikan langkah-langkah berikut untuk menemukan dan menghapus kunci duplikat.
Ekstrak
key_columns
dari header tabelNOTICE
, seperti yang ditampilkan dalam contoh output sebelumnya. Dalam contoh berikut, kolom kuncinya adalahemail
.Output
NOTICE: checking index=users_email_key on table=users key_columns="(email)"
Gunakan nilai ini di KEY_COLUMNS dalam kueri di langkah 3.
Temukan skema untuk tabel Anda. Gunakan
psql
untuk terhubung ke database Anda dan jalankan perintah berikut:Contoh Kode
Nilai di kolom\dt TABLE_NAME
schema
adalah nilai yang Anda gunakan untuk SCHEMA_NAME dalam kueri di langkah 3.Misalnya, untuk kueri berikut:
\dt games
Outputnya mirip dengan yang berikut ini:
List of relations Schema | Name | Type | Owner --------+-------+-------+---------- public | games | table | postgres (1 row)
Jalankan pernyataan berikut untuk memaksa pemindaian tabel lengkap dan mendapatkan kunci duplikat.
Contoh Kode
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;
Dalam pernyataan di atas, KEY_COLUMNS adalah satu atau beberapa kolom yang dicakup oleh kunci utama atau indeks unik dalam tabel yang Anda periksa. Kunci unik ini teridentifikasi saat Anda memeriksa pelanggaran kunci unik. Pernyataan ini menampilkan kunci duplikat dan jumlah duplikat untuk setiap kunci.
Misalnya, untuk kueri berikut:
SELECT name,count(*) FROM public.TEST_NAMES GROUP BY name HAVING count(*) > 1;
Outputnya mirip dengan yang berikut ini:
name | count --------------------+------- Johnny | 2 Peter | 2 (2 rows)
Dalam hal ini, lanjutkan ke langkah berikutnya untuk menghapus kunci duplikat.
Jika salah satu kolom di KEY_COLUMNS bernilai null, Anda dapat mengabaikannya karena batasan unik tidak berlaku untuk kolom NULL.
Jika tidak ada kunci duplikat yang ditemukan, Anda dapat beralih ke Memperbaiki indeks yang inkonsisten.
Opsional, tetapi direkomendasikan: Buat cadangan untuk data yang berisi kunci duplikat. Jalankan pernyataan berikut untuk membuat data cadangan:
Dalam pernyataan ini, KEY_VALUES adalah daftar nilai yang disalin dari hasil langkah sebelumnya. Contoh:Contoh Kode
CREATE TABLE SCHEMA_NAME.TABLE_NAME_bak AS SELECT * FROM SCHEMA_NAME.TABLE_NAME WHERE (KEY_COLUMNS) IN (KEY_VALUES);
Contoh Kode
CREATE TABLE public.TEST_NAMES_bak AS SELECT * FROM public.TEST_NAMES WHERE (name) IN (('Johnny'),('Peter'))
Untuk jumlah baris yang banyak, akan lebih mudah untuk mengganti ((KEY_VALUES )) di dalam
IN
dengan pernyataanSELECT
dari langkah 2 tanpa parametercount
. Misalnya:Contoh Kode
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);
Tambahkan peran replikasi ke pengguna untuk menonaktifkan pemicu:
Contoh Kode
ALTER USER CURRENT_USER with REPLICATION; SET session_replication_role = replica;
Jalankan pernyataan berikut untuk menghapus kunci duplikat:
Contoh Kode
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;
Misalnya, untuk multi-kolom KEY_COLUMNS:
Contoh Kode
Dengan day dan rnum adalah 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;
Menjalankan pernyataan ini akan mempertahankan satu baris dan menghapus baris lainnya untuk setiap kumpulan baris duplikat. Jika Anda ingin mengontrol versi baris yang akan dihapus, jalankan filter berikut pada pernyataan hapus:
Contoh Kode
DELETE FROM SCHEMA_NAME.TABLE_NAME WHERE ( KEY_COLUMNS, ctid) = (KEY_VALUES, CTID_VALUE);
Selesaikan langkah-langkah berikut untuk memeriksa apakah perintah
DELETE
menampilkan jumlah baris yang diharapkan tanpa error:Jalankan pernyataan berikut untuk mengidentifikasi baris tempat tabel diubah:
Contoh Kode
SELECT schemaname, relname, n_tup_del, n_tup_upd FROM pg_stat_xact_all_tables WHERE n_tup_del+n_tup_upd > 0;
Jika semua baris sudah benar, commit transaksi
DELETE
:Contoh Kode
END;
Jika terjadi error, roll back perubahan untuk memperbaiki error:
Contoh Kode
ROLLBACK;
Setelah kunci duplikat dihapus, Anda dapat mengindeks ulang indeks.
Memperbaiki indeks yang inkonsisten
Bagian berikut menjelaskan cara memperbaiki inkonsistensi indeks dalam instance Anda.
Bergantung pada cara database dikonfigurasi, Anda mungkin perlu melakukan hal berikut untuk setiap indeks yang diidentifikasi pada langkah sebelumnya:
Jika operasi indeks ulang gagal karena pelanggaran kunci asing, Anda harus menemukan dan memperbaiki pelanggaran ini.
Jalankan lagi operasi indeks ulang.
Bersiap mengindeks ulang indeks
Menemukan ukuran indeks
Mengindeks database yang lebih besar memerlukan waktu yang lebih lama daripada database yang lebih kecil. Untuk meningkatkan kecepatan operasi indeks dan mengindeks ulang database yang lebih besar, Anda dapat mengalokasikan lebih banyak memori dan daya CPU ke operasi ini. Langkah ini penting dalam merencanakan operasi indeks ulang. Setelah mengetahui ukuran indeks, Anda dapat menetapkan ukuran memori yang digunakan oleh operasi indeks ulang dan menetapkan jumlah worker paralel.
Jalankan pernyataan berikut untuk menemukan ukuran indeks, dalam kilobyte, dari indeks yang ingin Anda perbaiki:
Contoh Kode
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';
Output pernyataan ini mirip dengan hasil berikut:
Output
index_name | index_size ------------+------------ my_index | 16 kB (1 row)
Menetapkan ukuran memori yang akan digunakan untuk pengindeksan ulang
Berdasarkan ukuran indeks Anda seperti yang ditentukan di bagian sebelumnya, penting
untuk menetapkan nilai yang sesuai untuk
parameter konfigurasi maintenance_work_mem
. Parameter ini menentukan jumlah memori
yang akan digunakan untuk operasi pengindeksan ulang. Misalnya, jika ukuran indeks Anda
lebih besar dari 15 GB, sebaiknya sesuaikan memori
pemeliharaan.
Contoh berikut menunjukkan cara menetapkan maintenance_work_mem
:
Contoh Kode
SET maintenance_work_mem TO "1GB";
Mengindeks database yang lebih besar memerlukan waktu yang lebih lama daripada database yang lebih kecil. Untuk
meningkatkan kecepatan operasi indeks dan pengindeksan ulang, sebaiknya tetapkan
maintenance_work_mem
ke minimal 2% dari memori instance untuk instance dengan memori 4 GB atau lebih
selama operasi pengindeksan ulang ini.
Tetapkan jumlah worker paralel
Anda dapat meningkatkan jumlah pekerja paralel untuk pengindeksan ulang dengan menetapkan parameter konfigurasi max_parallel_maintenance_workers
di database. Nilai default parameter ini adalah 2, tetapi dapat ditetapkan ke nilai yang lebih tinggi untuk meningkatkan jumlah pekerja pengindeksan ulang. Untuk instance dengan 8 inti vCPU atau lebih, sebaiknya
tetapkan nilai flag max_parallel_maintenance_workers
ke 4.
Berikut ini cara mengidentifikasi nilai yang ditetapkan untuk parameter ini:
Contoh Kode
SHOW max_parallel_maintenance_workers; SHOW max_worker_processes; SHOW max_parallel_workers;
Parameter max_parallel_maintenance_workers
adalah subset dari
max_worker_processes
dan dibatasi oleh
max_parallel_workers
. Jika Anda memerlukan lebih banyak pekerja paralel,
tingkatkan nilai max_worker_processes
dan
max_parallel_workers
.
Contoh berikut menunjukkan cara menetapkan max_parallel_maintenance_workers
:
Contoh Kode
SET max_parallel_maintenance_workers TO 4;
Parameter max_parallel_maintenance_workers
tidak menjamin
alokasi pekerja. Untuk mengonfirmasi bahwa Anda memiliki beberapa pekerja paralel
yang dimulai oleh pengindeksan ulang, jalankan kueri berikut dari sesi lain setelah
Anda memulai pengindeksan ulang:
Contoh Kode
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;
Mengindeks ulang indeks
Anda dapat mengindeks ulang indeks tanpa memblokir workload produksi menggunakan
utilitas pg_repack
. Utilitas ini mengotomatiskan dan menyederhanakan proses pengindeksan ulang secara
konkurensi, sehingga Anda dapat mengindeks ulang tanpa periode nonaktif. Untuk prosedur ini, gunakan pg_repack
versi 1.4.7.
Selesaikan langkah-langkah berikut untuk mengindeks ulang indeks menggunakan pg_repack
:
Download, kompilasi, dan instal utilitas
pg_repack
dari halaman pg_repack.Buat ekstensi
pg_repack
:Contoh Kode
CREATE EXTENSION pg_repack;
Jalankan perintah berikut untuk mengindeks ulang indeks secara konkurensi:
Contoh Kode
pg_repack -h HOSTIP -p 5432 -U USERNAME -d "DATABASE_NAME" -i "INDEX_NAME" --no-superuser-check --no-kill-backend --wait-timeout=3600
Perintah ini memiliki output yang mirip dengan hasil berikut:
Output
INFO: repacking index "public.t_i_key"
Jika terjadi error saat menjalankan
pg_repack
, Anda dapat memperbaiki error tersebut dan mencoba lagi. Setelah memperbaiki semua indeks kunci unik dan indeks kunci utama, Anda harus memeriksa pelanggaran kunci asing dan memperbaiki setiap pelanggaran yang ditemukan.
Menemukan dan memperbaiki pelanggaran kunci asing
Untuk informasi tentang cara menemukan dan memperbaiki pelanggaran kunci asing, lihat Menemukan dan memperbaiki pelanggaran kunci asing.
Memantau operasi pengindeksan ulang
Terkadang, operasi indeks ulang mungkin diblokir oleh sesi lain. Sebaiknya periksa hal ini setiap 4 jam. Jika operasi indeks ulang diblokir, Anda dapat membatalkan sesi pemblokiran sehingga operasi pengindeksan ulang dapat selesai.
Selesaikan langkah-langkah berikut untuk mengidentifikasi sesi tunggu dan pemblokiran, lalu batalkan dalam operasi INDEX:
Untuk mengidentifikasi sesi pemblokiran, jalankan kueri berikut:
Contoh Kode
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;
Untuk membatalkan sesi, jalankan kueri berikut menggunakan PID sesi pemblokiran dari kueri sebelumnya:
Contoh Kode
SELECT pg_cancel_backend(PID);
Memverifikasi bahwa indeks Anda konsisten
Anda harus terus memeriksa inkonsistensi indeks untuk setiap indeks yang inkonsisten. Setelah memperbaiki semua indeks yang inkonsisten dan pelanggaran kunci instance, Anda dapat memeriksa bahwa tidak ada masalah saat mengikuti langkah-langkah di bagian sebelumnya: