I was upgrading from 2.7.0.beta1 and got this error message: 2021-01-22 20:16:22.015 UTC [4055] discourse@discourse LOG: duration: 75335.241 ms statement: UPDATE notifications SET processed = true 2021-01-22 20:16:23.792 UTC [4055] discourse@discourse LOG: duration: 1776.591 ms statement: ALTER TABLE "notifications" ALTER COLUMN "processed" SET NOT NULL 2021-01-22 20:16:25.198 UTC [4055] discourse@discourse LOG: duration: 1323.298 ms statement: CREATE INDEX "index_notifications_on_processed" ON "notifications" ("processed") 2021-01-22 20:16:25.458 UTC [4055] discourse@discourse LOG: duration: 241.063 ms statement: CREATE TABLE "user_notification_schedules" ("id" bigserial primary key, "user_id" integer NOT NULL, "enabled" boolean DEFAULT FALSE NOT NULL, "day_0_start_time" integer NOT NULL, "day_0_end_time" integer NOT NULL, "day_1_start_time" integer NOT NULL, "day_1_end_time" integer NOT NULL, "day_2_start_time" integer NOT NULL, "day_2_end_time" integer NOT NULL, "day_3_start_time" integer NOT NULL, "day_3_end_time" integer NOT NULL, "day_4_start_time" integer NOT NULL, "day_4_end_time" integer NOT NULL, "day_5_start_time" integer NOT NULL, "day_5_end_time" integer NOT NULL, "day_6_start_time" integer NOT NULL, "day_6_end_time" integer NOT NULL) 2021-01-22 20:16:25.560 UTC [4055] discourse@discourse LOG: duration: 100.868 ms statement: CREATE INDEX "index_user_notification_schedules_on_user_id" ON "user_notification_schedules" ("user_id") 2021-01-22 20:16:25.782 UTC [4055] discourse@discourse LOG: duration: 142.180 ms statement: CREATE INDEX "index_do_not_disturb_timings_on_scheduled" ON "do_not_disturb_timings" ("scheduled") 2021-01-22 20:16:26.414 UTC [4055] discourse@discourse LOG: duration: 361.514 ms statement: UPDATE users SET locale = 'en_GB' WHERE locale = 'en' 2021-01-22 20:16:26.656 UTC [4055] discourse@discourse LOG: duration: 132.778 ms statement: UPDATE theme_translation_overrides SET locale = 'en_GB' WHERE locale = 'en' 2021-01-22 20:16:42.745 UTC [4055] discourse@discourse ERROR: duplicate key value violates unique constraint "index_users_on_username" 2021-01-22 20:16:42.745 UTC [4055] discourse@discourse DETAIL: Key (username)=(DaveW) already exists. 2021-01-22 20:16:42.745 UTC [4055] discourse@discourse STATEMENT: UPDATE users SET locale = 'en' WHERE locale = 'en_US' rake aborted! StandardError: An error has occurred, this and all later migrations canceled: PG::UniqueViolation: ERROR: duplicate key value violates unique constraint "index_users_on_username" DETAIL: Key (username)=(DaveW) already exists. And then, at the end of the output: I, [2021-01-22T20:16:42.805286 #1] INFO -- : Terminating async processes I, [2021-01-22T20:16:42.805333 #1] INFO -- : Sending INT to HOME=/var/lib/postgresql USER=postgres exec chpst -u postgres:postgres:ssl-cert -U postgres:postgres:ssl-cert /usr/lib/postgresql/13/bin/postmaster -D /etc/postgresql/13/main pid: 49 I, [2021-01-22T20:16:42.805381 #1] INFO -- : Sending TERM to exec chpst -u redis -U redis /usr/bin/redis-server /etc/redis/redis.conf pid: 166 166:signal-handler (1611346602) Received SIGTERM scheduling shutdown... 2021-01-22 20:16:42.805 UTC [49] LOG: received fast shutdown request 2021-01-22 20:16:42.835 UTC [49] LOG: aborting any active transactions 2021-01-22 20:16:42.857 UTC [49] LOG: background worker "logical replication launcher" (PID 58) exited with exit code 1 166:M 22 Jan 2021 20:16:42.876 # User requested shutdown... 166:M 22 Jan 2021 20:16:42.876 * Saving the final RDB snapshot before exiting. 166:M 22 Jan 2021 20:16:44.758 * DB saved on disk 166:M 22 Jan 2021 20:16:44.758 # Redis is now ready to exit, bye bye... 2021-01-22 20:16:45.563 UTC [53] LOG: shutting down I, [2021-01-22T20:16:52.806177 #1] INFO -- : HOME=/var/lib/postgresql USER=postgres exec chpst -u postgres:postgres:ssl-cert -U postgres:postgres:ssl-cert /usr/lib/postgresql/13/bin/postmaster -D /etc/postgresql/13/main pid:49 did not terminate cleanly, forcing termination! FAILED -------------------- Pups::ExecError: cd /var/www/discourse && su discourse -c 'bundle exec rake db:migrate' failed with return # Location of failure: /pups/lib/pups/exec_command.rb:112:in `spawn' exec failed with the params {"cd"=>"$home", "hook"=>"db_migrate", "cmd"=>["su discourse -c 'bundle exec rake db:migrate'"]} d627ad17d1f22d839a7dc8099878e6272eb3ea1772539f6628e2a23dd830aca2 ** FAILED TO BOOTSTRAP ** please scroll up and look for earlier error messages, there may be more than one. ./discourse-doctor may help diagnose the problem. I have no idea how there could be two accounts with the same username. Our site is hard down. What do I do now? Thanks, Gunnar

I think you can get the site back up with ./launcher start app Then take a look at ways to delete the duplicate: [image] Duplicate data explorer key blocking rebuild Support Hey all, Rare I ask for help, but I’m puzzled about how to resolve this. Just rebuilt my site and getting this: discourse-data-explorer is already at latest compatible version I, [2020-08-28T17:53:57.578920 #1] INFO -- : > cd /var/www/discourse && su discourse -c 'bundle exec rake db:migrate' 2020-08-28 17:54:07.380 UTC [3060] discourse@discourse ERROR: duplicate key value violates unique constraint "data_explorer_queries_pkey" 2020-08-28 17:54:07.380 UTC [3060] discourse@discourse DETAIL:… and [image] Failed upgrade: UniqueViolation: duplicate key value violates unique constraint "data_explorer_queries_pkey" Self-hosting Today’s upgrade to latest failed: FAILED -------------------- Pups::ExecError: cd /var/www/discourse && su discourse -c 'bundle exec rake db:migrate' failed with return # Location of failure: /pups/lib/pups/exec_command.rb:112:in `spawn' exec failed with the params {"cd"=>"$home", "hook"=>"db_migrate", "cmd"=>["su discourse -c 'bundle exec rake db:migrate'"]} 590cf0611c566ea6df5f70ffdd2cec2359e84eaea29b7abcde77d56288a46370 ** FAILED TO BOOTSTRAP ** please scrol…

More information I should probably provide: This is a single container install. The first rebuild completed without any errors. This error occurred when I was running the second rebuild, after the first rebuild completed successfully. Thanks, Gunnar

Do you have enough disk space? Error related to the required PostgreSQL update to version 13 afaik. Did you follow this upgrade method? cd /var/discourse git pull ./launcher rebuild app If you search the forum for those errors you’ll see a lot of past information and possible fixes. [image] Gunnar: ./discourse-doctor may help diagnose the problem. Try this. Search the forum for your posted errors to see various fixes that worked for others.

I got the site back up by renaming postgres_data_old back to postgres_data and then running ./launcher start app to start the old image. So, I’m back to square 1, but at least the site is up. I tried to find the duplicate key, by entering this: select * from users WHERE username = 'DaveW'; (username = DaveW was the problem key as shown in the error message in my first post in this thread.) The command returned just one row, one user. What am I missing? Thanks, Gunnar

You do have two, possibly capitalized differently in the DB: [Screen Shot 2021-01-22 at 3.25.53 PM]

[image] omarfilip: You do have two Did you visit my site? :wink: I see what you mean, when I search the in the GUI, I do see two. However, when I click on both as an admin and bring up the details page, they appear to be the same user. I can’t find a single detail that’s different between the two. Basically, no matter which one of those I click, I get the data for the second one, the one with the full name entered. Since that user had never posted, and not visited in a while, I deleted the account from the GUI. Interestingly, I still have the other one, the first one on your list, without a full name. But now, if I click on that user, nothing happens. I can see a dialog trying to open, then immediately closing. Searching the database directly for username = DaveW now returns zero rows. However, if I search for select * from users WHERE name = 'DaveW'; (name, not username,) I get 1 row returned: 19732 | DaveW | 2016-11-15 12:43:02.708166 | 2016-11-15 12:43:02.708166 | DaveW | 0 | | | | t | davew | 2016-11-15 12:43:02.708166 | f | 2017-06-01 18:09:45.018058 | 1 | f | | | | | | | 0 | 0 | | f | | | | | | f | | | | | (1 row) Notice how it has DaveW (same spelling) in the username field! This account is also three years older than the other one. Can I get rid of it with: DELETE from users WHERE id = 19732; without any ill effects? Thanks for all your help! Gunnar

You might also change the username of one of the duplicates.

[image] pfaffman: change the username Right. Once I do, should I be able to restart the upgrade from scratch? I.e. run rebuild twice? Thanks! Gunnar

That should work. Unless there are other duplicate users.

[image] pfaffman: Unless there are other duplicate users. Turns out there are, at least one. PG::UniqueViolation: ERROR: duplicate key value violates unique constraint "index_users_on_username_lower" DETAIL: Key (username_lower)=(robs) already exists. Is there any way I can scan the database for duplicates? Not just users, but any table that could cause this error during the upgrade? I’d like to find them and fix them all before I try again. Also, it would be good if this could be part of the upgrade process. Perhaps run a scan first, and stop the process before any changes have been made. Warn the user and provide a list of the duplicates, with a link to a page here on meta with instructions on how to fix?

2.7.0.beta2 upgrade failed with ERROR: duplicate key

Product Support Self-hosting

omarfilip January 23, 2021, 12:18am 11

Of course!

That should delete 19732 but I can’t guarantee the absence of any ill effects. In any case, a backup before proceeding further is advisable.

Topic		Replies	Views
Upgrade failed with "duplicate key value violates unique constraint" error Self-hosting	17	2744	February 29, 2020
PG::UniqueViolation Problem during 3.1.0.beta4 upgrade Self-hosting	5	707	April 19, 2023
Upgrade Issues: Failed Upgrade due to Duplicate Key, Failed Snapshot Restore Self-hosting	7	1826	April 25, 2023
[Site Down :(] Upgrade\Rebuild Failure - DB Migration Issue? Self-hosting	8	945	June 19, 2023
Upgrade Failed from 2.7.0.beta1 to 2.7.0.beta3 Self-hosting	13	2385	February 15, 2021

2.7.0.beta2 upgrade failed with ERROR: duplicate key

Related topics