Mastodon database hickup

After being online for almost a year, my Mastodon instance is back up. However, it wasn’t as straightforward as i wanted it to be.

About a year ago my Mastodon server went down. I messed up my docker images big time and the then-new version 2.8.4 didn’t want to start up. I left it there, and as i had a pretty rough year (in the good sense) i didn’t have time to bring it up again.

One of the constant problems i had with this instance is the size of the attachment directory. It easily grew to an enormous size in no time, so i had to constantly clean things up. Plus, it took away the precious space away from my other projects. So my first move was moving all these files to a DigitalOcean Space; with the awscli Python package it was easy as pie. If you want to do it, too, i followed this article.

Next came the database.

I tried to load the database dump, created using pg_dump -f mastodon.sql mastodon, with psql -f mastodon.sql. Everything went fine, all the records created, except this error message:

psql:mastodon-dump.sql:1691661: ERROR:  could not create unique index "index_tags_on_name"
DETAIL:  Key (name)=(opensource) is duplicated.

First i tried to ignore it, but running rails db:migrate failed when it wanted to manipulate this index.

So i issued this query:

SELECT COUNT(*) AS count, name FROM tags GROUP BY name HAVING COUNT(*) > 1;

And the result was this:

count | name
    2 | opensource
    2 | xmpp
    2 | fdroid
    2 | socialmedia
    2 | blogging
    2 | c64
    2 | blog

Then collected the IDs of these duplicate tag records using this query:

SELECT id, name, created_at
FROM tags
WHERE name in ('opensource',
               'xmpp',
               'fdroid',
               'socialmedia',
               'blogging',
               'c64',
               'blog')
ORDER BY name, created_at;
 id    | name        | created_at
-------+-------------+------------
   159 | blog        | 2018-02-24 19:56:01.710702
 15941 | blog        | 2019-04-03 22:05:56.438488
   158 | blogging    | 2018-02-24 19:56:01.721354
 16006 | blogging    | 2019-04-09 12:13:20.852976
  5441 | c64         | 2018-07-31 00:50:56.172468
 16036 | c64         | 2019-04-14 19:56:40.692197
   924 | fdroid      | 2018-04-14 19:39:21.261817
 15947 | fdroid      | 2019-04-04 19:10:50.190317
   237 | opensource  | 2018-03-05 21:50:52.609723
 15929 | opensource  | 2019-04-03 11:49:21.772961
   251 | socialmedia | 2018-03-06 23:02:33.573775
 16034 | socialmedia | 2019-04-14 19:07:37.081635
   519 | xmpp        | 2018-03-28 16:02:05.023784
 15988 | xmpp        | 2019-04-07 08:06:59.429965

I wanted to see the created_at field just to know when things got bad. As it turns out, it was around April 2019, so it might have happened with the 2.8.0 upgrade. Was it me, or some problem with the migrations remains a mistery.

I also wanted to know about all the tables referencing tags:

SELECT
    tc.table_name, kcu.column_name
FROM
    information_schema.table_constraints AS tc
    JOIN information_schema.key_column_usage AS kcu
         ON tc.constraint_name = kcu.constraint_name
    JOIN information_schema.constraint_column_usage AS ccu
         ON ccu.constraint_name = tc.constraint_name
WHERE constraint_type = 'FOREIGN KEY' AND ccu.table_name = 'tags';
    table_name     | column_name
-------------------+-------------
 statuses_tags     | tag_id
 account_tag_stats | tag_id

And last, but not least, i had to massage the dump a bit. After creating a backup, i opened up the file in ViM and looked for the place where it was loading the data into the tags table. It looked like this:

COPY public.tags (name, created_at, updated_at, id) FROM stdin;
blogging        2018-02-24 19:56:01.710702      2018-02-24 19:56:01.710702      158
blog    2018-02-24 19:56:01.721354      2018-02-24 19:56:01.721354      159
opensource      2018-03-05 21:50:52.609723      2018-03-05 21:50:52.609723      237
socialmedia     2018-03-06 23:02:33.573775      2018-03-06 23:02:33.573775      251
xmpp    2018-03-28 16:02:05.023784      2018-03-28 16:02:05.023784      519
fdroid  2018-04-14 19:39:21.261817      2018-04-14 19:39:21.261817      924
c64     2018-07-31 00:50:56.172468      2018-07-31 00:50:56.172468      5441
opensource      2019-04-03 11:49:21.772961      2019-04-03 11:49:21.772961      15929
blog    2019-04-03 22:05:56.438488      2019-04-03 22:05:56.438488      15941
fdroid  2019-04-04 19:10:50.190317      2019-04-04 19:10:50.190317      15947
xmpp    2019-04-07 08:06:59.429965      2019-04-07 08:06:59.429965      15988
blogging        2019-04-09 12:13:20.852976      2019-04-09 12:13:20.852976      16006
socialmedia     2019-04-14 19:07:37.081635      2019-04-14 19:07:37.081635      16034
c64     2019-04-14 19:56:40.692197      2019-04-14 19:56:40.692197      16036

For every tag, i removed the line with the latest date, and noted their IDs, then went on to the statuses_tags table, which looked like this (i don’t put all rows here as it would take a lot of space):

COPY public.statuses_tags (status_id, tag_id) FROM stdin;
101862091116861098      15929

Here, i had to look for the IDs noted before in the last column (ie. at the end of a line), and for every line i change the newer ID to the old ones (15929 became 237, and so on).

Finally, the account_tag_stats table:

COPY public.account_tag_stats (id, tag_id, accounts_count, hidden, created_at, updated_at) FROM stdin;
1       15929   0       f       2019-04-03 11:49:21.810564      2019-04-03 11:49:21.810564

Here, the ID i was looking for is in the second column, so i used the magical ViM regex ^[0-9]\+\t\(15929\|15941\) (but with all the other IDs in the parentheses, too), and again changed the new IDs to the old ones.

I still can’t believe i didn’t mess up the database too much, but it loaded just fine, and the db:migrate command executet just fine.

I also had some problems with so nginx reverse proxying, but it all turned out well, and my instance is back online after a year. Come follow me at @gergely@polonkai.eu!

links

social