Internal Bottom line the index exists, it is just not being applied.
-> It seems to exist indeed, but not visible for pg_dump and some other catalog queries... Questions: 1) What is the restore command being used? -> It's just an untar of the full backup created with pg_basebackup. No need to focus on this imo, the restore was done from the production db so I could have a playground for this situation. It's clear the situation is the same on the original and the backup copy. 2) From this post: https://www.postgresql.org/message-id/AS2PR05MB10754BFE319E2594C9E076EE2EFFDA%40AS2PR05MB10754.eurprd05.prod.outlook.com What does this: "The steps that show the index is missing is an export of the database while the index is ‘corrupt’, and then importing it. The import fails on foreign keys that are pointing to this index because it is indeed not created." mean? -> Well, we export the database using pg_dump, and on import some foreign key indexes which reference the problem primary key index fail to create because it's not created, which makes sense. It's not created because it's not exported. If you REINDEX before the export is the index attached to the table on import? -> Yes, then it gets exported and hence imported Define 'corrupt'. -> Well, it's not behaving as it should, let's keep it at that. 3) The field the index points at, id, has: bigint nextval('bcf_work_type_id_seq'::regclass). Is that coming from a bigserial definition or a DEFAULT setting? -> I talked to the devs. There is no packaged code for this as it turns out this was originally a mysql/mariadb db which got converted to Postgresql using pgloader. But this issue has nothing to do with that migration, we did datarefreshes successfully (after the migration) before it started failing. 4) What happens if you create a test database and restore bcf_work_type by itself, with and without data? -> I could test this, how would you suggest to do the backup/restore part, also pg_dump? > > So the output of these queries before the reindex is: > > db_name_hidden=# select * from pg_index where indexrelid = > 'idx_376814_primary'::regclass; > -[ RECORD 1 ]-------+-------- > indexrelid | 2006873 > indrelid | 1998823 > indnatts | 1 > indnkeyatts | 1 > indisunique | t > indnullsnotdistinct | f > indisprimary | t > indisexclusion | f > indimmediate | t > indisclustered | f > indisvalid | t > indcheckxmin | f > indisready | t > indislive | t > indisreplident | f > indkey | 1 > indcollation | 0 > indclass | 3124 > indoption | 0 > indexprs | > indpred | > > db_name_hidden =# \d bcf_work_type > > Table "name_hidden.bcf_work_type" > Column | Type | Collation | Nullable | Default > -----------+--------+-----------+----------+------------------------------------------- > id | bigint | | not null | > nextval('bcf_work_type_id_seq'::regclass) > aml_score | bigint | | not null | > Referenced by: > TABLE "bcf_work_type_translation" CONSTRAINT "fk_3cf130ab108734b1" > FOREIGN KEY (work_type_id) REFERENCES bcf_work_type(id) ON UPDATE RESTRICT ON > DELETE RESTRICT > TABLE "bcf_investment" CONSTRAINT "fk_83580679108734b1" FOREIGN KEY > (work_type_id) REFERENCES bcf_work_type(id) ON UPDATE RESTRICT ON DELETE > RESTRICT > TABLE "bcf_id_information" CONSTRAINT "fk_f56a0f6b108734b1" FOREIGN KEY > (work_type_id) REFERENCES bcf_work_type(id) ON UPDATE RESTRICT ON DELETE > RESTRICT > > > After the REINDEX command (REINDEX INDEX idx_376814_primary; ) this becomes: > > db_name_hidden=# select * from pg_index where indexrelid = > 'idx_376814_primary'::regclass; > -[ RECORD 1 ]-------+-------- > indexrelid | 2006873 > indrelid | 1998823 > indnatts | 1 > indnkeyatts | 1 > indisunique | t > indnullsnotdistinct | f > indisprimary | t > indisexclusion | f > indimmediate | t > indisclustered | f > indisvalid | t > indcheckxmin | f > indisready | t > indislive | t > indisreplident | f > indkey | 1 > indcollation | 0 > indclass | 3124 > indoption | 0 > indexprs | > indpred | > > db_name_hidden =# \d bcf_work_type > Table "name_hidden.bcf_work_type" > Column | Type | Collation | Nullable | Default > -----------+--------+-----------+----------+------------------------------------------- > id | bigint | | not null | > nextval('bcf_work_type_id_seq'::regclass) > aml_score | bigint | | not null | > Indexes: > "idx_376814_primary" PRIMARY KEY, btree (id) > Referenced by: > TABLE "bcf_work_type_translation" CONSTRAINT "fk_3cf130ab108734b1" > FOREIGN KEY (work_type_id) REFERENCES bcf_work_type(id) ON UPDATE RESTRICT ON > DELETE RESTRICT > TABLE "bcf_investment" CONSTRAINT "fk_83580679108734b1" FOREIGN KEY > (work_type_id) REFERENCES bcf_work_type(id) ON UPDATE RESTRICT ON DELETE > RESTRICT > TABLE "bcf_id_information" CONSTRAINT "fk_f56a0f6b108734b1" FOREIGN KEY > (work_type_id) REFERENCES bcf_work_type(id) ON UPDATE RESTRICT ON DELETE > RESTRICT > > So the first result stays the same, in the description of the table now the > index shows up... > > > -- Adrian Klaver [email protected] Disclaimer <https://www.kbc.com/KBCmailDisclaimer>
