115 lines
4.0 KiB
HTML
115 lines
4.0 KiB
HTML
<!doctype html>
|
|
<html>
|
|
<head>
|
|
<meta charset="utf-8">
|
|
<meta http-equiv="x-ua-compatible" content="ie=edge">
|
|
<meta name="viewport" content="width=device-width, initial-scale=1, shrink-to-fit=no">
|
|
|
|
<meta name="theme-color" content="#00cc00">
|
|
<link rel="icon" type="image/png" sizes="192x192" href="https://tilde.team/apple-touch-icon-precomposed.png">
|
|
<link rel="icon" type="image/png" sizes="96x96" href="https://tilde.team/favicon-96x96.png">
|
|
|
|
<link rel="stylesheet" href="https://tilde.team/~ben/gruvbox/gruvbox.css">
|
|
<link rel="stylesheet" href="extra.css">
|
|
|
|
<link rel="alternate" type="application/rss+xml" title="subscribe to this page..." href="feed.rss" />
|
|
<title>mastodon postgres upgrade fun</title>
|
|
</head><body>
|
|
<main>
|
|
|
|
<div id="divbodyholder">
|
|
<div class="headerholder"><div class="header">
|
|
<div id="title">
|
|
<h1 class="nomargin"><a class="ablack" href="https://tilde.team/~ben/blog/index.html">blog // ~ben</a></h1>
|
|
<div id="description">a blog about tildes and other things</div>
|
|
</div></div></div>
|
|
<div id="divbody"><div class="content">
|
|
<!-- entry begin -->
|
|
<h3><a class="ablack" href="mastodon-postgres-upgrade-fun.html">
|
|
mastodon postgres upgrade fun
|
|
</a></h3>
|
|
<!-- bashblog_timestamp: #202010281631.39# -->
|
|
<div class="subtitle">October 28, 2020 —
|
|
~ben
|
|
</div>
|
|
<!-- text begin -->
|
|
|
|
<p>howdy friends!</p>
|
|
|
|
<p>if you’re a mastodon user on <a href="https://tilde.zone">tilde.zone</a> (the tildeverse
|
|
mastodon instance), you might’ve noticed some downtime recently.</p>
|
|
|
|
<p>here’s a quick recap of what went down during the upgrade process.</p>
|
|
|
|
<hr />
|
|
|
|
<p>we run the current stable version of postgresql from the <a href="https://wiki.postgresql.org/wiki/Apt">postgres
|
|
apt repos</a>. postgres <a href="https://www.postgresql.org/docs/release/13.0/">13</a> was released recently
|
|
and the apt upgrades automatically created a new cluster running 13.</p>
|
|
|
|
<p>the database for mastodon has gotten quite large (about 16gb) which
|
|
complicates this upgrade a bit. this was my inital plan:</p>
|
|
|
|
<ul>
|
|
<li>drop the 13 cluster created by the apt package upgrades</li>
|
|
<li>upgrade the 12-main cluster to 13</li>
|
|
<li>drop the 12 cluster</li>
|
|
</ul>
|
|
|
|
|
|
<p>these steps appeared to work fine, but closer inspection afterwards
|
|
led me to discover that the new cluster had ended up with <code>SQL_ASCII</code>
|
|
encoding somehow. this is not a situation we want to be in. time to fix it.</p>
|
|
|
|
<p>here’s the new plan:</p>
|
|
|
|
<ul>
|
|
<li><p>stop mastodon
|
|
<code>
|
|
for i in streaming sidekiq web; do systemctl stop mastodon-$i; done
|
|
</code></p></li>
|
|
<li><p>dump current database state
|
|
<code>
|
|
pg_dump mastodon_production > db.dump
|
|
</code></p></li>
|
|
<li><p>drop and recreate cluster with utf8 encoding
|
|
<code>
|
|
pg_dropcluster 13 main --stop
|
|
pg_createcluster --locale=en_US.UTF8 13 main --start
|
|
</code></p></li>
|
|
<li><p>restore backup
|
|
<code>
|
|
sudo -u postgres psql -c "create user mastodon createdb;"
|
|
sudo -u mastodon createdb -E utf8 mastodon_production
|
|
sudo -u mastodon psql < db.dump
|
|
</code></p></li>
|
|
</ul>
|
|
|
|
|
|
<p>i’m still not 100% sure how the encoding reverted to ascii but
|
|
it seems that the locale was not correctly set while running the
|
|
apt upgrades…</p>
|
|
|
|
<p>if this happens to you, hopefully this helps you wade out while
|
|
keeping all your data :)</p>
|
|
|
|
<p>tags: <a href='tag_sysadmin.html'>sysadmin</a>, <a href='tag_tilde.html'>tilde</a>, <a href='tag_social-networks.html'>social-networks</a></p>
|
|
|
|
<!-- text end -->
|
|
<!-- entry end -->
|
|
</div>
|
|
<div id="footer">CC by-nc-nd <a href="https://tilde.team/~ben/">~ben</a> — <a href="mailto:ben@tilde.team">ben@tilde.team</a><br>
|
|
generated with <a href="https://tildegit.org/team/bashblog">bashblog</a>, a single bash script to easily create blogs like this one</div>
|
|
</div></div>
|
|
<script src="https://utteranc.es/client.js"
|
|
repo="benharri/tilde"
|
|
issue-term="title"
|
|
crossorigin="anonymous"
|
|
theme="github-dark"
|
|
async>
|
|
</script>
|
|
|
|
</main>
|
|
<br>
|
|
</body></html>
|