Conversation

For anyone backing up a pleroma instance DB, if you're using an sql backup (all variants of pg_dump) - YA DON'T HAVE A WORKABLE RESTORE WITHOUT SOME MASSAGING! You MUST have indexes pre-created to load data. Post creation of indexes effectively won't work...unless your DB is small.

Soooo I think I know what causes the restores to basically never complete, maybe...I'm near the limits of my psql debugging capability because I've never hit this so I actually don't exactly know how to see what the CREATE INDEX is actively doing... But tl;dr; 1.8T rows. One Point Eight TRILLION rows. But...HOW?!

The dump+load upgrade method I tend to use causes this. In the default mode it creates the schema (sequences setup, all the functions, that sort of thing, BUT NO INDEXES), loads the data, then creates the indexes.

The index it's never finishing creating is calling this function:

CREATE OR REPLACE FUNCTION public.activity_visibility(actor character varying, recipients character varying[], data jsonb) RETURNS character varying LANGUAGE plpgsql IMMUTABLE PARALLEL SAFE SECURITY DEFINER AS $function$ DECLARE fa varchar; public varchar := 'https://www.w3.org/ns/activitystreams#Public'; BEGIN SELECT COALESCE(users.follower_address, '') into fa from public.users where users.ap_id = actor; IF data->'to' ? public THEN RETURN 'public'; ELSIF data->'cc' ? public THEN RETURN 'unlisted'; ELSIF ARRAY[fa] && recipients THEN RETURN 'private'; ELSIF not(ARRAY[fa, public] && recipients) THEN RETURN 'direct'; ELSE RETURN 'unknown'; END IF; END; $function$

Well. users is ~500k rows, and this function would be called for the ~3.7M activities rows....at a point in time where users has no indexes! So each invocation of activity_visibility would scan ~500k rows, times 3.7M rows in activities, and .... that's ~1.8T rows.

Why did I footgun myself like this when pg_upgrade exists? Well, binary incompatibilities, index collation differences, in the past causing Major Heartburn. I'm not going to dive into how it might be possible to fix the dump+load procedure, since, well, yeah. But for anyone backing up a pleroma DB be warned. Either take a binary copy (f/ex pg_basebackup) or dump schema and data seperately so you can load the data with indexes. Or find another workaround!

cc @feld

Public

Notices

Feeds