Wed Jul 8 07:58:58 PM UTC 2026
PostgreSQL 13 to 17 Upgrade, Foreign-Key Integrity Blocker and Resolution
A planned upgrade of the primary database (mastodon.sdf.org) from PostgreSQL 13
to PostgreSQL 17 was blocked by a small set of orphaned records that had
accumulated silently over the instance's ten-year history. Completing the
upgrade required removing those defunct records first. A total of 2,018 rows
were deleted from a database of roughly half a billion rows (~174 GB). No
user-visible content, no posts, accounts, or interactions anyone could see,
was affected.
WHAT THE ISSUE WAS
PostgreSQL uses foreign-key constraints to guarantee referential integrity:
rules ensuring, for example, that every "favourite" points to a real post and
every "mention" points to a real account. During normal operation, PostgreSQL
enforces these rules only on new or changed rows, it does not continuously
re-verify data already stored.
The major-version upgrade tool (pg_upgradecluster) works by exporting the entire
database and re-importing it into the new version. During that re-import,
PostgreSQL re-validates EVERY foreign key against EVERY row. This is effectively
the first time in the instance's decade of operation that the whole dataset was
checked for referential consistency in one pass.
WHY IT WAS A SHOW-STOPPER
When the re-import reached the foreign-key validation stage, it hit the first
orphaned record and aborted the entire upgrade, discarding hours of work. The
actual error:
ERROR: insert or update on table "statuses_tags"
violates foreign key constraint "fk_3081861e21"
DETAIL: Key (tag_id)=(45797) is not present in table "tags".
In plain terms: a tag-link referenced tag #45797, which no longer existed, so
PostgreSQL refused to finish. Because the tool aborts on the FIRST violation it
finds, every failed attempt cost the full multi-hour restore before revealing
just one bad record, making blind trial-and-error impractical.
HOW IT WAS DIAGNOSED
Rather than discover orphans one failed upgrade at a time, we queried
PostgreSQL's own system catalog to enumerate all 144 foreign-key constraints in
the schema and count orphaned rows for each, in a single read-only sweep. This
produced a complete, exact inventory: 26 constraints had orphaned records; the
other 118 were clean.
WHAT HAD TO BE DONE
The orphaned rows were deleted in dependency order, removing orphaned parent
records first so PostgreSQL's cascade rules cleaned up their children
automatically, and the sweep was then re-run to confirm the database reached
zero orphans. Only with a fully consistent dataset could the upgrade's
validation stage pass.
RECORDS DELETED (exact, by constraint)
statuses account_id .................. 219
statuses in_reply_to_account_id ...... 2
statuses in_reply_to_id .............. 6
statuses reblog_of_id ................ 3
polls status_id ................... 2
polls account_id .................. 0
poll_votes poll_id ..................... 62
poll_votes account_id .................. 2
statuses_tags status_id ................ 775
status_stats status_id ................ 291
account_stats account_id ............... 203
media_attachments account_id ........... 121
media_attachments status_id ............ 77
follows account_id .................. 79
follows target_account_id .......... 17
mentions status_id ................... 68
mentions account_id .................. 0
featured_tags account_id ............... 33
status_edits status_id ................ 18
status_edits account_id ............... 0
favourites status_id ................... 13
favourites account_id .................. 11
web_push_subscriptions access_token_id .. 12
web_push_subscriptions user_id .......... 1
status_pins account_id .................. 0
oauth_access_tokens resource_owner_id ... 3
-----------------------------------------------
TOTAL .................................. 2,018 rows
Every deleted row was a broken reference, a record pointing at a parent (post,
account, tag, poll, token) that had already been deleted, in some cases years
earlier.
WHAT PERSISTS (approximate, for scale)
~85 million statuses (posts)
~300 million conversation records
~70 million status/tag associations
~35 million mentions
hundreds of thousands of accounts
-------------------------------------------
~500 million rows total, ~174 GB on disk
The 2,018 removed records are about 0.0004% of the database, roughly four ten-
thousandths of one percent. Nothing a user could see, post, or interact with was
touched; the deleted rows were dangling pointers with nothing on the other end.
WHY THIS HAPPENED (AND WHY IT REFLECTS NO CURRENT FAULT)
These orphans are archaeological, not active problems. Over ten years the
instance ran many versions of Mastodon. Early versions lacked some of these
foreign-key constraints, or added them in a mode that enforces new rows without
re-checking existing ones. Combined with historical events, a past database-
corruption repair, background deletion jobs interrupted by restarts or errors,
and other ordinary imperfections of running a busy service for a decade, a
small residue of broken references built up invisibly. The live database was
fully functional the entire time. These records surfaced only because the
upgrade performed the first complete referential-integrity check in the
instance's history.
LESSONS RECORDED FOR FUTURE MAJOR UPGRADES
- Before any PostgreSQL major upgrade, run the catalog-driven foreign-key
sweep in advance and clean any orphans while the service is still running,
so the upgrade window itself starts from a consistent dataset.
- Perform the cleanup with the application stopped (or otherwise against a
quiet database). Run against live traffic, the whole-table scans are
dramatically slower and new orphans can appear underneath the operation.
- Delete per constraint as individually committed statements rather than one
large transaction, so progress is preserved and long-held transactions do
not block routine maintenance.
Tue Jul 7 07:59:09 PM UTC 2026
The instance has been brought forward from 4.1.25 to 4.4.19 this past holiday
weekend. The next step will be to migrate from postgres-13 to postgres-17. This
upgrade is required for 4.5.x and beyond.
Sun Jun 28 07:29:55 PM UTC 2026
Our Dec 2022 notes on pg_toast corruption in the 'accounts' table (below).
A similar issue resurfaced on our aging database minor in scope (a handful
of unused remote accounts), but tricky to resolve, and with 4.1.x long
deprecated it had become a real hurdle to moving forward:
it blocked cull, prune and a clean pg_dump, which in turn blocked the version
and database upgrades we needed. Cleared now, with the path open again.
If you're another instance admin who found this page while hitting the same
wall, take heart. It looks alarming and it blocks upgrades, but the fix is
surgical and the stakes are usually low. Our full method is below; we hope it
saves you an evening.
Repairing pg_toast corruption in the accounts table, what we did
Even a trivial query bombs:
SELECT count(*) FROM accounts;
ERROR: missing chunk number 0 for toast value N in pg_toast_N
pg_dump fails on the same table, and cull/prune/vacuum all choke, because
each one has to detoast every row, and one row points at storage that
isn't there. Almost always a single (or a few) bad tuples, not a whole
corrupt table. Don't panic, and don't REINDEX or VACUUM blindly first,
those detoast too and just re-hit the error.
1. Get the table name from the toast relation in the error:
SELECT c.relname AS owning_table
FROM pg_class t
JOIN pg_class c ON c.reltoastrelid = t.oid
WHERE t.relname = 'pg_toast_N'; , N from the error message
2. Find the bad rows. You can't select the toasted column directly (that
re-triggers the fault), so walk the table by ctid, force each full row to
materialize with row_to_json, and trap the failures so the sweep runs to
the end instead of aborting on the first hit:
DO $$
DECLARE r record;
BEGIN
FOR r IN SELECT ctid, id FROM accounts LOOP
BEGIN
PERFORM row_to_json(accounts.*) FROM accounts WHERE ctid = r.ctid;
EXCEPTION WHEN OTHERS THEN
RAISE NOTICE 'bad row: ctid=% id=% err=%', r.ctid, r.id, SQLERRM;
END;
END LOOP;
END $$;
It's read-only and safe on a live instance; it just prints the offending
ids. Let it finish the full table, there may be more than one.
3. Before deleting anything:
SELECT id, username, domain FROM accounts WHERE id IN ();
domain IS NOT NULL -> remote account: re-federates from its home server,
safe to delete.
domain IS NULL -> a LOCAL user of yours: do NOT delete. Repair in
place instead, e.g. blank the toasted column:
UPDATE accounts SET note = '' WHERE id = 'N';
4. Either remove the bad rows or try to preserve them. If (as is usual)
the bad rows are unused remotes, delete surgically; Mastodon's foreign
keys cascade the associated rows:
DELETE FROM accounts WHERE id IN ();
5. The table should now read clean:
SELECT count(*) FROM accounts; # succeeds
VACUUM FULL accounts; # (or whole-DB) completes
pg_dump ... mastodon_production # completes
With this necessary downtime maintenance behind us, we are able to bring the
instance forward as well as upgrade from postgres-13 onto postgres-17.
Sun 17 Nov 2024 07:00:00 AM UTC
A 45 minute preventative maintenance on the instance was performed.
Thank you for your patience.
Sun 10 Nov 2024 12:09:41 PM UTC
Database maintenance has been completed and any backlog is
now being processed. Thank you for your patience.
Thu 23 Nov 2023 11:11:10 PM UTC
During the holiday we took the instance down to perform full backups
and conduct 4.2.x migration testing on a working copy of the database.
Previously a working copy of the existing database was successfully
migrated to 4.2.0 on the development server and then upgraded to the
subsequent 4.2.1 release successfully. However, there were issues
that prevented a successful upgrade on the production server which have
been noted and will be addressed before attempting an upgrade. The instance
is now on 4.1.10 with the preserved database prior to any modifications
Thank you for your patience.
Mon 09 Oct 2023 07:34:28 AM UTC
The servers were taken offline briefly to perform maintenance
and full backups.
Wed 27 Sep 2023 05:20:27 PM UTC
We experienced a prolong power outage at the SEA2 DC in
Tukwila Washington which caused the site to be inaccessible.
The PNW is currently experiencing waves of wind and rain due
to a cyclone off the coast. Normally in a power serivce
failure we would run on batteries and a diesel generator but
it looks like the transfer failed. We are waiting to hear
from our provider regarding the outage. At this point service
has been restored. Thank you for your patience.
Sat 05 Aug 2023 04:35:07 AM UTC
The instance was upgraded from v4.1.5 to v4.1.6
Sun 25 Dec 2022 06:00:00 AM UTC
The instance is currently in scheduled maintenance which took 57 minutes.
Happy Christmas!
Full offline backups were completed
New Replication server was configured for live database redundancy
Third database server for testing new features was implemented
Thu 15 Dec 2022 06:40:32 AM UTC
The instance was in scheduled maintenance for 32 minutes to correct
inconsistencies in database indices for media_attachments,
featured_tags, statuses and status_stats.
Wed 14 Dec 2022 06:01:37 AM UTC
The instance was in scheduled maintenance for 53 minutes to:
perform a full offline backup
run bin/tootctl fix-duplicates
reindex several indexes
check pg_toast tables
Feeds were then rebuild once the instance was back line.
Sun 11 Dec 2022 07:25:42 PM UTC
On Monday December 5th we experienced a RAID failure specifically affecting
our postgresql server. The database was moved to another machine where it
could be rolled back 12 days and repairs could be made. This affected new
accounts and new statuses created after November 20th. The instance was
brought back online within 3 hours to resume service. New accounts that no
longer existed due to the rollback were identified and have since been
contacted via email.
Our database was created in April of 2017 and contains status for over 5 years.
It has been in continuous service and has migrated through several versions of
mastodon/postgresql. This is the first time we've had to roll back the database.
* Are my statuses from that time period lost?
Yes and no, public statuses are federated and many folks have found their posts
cached both in their apps and on remote instances. If you are able to, you can
repost what is missing from the SDF localtime line.
Further details on the postgresql server
On November 11th we leased a machine from our datacenter temporarily as we were
still in negotiation for our second expansion cabinet and we needed another
machine to scale the Mastodon instance. After preliminary testing and staging,
we were able to move the database into production on November 12th. When the
RAID failure occurred the datacenter staff identified the components used in
the build to be at fault and has taken full responsibility for the hardware. A
new machine with new disks was rebuilt to replace this machine at no charge to
us. Recovery and rollback of the database was, of course, our responsbility.
November has been a hectic time for many instances and in fact, almost none of
us were prepared to scale as fast as we've had to in order to accommodate the
twitter exodous. For SDF, many things have gone very well and while it is
unfortunate that this brief time period is affected, it is a minor setback and
we ask everyone to be positive and to move forward from this. With the
implementation of our daily maintenance window we can minimize the impact of
future issues.
postgresql pg_toast corruption for other instance maintainers
In our case pg_toast corruption affected the complicated 'accounts' table. Here
are some notes that may help you if you see log messages like:
ERROR: missing chunk number 0 for toast value N in pg_toast_N
When this corruption occurs, it is not possible to run a cull or even successfully
dump the table. Identifying the row (and in our case, the column) can be
tricky for larger/older instances.
See https://gist.github.com/supix/80f9a6111dc954cf38ee99b9dedf187a
for notes on tracking things down.
The column in our case was fortunately 'note' in accounts, so while the link
above suggests
psql> delete from mytable where id = 'N';
all we really needed to do was:
psql> update accounts set note = '' where id = 'N';
The above URL has both a shell script and a perl script to run a select against
every row to identify which row is affected by the pg_toast corruption.
Thankfully for mastodon if you enable postgresql logging during a tootctl
accounts cull the id will be logged. You can confirm you've got the right id
by doing a:
psql> select * from accounts where id = 'N';
and you should see:
missing chunk number 0 for toast value N in pg_toast_N
From there, do a simple select against each individual column in the table where
id = 'N' and update where you can.