@agriesser I tried the ALTER as follows and unfortunately, it still did not resolve it. I noticed we had an extra row that you didn’t have in the test, might be related?
psql (13.7, server 13.6)
SSL connection (protocol: TLSv1.2, cipher: ECDHE-RSA-AES256-GCM-SHA384, bits: 256, compression: off)
Type "help" for help.
internal-services-mm-prod=> select indexname,indexdef from pg_indexes where tablename='reactions';
indexname | indexdef
--------------------------+---------------------------------------------------------------------------------------------------
idx_34040_primary | CREATE UNIQUE INDEX idx_34040_primary ON public.reactions USING btree (postid, userid, emojiname)
idx_reactions_channel_id | CREATE INDEX idx_reactions_channel_id ON public.reactions USING btree (channelid)
(2 rows)
internal-services-mm-prod=> alter index idx_34040_primary RENAME TO pkey_reactions;
ALTER INDEX
internal-services-mm-prod=> select indexname,indexdef from pg_indexes where tablename='reactions';
indexname | indexdef
--------------------------+------------------------------------------------------------------------------------------------
pkey_reactions | CREATE UNIQUE INDEX pkey_reactions ON public.reactions USING btree (postid, userid, emojiname)
idx_reactions_channel_id | CREATE INDEX idx_reactions_channel_id ON public.reactions USING btree (channelid)
(2 rows)
internal-services-mm-prod=> \q
[ec2-user@ mattermost]$ docker-compose -f docker-compose.yml -f docker-compose.without-nginx.yml up
Building with native build. Learn about native build in Compose here: https://docs.docker.com/go/compose-native-build/
Creating network "mattermost_default" with the default driver
Creating mattermost_mattermost_1 ...
Attaching to mattermost_mattermost_1
mattermost_1 | {"timestamp":"2022-07-29 02:24:44.284 Z","level":"info","msg":"Server is initializing...","caller":"app/server.go:264","go_version":"go1.18.1"}
mattermost_1 | {"timestamp":"2022-07-29 02:24:44.320 Z","level":"info","msg":"Pinging SQL","caller":"sqlstore/store.go:228","database":"master"}
mattermost_1 | {"timestamp":"2022-07-29 02:24:44.352 Z","level":"fatal","msg":"Failed to apply database migrations.","caller":"sqlstore/store.go:165","error":"driver: postgres, message: failed to execute migration, command: executing_query, originalError: pq: multiple primary keys for table \"reactions\" are not allowed, query: \n\nCREATE TABLE IF NOT EXISTS reactions(\n userid VARCHAR(26) NOT NULL,\n postid VARCHAR(26) NOT NULL,\n emojiname VARCHAR(64) NOT NULL,\n createat bigint\n);\n\nALTER TABLE reactions ADD COLUMN IF NOT EXISTS updateat bigint;\nALTER TABLE reactions ADD COLUMN IF NOT EXISTS deleteat bigint;\n\nDO $$\n<<alter_pk>>\nDECLARE\n existing_index text;\nBEGIN\n SELECT string_agg(a.attname, ',') INTO existing_index\n FROM pg_constraint AS c\n CROSS JOIN\n (SELECT unnest(conkey) FROM pg_constraint WHERE conrelid = 'reactions'::regclass AND contype='p') AS cols(colnum)\n INNER JOIN pg_attribute AS a ON a.attrelid = c.conrelid AND cols.colnum = a.attnum\n WHERE c.contype = 'p'\n AND c.conrelid = 'reactions'::regclass;\n\n IF COALESCE (existing_index, '') <> text('postid,userid,emojiname') THEN\n ALTER TABLE reactions\n DROP CONSTRAINT IF EXISTS reactions_pkey,\n ADD PRIMARY KEY (postid, userid, emojiname);\n END IF;\nEND alter_pk $$;\n\nALTER TABLE reactions ADD COLUMN IF NOT EXISTS remoteid VARCHAR(26);\n\n"}
mattermost_1 | {"timestamp":"2022-07-29 02:24:45.696 Z","level":"info","msg":"Server is initializing...","caller":"app/server.go:264","go_version":"go1.18.1"}
mattermost_1 | {"timestamp":"2022-07-29 02:24:45.696 Z","level":"info","msg":"Pinging SQL","caller":"sqlstore/store.go:228","database":"master"}
mattermost_1 | {"timestamp":"2022-07-29 02:24:45.715 Z","level":"fatal","msg":"Failed to apply database migrations.","caller":"sqlstore/store.go:165","error":"driver: postgres, message: failed to execute migration, command: executing_query, originalError: pq: multiple primary keys for table \"reactions\" are not allowed, query: \n\nCREATE TABLE IF NOT EXISTS reactions(\n userid VARCHAR(26) NOT NULL,\n postid VARCHAR(26) NOT NULL,\n emojiname VARCHAR(64) NOT NULL,\n createat bigint\n);\n\nALTER TABLE reactions ADD COLUMN IF NOT EXISTS updateat bigint;\nALTER TABLE reactions ADD COLUMN IF NOT EXISTS deleteat bigint;\n\nDO $$\n<<alter_pk>>\nDECLARE\n existing_index text;\nBEGIN\n SELECT string_agg(a.attname, ',') INTO existing_index\n FROM pg_constraint AS c\n CROSS JOIN\n (SELECT unnest(conkey) FROM pg_constraint WHERE conrelid = 'reactions'::regclass AND contype='p') AS cols(colnum)\n INNER JOIN pg_attribute AS a ON a.attrelid = c.conrelid AND cols.colnum = a.attnum\n WHERE c.contype = 'p'\n AND c.conrelid = 'reactions'::regclass;\n\n IF COALESCE (existing_index, '') <> text('postid,userid,emojiname') THEN\n ALTER TABLE reactions\n DROP CONSTRAINT IF EXISTS reactions_pkey,\n ADD PRIMARY KEY (postid, userid, emojiname);\n END IF;\nEND alter_pk $$;\n\nALTER TABLE reactions ADD COLUMN IF NOT EXISTS remoteid VARCHAR(26);\n\n"}
mattermost_mattermost_1 exited with code 1
[ec2-user@ mattermost]$ docker-compose -f docker-compose.yml -f docker-compose.without-nginx.yml down