Posting the solution here for anyone else that comes up against a similar problem
I upgraded our forums from v1.6 to 3.1 using a backup and restore on a new server
Since updating new user sign ups stopped working via using the discourse_api and SSO
the error was a
PG::UniqueViolation: ERROR: duplicate key value violates unique constraint “user_emails_pkey”
DETAIL: Key (id)=(xx) already exists.
after some digging i logged into the droplet and accessed postgres - specifically the “user_emails” table
Table "public.user_emails"
Column | Type | Collation | Nullable | Default
------------------+-----------------------------+-----------+----------+-----------------------------------------
id | integer | | not null | nextval('user_emails_id_seq'::regclass)
user_id | integer | | not null |
email | character varying(513) | | not null |
primary | boolean | | not null | false
created_at | timestamp without time zone | | not null |
updated_at | timestamp without time zone | | not null |
normalized_email | character varying | | |
Indexes:
"user_emails_pkey" PRIMARY KEY, btree (id)
"index_user_emails_on_email" UNIQUE, btree (lower(email::text))
"index_user_emails_on_normalized_email" btree (lower(normalized_email::text))
"index_user_emails_on_user_id" btree (user_id)
"index_user_emails_on_user_id_and_primary" UNIQUE, btree (user_id, "primary") WHERE "primary"
The id column has a “user_emails_id_seq” for selecting the next available ID to use when inserting a new user
i ran SELECT MAX(id) FROM user_emails; which returned 237023
and then SELECT nextval(‘user_emails_id_seq’); which returned 23
so the table was out of sequence.
to fix
SELECT setval(‘user_emails_id_seq’, (SELECT MAX(id) FROM user_emails)+1);