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/
Restore the backup. I prefer to use this method, and if you got there, you should be able to use it easily : Restore a backup from command line