خطأ في استيراد النسخ الاحتياطي: "تعذر إنشاء فهرس فريد"

[glynhudson]

مرحباً،

أحاول ترحيل منتدى إلى خادم جديد. كلا الخادمين يعملان بأحدث إصدار من discourse docker. عند استيراد النسخة الاحتياطية عبر سطر الأوامر، أحصل على الخطأ التالي:

ERROR:  could not create unique index "index_incoming_referers_on_path_and_incoming_domain_id"
DETAIL:  Key (path, incoming_domain_id)=(/search/, 418) is duplicated.
EXCEPTION: psql failed: DETAIL:  Key (path, incoming_domain_id)=(/search/, 418) is duplicated.

يبدو هذا هو نفس الخطأ أو خطأ مشابه للخطأ الموجود في:

Restore fails - could not create unique index

I have a duplicate key issue also, is there a documented fix? discourse=# REINDEX SCHEMA CONCURRENTLY public; ERROR: could not create unique index "index_incoming_referers_on_path_and_incoming_domain_id_ccnew" DETAIL: Key (path, incoming_domain_id)=(/search/, 1905) is duplicated. [1] pry(main)> IncomingReferer.where(path: "/m/search") => [#<IncomingReferer:0x0000557176d3f210 id: 44231, path: "/m/search", incoming_domain_id: 4>, #<IncomingReferer:0x0000557176d925c8 id: 42228, path: "/m/…

ومع ذلك، في حالتي، السجلات المكررة موجودة في المسار /search/ بدلاً من /m/search كما هو الحال في الموضوع المرتبط أعلاه.

لقد اتصلت بالحاوية على الخادم القديم (./launcher enter app) وفي وحدة تحكم Rails (rails c) حاولت البحث عن السجلات المكررة باستخدام:

IncomingReferer.where(path: "/search")
و
IncomingReferer.where("path LIKE '%/search%'")

ومع ذلك، ينتج عن هذا مئات السجلات التي يتم عرضها. كيف يمكنني معرفة السجلات المكررة، وكيف يمكنني حذفها بأمان وإعادة بنائها؟ يعمل المنتدى حاليًا بشكل جيد على الخادم القديم، ونحن بحاجة فقط إلى الانتقال إلى أجهزة جديدة.

[IAmGav (Gavin Perch)]

Have you tried using the Admin GUI ?

[glynhudson]

No, I assumed importing via the GUI would be invoking the same import process? I’ll try that now.

[pfaffman (Jay Pfaffman)]

I suspect this means that you have a corrupt index. What version of Postgres are you running?

something like:

cd /var/discourse
cat shared/standalone/postgres_data/PG*

(I can’t quite remember the postgres filename).

You can search here for “postgres corrupt index” and find a topic that I once wrote about how to track down those bad records and delete them.

You basically try to rebuild that index and delete the records that it complains about then try to rebuild the index again until it rebuilds.

[glynhudson]

Just tried importing via the GUI, with exactly the same result:

@pfaffman

What version of Postgres are you running?

The old server doesn’t have a file called PG_VERSION, how can I tell what version its running? I’ve updated the docket install to the latest version today.

The new server (newly bootstrapped) is running postgres V13

cat shared/standalone/postgres_data/PG_VERSION
13

@pfaffman

You can search here for “postgres corrupt index” and find a topic that I once wrote about how to track down those bad records and delete them.

You basically try to rebuild that index and delete the records that it complains about then try to rebuild the index again until it rebuilds.

Is there a recommended procedure on how to do this?

[pfaffman (Jay Pfaffman)]

I had a topic that had some hints, but I don’t see it anymore. It’s been close to a year since the postgres 12 upgrade.

  reindex index index_incoming_referers_on_path_and_incoming_domain_id;

And

 ActiveRecord::Base.connection.execute('reindex index index_incoming_referers_on_path_and_incoming_domain_id;')

Are ways to try to rebuild the index. It’ll give you an error and you can then go and delete the errant records. You’ll need to include both the path and the ID.

[glynhudson]

Ok, I’ve fixed it. I did the following

Enter container

./launcher enter app

Connect to database

su postgres -c 'psql discourse'

Try to find duplicates

discourse=# select * from incoming_referers where path LIKE '%/search/' ORDER BY incoming_domain_id;`

  id  |    path    | incoming_domain_id
------+------------+--------------------
 3339 | /search/   |                 33
 6257 | /search/   |                 91
 1567 | /search/   |                298
 1777 | /search/   |                341
 3010 | /search/   |                418
 6247 | /search/   |                418
 4293 | /search/   |                644
 2899 | /search/   |                653
 3447 | /search/   |                793
 3696 | /search/   |                852
 4395 | /a/search/ |               1050
 6968 | /search/   |               1305
 5634 | /search/   |               1387
 5834 | /search/   |               1437
 6519 | /search/   |               1637
 7127 | /search/   |               1787
 7280 | /search/   |               1827
(17 rows)

Delete duplicate

DELETE FROM incoming_referers WHERE path LIKE '%/search/' AND id IN (6247);

Then rebuild

discourse=# REINDEX SCHEMA CONCURRENTLY public;
WARNING:  cannot reindex invalid index "public.incoming_referers_pkey_ccnew" concurrently, skipping
WARNING:  cannot reindex invalid index "public.index_incoming_referers_on_path_and_incoming_domain_id_ccnew" concurrently, skipping
WARNING:  cannot reindex invalid index "pg_toast.pg_toast_20732_index_ccnew" concurrently, skipping
REINDEX

Then I took another backup, copied it to the new server, and it imported successfully

It would be nice if the backup process could spot duplicates to avoid any issues, I was luckily that I had access to the original server which was still running. If I was restoring a cold backup, this would probably have been more of an issue>

Thanks a lot for your help.