Mattermost-server: Upgraded to 5.30.1 from 5.21.0, numerous (database?) problems
Summary
Mattermost 5.30.1 can't reply to posts, can't edit posts, and loading channels is extremely slow or fails.
Steps to reproduce
- Upgrade from 5.21.0 to 5.24.1, 5.25.1, or 5.30.1.
- Reload clients. Desktop clients, mobile clients, web clients, all exhibit the same behaviour.
- Attempt to load a channel, any channel. If it doesn't leave you on "Loading..." indefinitely, it will take anywhere from 30 seconds to a couple of minutes.
- Attempt to reply to any message. The reply turns grey with a loading... icon, then vanishes after around
QueryTimeoutseconds. - Attempt to edit any message. Clicking Save has no effect at all, Cancel lets you out of the dialog.
Expected behavior
Mattermost features work as advertised, channels load quickly.
Observed behavior (that appears unintentional)
Channels load slowly or not at all. Replies don't work. Edits don't work.
Possible fixes
Sorry, I'm useless in the code.
More details
What OS and version is the Mattermost server installed? Ubuntu 20.04.1 LTS
What is your Mattermost server version? 5.30.1
Are you experiencing the issues with the browser webapp, if so which one? Yes, all.
Are you experiencing the issues with the Mattermost Desktop App, if so what version and OS? Yes, 4.6.1 on Mac, Linux, Windows.
Are you experiencing the issues with the Mattermost Mobile App, if so what version and OS? Yes, current on Android and iOS.
Can you send a snippet of the Mattermost server logs around the time of the incident? Yes, see below.
Are you running Mattermost in a container and/or using container orchestration? No
Additional info:
- PostgreSQL 10.14
- server RAM/CPU are not taxed at all
Symptoms:
- Attempts to reply time out after the Mattermost
QueryTimeoutsetting value. Reply text discarded by the client. - Attempts to edit fail; nothing happens when you click Save. The PUT "patch" request is sent according to Firefox's Network panel, no failure returned.
- Channels take a very long time to load. Clients sit indefinitely on the "Loading..." screen. Sometimes Cmd-R (which refreshes the whole app) works, sometimes switching channels works, sometimes nothing works.
Some fails from the mattermost.log. Lots of these:
{"level":"error","ts":1610372929.7626736,"caller":"mlog/log.go:229","msg":"Unable to get the post.","path":"/api/v4/users/isc1exemebr8fmf9par7g4y6yw/channels/z1nhpog8npdubqjmhym6oquycr/posts/unread","request_id":"htu5g9xfo3ymfyt5pn8bxgso5a","ip_addr":"198.251.52.219","user_id":"isc1exemebr8fmf9par7g4y6yw","method":"GET","err_where":"GetPostThread","http_code":500,"err_details":"failed to get Post with id=g1dckxk6ctnfdkuwaiqfiyd3fe: pq: canceling statement due to user request"}
{"level":"error","ts":1610372931.0141468,"caller":"mlog/log.go:229","msg":"Unable to get the post.","path":"/api/v4/users/isc1exemebr8fmf9par7g4y6yw/channels/51wupdcaq7yw3q8mfftptw47ho/posts/unread","request_id":"fp5emt89opd5zj8ry8kz1kq84a","ip_addr":"198.251.52.219","user_id":"isc1exemebr8fmf9par7g4y6yw","method":"GET","err_where":"GetPostThread","http_code":500,"err_details":"failed to get Post with id=zb47uz181pbippqhy6egcur4xc: pq: canceling statement due to user request"}
{"level":"error","ts":1610372931.0144043,"caller":"mlog/log.go:229","msg":"Unable to get the post.","path":"/api/v4/users/isc1exemebr8fmf9par7g4y6yw/channels/h5jo949oi7d7dcnxstwoocx7nr/posts/unread","request_id":"8xyjkrn85b8s7dbrudxk3e6aih","ip_addr":"198.251.52.219","user_id":"isc1exemebr8fmf9par7g4y6yw","method":"GET","err_where":"GetPostThread","http_code":500,"err_details":"failed to get Post with id=49bkt77kdib7mn6oso54mpqbpy: pq: canceling statement due to user request"}
{"level":"error","ts":1610372934.1321552,"caller":"mlog/log.go:229","msg":"Unable to get the post.","path":"/api/v4/users/isc1exemebr8fmf9par7g4y6yw/channels/syc1iuesr38mimwibxgasu7obr/posts/unread","request_id":"uqwuowz8up8zmjmwa9f3cgi7oe","ip_addr":"198.251.52.219","user_id":"isc1exemebr8fmf9par7g4y6yw","method":"GET","err_where":"GetPostThread","http_code":500,"err_details":"failed to get Post with id=xd8e5bfrmtr63xeqm7c4ieouwc: pq: canceling statement due to user request"}
{"level":"error","ts":1610372934.1486177,"caller":"mlog/log.go:229","msg":"Unable to get the post.","path":"/api/v4/users/isc1exemebr8fmf9par7g4y6yw/channels/ccrfxcsydp8zbmpzsknpnimako/posts/unread","request_id":"79h7aj6suffr7bknnmycjghw3y","ip_addr":"198.251.52.219","user_id":"isc1exemebr8fmf9par7g4y6yw","method":"GET","err_where":"GetPostThread","http_code":500,"err_details":"failed to get Post with id=k83whdae7bym7gr6ahmkz79t1r: pq: canceling statement due to user request"}
Lots of these:
{"level":"error","ts":1610375164.733936,"caller":"mlog/log.go:229","msg":"Unable to get the post.","path":"/api/v4/users/7dyyaukp97re3prnrdoj1gigry/channels/c8dtzuk6x7rtxk88i3jqep4yqr/posts/unread","request_id":"8cjqsjf3otni3bhrk1bxemgtye","ip_addr":"184.175.47.3","user_id":"7dyyaukp97re3prnrdoj1gigry","method":"GET","err_where":"GetPostThread","http_code":500,"err_details":"failed to get Post with id=bodq7ucqwf8y8bwjb57g5o7i8h: pq: canceling statement due to user request"}
{"level":"error","ts":1610375341.0068262,"caller":"mlog/log.go:229","msg":"Unable to get the post.","path":"/api/v4/posts/qkmtp78n4iypbynootrmaxrrey/patch","request_id":"bac1b7zottncme4zqy8kf9uc7r","ip_addr":"157.52.11.88","user_id":"86wyy4d69tyfmjupze3orobmrc","method":"PUT","err_where":"UpdatePost","http_code":500,"err_details":"failed to get Post with id=qkmtp78n4iypbynootrmaxrrey: pq: canceling statement due to user request"}
{"level":"error","ts":1610375345.4909415,"caller":"mlog/log.go:229","msg":"Unable to get the post.","path":"/api/v4/posts/qkmtp78n4iypbynootrmaxrrey/patch","request_id":"kspqeohas38h8npr7bxk69qnhc","ip_addr":"157.52.11.88","user_id":"86wyy4d69tyfmjupze3orobmrc","method":"PUT","err_where":"UpdatePost","http_code":500,"err_details":"failed to get Post with id=qkmtp78n4iypbynootrmaxrrey: pq: canceling statement due to user request"}
{"level":"error","ts":1610375346.3205173,"caller":"mlog/log.go:229","msg":"Unable to get the post.","path":"/api/v4/posts/qkmtp78n4iypbynootrmaxrrey/patch","request_id":"fyebjgyt47nbikdghyypa3b9wr","ip_addr":"157.52.11.88","user_id":"86wyy4d69tyfmjupze3orobmrc","method":"PUT","err_where":"UpdatePost","http_code":500,"err_details":"failed to get Post with id=qkmtp78n4iypbynootrmaxrrey: pq: canceling statement due to user request"}
{"level":"error","ts":1610375368.4314065,"caller":"mlog/log.go:229","msg":"Unable to get the post.","path":"/api/v4/posts/qkmtp78n4iypbynootrmaxrrey/patch","request_id":"ueu1cmghk3db38ybos787tkzce","ip_addr":"157.52.11.88","user_id":"86wyy4d69tyfmjupze3orobmrc","method":"PUT","err_where":"UpdatePost","http_code":500,"err_details":"failed to get Post with id=qkmtp78n4iypbynootrmaxrrey: pq: canceling statement due to user request"}
{"level":"error","ts":1610375378.7256417,"caller":"mlog/log.go:229","msg":"Unable to get the post.","path":"/api/v4/users/zb8d5dh4yjn88jpn19zc4cqsqa/channels/zjfojscqx3fxdbrh48mofqhs8r/posts/unread","request_id":"bw3cicc7g3fame139udgbiz8xa","ip_addr":"99.251.19.131","user_id":"zb8d5dh4yjn88jpn19zc4cqsqa","method":"GET","err_where":"GetPostThread","http_code":500,"err_details":"failed to get Post with id=nu6jknwajib75kmpbasz96u1xe: pq: canceling statement due to user request"}
{"level":"error","ts":1610375486.452486,"caller":"mlog/log.go:229","msg":"Unable to get the posts for the channel.","path":"/api/v4/channels/c8dtzuk6x7rtxk88i3jqep4yqr/posts","request_id":"uzp3386k5pbmubxyy5zhi3j93w","ip_addr":"157.52.11.88","user_id":"86wyy4d69tyfmjupze3orobmrc","method":"GET","err_where":"GetPostsBeforePost","http_code":500,"err_details":"failed to find Posts with channelId=c8dtzuk6x7rtxk88i3jqep4yqr: pq: canceling statement due to user request"}
{"level":"error","ts":1610375562.919171,"caller":"mlog/log.go:229","msg":"Unable to get the posts for the channel.","path":"/api/v4/users/zb8d5dh4yjn88jpn19zc4cqsqa/channels/nmwqse9hgtg3x8fb1q6pjdxm3c/posts/unread","request_id":"36kjn5oobt85p8x8bank77kfya","ip_addr":"99.251.19.131","user_id":"zb8d5dh4yjn88jpn19zc4cqsqa","method":"GET","err_where":"GetPostsPage","http_code":500,"err_details":"failed to find Posts with channelId=nmwqse9hgtg3x8fb1q6pjdxm3c: pq: canceling statement due to user request"}
Some complaints from PostgreSQL's log:
2021-01-11 09:29:38.883 EST [13080] oscar@eve LOG: duration: 28758.067 ms execute <unnamed>: SELECT q2.*
FROM
Posts q2
INNER JOIN
(SELECT DISTINCT
q3.RootId
FROM
(SELECT
RootId
FROM
Posts
WHERE
ChannelId = $1
AND DeleteAt = 0
ORDER BY CreateAt DESC
LIMIT $2 OFFSET $3) q3
WHERE q3.RootId != '') q1
ON q1.RootId = q2.Id OR q1.RootId = q2.RootId
WHERE
ChannelId = $4
AND DeleteAt = 0
ORDER BY CreateAt
2021-01-11 09:29:38.883 EST [13080] oscar@eve DETAIL: parameters: $1 = 'n6xros8npfbntrn7gxbkwpjkoe', $2 = '30', $3 = '0', $4 = 'n6xros8npfbntrn7gxbkwpjkoe'
2021-01-11 09:29:38.883 EST [13081] oscar@eve LOG: duration: 25987.145 ms execute <unnamed>: SELECT q2.*
FROM
Posts q2
INNER JOIN
(SELECT DISTINCT
q3.RootId
FROM
(SELECT
RootId
FROM
Posts
WHERE
ChannelId = $1
AND DeleteAt = 0
ORDER BY CreateAt DESC
LIMIT $2 OFFSET $3) q3
WHERE q3.RootId != '') q1
ON q1.RootId = q2.Id OR q1.RootId = q2.RootId
WHERE
ChannelId = $4
AND DeleteAt = 0
ORDER BY CreateAt
2021-01-11 09:33:48.622 EST [13082] oscar@eve STATEMENT: SELECT p.*, (SELECT count(Posts.Id) FROM Posts WHERE Posts.RootId = (CASE WHEN p.RootId = '' THEN p.Id ELSE p.RootId END) AND Posts.DeleteAt = 0) as ReplyCount FROM Posts p WHERE p.Id = $1 AND p.DeleteAt = 0
2021-01-11 09:33:48.650 EST [10629] oscar@eve ERROR: canceling statement due to user request
2021-01-11 09:33:48.650 EST [10629] oscar@eve STATEMENT: SELECT p.*, (SELECT count(Posts.Id) FROM Posts WHERE Posts.RootId = (CASE WHEN p.RootId = '' THEN p.Id ELSE p.RootId END) AND Posts.DeleteAt = 0) as ReplyCount FROM Posts p WHERE p.Id = $1 AND p.DeleteAt = 0
2021-01-11 09:33:58.577 EST [10582] oscar@eve ERROR: canceling statement due to user request
2021-01-11 09:33:58.577 EST [10582] oscar@eve STATEMENT: SELECT p.*, (SELECT count(Posts.Id) FROM Posts WHERE Posts.RootId = (CASE WHEN p.RootId = '' THEN p.Id ELSE p.RootId END) AND Posts.DeleteAt = 0) as ReplyCount FROM Posts p WHERE p.Id = $1 AND p.DeleteAt = 0
2021-01-11 09:33:58.619 EST [10687] oscar@eve ERROR: canceling statement due to user request
2021-01-11 09:33:58.619 EST [10687] oscar@eve STATEMENT: SELECT p.*, (SELECT count(Posts.Id) FROM Posts WHERE Posts.RootId = (CASE WHEN p.RootId = '' THEN p.Id ELSE p.RootId END) AND Posts.DeleteAt = 0) as ReplyCount FROM Posts p WHERE p.Id = $1 AND p.DeleteAt = 0
2021-01-11 09:33:59.091 EST [10731] oscar@eve ERROR: canceling statement due to user request
2021-01-11 09:33:59.091 EST [10731] oscar@eve STATEMENT: SELECT
COUNT(Posts.Id) AS Value
FROM
Posts,
Channels
WHERE
Posts.ChannelId = Channels.Id
2021-01-11 09:33:59.091 EST [17227] FATAL: terminating connection due to administrator command
2021-01-11 09:33:59.091 EST [17227] STATEMENT: SELECT
COUNT(Posts.Id) AS Value
FROM
Posts,
Channels
WHERE
Posts.ChannelId = Channels.Id
2021-01-11 09:33:59.093 EST [1174] LOG: worker process: parallel worker for PID 10731 (PID 17227) exited with exit code 1
2021-01-11 09:33:59.093 EST [17228] FATAL: terminating connection due to administrator command
2021-01-11 09:33:59.093 EST [17228] STATEMENT: SELECT
COUNT(Posts.Id) AS Value
FROM
Posts,
Channels
WHERE
Posts.ChannelId = Channels.Id
2021-01-11 09:33:59.095 EST [1174] LOG: worker process: parallel worker for PID 10731 (PID 17228) exited with exit code 1
2021-01-11 09:33:59.544 EST [13081] oscar@eve ERROR: canceling statement due to user request
2021-01-11 09:33:59.544 EST [13081] oscar@eve STATEMENT: SELECT p.*, (SELECT count(Posts.Id) FROM Posts WHERE Posts.RootId = (CASE WHEN p.RootId = '' THEN p.Id ELSE p.RootId END) AND Posts.DeleteAt = 0) as ReplyCount FROM Posts p WHERE p.Id = $1 AND p.DeleteAt = 0
2021-01-11 09:33:59.551 EST [10676] oscar@eve ERROR: canceling statement due to user request
2021-01-11 09:33:59.551 EST [10676] oscar@eve STATEMENT: SELECT p.*, (SELECT count(Posts.Id) FROM Posts WHERE Posts.RootId = (CASE WHEN p.RootId = '' THEN p.Id ELSE p.RootId END) AND Posts.DeleteAt = 0) as ReplyCount FROM Posts p WHERE p.Id = $1 AND p.DeleteAt = 0
2021-01-11 09:34:04.119 EST [13080] oscar@eve ERROR: canceling statement due to user request
2021-01-11 09:34:04.119 EST [13080] oscar@eve STATEMENT: SELECT p.*, (SELECT COUNT(Posts.Id) FROM Posts WHERE Posts.RootId = (CASE WHEN p.RootId = '' THEN p.Id ELSE p.RootId END) AND Posts.DeleteAt = 0) AS ReplyCount FROM Posts p WHERE (CreateAt < (SELECT CreateAt FROM Posts WHERE Id = $1) AND ChannelId = $2 AND DeleteAt = $3) ORDER BY ChannelId, DeleteAt, CreateAt DESC LIMIT 30 OFFSET 0
Taffer
All 24 comments
Hi @Taffer,
It looks like you are experience some serious performance issues. I am assuming that before the upgrade everything was working smoothly.
To help debugging this further, I have a couple of requests:
- How big is your database? Number of posts/channels/teams/users will do.
- Does the PostgreSQL instance run alongside the Mattermost server? (e.g. on the same machine)
- Can you please share the content of the
SqlSettingssection fromconfig/config.json? Please remove/omit any sensitive information like username/password/hostname but otherwise leave the rest unaltered.
streamer45
on 11 Jan 2021
Just ran mattermost integrity --verbose:
{"level":"info","ts":1610382500.3648016,"caller":"sqlstore/integrity.go:524","msg":"Starting relational integrity checks..."}
Found 0 records in relation CommandWebhooks orphans of relation Channels
Found 164 records in relation ChannelMemberHistory orphans of relation Channels
Child is missing Parent 16yxg58h6pybjgj5tz3grz65eo (ChannelMemberHistory.ChannelId)
...
Found 0 records in relation ChannelMembers orphans of relation Channels
Found 0 records in relation IncomingWebhooks orphans of relation Channels
Found 0 records in relation OutgoingWebhooks orphans of relation Channels
{"level":"error","ts":1610382513.435535,"caller":"app/product_notices.go:307","msg":"Failed to fetch post count","error":"failed to count Posts: pq: canceling statement due to user request"}
{"level":"error","ts":1610382530.6561651,"caller":"sqlstore/integrity.go:69","msg":"pq: canceling statement due to user request"}
pq: canceling statement due to user request
I'll post num posts/channels/teams/users if the System Console manages to pull the info. No luck so far... There's one team and ~50 active users.
PostgreSQL is running on the same VM. There appears to be no memory or CPU pressure at all (~800MB used of 12GB RAM, CPU usage around 7% for each of the six cores).
SqlSettings block:
"SqlSettings": {
"DriverName": "postgres",
"DataSource": "postgres://oscar:password@localhost:5432/eve?sslmode=require\u0026connect_timeout=10",
"DataSourceReplicas": [],
"DataSourceSearchReplicas": [],
"MaxIdleConns": 20,
"ConnMaxLifetimeMilliseconds": 3600000,
"MaxOpenConns": 300,
"Trace": false,
"AtRestEncryptKey": "dw3fckx7aj4zi6qwnzb98ns3co36i8aj",
"QueryTimeout": 30,
"DisableDatabaseSearch": false
},
Per command-line tools, 188 public channels. Stats in the System Console won't load, although it was able to pull enough data to draw the graphs for Total Posts, etc.
Taffer
on 11 Jan 2021
@Taffer
Thanks. Everything seems to be pointing at a database issue.
Did anything change on the database side since you upgraded?
It looks like it's timing out at 30 seconds, as defined by SqlSettings.QueryTimeout. It should never get to such high query times especially since there's no apparent load on it.
Are you able to run a few queries directly on the DB? If so can you paste the output of:
EXPLAIN ANALYZE SELECT COUNT(Id) FROM Posts;
Can confirm, it's timing out at the QueryTimeout setting.
Database hasn't changed other than to install patches; I've been afraid of migrating to PostgreSQL 12 because it might break something and we really depend on Mattermost while working from home.
EXPLAIN ANALYZE SELECT COUNT(Id) FROM Posts; doesn't seem able to complete (!). Our Posts table appears to be around 83GB.
I can't seem to set shared_buffers on this instance above 256MB. ulimit says unlimited, kernel shared memory limits are set to max, there's 12GB of RAM in the VM and Mattermost itself is using around 2GB. Going to get the infrastructure folks to bump the VM size up, see if that'll help at all.
Taffer
on 11 Jan 2021
Somehow, the mobile app can switch channels quickly. It can't do replies or Edit (Edit says "Received invalid response from the server." after a moment).
Taffer
on 11 Jan 2021
If I have to guess, my money would be on the fact that the schema migration hasn't worked properly. And some indexes, that were supposed to be applied, didn't get applied.
Can you show us a pg_dump of the current schema of your database?
agnivade
on 12 Jan 2021
Certainly:
--
-- PostgreSQL database dump
--
-- Dumped from database version 10.14 (Ubuntu 10.14-0ubuntu0.18.04.1)
-- Dumped by pg_dump version 10.14 (Ubuntu 10.14-0ubuntu0.18.04.1)
SET statement_timeout = 0;
SET lock_timeout = 0;
SET idle_in_transaction_session_timeout = 0;
SET client_encoding = 'UTF8';
SET standard_conforming_strings = on;
SELECT pg_catalog.set_config('search_path', '', false);
SET check_function_bodies = false;
SET xmloption = content;
SET client_min_messages = warning;
SET row_security = off;
--
-- Name: eve; Type: DATABASE; Schema: -; Owner: postgres
--
CREATE DATABASE eve WITH TEMPLATE = template0 ENCODING = 'UTF8' LC_COLLATE = 'en_US.UTF-8' LC_CTYPE = 'en_US.UTF-8';
ALTER DATABASE eve OWNER TO postgres;
\connect eve
SET statement_timeout = 0;
SET lock_timeout = 0;
SET idle_in_transaction_session_timeout = 0;
SET client_encoding = 'UTF8';
SET standard_conforming_strings = on;
SELECT pg_catalog.set_config('search_path', '', false);
SET check_function_bodies = false;
SET xmloption = content;
SET client_min_messages = warning;
SET row_security = off;
--
-- Name: plpgsql; Type: EXTENSION; Schema: -; Owner:
--
CREATE EXTENSION IF NOT EXISTS plpgsql WITH SCHEMA pg_catalog;
--
-- Name: EXTENSION plpgsql; Type: COMMENT; Schema: -; Owner:
--
COMMENT ON EXTENSION plpgsql IS 'PL/pgSQL procedural language';
SET default_tablespace = '';
SET default_with_oids = false;
--
-- Name: audits; Type: TABLE; Schema: public; Owner: oscar
--
CREATE TABLE public.audits (
id character varying(26) NOT NULL,
createat bigint,
userid character varying(26),
action character varying(512),
extrainfo character varying(1024),
ipaddress character varying(64),
sessionid character varying(26)
);
ALTER TABLE public.audits OWNER TO oscar;
--
-- Name: bots; Type: TABLE; Schema: public; Owner: oscar
--
CREATE TABLE public.bots (
userid character varying(26) NOT NULL,
description character varying(1024),
ownerid character varying(190),
createat bigint,
updateat bigint,
deleteat bigint,
lasticonupdate bigint
);
ALTER TABLE public.bots OWNER TO oscar;
--
-- Name: channelmemberhistory; Type: TABLE; Schema: public; Owner: oscar
--
CREATE TABLE public.channelmemberhistory (
channelid character varying(26) NOT NULL,
userid character varying(26) NOT NULL,
jointime bigint NOT NULL,
leavetime bigint
);
ALTER TABLE public.channelmemberhistory OWNER TO oscar;
--
-- Name: channelmembers; Type: TABLE; Schema: public; Owner: oscar
--
CREATE TABLE public.channelmembers (
channelid character varying(26) NOT NULL,
userid character varying(26) NOT NULL,
roles character varying(64),
lastviewedat bigint,
msgcount bigint,
mentioncount bigint,
notifyprops character varying(2000),
lastupdateat bigint,
schemeuser boolean,
schemeadmin boolean,
schemeguest boolean
);
ALTER TABLE public.channelmembers OWNER TO oscar;
--
-- Name: channels; Type: TABLE; Schema: public; Owner: oscar
--
CREATE TABLE public.channels (
id character varying(26) NOT NULL,
createat bigint,
updateat bigint,
deleteat bigint,
teamid character varying(26),
type character varying(1),
displayname character varying(64),
name character varying(64),
header character varying(1024),
purpose character varying(250),
lastpostat bigint,
totalmsgcount bigint,
extraupdateat bigint,
creatorid character varying(26),
schemeid character varying(26),
groupconstrained boolean
);
ALTER TABLE public.channels OWNER TO oscar;
--
-- Name: clusterdiscovery; Type: TABLE; Schema: public; Owner: oscar
--
CREATE TABLE public.clusterdiscovery (
id character varying(26) NOT NULL,
type character varying(64),
clustername character varying(64),
hostname character varying(512),
gossipport integer,
port integer,
createat bigint,
lastpingat bigint
);
ALTER TABLE public.clusterdiscovery OWNER TO oscar;
--
-- Name: commands; Type: TABLE; Schema: public; Owner: oscar
--
CREATE TABLE public.commands (
id character varying(26) NOT NULL,
token character varying(26),
createat bigint,
updateat bigint,
deleteat bigint,
creatorid character varying(26),
teamid character varying(26),
trigger character varying(128),
method character varying(1),
username character varying(64),
iconurl character varying(1024),
autocomplete boolean,
autocompletedesc character varying(1024),
autocompletehint character varying(1024),
displayname character varying(64),
description character varying(128),
url character varying(1024),
pluginid character varying(190)
);
ALTER TABLE public.commands OWNER TO oscar;
--
-- Name: commandwebhooks; Type: TABLE; Schema: public; Owner: oscar
--
CREATE TABLE public.commandwebhooks (
id character varying(26) NOT NULL,
createat bigint,
commandid character varying(26),
userid character varying(26),
channelid character varying(26),
rootid character varying(26),
parentid character varying(26),
usecount integer
);
ALTER TABLE public.commandwebhooks OWNER TO oscar;
--
-- Name: compliances; Type: TABLE; Schema: public; Owner: oscar
--
CREATE TABLE public.compliances (
id character varying(26) NOT NULL,
createat bigint,
userid character varying(26),
status character varying(64),
count integer,
"desc" character varying(512),
type character varying(64),
startat bigint,
endat bigint,
keywords character varying(512),
emails character varying(1024)
);
ALTER TABLE public.compliances OWNER TO oscar;
--
-- Name: emoji; Type: TABLE; Schema: public; Owner: oscar
--
CREATE TABLE public.emoji (
id character varying(26) NOT NULL,
createat bigint,
updateat bigint,
deleteat bigint,
creatorid character varying(26),
name character varying(64)
);
ALTER TABLE public.emoji OWNER TO oscar;
--
-- Name: fileinfo; Type: TABLE; Schema: public; Owner: oscar
--
CREATE TABLE public.fileinfo (
id character varying(26) NOT NULL,
creatorid character varying(26),
postid character varying(26),
createat bigint,
updateat bigint,
deleteat bigint,
path character varying(512),
thumbnailpath character varying(512),
previewpath character varying(512),
name character varying(256),
extension character varying(64),
size bigint,
mimetype character varying(256),
width integer,
height integer,
haspreviewimage boolean,
minipreview bytea,
content text
);
ALTER TABLE public.fileinfo OWNER TO oscar;
--
-- Name: groupchannels; Type: TABLE; Schema: public; Owner: oscar
--
CREATE TABLE public.groupchannels (
groupid character varying(26) NOT NULL,
autoadd boolean,
createat bigint,
deleteat bigint,
updateat bigint,
channelid character varying(26) NOT NULL,
schemeadmin boolean DEFAULT false
);
ALTER TABLE public.groupchannels OWNER TO oscar;
--
-- Name: groupmembers; Type: TABLE; Schema: public; Owner: oscar
--
CREATE TABLE public.groupmembers (
groupid character varying(26) NOT NULL,
userid character varying(26) NOT NULL,
createat bigint,
deleteat bigint
);
ALTER TABLE public.groupmembers OWNER TO oscar;
--
-- Name: groupteams; Type: TABLE; Schema: public; Owner: oscar
--
CREATE TABLE public.groupteams (
groupid character varying(26) NOT NULL,
autoadd boolean,
createat bigint,
deleteat bigint,
updateat bigint,
teamid character varying(26) NOT NULL,
schemeadmin boolean DEFAULT false
);
ALTER TABLE public.groupteams OWNER TO oscar;
--
-- Name: incomingwebhooks; Type: TABLE; Schema: public; Owner: oscar
--
CREATE TABLE public.incomingwebhooks (
id character varying(26) NOT NULL,
createat bigint,
updateat bigint,
deleteat bigint,
userid character varying(26),
channelid character varying(26),
teamid character varying(26),
displayname character varying(64),
description character varying(500),
username character varying(255),
iconurl character varying(1024),
channellocked boolean
);
ALTER TABLE public.incomingwebhooks OWNER TO oscar;
--
-- Name: jobs; Type: TABLE; Schema: public; Owner: oscar
--
CREATE TABLE public.jobs (
id character varying(26) NOT NULL,
type character varying(32),
priority bigint,
createat bigint,
startat bigint,
lastactivityat bigint,
status character varying(32),
progress bigint,
data character varying(1024)
);
ALTER TABLE public.jobs OWNER TO oscar;
--
-- Name: licenses; Type: TABLE; Schema: public; Owner: oscar
--
CREATE TABLE public.licenses (
id character varying(26) NOT NULL,
createat bigint,
bytes character varying(10000)
);
ALTER TABLE public.licenses OWNER TO oscar;
--
-- Name: linkmetadata; Type: TABLE; Schema: public; Owner: oscar
--
CREATE TABLE public.linkmetadata (
hash bigint NOT NULL,
url character varying(2048),
"timestamp" bigint,
type character varying(16),
data character varying(4096)
);
ALTER TABLE public.linkmetadata OWNER TO oscar;
--
-- Name: oauthaccessdata; Type: TABLE; Schema: public; Owner: oscar
--
CREATE TABLE public.oauthaccessdata (
clientid character varying(26),
userid character varying(26),
token character varying(26) NOT NULL,
refreshtoken character varying(26),
redirecturi character varying(256),
expiresat bigint,
scope character varying(128)
);
ALTER TABLE public.oauthaccessdata OWNER TO oscar;
--
-- Name: oauthapps; Type: TABLE; Schema: public; Owner: oscar
--
CREATE TABLE public.oauthapps (
id character varying(26) NOT NULL,
creatorid character varying(26),
createat bigint,
updateat bigint,
clientsecret character varying(128),
name character varying(64),
description character varying(512),
iconurl character varying(512),
callbackurls character varying(1024),
homepage character varying(256),
istrusted boolean
);
ALTER TABLE public.oauthapps OWNER TO oscar;
--
-- Name: oauthauthdata; Type: TABLE; Schema: public; Owner: oscar
--
CREATE TABLE public.oauthauthdata (
clientid character varying(26),
userid character varying(26),
code character varying(128) NOT NULL,
expiresin integer,
createat bigint,
redirecturi character varying(256),
state character varying(1024),
scope character varying(128)
);
ALTER TABLE public.oauthauthdata OWNER TO oscar;
--
-- Name: outgoingwebhooks; Type: TABLE; Schema: public; Owner: oscar
--
CREATE TABLE public.outgoingwebhooks (
id character varying(26) NOT NULL,
token character varying(26),
createat bigint,
updateat bigint,
deleteat bigint,
creatorid character varying(26),
channelid character varying(26),
teamid character varying(26),
triggerwords character varying(1024),
triggerwhen integer,
callbackurls character varying(1024),
displayname character varying(64),
description character varying(500),
contenttype character varying(128),
username character varying(64),
iconurl character varying(1024)
);
ALTER TABLE public.outgoingwebhooks OWNER TO oscar;
--
-- Name: pluginkeyvaluestore; Type: TABLE; Schema: public; Owner: oscar
--
CREATE TABLE public.pluginkeyvaluestore (
pluginid character varying(190) NOT NULL,
pkey character varying(50) NOT NULL,
pvalue bytea,
expireat bigint
);
ALTER TABLE public.pluginkeyvaluestore OWNER TO oscar;
--
-- Name: posts; Type: TABLE; Schema: public; Owner: oscar
--
CREATE TABLE public.posts (
id character varying(26) NOT NULL,
createat bigint,
updateat bigint,
editat bigint,
deleteat bigint,
ispinned boolean,
userid character varying(26),
channelid character varying(26),
rootid character varying(26),
parentid character varying(26),
originalid character varying(26),
message character varying(65535),
type character varying(26),
props character varying(8000),
hashtags character varying(1000),
filenames character varying(4000),
fileids character varying(150),
hasreactions boolean
);
ALTER TABLE public.posts OWNER TO oscar;
--
-- Name: preferences; Type: TABLE; Schema: public; Owner: oscar
--
CREATE TABLE public.preferences (
userid character varying(26) NOT NULL,
category character varying(32) NOT NULL,
name character varying(32) NOT NULL,
value character varying(2000)
);
ALTER TABLE public.preferences OWNER TO oscar;
--
-- Name: productnoticeviewstate; Type: TABLE; Schema: public; Owner: oscar
--
CREATE TABLE public.productnoticeviewstate (
userid character varying(26) NOT NULL,
noticeid character varying(26) NOT NULL,
viewed integer,
"timestamp" bigint
);
ALTER TABLE public.productnoticeviewstate OWNER TO oscar;
--
-- Name: publicchannels; Type: TABLE; Schema: public; Owner: oscar
--
CREATE TABLE public.publicchannels (
id character varying(26) NOT NULL,
deleteat bigint,
teamid character varying(26),
displayname character varying(64),
name character varying(64),
header character varying(1024),
purpose character varying(250)
);
ALTER TABLE public.publicchannels OWNER TO oscar;
--
-- Name: reactions; Type: TABLE; Schema: public; Owner: oscar
--
CREATE TABLE public.reactions (
userid character varying(26) NOT NULL,
postid character varying(26) NOT NULL,
emojiname character varying(64) NOT NULL,
createat bigint
);
ALTER TABLE public.reactions OWNER TO oscar;
--
-- Name: roles; Type: TABLE; Schema: public; Owner: oscar
--
CREATE TABLE public.roles (
id character varying(26) NOT NULL,
name character varying(64),
displayname character varying(128),
description character varying(1024),
createat bigint,
updateat bigint,
deleteat bigint,
permissions character varying(4096),
schememanaged boolean,
builtin boolean
);
ALTER TABLE public.roles OWNER TO oscar;
--
-- Name: schemes; Type: TABLE; Schema: public; Owner: oscar
--
CREATE TABLE public.schemes (
id character varying(26) NOT NULL,
name character varying(64),
displayname character varying(128),
description character varying(1024),
createat bigint,
updateat bigint,
deleteat bigint,
scope character varying(32),
defaultteamadminrole character varying(64),
defaultteamuserrole character varying(64),
defaultchanneladminrole character varying(64),
defaultchanneluserrole character varying(64),
defaultteamguestrole character varying(64),
defaultchannelguestrole character varying(64)
);
ALTER TABLE public.schemes OWNER TO oscar;
--
-- Name: sessions; Type: TABLE; Schema: public; Owner: oscar
--
CREATE TABLE public.sessions (
id character varying(26) NOT NULL,
token character varying(26),
createat bigint,
expiresat bigint,
lastactivityat bigint,
userid character varying(26),
deviceid character varying(512),
roles character varying(64),
isoauth boolean,
props character varying(1000),
expirednotify boolean DEFAULT false
);
ALTER TABLE public.sessions OWNER TO oscar;
--
-- Name: sidebarcategories; Type: TABLE; Schema: public; Owner: oscar
--
CREATE TABLE public.sidebarcategories (
id character varying(128) NOT NULL,
userid character varying(26),
teamid character varying(26),
sortorder bigint,
sorting character varying(64),
type character varying(64),
displayname character varying(64),
muted boolean
);
ALTER TABLE public.sidebarcategories OWNER TO oscar;
--
-- Name: sidebarchannels; Type: TABLE; Schema: public; Owner: oscar
--
CREATE TABLE public.sidebarchannels (
channelid character varying(26) NOT NULL,
userid character varying(26) NOT NULL,
categoryid character varying(128) NOT NULL,
sortorder bigint
);
ALTER TABLE public.sidebarchannels OWNER TO oscar;
--
-- Name: status; Type: TABLE; Schema: public; Owner: oscar
--
CREATE TABLE public.status (
userid character varying(26) NOT NULL,
status character varying(32),
manual boolean,
lastactivityat bigint
);
ALTER TABLE public.status OWNER TO oscar;
--
-- Name: systems; Type: TABLE; Schema: public; Owner: oscar
--
CREATE TABLE public.systems (
name character varying(64) NOT NULL,
value character varying(1024)
);
ALTER TABLE public.systems OWNER TO oscar;
--
-- Name: teammembers; Type: TABLE; Schema: public; Owner: oscar
--
CREATE TABLE public.teammembers (
teamid character varying(26) NOT NULL,
userid character varying(26) NOT NULL,
roles character varying(64),
deleteat bigint,
schemeuser boolean,
schemeadmin boolean,
schemeguest boolean
);
ALTER TABLE public.teammembers OWNER TO oscar;
--
-- Name: teams; Type: TABLE; Schema: public; Owner: oscar
--
CREATE TABLE public.teams (
id character varying(26) NOT NULL,
createat bigint,
updateat bigint,
deleteat bigint,
displayname character varying(64),
name character varying(64),
description character varying(255),
email character varying(128),
type character varying(255),
companyname character varying(64),
alloweddomains character varying(1000),
inviteid character varying(32),
allowopeninvite boolean,
lastteamiconupdate bigint,
schemeid character varying(26),
groupconstrained boolean
);
ALTER TABLE public.teams OWNER TO oscar;
--
-- Name: termsofservice; Type: TABLE; Schema: public; Owner: oscar
--
CREATE TABLE public.termsofservice (
id character varying(26) NOT NULL,
createat bigint,
userid character varying(26),
text character varying(65535)
);
ALTER TABLE public.termsofservice OWNER TO oscar;
--
-- Name: threadmemberships; Type: TABLE; Schema: public; Owner: oscar
--
CREATE TABLE public.threadmemberships (
postid character varying(26) NOT NULL,
userid character varying(26) NOT NULL,
following boolean,
lastviewed bigint,
lastupdated bigint,
unreadmentions bigint
);
ALTER TABLE public.threadmemberships OWNER TO oscar;
--
-- Name: threads; Type: TABLE; Schema: public; Owner: oscar
--
CREATE TABLE public.threads (
postid character varying(26) NOT NULL,
channelid character varying(26),
replycount bigint,
lastreplyat bigint,
participants text
);
ALTER TABLE public.threads OWNER TO oscar;
--
-- Name: tokens; Type: TABLE; Schema: public; Owner: oscar
--
CREATE TABLE public.tokens (
token character varying(64) NOT NULL,
createat bigint,
type character varying(64),
extra character varying(2048)
);
ALTER TABLE public.tokens OWNER TO oscar;
--
-- Name: uploadsessions; Type: TABLE; Schema: public; Owner: oscar
--
CREATE TABLE public.uploadsessions (
id character varying(26) NOT NULL,
type character varying(32),
createat bigint,
userid character varying(26),
channelid character varying(26),
filename character varying(256),
path character varying(512),
filesize bigint,
fileoffset bigint
);
ALTER TABLE public.uploadsessions OWNER TO oscar;
--
-- Name: useraccesstokens; Type: TABLE; Schema: public; Owner: oscar
--
CREATE TABLE public.useraccesstokens (
id character varying(26) NOT NULL,
token character varying(26),
userid character varying(26),
description character varying(512),
isactive boolean
);
ALTER TABLE public.useraccesstokens OWNER TO oscar;
--
-- Name: usergroups; Type: TABLE; Schema: public; Owner: oscar
--
CREATE TABLE public.usergroups (
id character varying(26) NOT NULL,
name character varying(64),
displayname character varying(128),
description character varying(1024),
source character varying(64),
remoteid character varying(48),
createat bigint,
updateat bigint,
deleteat bigint,
allowreference boolean DEFAULT false
);
ALTER TABLE public.usergroups OWNER TO oscar;
--
-- Name: users; Type: TABLE; Schema: public; Owner: oscar
--
CREATE TABLE public.users (
id character varying(26) NOT NULL,
createat bigint,
updateat bigint,
deleteat bigint,
username character varying(64),
password character varying(128),
authdata character varying(128),
authservice character varying(32),
email character varying(128),
emailverified boolean,
nickname character varying(64),
firstname character varying(64),
lastname character varying(64),
"position" character varying(128),
roles character varying(256),
allowmarketing boolean,
props character varying(4000),
notifyprops character varying(2000),
lastpasswordupdate bigint,
lastpictureupdate bigint,
failedattempts integer,
locale character varying(5),
timezone character varying(256),
mfaactive boolean,
mfasecret character varying(128),
acceptedtermsofserviceid character varying(64) DEFAULT ''::character varying
);
ALTER TABLE public.users OWNER TO oscar;
--
-- Name: usertermsofservice; Type: TABLE; Schema: public; Owner: oscar
--
CREATE TABLE public.usertermsofservice (
userid character varying(26) NOT NULL,
termsofserviceid character varying(26),
createat bigint
);
ALTER TABLE public.usertermsofservice OWNER TO oscar;
--
-- Name: audits audits_pkey; Type: CONSTRAINT; Schema: public; Owner: oscar
--
ALTER TABLE ONLY public.audits
ADD CONSTRAINT audits_pkey PRIMARY KEY (id);
--
-- Name: bots bots_pkey; Type: CONSTRAINT; Schema: public; Owner: oscar
--
ALTER TABLE ONLY public.bots
ADD CONSTRAINT bots_pkey PRIMARY KEY (userid);
--
-- Name: channelmemberhistory channelmemberhistory_pkey; Type: CONSTRAINT; Schema: public; Owner: oscar
--
ALTER TABLE ONLY public.channelmemberhistory
ADD CONSTRAINT channelmemberhistory_pkey PRIMARY KEY (channelid, userid, jointime);
--
-- Name: channelmembers channelmembers_pkey; Type: CONSTRAINT; Schema: public; Owner: oscar
--
ALTER TABLE ONLY public.channelmembers
ADD CONSTRAINT channelmembers_pkey PRIMARY KEY (channelid, userid);
--
-- Name: channels channels_name_teamid_key; Type: CONSTRAINT; Schema: public; Owner: oscar
--
ALTER TABLE ONLY public.channels
ADD CONSTRAINT channels_name_teamid_key UNIQUE (name, teamid);
--
-- Name: channels channels_pkey; Type: CONSTRAINT; Schema: public; Owner: oscar
--
ALTER TABLE ONLY public.channels
ADD CONSTRAINT channels_pkey PRIMARY KEY (id);
--
-- Name: clusterdiscovery clusterdiscovery_pkey; Type: CONSTRAINT; Schema: public; Owner: oscar
--
ALTER TABLE ONLY public.clusterdiscovery
ADD CONSTRAINT clusterdiscovery_pkey PRIMARY KEY (id);
--
-- Name: commands commands_pkey; Type: CONSTRAINT; Schema: public; Owner: oscar
--
ALTER TABLE ONLY public.commands
ADD CONSTRAINT commands_pkey PRIMARY KEY (id);
--
-- Name: commandwebhooks commandwebhooks_pkey; Type: CONSTRAINT; Schema: public; Owner: oscar
--
ALTER TABLE ONLY public.commandwebhooks
ADD CONSTRAINT commandwebhooks_pkey PRIMARY KEY (id);
--
-- Name: compliances compliances_pkey; Type: CONSTRAINT; Schema: public; Owner: oscar
--
ALTER TABLE ONLY public.compliances
ADD CONSTRAINT compliances_pkey PRIMARY KEY (id);
--
-- Name: emoji emoji_name_deleteat_key; Type: CONSTRAINT; Schema: public; Owner: oscar
--
ALTER TABLE ONLY public.emoji
ADD CONSTRAINT emoji_name_deleteat_key UNIQUE (name, deleteat);
--
-- Name: emoji emoji_pkey; Type: CONSTRAINT; Schema: public; Owner: oscar
--
ALTER TABLE ONLY public.emoji
ADD CONSTRAINT emoji_pkey PRIMARY KEY (id);
--
-- Name: fileinfo fileinfo_pkey; Type: CONSTRAINT; Schema: public; Owner: oscar
--
ALTER TABLE ONLY public.fileinfo
ADD CONSTRAINT fileinfo_pkey PRIMARY KEY (id);
--
-- Name: groupchannels groupchannels_pkey; Type: CONSTRAINT; Schema: public; Owner: oscar
--
ALTER TABLE ONLY public.groupchannels
ADD CONSTRAINT groupchannels_pkey PRIMARY KEY (groupid, channelid);
--
-- Name: groupmembers groupmembers_pkey; Type: CONSTRAINT; Schema: public; Owner: oscar
--
ALTER TABLE ONLY public.groupmembers
ADD CONSTRAINT groupmembers_pkey PRIMARY KEY (groupid, userid);
--
-- Name: groupteams groupteams_pkey; Type: CONSTRAINT; Schema: public; Owner: oscar
--
ALTER TABLE ONLY public.groupteams
ADD CONSTRAINT groupteams_pkey PRIMARY KEY (groupid, teamid);
--
-- Name: incomingwebhooks incomingwebhooks_pkey; Type: CONSTRAINT; Schema: public; Owner: oscar
--
ALTER TABLE ONLY public.incomingwebhooks
ADD CONSTRAINT incomingwebhooks_pkey PRIMARY KEY (id);
--
-- Name: jobs jobs_pkey; Type: CONSTRAINT; Schema: public; Owner: oscar
--
ALTER TABLE ONLY public.jobs
ADD CONSTRAINT jobs_pkey PRIMARY KEY (id);
--
-- Name: licenses licenses_pkey; Type: CONSTRAINT; Schema: public; Owner: oscar
--
ALTER TABLE ONLY public.licenses
ADD CONSTRAINT licenses_pkey PRIMARY KEY (id);
--
-- Name: linkmetadata linkmetadata_pkey; Type: CONSTRAINT; Schema: public; Owner: oscar
--
ALTER TABLE ONLY public.linkmetadata
ADD CONSTRAINT linkmetadata_pkey PRIMARY KEY (hash);
--
-- Name: oauthaccessdata oauthaccessdata_clientid_userid_key; Type: CONSTRAINT; Schema: public; Owner: oscar
--
ALTER TABLE ONLY public.oauthaccessdata
ADD CONSTRAINT oauthaccessdata_clientid_userid_key UNIQUE (clientid, userid);
--
-- Name: oauthaccessdata oauthaccessdata_pkey; Type: CONSTRAINT; Schema: public; Owner: oscar
--
ALTER TABLE ONLY public.oauthaccessdata
ADD CONSTRAINT oauthaccessdata_pkey PRIMARY KEY (token);
--
-- Name: oauthapps oauthapps_pkey; Type: CONSTRAINT; Schema: public; Owner: oscar
--
ALTER TABLE ONLY public.oauthapps
ADD CONSTRAINT oauthapps_pkey PRIMARY KEY (id);
--
-- Name: oauthauthdata oauthauthdata_pkey; Type: CONSTRAINT; Schema: public; Owner: oscar
--
ALTER TABLE ONLY public.oauthauthdata
ADD CONSTRAINT oauthauthdata_pkey PRIMARY KEY (code);
--
-- Name: outgoingwebhooks outgoingwebhooks_pkey; Type: CONSTRAINT; Schema: public; Owner: oscar
--
ALTER TABLE ONLY public.outgoingwebhooks
ADD CONSTRAINT outgoingwebhooks_pkey PRIMARY KEY (id);
--
-- Name: pluginkeyvaluestore pluginkeyvaluestore_pkey; Type: CONSTRAINT; Schema: public; Owner: oscar
--
ALTER TABLE ONLY public.pluginkeyvaluestore
ADD CONSTRAINT pluginkeyvaluestore_pkey PRIMARY KEY (pluginid, pkey);
--
-- Name: posts posts_pkey; Type: CONSTRAINT; Schema: public; Owner: oscar
--
ALTER TABLE ONLY public.posts
ADD CONSTRAINT posts_pkey PRIMARY KEY (id);
--
-- Name: preferences preferences_pkey; Type: CONSTRAINT; Schema: public; Owner: oscar
--
ALTER TABLE ONLY public.preferences
ADD CONSTRAINT preferences_pkey PRIMARY KEY (userid, category, name);
--
-- Name: productnoticeviewstate productnoticeviewstate_pkey; Type: CONSTRAINT; Schema: public; Owner: oscar
--
ALTER TABLE ONLY public.productnoticeviewstate
ADD CONSTRAINT productnoticeviewstate_pkey PRIMARY KEY (userid, noticeid);
--
-- Name: publicchannels publicchannels_name_teamid_key; Type: CONSTRAINT; Schema: public; Owner: oscar
--
ALTER TABLE ONLY public.publicchannels
ADD CONSTRAINT publicchannels_name_teamid_key UNIQUE (name, teamid);
--
-- Name: publicchannels publicchannels_pkey; Type: CONSTRAINT; Schema: public; Owner: oscar
--
ALTER TABLE ONLY public.publicchannels
ADD CONSTRAINT publicchannels_pkey PRIMARY KEY (id);
--
-- Name: reactions reactions_pkey; Type: CONSTRAINT; Schema: public; Owner: oscar
--
ALTER TABLE ONLY public.reactions
ADD CONSTRAINT reactions_pkey PRIMARY KEY (postid, userid, emojiname);
--
-- Name: roles roles_name_key; Type: CONSTRAINT; Schema: public; Owner: oscar
--
ALTER TABLE ONLY public.roles
ADD CONSTRAINT roles_name_key UNIQUE (name);
--
-- Name: roles roles_pkey; Type: CONSTRAINT; Schema: public; Owner: oscar
--
ALTER TABLE ONLY public.roles
ADD CONSTRAINT roles_pkey PRIMARY KEY (id);
--
-- Name: schemes schemes_name_key; Type: CONSTRAINT; Schema: public; Owner: oscar
--
ALTER TABLE ONLY public.schemes
ADD CONSTRAINT schemes_name_key UNIQUE (name);
--
-- Name: schemes schemes_pkey; Type: CONSTRAINT; Schema: public; Owner: oscar
--
ALTER TABLE ONLY public.schemes
ADD CONSTRAINT schemes_pkey PRIMARY KEY (id);
--
-- Name: sessions sessions_pkey; Type: CONSTRAINT; Schema: public; Owner: oscar
--
ALTER TABLE ONLY public.sessions
ADD CONSTRAINT sessions_pkey PRIMARY KEY (id);
--
-- Name: sidebarcategories sidebarcategories_pkey; Type: CONSTRAINT; Schema: public; Owner: oscar
--
ALTER TABLE ONLY public.sidebarcategories
ADD CONSTRAINT sidebarcategories_pkey PRIMARY KEY (id);
--
-- Name: sidebarchannels sidebarchannels_pkey; Type: CONSTRAINT; Schema: public; Owner: oscar
--
ALTER TABLE ONLY public.sidebarchannels
ADD CONSTRAINT sidebarchannels_pkey PRIMARY KEY (channelid, userid, categoryid);
--
-- Name: status status_pkey; Type: CONSTRAINT; Schema: public; Owner: oscar
--
ALTER TABLE ONLY public.status
ADD CONSTRAINT status_pkey PRIMARY KEY (userid);
--
-- Name: systems systems_pkey; Type: CONSTRAINT; Schema: public; Owner: oscar
--
ALTER TABLE ONLY public.systems
ADD CONSTRAINT systems_pkey PRIMARY KEY (name);
--
-- Name: teammembers teammembers_pkey; Type: CONSTRAINT; Schema: public; Owner: oscar
--
ALTER TABLE ONLY public.teammembers
ADD CONSTRAINT teammembers_pkey PRIMARY KEY (teamid, userid);
--
-- Name: teams teams_name_key; Type: CONSTRAINT; Schema: public; Owner: oscar
--
ALTER TABLE ONLY public.teams
ADD CONSTRAINT teams_name_key UNIQUE (name);
--
-- Name: teams teams_pkey; Type: CONSTRAINT; Schema: public; Owner: oscar
--
ALTER TABLE ONLY public.teams
ADD CONSTRAINT teams_pkey PRIMARY KEY (id);
--
-- Name: termsofservice termsofservice_pkey; Type: CONSTRAINT; Schema: public; Owner: oscar
--
ALTER TABLE ONLY public.termsofservice
ADD CONSTRAINT termsofservice_pkey PRIMARY KEY (id);
--
-- Name: threadmemberships threadmemberships_pkey; Type: CONSTRAINT; Schema: public; Owner: oscar
--
ALTER TABLE ONLY public.threadmemberships
ADD CONSTRAINT threadmemberships_pkey PRIMARY KEY (postid, userid);
--
-- Name: threads threads_pkey; Type: CONSTRAINT; Schema: public; Owner: oscar
--
ALTER TABLE ONLY public.threads
ADD CONSTRAINT threads_pkey PRIMARY KEY (postid);
--
-- Name: tokens tokens_pkey; Type: CONSTRAINT; Schema: public; Owner: oscar
--
ALTER TABLE ONLY public.tokens
ADD CONSTRAINT tokens_pkey PRIMARY KEY (token);
--
-- Name: uploadsessions uploadsessions_pkey; Type: CONSTRAINT; Schema: public; Owner: oscar
--
ALTER TABLE ONLY public.uploadsessions
ADD CONSTRAINT uploadsessions_pkey PRIMARY KEY (id);
--
-- Name: useraccesstokens useraccesstokens_pkey; Type: CONSTRAINT; Schema: public; Owner: oscar
--
ALTER TABLE ONLY public.useraccesstokens
ADD CONSTRAINT useraccesstokens_pkey PRIMARY KEY (id);
--
-- Name: useraccesstokens useraccesstokens_token_key; Type: CONSTRAINT; Schema: public; Owner: oscar
--
ALTER TABLE ONLY public.useraccesstokens
ADD CONSTRAINT useraccesstokens_token_key UNIQUE (token);
--
-- Name: usergroups usergroups_name_key; Type: CONSTRAINT; Schema: public; Owner: oscar
--
ALTER TABLE ONLY public.usergroups
ADD CONSTRAINT usergroups_name_key UNIQUE (name);
--
-- Name: usergroups usergroups_pkey; Type: CONSTRAINT; Schema: public; Owner: oscar
--
ALTER TABLE ONLY public.usergroups
ADD CONSTRAINT usergroups_pkey PRIMARY KEY (id);
--
-- Name: usergroups usergroups_source_remoteid_key; Type: CONSTRAINT; Schema: public; Owner: oscar
--
ALTER TABLE ONLY public.usergroups
ADD CONSTRAINT usergroups_source_remoteid_key UNIQUE (source, remoteid);
--
-- Name: users users_authdata_key; Type: CONSTRAINT; Schema: public; Owner: oscar
--
ALTER TABLE ONLY public.users
ADD CONSTRAINT users_authdata_key UNIQUE (authdata);
--
-- Name: users users_email_key; Type: CONSTRAINT; Schema: public; Owner: oscar
--
ALTER TABLE ONLY public.users
ADD CONSTRAINT users_email_key UNIQUE (email);
--
-- Name: users users_pkey; Type: CONSTRAINT; Schema: public; Owner: oscar
--
ALTER TABLE ONLY public.users
ADD CONSTRAINT users_pkey PRIMARY KEY (id);
--
-- Name: users users_username_key; Type: CONSTRAINT; Schema: public; Owner: oscar
--
ALTER TABLE ONLY public.users
ADD CONSTRAINT users_username_key UNIQUE (username);
--
-- Name: usertermsofservice usertermsofservice_pkey; Type: CONSTRAINT; Schema: public; Owner: oscar
--
ALTER TABLE ONLY public.usertermsofservice
ADD CONSTRAINT usertermsofservice_pkey PRIMARY KEY (userid);
--
-- Name: idx_audits_user_id; Type: INDEX; Schema: public; Owner: oscar
--
CREATE INDEX idx_audits_user_id ON public.audits USING btree (userid);
--
-- Name: idx_channel_search_txt; Type: INDEX; Schema: public; Owner: oscar
--
CREATE INDEX idx_channel_search_txt ON public.channels USING gin (to_tsvector('english'::regconfig, (((((name)::text || ' '::text) || (displayname)::text) || ' '::text) || (purpose)::text)));
--
-- Name: idx_channelmembers_channel_id; Type: INDEX; Schema: public; Owner: oscar
--
CREATE INDEX idx_channelmembers_channel_id ON public.channelmembers USING btree (channelid);
--
-- Name: idx_channelmembers_user_id; Type: INDEX; Schema: public; Owner: oscar
--
CREATE INDEX idx_channelmembers_user_id ON public.channelmembers USING btree (userid);
--
-- Name: idx_channels_create_at; Type: INDEX; Schema: public; Owner: oscar
--
CREATE INDEX idx_channels_create_at ON public.channels USING btree (createat);
--
-- Name: idx_channels_delete_at; Type: INDEX; Schema: public; Owner: oscar
--
CREATE INDEX idx_channels_delete_at ON public.channels USING btree (deleteat);
--
-- Name: idx_channels_displayname_lower; Type: INDEX; Schema: public; Owner: oscar
--
CREATE INDEX idx_channels_displayname_lower ON public.channels USING btree (lower((displayname)::text));
--
-- Name: idx_channels_name; Type: INDEX; Schema: public; Owner: oscar
--
CREATE INDEX idx_channels_name ON public.channels USING btree (name);
--
-- Name: idx_channels_name_lower; Type: INDEX; Schema: public; Owner: oscar
--
CREATE INDEX idx_channels_name_lower ON public.channels USING btree (lower((name)::text));
--
-- Name: idx_channels_scheme_id; Type: INDEX; Schema: public; Owner: oscar
--
CREATE INDEX idx_channels_scheme_id ON public.channels USING btree (schemeid);
--
-- Name: idx_channels_team_id; Type: INDEX; Schema: public; Owner: oscar
--
CREATE INDEX idx_channels_team_id ON public.channels USING btree (teamid);
--
-- Name: idx_channels_update_at; Type: INDEX; Schema: public; Owner: oscar
--
CREATE INDEX idx_channels_update_at ON public.channels USING btree (updateat);
--
-- Name: idx_command_create_at; Type: INDEX; Schema: public; Owner: oscar
--
CREATE INDEX idx_command_create_at ON public.commands USING btree (createat);
--
-- Name: idx_command_delete_at; Type: INDEX; Schema: public; Owner: oscar
--
CREATE INDEX idx_command_delete_at ON public.commands USING btree (deleteat);
--
-- Name: idx_command_team_id; Type: INDEX; Schema: public; Owner: oscar
--
CREATE INDEX idx_command_team_id ON public.commands USING btree (teamid);
--
-- Name: idx_command_update_at; Type: INDEX; Schema: public; Owner: oscar
--
CREATE INDEX idx_command_update_at ON public.commands USING btree (updateat);
--
-- Name: idx_command_webhook_create_at; Type: INDEX; Schema: public; Owner: oscar
--
CREATE INDEX idx_command_webhook_create_at ON public.commandwebhooks USING btree (createat);
--
-- Name: idx_emoji_create_at; Type: INDEX; Schema: public; Owner: oscar
--
CREATE INDEX idx_emoji_create_at ON public.emoji USING btree (createat);
--
-- Name: idx_emoji_delete_at; Type: INDEX; Schema: public; Owner: oscar
--
CREATE INDEX idx_emoji_delete_at ON public.emoji USING btree (deleteat);
--
-- Name: idx_emoji_name; Type: INDEX; Schema: public; Owner: oscar
--
CREATE INDEX idx_emoji_name ON public.emoji USING btree (name);
--
-- Name: idx_emoji_update_at; Type: INDEX; Schema: public; Owner: oscar
--
CREATE INDEX idx_emoji_update_at ON public.emoji USING btree (updateat);
--
-- Name: idx_fileinfo_create_at; Type: INDEX; Schema: public; Owner: oscar
--
CREATE INDEX idx_fileinfo_create_at ON public.fileinfo USING btree (createat);
--
-- Name: idx_fileinfo_delete_at; Type: INDEX; Schema: public; Owner: oscar
--
CREATE INDEX idx_fileinfo_delete_at ON public.fileinfo USING btree (deleteat);
--
-- Name: idx_fileinfo_postid_at; Type: INDEX; Schema: public; Owner: oscar
--
CREATE INDEX idx_fileinfo_postid_at ON public.fileinfo USING btree (postid);
--
-- Name: idx_fileinfo_update_at; Type: INDEX; Schema: public; Owner: oscar
--
CREATE INDEX idx_fileinfo_update_at ON public.fileinfo USING btree (updateat);
--
-- Name: idx_groupchannels_channelid; Type: INDEX; Schema: public; Owner: oscar
--
CREATE INDEX idx_groupchannels_channelid ON public.groupchannels USING btree (channelid);
--
-- Name: idx_groupchannels_schemeadmin; Type: INDEX; Schema: public; Owner: oscar
--
CREATE INDEX idx_groupchannels_schemeadmin ON public.groupchannels USING btree (schemeadmin);
--
-- Name: idx_groupmembers_create_at; Type: INDEX; Schema: public; Owner: oscar
--
CREATE INDEX idx_groupmembers_create_at ON public.groupmembers USING btree (createat);
--
-- Name: idx_groupteams_schemeadmin; Type: INDEX; Schema: public; Owner: oscar
--
CREATE INDEX idx_groupteams_schemeadmin ON public.groupteams USING btree (schemeadmin);
--
-- Name: idx_groupteams_teamid; Type: INDEX; Schema: public; Owner: oscar
--
CREATE INDEX idx_groupteams_teamid ON public.groupteams USING btree (teamid);
--
-- Name: idx_incoming_webhook_create_at; Type: INDEX; Schema: public; Owner: oscar
--
CREATE INDEX idx_incoming_webhook_create_at ON public.incomingwebhooks USING btree (createat);
--
-- Name: idx_incoming_webhook_delete_at; Type: INDEX; Schema: public; Owner: oscar
--
CREATE INDEX idx_incoming_webhook_delete_at ON public.incomingwebhooks USING btree (deleteat);
--
-- Name: idx_incoming_webhook_team_id; Type: INDEX; Schema: public; Owner: oscar
--
CREATE INDEX idx_incoming_webhook_team_id ON public.incomingwebhooks USING btree (teamid);
--
-- Name: idx_incoming_webhook_update_at; Type: INDEX; Schema: public; Owner: oscar
--
CREATE INDEX idx_incoming_webhook_update_at ON public.incomingwebhooks USING btree (updateat);
--
-- Name: idx_incoming_webhook_user_id; Type: INDEX; Schema: public; Owner: oscar
--
CREATE INDEX idx_incoming_webhook_user_id ON public.incomingwebhooks USING btree (userid);
--
-- Name: idx_jobs_type; Type: INDEX; Schema: public; Owner: oscar
--
CREATE INDEX idx_jobs_type ON public.jobs USING btree (type);
--
-- Name: idx_link_metadata_url_timestamp; Type: INDEX; Schema: public; Owner: oscar
--
CREATE INDEX idx_link_metadata_url_timestamp ON public.linkmetadata USING btree (url, "timestamp");
--
-- Name: idx_notice_views_notice_id; Type: INDEX; Schema: public; Owner: oscar
--
CREATE INDEX idx_notice_views_notice_id ON public.productnoticeviewstate USING btree (noticeid);
--
-- Name: idx_notice_views_timestamp; Type: INDEX; Schema: public; Owner: oscar
--
CREATE INDEX idx_notice_views_timestamp ON public.productnoticeviewstate USING btree ("timestamp");
--
-- Name: idx_notice_views_user_id; Type: INDEX; Schema: public; Owner: oscar
--
CREATE INDEX idx_notice_views_user_id ON public.productnoticeviewstate USING btree (userid);
--
-- Name: idx_notice_views_user_notice; Type: INDEX; Schema: public; Owner: oscar
--
CREATE INDEX idx_notice_views_user_notice ON public.productnoticeviewstate USING btree (userid, noticeid);
--
-- Name: idx_oauthaccessdata_client_id; Type: INDEX; Schema: public; Owner: oscar
--
CREATE INDEX idx_oauthaccessdata_client_id ON public.oauthaccessdata USING btree (clientid);
--
-- Name: idx_oauthaccessdata_refresh_token; Type: INDEX; Schema: public; Owner: oscar
--
CREATE INDEX idx_oauthaccessdata_refresh_token ON public.oauthaccessdata USING btree (refreshtoken);
--
-- Name: idx_oauthaccessdata_user_id; Type: INDEX; Schema: public; Owner: oscar
--
CREATE INDEX idx_oauthaccessdata_user_id ON public.oauthaccessdata USING btree (userid);
--
-- Name: idx_oauthapps_creator_id; Type: INDEX; Schema: public; Owner: oscar
--
CREATE INDEX idx_oauthapps_creator_id ON public.oauthapps USING btree (creatorid);
--
-- Name: idx_oauthauthdata_client_id; Type: INDEX; Schema: public; Owner: oscar
--
CREATE INDEX idx_oauthauthdata_client_id ON public.oauthauthdata USING btree (code);
--
-- Name: idx_outgoing_webhook_create_at; Type: INDEX; Schema: public; Owner: oscar
--
CREATE INDEX idx_outgoing_webhook_create_at ON public.outgoingwebhooks USING btree (createat);
--
-- Name: idx_outgoing_webhook_delete_at; Type: INDEX; Schema: public; Owner: oscar
--
CREATE INDEX idx_outgoing_webhook_delete_at ON public.outgoingwebhooks USING btree (deleteat);
--
-- Name: idx_outgoing_webhook_team_id; Type: INDEX; Schema: public; Owner: oscar
--
CREATE INDEX idx_outgoing_webhook_team_id ON public.outgoingwebhooks USING btree (teamid);
--
-- Name: idx_outgoing_webhook_update_at; Type: INDEX; Schema: public; Owner: oscar
--
CREATE INDEX idx_outgoing_webhook_update_at ON public.outgoingwebhooks USING btree (updateat);
--
-- Name: idx_posts_channel_id; Type: INDEX; Schema: public; Owner: oscar
--
CREATE INDEX idx_posts_channel_id ON public.posts USING btree (channelid);
--
-- Name: idx_posts_channel_id_delete_at_create_at; Type: INDEX; Schema: public; Owner: oscar
--
CREATE INDEX idx_posts_channel_id_delete_at_create_at ON public.posts USING btree (channelid, deleteat, createat);
--
-- Name: idx_posts_channel_id_update_at; Type: INDEX; Schema: public; Owner: oscar
--
CREATE INDEX idx_posts_channel_id_update_at ON public.posts USING btree (channelid, updateat);
--
-- Name: idx_posts_create_at; Type: INDEX; Schema: public; Owner: oscar
--
CREATE INDEX idx_posts_create_at ON public.posts USING btree (createat);
--
-- Name: idx_posts_delete_at; Type: INDEX; Schema: public; Owner: oscar
--
CREATE INDEX idx_posts_delete_at ON public.posts USING btree (deleteat);
--
-- Name: idx_posts_hashtags_txt; Type: INDEX; Schema: public; Owner: oscar
--
CREATE INDEX idx_posts_hashtags_txt ON public.posts USING gin (to_tsvector('english'::regconfig, (hashtags)::text));
--
-- Name: idx_posts_is_pinned; Type: INDEX; Schema: public; Owner: oscar
--
CREATE INDEX idx_posts_is_pinned ON public.posts USING btree (ispinned);
--
-- Name: idx_posts_message_txt; Type: INDEX; Schema: public; Owner: oscar
--
CREATE INDEX idx_posts_message_txt ON public.posts USING gin (to_tsvector('english'::regconfig, (message)::text));
--
-- Name: idx_posts_root_id; Type: INDEX; Schema: public; Owner: oscar
--
CREATE INDEX idx_posts_root_id ON public.posts USING btree (rootid);
--
-- Name: idx_posts_update_at; Type: INDEX; Schema: public; Owner: oscar
--
CREATE INDEX idx_posts_update_at ON public.posts USING btree (updateat);
--
-- Name: idx_posts_user_id; Type: INDEX; Schema: public; Owner: oscar
--
CREATE INDEX idx_posts_user_id ON public.posts USING btree (userid);
--
-- Name: idx_preferences_category; Type: INDEX; Schema: public; Owner: oscar
--
CREATE INDEX idx_preferences_category ON public.preferences USING btree (category);
--
-- Name: idx_preferences_name; Type: INDEX; Schema: public; Owner: oscar
--
CREATE INDEX idx_preferences_name ON public.preferences USING btree (name);
--
-- Name: idx_preferences_user_id; Type: INDEX; Schema: public; Owner: oscar
--
CREATE INDEX idx_preferences_user_id ON public.preferences USING btree (userid);
--
-- Name: idx_publicchannels_delete_at; Type: INDEX; Schema: public; Owner: oscar
--
CREATE INDEX idx_publicchannels_delete_at ON public.publicchannels USING btree (deleteat);
--
-- Name: idx_publicchannels_displayname_lower; Type: INDEX; Schema: public; Owner: oscar
--
CREATE INDEX idx_publicchannels_displayname_lower ON public.publicchannels USING btree (lower((displayname)::text));
--
-- Name: idx_publicchannels_name; Type: INDEX; Schema: public; Owner: oscar
--
CREATE INDEX idx_publicchannels_name ON public.publicchannels USING btree (name);
--
-- Name: idx_publicchannels_name_lower; Type: INDEX; Schema: public; Owner: oscar
--
CREATE INDEX idx_publicchannels_name_lower ON public.publicchannels USING btree (lower((name)::text));
--
-- Name: idx_publicchannels_search_txt; Type: INDEX; Schema: public; Owner: oscar
--
CREATE INDEX idx_publicchannels_search_txt ON public.publicchannels USING gin (to_tsvector('english'::regconfig, (((((name)::text || ' '::text) || (displayname)::text) || ' '::text) || (purpose)::text)));
--
-- Name: idx_publicchannels_team_id; Type: INDEX; Schema: public; Owner: oscar
--
CREATE INDEX idx_publicchannels_team_id ON public.publicchannels USING btree (teamid);
--
-- Name: idx_schemes_channel_admin_role; Type: INDEX; Schema: public; Owner: oscar
--
CREATE INDEX idx_schemes_channel_admin_role ON public.schemes USING btree (defaultchanneladminrole);
--
-- Name: idx_schemes_channel_guest_role; Type: INDEX; Schema: public; Owner: oscar
--
CREATE INDEX idx_schemes_channel_guest_role ON public.schemes USING btree (defaultchannelguestrole);
--
-- Name: idx_schemes_channel_user_role; Type: INDEX; Schema: public; Owner: oscar
--
CREATE INDEX idx_schemes_channel_user_role ON public.schemes USING btree (defaultchanneluserrole);
--
-- Name: idx_sessions_create_at; Type: INDEX; Schema: public; Owner: oscar
--
CREATE INDEX idx_sessions_create_at ON public.sessions USING btree (createat);
--
-- Name: idx_sessions_expires_at; Type: INDEX; Schema: public; Owner: oscar
--
CREATE INDEX idx_sessions_expires_at ON public.sessions USING btree (expiresat);
--
-- Name: idx_sessions_last_activity_at; Type: INDEX; Schema: public; Owner: oscar
--
CREATE INDEX idx_sessions_last_activity_at ON public.sessions USING btree (lastactivityat);
--
-- Name: idx_sessions_token; Type: INDEX; Schema: public; Owner: oscar
--
CREATE INDEX idx_sessions_token ON public.sessions USING btree (token);
--
-- Name: idx_sessions_user_id; Type: INDEX; Schema: public; Owner: oscar
--
CREATE INDEX idx_sessions_user_id ON public.sessions USING btree (userid);
--
-- Name: idx_status_status; Type: INDEX; Schema: public; Owner: oscar
--
CREATE INDEX idx_status_status ON public.status USING btree (status);
--
-- Name: idx_status_user_id; Type: INDEX; Schema: public; Owner: oscar
--
CREATE INDEX idx_status_user_id ON public.status USING btree (userid);
--
-- Name: idx_teammembers_delete_at; Type: INDEX; Schema: public; Owner: oscar
--
CREATE INDEX idx_teammembers_delete_at ON public.teammembers USING btree (deleteat);
--
-- Name: idx_teammembers_team_id; Type: INDEX; Schema: public; Owner: oscar
--
CREATE INDEX idx_teammembers_team_id ON public.teammembers USING btree (teamid);
--
-- Name: idx_teammembers_user_id; Type: INDEX; Schema: public; Owner: oscar
--
CREATE INDEX idx_teammembers_user_id ON public.teammembers USING btree (userid);
--
-- Name: idx_teams_create_at; Type: INDEX; Schema: public; Owner: oscar
--
CREATE INDEX idx_teams_create_at ON public.teams USING btree (createat);
--
-- Name: idx_teams_delete_at; Type: INDEX; Schema: public; Owner: oscar
--
CREATE INDEX idx_teams_delete_at ON public.teams USING btree (deleteat);
--
-- Name: idx_teams_invite_id; Type: INDEX; Schema: public; Owner: oscar
--
CREATE INDEX idx_teams_invite_id ON public.teams USING btree (inviteid);
--
-- Name: idx_teams_name; Type: INDEX; Schema: public; Owner: oscar
--
CREATE INDEX idx_teams_name ON public.teams USING btree (name);
--
-- Name: idx_teams_scheme_id; Type: INDEX; Schema: public; Owner: oscar
--
CREATE INDEX idx_teams_scheme_id ON public.teams USING btree (schemeid);
--
-- Name: idx_teams_update_at; Type: INDEX; Schema: public; Owner: oscar
--
CREATE INDEX idx_teams_update_at ON public.teams USING btree (updateat);
--
-- Name: idx_thread_memberships_last_update_at; Type: INDEX; Schema: public; Owner: oscar
--
CREATE INDEX idx_thread_memberships_last_update_at ON public.threadmemberships USING btree (lastupdated);
--
-- Name: idx_thread_memberships_last_view_at; Type: INDEX; Schema: public; Owner: oscar
--
CREATE INDEX idx_thread_memberships_last_view_at ON public.threadmemberships USING btree (lastviewed);
--
-- Name: idx_thread_memberships_user_id; Type: INDEX; Schema: public; Owner: oscar
--
CREATE INDEX idx_thread_memberships_user_id ON public.threadmemberships USING btree (userid);
--
-- Name: idx_threads_channel_id; Type: INDEX; Schema: public; Owner: oscar
--
CREATE INDEX idx_threads_channel_id ON public.threads USING btree (channelid);
--
-- Name: idx_uploadsessions_create_at; Type: INDEX; Schema: public; Owner: oscar
--
CREATE INDEX idx_uploadsessions_create_at ON public.uploadsessions USING btree (createat);
--
-- Name: idx_uploadsessions_user_id; Type: INDEX; Schema: public; Owner: oscar
--
CREATE INDEX idx_uploadsessions_user_id ON public.uploadsessions USING btree (type);
--
-- Name: idx_user_access_tokens_token; Type: INDEX; Schema: public; Owner: oscar
--
CREATE INDEX idx_user_access_tokens_token ON public.useraccesstokens USING btree (token);
--
-- Name: idx_user_access_tokens_user_id; Type: INDEX; Schema: public; Owner: oscar
--
CREATE INDEX idx_user_access_tokens_user_id ON public.useraccesstokens USING btree (userid);
--
-- Name: idx_user_terms_of_service_user_id; Type: INDEX; Schema: public; Owner: oscar
--
CREATE INDEX idx_user_terms_of_service_user_id ON public.usertermsofservice USING btree (userid);
--
-- Name: idx_usergroups_delete_at; Type: INDEX; Schema: public; Owner: oscar
--
CREATE INDEX idx_usergroups_delete_at ON public.usergroups USING btree (deleteat);
--
-- Name: idx_usergroups_remote_id; Type: INDEX; Schema: public; Owner: oscar
--
CREATE INDEX idx_usergroups_remote_id ON public.usergroups USING btree (remoteid);
--
-- Name: idx_users_all_no_full_name_txt; Type: INDEX; Schema: public; Owner: oscar
--
CREATE INDEX idx_users_all_no_full_name_txt ON public.users USING gin (to_tsvector('english'::regconfig, (((((username)::text || ' '::text) || (nickname)::text) || ' '::text) || (email)::text)));
--
-- Name: idx_users_all_txt; Type: INDEX; Schema: public; Owner: oscar
--
CREATE INDEX idx_users_all_txt ON public.users USING gin (to_tsvector('english'::regconfig, (((((((((username)::text || ' '::text) || (firstname)::text) || ' '::text) || (lastname)::text) || ' '::text) || (nickname)::text) || ' '::text) || (email)::text)));
--
-- Name: idx_users_create_at; Type: INDEX; Schema: public; Owner: oscar
--
CREATE INDEX idx_users_create_at ON public.users USING btree (createat);
--
-- Name: idx_users_delete_at; Type: INDEX; Schema: public; Owner: oscar
--
CREATE INDEX idx_users_delete_at ON public.users USING btree (deleteat);
--
-- Name: idx_users_email; Type: INDEX; Schema: public; Owner: oscar
--
CREATE INDEX idx_users_email ON public.users USING btree (email);
--
-- Name: idx_users_email_lower_textpattern; Type: INDEX; Schema: public; Owner: oscar
--
CREATE INDEX idx_users_email_lower_textpattern ON public.users USING btree (lower((email)::text) text_pattern_ops);
--
-- Name: idx_users_firstname_lower_textpattern; Type: INDEX; Schema: public; Owner: oscar
--
CREATE INDEX idx_users_firstname_lower_textpattern ON public.users USING btree (lower((firstname)::text) text_pattern_ops);
--
-- Name: idx_users_lastname_lower_textpattern; Type: INDEX; Schema: public; Owner: oscar
--
CREATE INDEX idx_users_lastname_lower_textpattern ON public.users USING btree (lower((lastname)::text) text_pattern_ops);
--
-- Name: idx_users_names_no_full_name_txt; Type: INDEX; Schema: public; Owner: oscar
--
CREATE INDEX idx_users_names_no_full_name_txt ON public.users USING gin (to_tsvector('english'::regconfig, (((username)::text || ' '::text) || (nickname)::text)));
--
-- Name: idx_users_names_txt; Type: INDEX; Schema: public; Owner: oscar
--
CREATE INDEX idx_users_names_txt ON public.users USING gin (to_tsvector('english'::regconfig, (((((((username)::text || ' '::text) || (firstname)::text) || ' '::text) || (lastname)::text) || ' '::text) || (nickname)::text)));
--
-- Name: idx_users_nickname_lower_textpattern; Type: INDEX; Schema: public; Owner: oscar
--
CREATE INDEX idx_users_nickname_lower_textpattern ON public.users USING btree (lower((nickname)::text) text_pattern_ops);
--
-- Name: idx_users_update_at; Type: INDEX; Schema: public; Owner: oscar
--
CREATE INDEX idx_users_update_at ON public.users USING btree (updateat);
--
-- Name: idx_users_username_lower_textpattern; Type: INDEX; Schema: public; Owner: oscar
--
CREATE INDEX idx_users_username_lower_textpattern ON public.users USING btree (lower((username)::text) text_pattern_ops);
--
-- PostgreSQL database dump complete
--
Okay, schema looks good to me. I see one extra column acceptedtermsofserviceid character varying(64) DEFAULT ''::character varying though.
Next, I would like to take a look at your postgres config. You mentioned that you are unable to set shared_buffers above 256MB? It might be because of your shmmax setting. If that's too low, we might need to bump it:
``
$ sysctl -w kernel.shmmax=17179869184
$ sysctl -w kernel.shmall=4194304
````
And then try bumping theshared_buffers` limit. Ideally, given 12GB RAM, it should be around 7GB to serve Mattermost's workload properly. But this is assuming entire 12GB is given to the DB.
For a complete recommended Postgres config, take a look at: https://docs.mattermost.com/deployment/cluster.html#recommended-configuration-settings
agnivade
on 12 Jan 2021
kernel.shmmax and kernel.shmall are set to 18446744073692774399 each; AFAIK that means "unlimited".
I'll try adjusting Postgres based on those docs, I probably originally ignored them because I wasn't setting up a cluster.
Taffer
on 12 Jan 2021
Again, I should point out that this config worked fine with MM 5.21.0 and failed when upgrading to 5.24.1 originally, so I'm thinking it's something in the code. Hopefully the releases are tagged so I can see what changed...
Taffer
on 12 Jan 2021
Again, I should point out that this config worked fine with MM 5.21.0 and failed when upgrading to 5.24.1 originally, so I'm thinking it's something in the code. Hopefully the releases are tagged so I can see what changed...
So you are saying that performance started worsening as soon as it got to 5.24.1?
We can definitely help looking into what might have changed code-wise that could have resulted in such issues.
I am thinking a simple performance comparison between the two release should show something if the issue is indeed caused by the application.
streamer45
on 12 Jan 2021
Can confirm, it's timing out at the
QueryTimeoutsetting.Database hasn't changed other than to install patches; I've been afraid of migrating to PostgreSQL 12 because it might break something and we really depend on Mattermost while working from home.
EXPLAIN ANALYZE SELECT COUNT(Id) FROM Posts;doesn't seem able to complete (!). Our Posts table appears to be around 83GB.
That's a pretty significant table size. Do you have an estimate on how many posts it might have? Several millions I am thinking.
I'd definitely start from disabling the post counting queries. Can you make sure the following config values are set to false?
AnnouncementSettings.AdminNoticesEnabledAnnouncementSettings.UserNoticesEnabledLogSettings.EnableDiagnostics
streamer45
on 12 Jan 2021
We have approximately 80 million posts, I got a select count(*) from Posts; to complete yesterday.
I'm going to try reverting to 5.21.0 today, then upgrading through the releases until I can pinpoint exactly which one makes our setup go haywire. I was looking through diffs from 5.21.0 to 5.24.1 yesterday and didn't see anything useful, but I don't know the code base at all.
I'll make sure those three settings are false before I start the reversion, in case that helps.
Taffer
on 13 Jan 2021
👍1
Basically no change in behaviour, so I'm off to start reverting to 5.21.0; I'll report back with my results.
Taffer
on 13 Jan 2021
👍1
Reversion to 5.21.0 successful; I looked through diffs of the database schema between my problematic server and a fresh, clean install of 5.30.1 and they're virtually identical (one extra column in one table in the legacy db) so I felt much safer.
Verified that 5.21.0 is working correctly in our setup. 馃憤 There was great rejoicing.
Upgraded to 5.22.1... replies (and presumably edits, we didn't test for long) fail in the same way. Reverted before testing edits and channel loading. At least that narrows down the change window where things start breaking for me. 5.21.0 release to 5.22.1 release.
Loading the legacy server's contents into the fresh 5.30.1 server, will see how it fares.
Taffer
on 13 Jan 2021
Thank you @Taffer, that's extremely helpful information. We'll have someone take a loop at this asap.
streamer45
on 13 Jan 2021
👍1
The 5.21.0 -> 5.22.1 diff -ru for Mattermost is almost 10MB, I'm hoping a lot of that is just noise. 馃槃
Taffer
on 13 Jan 2021
Wow, this is great stuff @Taffer ! Thank you for taking the time to bisect it. So IIUC, the performance drop began in 5.22.1 and then never improved until 5.30.1. Is that accurate?
agnivade
on 13 Jan 2021
I just remembered a similar issue that some people saw when upgrading to 5.22 here: https://github.com/mattermost/mattermost-server/issues/14780. OP reported that upgrading to 5.24.2 solved it for them. But this is probably something else since even 5.24 did not work for you.
agnivade
on 13 Jan 2021
Yeah, the performance has been abysmal for every version since 5.21.0 that I've tried (granted, only four of the released versions in there).
Taffer
on 14 Jan 2021
Just for yucks, select count(*) from posts; gives me the result in about 13 minutes.
SELECT
(reltuples/relpages) * (
pg_relation_size('posts') /
(current_setting('block_size')::integer)
)
FROM pg_class where relname = 'posts';
gives roughly the same result in 113ms; it's not going to be completely accurate, but it's good enough for the system console's # of posts statistic.
Arcane SQL provided by one of my co-workers, I have no idea what I'm doing. ;-)
Taffer
on 14 Jan 2021
I probably have the same problem. However, my database server is MySQL.
I've upgraded from 5.26.2 to 5.29.2, because 5.30.2 fall down with the mentioned error.
The version 5.29.2 wrote to the log the message like below:
"Failed to fetch post count","error":"SqlPostStore.AnalyticsPostCount: Unable to get post counts., context deadline exceeded"}
I can't read the site statistics and the query like:
SELECT
COUNT(Posts.Id) AS Value
FROM
Posts,
Channels
WHERE
Posts.ChannelId = Channels.Id
working an infinity time: 13 seconds and shows 28195402.
Additionally, I think it is inappropriate to send a message daily if I have more than 500 users.
swood
on 21 Jan 2021
Was this page helpful?
0 / 5 - 0 ratings
Related issues
jasonblais
路
3Comments
esethna
路
3Comments
esethna
路
3Comments
NotSqrt
路
3Comments
jwilander
路
3Comments