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!