Steven encountered an error during a database restore due to a duplicate username_lower entry (“Lea” and “lea”), which prevented the unique index from being created. The issue likely stemmed from a Twitter login or an old forum version. After identifying the duplicate in the dump.sql file using Vim, Steven manually corrected the entries, compressed the file, and restored it by replacing the broken backup. He detailed a step-by-step process involving editing the SQL file, re-compressing it, and using the command-line restore method to successfully fix the issue Steven.
Last night, I had a massive issue on a forum and I had to re-create everything. But during the restore, I have an issue and it failed. Here is the error
ERROR: could not create unique index "index_users_on_username_lower"
DETAIL: Key (username_lower)=(lea) is duplicated.
EXCEPTION: psql failed: DETAIL: Key (username_lower)=(lea) is duplicated.
I think it can be connected to twitter login, but can the restore change a username_lower name if it’s duplicated? I don’t think I would be able to change in the sql file (it’s pretty big) and reupload it.
It was a different issue, the server was unable to access pups.git, Could not resolve host: github.com. I tried some solutions found last night, but it didn’t work.
With the restoration, I think it’s only an issue with one username (maybe linked to twitter one click account creation, or something like that). I’m trying to fix it manually, but managing a 1gb sql file is not ideal
edit : with the software vim, I was able to edit the sql file, I found the right lines. Restore worked. I just need to rebake everything and it should be good to go
There was a “Lea” username and a “lea” username. It’s weird that Discourse let that happened. It’s a pretty old forum (june 2014), and I updated it frequently, so it was maybe due to a specific version.
I suck so bad to sql. To find the right table, I searched username_lower,. It pointed me to the users table, then I researched “lea”. I edited the two Lea entries. It was probably way easier. But remember, I suck at sql, especially when they are 1.5gb. Save the file.
I gzip the dump.sql with 7zip. You should have a new file named : dump.sql.gz
Create a new folder in /var/discourse/shared/standalone/backups/default/ I used test
If you have a good internet connection, upload the uploads folder you obtained when you decompressed your backup file, put the folder in /var/discourse/shared/standalone/backups/default/test/
6.1 If you don’t have a good connection like me, you have to use your server note the name of your backup file and use this command tar xvzf /var/discourse/shared/standalone/backups/default/yourbackupfile.tar.gz -C /var/discourse/shared/standalone/backups/default/test
6.2 In the test folder, you’ll have a dump.sql.gz file and uploads folder. It’s good.
Upload the dump.sql.gz file in your test folder to replace the broken dump.sql.gz
In your server : go to cd /var/discourse/shared/standalone/backups/default/test
You need to remake your backup file, you have to use the exact name of the older backup file : tar -czvf yourbackupfile.tar.gz uploads/ dump.sql.gz
In your ftp go to /var/discourse/shared/standalone/backups/default/, delete the broken backup or move in to another folder
Move the new backup file in /var/discourse/shared/standalone/backups/default/