Error restoring backup "key is duplicated"

Support Self-hosting

AI-generated summary

The user appcomq encountered an error “key is duplicated” when restoring a backup on their Discourse forum (v1.9.0.beta7 +341). The error message pointed to a duplicated key in the topic_users table. pfaffman suggested a manual method to restore the backup, but appcomq had already lost their forum with 50,000 users after reinstalling the OS.

appcomq investigated the issue by checking the topic_users_id_seq sequence and found a discrepancy. They then identified the problematic row in the topic_users table with id=59700.

To resolve the issue, appcomq manually edited the .sql file, deleting all rows that included the problematic id values (59700 and 11226). This fix successfully restored the forum.

1

:joy::joy: I…

version is v1.9.0.beta7 +341

[2017-08-29 15:27:03] ERROR:  could not create unique index "index_topic_users_on_topic_id_and_user_id"
[2017-08-29 15:27:03] DETAIL:  Key (topic_id, user_id)=(59700, 11226) is duplicated.
[2017-08-29 15:27:03] ERROR:  current transaction is aborted, commands ignored until end of transaction block
[2017-08-29 15:27:03] ERROR:  current transaction is aborted, commands ignored until end of transaction block
[2017-08-29 15:27:03] ERROR:  current transaction is aborted, commands ignored until end of transaction block
[2017-08-29 15:27:03] ERROR:  current transaction is aborted, commands ignored until end of transaction block
....

[2017-08-29 15:27:03] EXCEPTION: psql failed
[2017-08-29 15:27:03] /var/www/discourse/lib/backup_restore/restorer.rb:279:in `restore_dump'
/var/www/discourse/lib/backup_restore/restorer.rb:42:in `run'
/var/www/discourse/lib/backup_restore/backup_restore.rb:163:in `block in start!'
/var/www/discourse/lib/backup_restore/backup_restore.rb:160:in `fork'

...
2

now, I just have backup tar.gz, my old server has been resintall OS/

Plz help me .:disappointed_relieved:

3

I’ve seen a few similar reports like https://meta.discourse.org/t/error-could-not-create-unique-index-badges-pkey-attempting-to-restore-backup/67726. I suspect that there is a bug somewhere, but no one has shown a way to replicate it.

I think your solution is to do this: Manually create and restore Discourse backups.

4

:disappointed_relieved::disappointed_relieved:

but the server has reinstll OS, so I lost my forum 50,000 user. :persevere::persevere:

5

That looks a lot like this one:

6 
discourse=> \d topic_users_id_seq
     Sequence "public.topic_users_id_seq"
    Column     |  Type   |        Value        
---------------+---------+---------------------
 sequence_name | name    | topic_users_id_seq
 last_value    | bigint  | 2481334
 start_value   | bigint  | 1
 increment_by  | bigint  | 1
 max_value     | bigint  | 9223372036854775807
 min_value     | bigint  | 1
 cache_value   | bigint  | 1
 log_cnt       | bigint  | 0
 is_cycled     | boolean | f
 is_called     | boolean | f
Owned by: public.topic_users.id

discourse=> select count(id),max(id) from topic_users;
  count  |   max   
---------+---------
 2467987 | 2481334
(1 row)
7 
discourse=> select * from topic_users where id=59700;
user_id | topic_id | posted | last_read_post_number | highest_seen_post_number |      last_visited_at       |      first_visited_at      | notification_level |  notifications_changed_at  | notifications_reason_id | total_msecs_viewed | cleared_pinned_at |  id   | last_emailed_post_number | liked | bookmarked 
---------+----------+--------+-----------------------+--------------------------+----------------------------+----------------------------+--------------------+----------------------------+-------------------------+--------------------+-------------------+-------+--------------------------+-------+------------
       1 |    11091 | t      |                     1 |                        1 | 2017-02-13 12:51:41.857104 | 2017-02-13 12:51:41.857104 |                  3 | 2017-02-13 12:51:41.855492 |                       1 |                  0 |                   | 59700 |                          | f     | f
(1 row)

~

8

I have fix this.

vi .sql file, delete all include 59700 and 11226

:joy::joy::joy: everything is fine.