<?xml version="1.0" encoding="UTF-8"?><feed xmlns="http://www.w3.org/2005/Atom" xmlns:fh="http://purl.org/syndication/history/1.0" xml:base="/feed.atom" xml:lang="en-CA"><title>Kevin Cox's Blog</title><id>https://kevincox.ca/</id><link rel="alternate" href="https://kevincox.ca/"/><link rel="hub" href="https://pubsubhubbub.appspot.com/"/><link href="https://kevincox.ca/feed.atom" rel="self"/><link href="https://kevincox.ca/feed.atom" rel="first"/><link href="https://kevincox.ca/feed/2013-04-12.atom" rel="last"/><link href="https://kevincox.ca/feed/2025-08-05.atom" rel="next"/><fh:archive/><link href="https://kevincox.ca/feed.atom" rel="current"/><link href="https://kevincox.ca/feed/2025-08-05.atom" rel="prev-archive"/><icon>/logo.svg</icon><updated>2026-11-05T18:50:00-05:00</updated><author><name>Kevin Cox</name><uri>https://kevincox.ca</uri><email>kevincox@kevincox.ca</email></author><entry xml:base="/2026/11/05/sidewalk-game/"><title>The Sidewalk Game</title><link rel="alternate" type="text/html" href="https://kevincox.ca/2026/11/05/sidewalk-game/"/><id>https://kevincox.ca/2026/11/05/sidewalk-game/</id><published>2026-11-05T18:50:00-05:00</published><updated>2026-11-05T18:50:00-05:00</updated><category term="Life"/><category term="Games"/><content type="html">&lt;main class=_0 style="--gap:1em;display:grid;gap:var(--gap);grid:none/1fr minmax(0,36em) 1fr;margin:0;overflow:auto;padding:0"&gt;&lt;p style=grid-column:2;margin:0;padding:0&gt;When new sidewalks are poured they are stamped with the year and construction company, presumably so that if they aren’t properly made they know who to blame. Our family has taken to playing a “game” where we try to find sidewalks from our birth years.&lt;/p&gt;&lt;p style=grid-column:2;margin:0;padding:0&gt;&lt;a class=_1 href=https://kevincox.ca/a/on495_/sidewalk.avif style=display:grid;margin:0;padding:0;place-items:center&gt;&lt;img alt="sidewalk with year stamp" src=https://kevincox.ca/a/on495_/sidewalk.avif loading=lazy style=margin:0;padding:0;width:100%&gt;&lt;/a&gt;&lt;/p&gt;&lt;p style=grid-column:2;margin:0;padding:0&gt;The dynamics of this game are mildly interesting. You have the most matching sidewalks at the end of your birth year and it only goes down from there as they slowly get replaced. (Also for some reason only the ends get stamped, so once the two ends are replaced you can’t “win” with that sidewalk anymore even if the middle survives.) Additionally the stamp often gets harder to read as it ages and the sidewalk wears down. So the game gets harder and victories more exciting over time.&lt;/p&gt;&lt;p style=grid-column:2;margin:0;padding:0&gt;We end up noticing certain areas with lots of matching sidewalks where lots of new construction happened in our birth years. We also notice areas with years that are frustratingly close, but have no matches. In general we become a bit more aware of how old various neighbourhoods are. As a bonus game, keep track of the oldest sidewalk you’ve found.&lt;/p&gt;&lt;figure style=grid-column:2;margin:0;padding:0&gt;&lt;a class=_1 href=https://kevincox.ca/a/LWz1CX/sidewalk-old.avif style=display:grid;margin:0;padding:0;place-items:center&gt;&lt;img alt="Cobblestone paving with &amp;quot;1886&amp;quot; scratched into one stone." src=https://kevincox.ca/a/LWz1CX/sidewalk-old.avif loading=lazy style=margin:0;padding:0;width:100%&gt;&lt;/a&gt;&lt;figcaption class=_2 style=font-style:italic;margin:0;padding:0;text-align:center&gt;I doubt the originality of this "stamp".&lt;/figcaption&gt;&lt;/figure&gt;&lt;p style=grid-column:2;margin:0;padding:0&gt;All in all calling this a “game” is a stretch, but it is a slightly interesting addition to routine walks and lets us learn a bit about the neighbourhoods we walk through. Sometimes we even pose for photos with our sidewalks just for fun.&lt;/p&gt;&lt;/main&gt;</content></entry><entry xml:base="/2026/08/07/lost-inputs/"><title>Lost Inputs</title><link rel="alternate" type="text/html" href="https://kevincox.ca/2026/08/07/lost-inputs/"/><id>https://kevincox.ca/2026/08/07/lost-inputs/</id><published>2026-08-07T20:05:00-04:00</published><updated>2026-08-07T20:05:00-04:00</updated><category term="UX"/><content type="html"><![CDATA[<main class=_0 style="--gap:1em;display:grid;gap:var(--gap);grid:none/1fr minmax(0,36em) 1fr;margin:0;overflow:auto;padding:0"><p style=grid-column:2;margin:0;padding:0>A UI shouldn’t lose your inputs.</p><ul class="_1 _2" style="grid-column:2;list-style:disc;list-style:&quot;- &quot;;margin:0;padding:0;padding-inline-start:2em"><li style=margin:0;padding:0>If you are typing, each and every key should result in a letter in order. No characters should e mssig or rerodeerd.</li><li style=margin:0;padding:0>If I switch windows with <kbd class=_3 style="border:2px solid light-dark(#ccc,#555);border-radius:.2em;box-shadow:2px 2px light-dark(#999,#333);color:light-dark(#000,#fff);margin:0 1px;padding:0 .1em">Alt+Tab</kbd> and then type, the events must always go to the newly focused window.</li><li style=margin:0;padding:0>If I close a tab with <kbd class=_3 style="border:2px solid light-dark(#ccc,#555);border-radius:.2em;box-shadow:2px 2px light-dark(#999,#333);color:light-dark(#000,#fff);margin:0 1px;padding:0 .1em">Ctrl+w</kbd> and then type, the events must go to the newly focused tab.</li></ul><p style=grid-column:2;margin:0;padding:0>This may seem obvious, but I often see applications that behave incorrectly if your inputs come too fast. One common situation where I see interfaces falter is keyboard shortcuts that bring you to a text input. Keypresses after the shortcut should always land in that input.</p><h2 id=examples class=_4 style="grid-column:2;margin:.4em 0 0;padding:0"><a href=https://kevincox.ca/2026/08/07/lost-inputs/#examples style=color:inherit!important;margin:0;padding:0;text-decoration:inherit;text-shadow:inherit!important>Examples</a></h2><h3 id=app-launchers class=_4 style="grid-column:2;margin:.4em 0 0;padding:0"><a href=https://kevincox.ca/2026/08/07/lost-inputs/#app-launchers style=color:inherit!important;margin:0;padding:0;text-decoration:inherit;text-shadow:inherit!important>App Launchers</a></h3><p style=grid-column:2;margin:0;padding:0>A common example is an app launcher. On GNOME I tap <kbd class=_3 style="border:2px solid light-dark(#ccc,#555);border-radius:.2em;box-shadow:2px 2px light-dark(#999,#333);color:light-dark(#000,#fff);margin:0 1px;padding:0 .1em">Super</kbd> and type to search my apps. If I type <kbd class=_3 style="border:2px solid light-dark(#ccc,#555);border-radius:.2em;box-shadow:2px 2px light-dark(#999,#333);color:light-dark(#000,#fff);margin:0 1px;padding:0 .1em">Super</kbd><kbd class=_3 style="border:2px solid light-dark(#ccc,#555);border-radius:.2em;box-shadow:2px 2px light-dark(#999,#333);color:light-dark(#000,#fff);margin:0 1px;padding:0 .1em">f</kbd><kbd class=_3 style="border:2px solid light-dark(#ccc,#555);border-radius:.2em;box-shadow:2px 2px light-dark(#999,#333);color:light-dark(#000,#fff);margin:0 1px;padding:0 .1em">o</kbd><kbd class=_3 style="border:2px solid light-dark(#ccc,#555);border-radius:.2em;box-shadow:2px 2px light-dark(#999,#333);color:light-dark(#000,#fff);margin:0 1px;padding:0 .1em">o</kbd> it should search for “foo” every time. GNOME works correctly here. No matter how overloaded and laggy my system is, and no matter how fast I type, the search is accurately captured. KDE’s KRunner does not pass this test. Oftentimes the first few characters of my search will land in the previously-focused application. I think this is because it is implemented as a separate process, and it can take a moment to start up and capture keyboard focus. macOS’s Spotlight also fails, although the failure window seems much smaller than KDE’s and required significant system load to hit.</p><h3 id=clipboard class=_4 style="grid-column:2;margin:.4em 0 0;padding:0"><a href=https://kevincox.ca/2026/08/07/lost-inputs/#clipboard style=color:inherit!important;margin:0;padding:0;text-decoration:inherit;text-shadow:inherit!important>Clipboard</a></h3><p style=grid-column:2;margin:0;padding:0><a href=https://ghostty.org/ class=_5 style=color:inherit;margin:0;padding:0;text-decoration:underline;-webkit-text-decoration-color:gray;text-decoration-color:gray>Ghostty</a> handles paste events asynchronously. Typing <kbd class=_3 style="border:2px solid light-dark(#ccc,#555);border-radius:.2em;box-shadow:2px 2px light-dark(#999,#333);color:light-dark(#000,#fff);margin:0 1px;padding:0 .1em">Ctrl+Shift+v</kbd><kbd class=_3 style="border:2px solid light-dark(#ccc,#555);border-radius:.2em;box-shadow:2px 2px light-dark(#999,#333);color:light-dark(#000,#fff);margin:0 1px;padding:0 .1em">Enter</kbd> can trigger the <kbd class=_3 style="border:2px solid light-dark(#ccc,#555);border-radius:.2em;box-shadow:2px 2px light-dark(#999,#333);color:light-dark(#000,#fff);margin:0 1px;padding:0 .1em">Enter</kbd> first, then the pasted text lands on the next line.</p><h3 id=web-apps class=_4 style="grid-column:2;margin:.4em 0 0;padding:0"><a href=https://kevincox.ca/2026/08/07/lost-inputs/#web-apps style=color:inherit!important;margin:0;padding:0;text-decoration:inherit;text-shadow:inherit!important>Web Apps</a></h3><p style=grid-column:2;margin:0;padding:0>This occurs on web apps too. I’ll pick on <a href=https://linear.app/ class=_5 style=color:inherit;margin:0;padding:0;text-decoration:underline;-webkit-text-decoration-color:gray;text-decoration-color:gray>Linear</a> as they appear to have thought about this to some degree. Some shortcuts like <kbd class=_3 style="border:2px solid light-dark(#ccc,#555);border-radius:.2em;box-shadow:2px 2px light-dark(#999,#333);color:light-dark(#000,#fff);margin:0 1px;padding:0 .1em">a</kbd><kbd class=_3 style="border:2px solid light-dark(#ccc,#555);border-radius:.2em;box-shadow:2px 2px light-dark(#999,#333);color:light-dark(#000,#fff);margin:0 1px;padding:0 .1em">1</kbd> will reliably self-assign the ticket no matter how fast I type or how locked up my system is. Even complex interactions like <kbd class=_3 style="border:2px solid light-dark(#ccc,#555);border-radius:.2em;box-shadow:2px 2px light-dark(#999,#333);color:light-dark(#000,#fff);margin:0 1px;padding:0 .1em">m</kbd><kbd class=_3 style="border:2px solid light-dark(#ccc,#555);border-radius:.2em;box-shadow:2px 2px light-dark(#999,#333);color:light-dark(#000,#fff);margin:0 1px;padding:0 .1em">b</kbd><kbd class=_3 style="border:2px solid light-dark(#ccc,#555);border-radius:.2em;box-shadow:2px 2px light-dark(#999,#333);color:light-dark(#000,#fff);margin:0 1px;padding:0 .1em">Ctrl+V</kbd><kbd class=_3 style="border:2px solid light-dark(#ccc,#555);border-radius:.2em;box-shadow:2px 2px light-dark(#999,#333);color:light-dark(#000,#fff);margin:0 1px;padding:0 .1em">Enter</kbd> will <kbd class=_3 style="border:2px solid light-dark(#ccc,#555);border-radius:.2em;box-shadow:2px 2px light-dark(#999,#333);color:light-dark(#000,#fff);margin:0 1px;padding:0 .1em">m</kbd>ark the current issue as <kbd class=_3 style="border:2px solid light-dark(#ccc,#555);border-radius:.2em;box-shadow:2px 2px light-dark(#999,#333);color:light-dark(#000,#fff);margin:0 1px;padding:0 .1em">b</kbd>locked by the issue in my clipboard. That is how it should work!</p><p style=grid-column:2;margin:0;padding:0>However, they aren’t perfect. <kbd class=_3 style="border:2px solid light-dark(#ccc,#555);border-radius:.2em;box-shadow:2px 2px light-dark(#999,#333);color:light-dark(#000,#fff);margin:0 1px;padding:0 .1em">/</kbd><kbd class=_3 style="border:2px solid light-dark(#ccc,#555);border-radius:.2em;box-shadow:2px 2px light-dark(#999,#333);color:light-dark(#000,#fff);margin:0 1px;padding:0 .1em">f</kbd><kbd class=_3 style="border:2px solid light-dark(#ccc,#555);border-radius:.2em;box-shadow:2px 2px light-dark(#999,#333);color:light-dark(#000,#fff);margin:0 1px;padding:0 .1em">o</kbd><kbd class=_3 style="border:2px solid light-dark(#ccc,#555);border-radius:.2em;box-shadow:2px 2px light-dark(#999,#333);color:light-dark(#000,#fff);margin:0 1px;padding:0 .1em">o</kbd> can have various behaviours depending on how fast you type. It should search for “foo” every time, but I have seen a few failures:</p><ul class="_1 _2" style="grid-column:2;list-style:disc;list-style:&quot;- &quot;;margin:0;padding:0;padding-inline-start:2em"><li style=margin:0;padding:0>A prefix lands in the search box, but then it loses focus and future inputs are treated as global shortcuts again.</li><li style=margin:0;padding:0>Reordering such as “oof”. The “f” lands in the search box, but the cursor then resets to the start of the search box before I type “oo”.</li></ul><h3 id=mouse-events class=_4 style="grid-column:2;margin:.4em 0 0;padding:0"><a href=https://kevincox.ca/2026/08/07/lost-inputs/#mouse-events style=color:inherit!important;margin:0;padding:0;text-decoration:inherit;text-shadow:inherit!important>Mouse Events</a></h3><p style=grid-column:2;margin:0;padding:0>This can happen for mouse events too. For example Firefox has an excellent feature where after clicking the close button on a tab, the next tab’s close button will always land in the exact same spot. Even if the tabs were previously compressed because too many are open, they will wait until you move your mouse away to expand again. This way you can just keep clicking in the same location to close many tabs with ease.</p><p style=grid-column:2;margin:0;padding:0>But the tab closing animation ruins this! There is a gap in time where the old tab hasn’t gone away and clicks just disappear. So you can’t simply click 4 times to close 4 tabs, it may close fewer than you intended. You instead need to wait until the previous click is fully processed before performing the next click. This isn’t the worst failure mode but can make workflows where you close lots of tabs unnecessarily slow and annoying. <kbd class=_3 style="border:2px solid light-dark(#ccc,#555);border-radius:.2em;box-shadow:2px 2px light-dark(#999,#333);color:light-dark(#000,#fff);margin:0 1px;padding:0 .1em">Ctrl+W</kbd> doesn’t appear to suffer this issue and always closes the right number of tabs.</p><p style=grid-column:2;margin:0;padding:0>This reminds me of the classic moving click target UX failure. This is the opposite of that: instead of the target jumping away as you try to click, the predictable target is instead not ready yet.</p><h2 id=difficulty class=_4 style="grid-column:2;margin:.4em 0 0;padding:0"><a href=https://kevincox.ca/2026/08/07/lost-inputs/#difficulty style=color:inherit!important;margin:0;padding:0;text-decoration:inherit;text-shadow:inherit!important>Difficulty</a></h2><p style=grid-column:2;margin:0;padding:0>The main difficulty in implementing this correctly is that you need some sort of global input routing that buffers events until the application is ready for them. This can be quite difficult in browser applications as there is no way to “defer” input events other than completely blocking the main thread. So if follow-up keypresses are required you need to synchronously adjust focus, which may be difficult in your framework. This typically also precludes any form of lazy loading. Alternatively you would need to implement your own focus and input event handling logic, which is <a href=https://kevincox.ca/2021/08/18/fake-links/ class=_5 style=color:inherit;margin:0;padding:0;text-decoration:underline;-webkit-text-decoration-color:gray;text-decoration-color:gray>almost certainly a bad idea</a>.</p><p style=grid-column:2;margin:0;padding:0>Asynchronous work is a complicated case in general, because buffering inputs for an extended period is confusing to the user. What if that work can fail? Do you drop the buffered input events? Or feed it to the previously focused input control? Do you need some sort of timeout? Luckily I find that this case is very rare. (Although the clipboard example above is one of these cases.)</p><p style=grid-column:2;margin:0;padding:0>If you have any sort of UI automation, this is easy to test: feed the keys in as a single batch and be sure that your app handles them properly!</p><h2 id=why class=_4 style="grid-column:2;margin:.4em 0 0;padding:0"><a href=https://kevincox.ca/2026/08/07/lost-inputs/#why style=color:inherit!important;margin:0;padding:0;text-decoration:inherit;text-shadow:inherit!important>Why?</a></h2><p style=grid-column:2;margin:0;padding:0>Typing speed shouldn’t affect functionality! Keyboard shortcuts are designed for people to go fast, nothing will harm your productivity more than familiar keyboard shortcuts doing something unexpected! (Bonus points if those inputs aren’t lost, but trigger a completely different action like “delete”.) It is easy to say that it is a 2 millisecond gap and no one will hit a window that small, but that gap may be much larger on a low-end machine swapping like crazy. Or maybe my local LLM is hogging my GPU for a second or two. Don’t let fingers race your UI, just close the gap.</p></main>]]></content></entry><entry xml:base="/2025/11/18/digdoku/"><title>Digdoku</title><link rel="alternate" type="text/html" href="https://kevincox.ca/2025/11/18/digdoku/"/><id>https://kevincox.ca/2025/11/18/digdoku/</id><published>2025-11-18T20:45:00-05:00</published><updated>2025-11-18T20:45:00-05:00</updated><content type="html"><![CDATA[<main class=_0 style="--gap:1em;display:grid;gap:var(--gap);grid:none/1fr minmax(0,36em) 1fr;margin:0;overflow:auto;padding:0"><p style=grid-column:2;margin:0;padding:0>I randomly had the idea that it would be interesting to “dig into” all possible Sudoku puzzles. So I created <a href=https://digdoku.kevincox.ca/ class=_1 style=color:inherit;margin:0;padding:0;text-decoration:underline;-webkit-text-decoration-color:gray;text-decoration-color:gray>Digdoku</a>. The landing page is an “empty” puzzle, each link leads to a puzzle with one additional square filled. Values that are not possible are excluded. So you can never get an invalid puzzle by clicking links (but you can by editing the URL).</p><p style=grid-column:2;margin:0;padding:0>The concept is simple. Basically for every unset cell it attempts to solve the puzzle with every number from 1-9. If any solution is found that value is possible, otherwise it is omitted.</p><p style=grid-column:2;margin:0;padding:0>This “eager” removal of impossible values means that it functions as a solver as well. By the time you click all of the provided squares (or often sooner) there will be only one possible grid, and it will be revealed.</p><p style=grid-column:2;margin:0;padding:0>However, implementing this simple solution is fairly slow. So some optimizations were necessary. These are primarily around skipping options that are easily determined to be impossible or were already found to be possible. The solver is still mostly a brute-force depth-first solver with some very basic rules put on top (such as eliminating duplicate values in a box/column/row group and if a value can only be in one place as a group it must be that).</p><p style=grid-column:2;margin:0;padding:0>The slowest case I am aware of takes about 100ms on my desktop. It could almost certainly be much faster, I suspect another order of magnitude would be fairly easy to achieve. But in the first version some cases took over six minutes, so I’m calling it good enough for now.</p><p style=grid-column:2;margin:0;padding:0>One downside is that the URL only keeps minimal state, so a lot of options need to be re-eliminated every step of the way. Maybe compiling to WASM and running an incremental solve client-side would be better. Might be something interesting to try. It would also avoid the risk that my single-core VPS gets overloaded if this gets popular. I do have caching enabled, but I suspect after a few clicks hits will become quite rare.</p><p style=grid-column:2;margin:0;padding:0>There is also something about a simple HTML response that I am very fond of. Theoretically you could pre-render every possible grid into a static site. But according to Wikipedia there are 6.6e27 valid Sudoku grids, so I’ll hold off on the pre-render for now.</p></main>]]></content></entry><entry xml:base="/2025/08/24/nixos-postgres-upgrade/"><title>NixOS PostgreSQL Major Version Upgrade</title><link rel="alternate" type="text/html" href="https://kevincox.ca/2025/08/24/nixos-postgres-upgrade/"/><id>https://kevincox.ca/2025/08/24/nixos-postgres-upgrade/</id><published>2025-08-24T10:55:00-04:00</published><updated>2025-08-24T10:55:00-04:00</updated><category term="PostgreSQL"/><category term="NixOS"/><content type="html"><![CDATA[<main class=_0 style="--gap:1em;display:grid;gap:var(--gap);grid:none/1fr minmax(0,36em) 1fr;margin:0;overflow:auto;padding:0"><p style=grid-column:2;margin:0;padding:0>PostgreSQL major version upgrades are something that NixOS currently doesn’t handle for you. This is my process, documented mostly so that I can remember it next time I want to upgrade.</p><aside class="_1 _2" style="background:#600;box-shadow:4px 4px 8px #000;color:#fff;color-scheme:dark;display:grid;gap:.4em;grid-column:2;grid-template-columns:0 minmax(0,1fr) 0;margin:0;padding:.4em 0"><b style=grid-column:2;margin:0;padding:0>Warning</b><p style=grid-column:2;margin:0;padding:0>I highly recommend that you read the <a href=https://www.postgresql.org/docs/17/upgrading.html class=_3 style=color:inherit;margin:0;padding:0;text-decoration:underline;-webkit-text-decoration-color:gray;text-decoration-color:gray>PostgreSQL Upgrading documentation</a>. It is well written and gives important caveats. It would be risky to follow through these steps without understanding the process.</p></aside><h2 id=configuration class=_4 style="grid-column:2;margin:.4em 0 0;padding:0"><a href=https://kevincox.ca/2025/08/24/nixos-postgres-upgrade/#configuration style=color:inherit!important;margin:0;padding:0;text-decoration:inherit;text-shadow:inherit!important>Configuration</a></h2><p style=grid-column:2;margin:0;padding:0>I use the standard NixOS module for Postgres. My main config looks like this:</p><pre class=_5 style="background:light-dark(#ddd,#222);border-block:1px solid light-dark(#aaa,#444);display:grid;grid-column:2;grid-column:1/4;grid-template-columns:subgrid;justify-content:flex-start;margin:0;overflow:auto hidden;padding:.4em 0"><code class="_6 _7 language-nix" style=color:light-dark(#222,#ddd);grid-column:2;margin:0;padding:0;-moz-tab-size:2;-o-tab-size:2;tab-size:2;white-space:pre>{ <span class=_8 style=color:light-dark(#47a,#fff);font-weight:700;margin:0;padding:0>lib</span><span class=_9 style=color:light-dark(#85a,#f90);margin:0;padding:0>,</span> <span class=_8 style=color:light-dark(#47a,#fff);font-weight:700;margin:0;padding:0>pkgs</span><span class=_9 style=color:light-dark(#85a,#f90);margin:0;padding:0>,</span> <span class=_9 style=color:light-dark(#85a,#f90);margin:0;padding:0>... </span>}: {
	<span class=_a style=color:light-dark(#47a,#fff);font-weight:500;margin:0;padding:0>services</span>.<span class=_a style=color:light-dark(#47a,#fff);font-weight:500;margin:0;padding:0>postgresql</span> <span class=_9 style=color:light-dark(#85a,#f90);margin:0;padding:0>=</span> {
		<span class=_a style=color:light-dark(#47a,#fff);font-weight:500;margin:0;padding:0>enable</span> <span class=_9 style=color:light-dark(#85a,#f90);margin:0;padding:0>=</span> <span class=_b style=color:light-dark(#85a,#f90);font-weight:500;margin:0;padding:0>true</span>;
		<span class=_a style=color:light-dark(#47a,#fff);font-weight:500;margin:0;padding:0>package</span> <span class=_9 style=color:light-dark(#85a,#f90);margin:0;padding:0>=</span> <span class=_c style=color:light-dark(#111,#eee);margin:0;padding:0>pkgs</span><span class=_9 style=color:light-dark(#85a,#f90);margin:0;padding:0>.</span><span class=_c style=color:light-dark(#111,#eee);margin:0;padding:0>postgresql_17</span>;
		<span class=_a style=color:light-dark(#47a,#fff);font-weight:500;margin:0;padding:0>initdbArgs</span> <span class=_9 style=color:light-dark(#85a,#f90);margin:0;padding:0>=</span> [
			<span class=_d style=color:light-dark(#780,#bd3);margin:0;padding:0><span class=_d style=color:light-dark(#780,#bd3);margin:0;padding:0>"</span>--encoding=UTF8<span class=_d style=color:light-dark(#780,#bd3);margin:0;padding:0>"</span></span>
			<span class=_d style=color:light-dark(#780,#bd3);margin:0;padding:0><span class=_d style=color:light-dark(#780,#bd3);margin:0;padding:0>"</span>--no-locale<span class=_d style=color:light-dark(#780,#bd3);margin:0;padding:0>"</span></span>
		];
		<span class=_a style=color:light-dark(#47a,#fff);font-weight:500;margin:0;padding:0>ensureUsers</span> <span class=_9 style=color:light-dark(#85a,#f90);margin:0;padding:0>=</span> [{
			<span class=_a style=color:light-dark(#47a,#fff);font-weight:500;margin:0;padding:0>name</span> <span class=_9 style=color:light-dark(#85a,#f90);margin:0;padding:0>=</span> <span class=_d style=color:light-dark(#780,#bd3);margin:0;padding:0><span class=_d style=color:light-dark(#780,#bd3);margin:0;padding:0>"</span>kevincox<span class=_d style=color:light-dark(#780,#bd3);margin:0;padding:0>"</span></span>;
		} {
			<span class=_a style=color:light-dark(#47a,#fff);font-weight:500;margin:0;padding:0>name</span> <span class=_9 style=color:light-dark(#85a,#f90);margin:0;padding:0>=</span> <span class=_d style=color:light-dark(#780,#bd3);margin:0;padding:0><span class=_d style=color:light-dark(#780,#bd3);margin:0;padding:0>"</span>root<span class=_d style=color:light-dark(#780,#bd3);margin:0;padding:0>"</span></span>;
		}];
	};

	<span class=_a style=color:light-dark(#47a,#fff);font-weight:500;margin:0;padding:0>systemd</span>.<span class=_a style=color:light-dark(#47a,#fff);font-weight:500;margin:0;padding:0>services</span>.<span class=_a style=color:light-dark(#47a,#fff);font-weight:500;margin:0;padding:0>postgresql</span> <span class=_9 style=color:light-dark(#85a,#f90);margin:0;padding:0>=</span> {
		<span class=_a style=color:light-dark(#47a,#fff);font-weight:500;margin:0;padding:0>postStart</span> <span class=_9 style=color:light-dark(#85a,#f90);margin:0;padding:0>=</span> <span class=_c style=color:light-dark(#111,#eee);margin:0;padding:0>lib</span><span class=_9 style=color:light-dark(#85a,#f90);margin:0;padding:0>.</span><span class=_c style=color:light-dark(#111,#eee);margin:0;padding:0>mkAfter</span> <span class=_d style=color:light-dark(#780,#bd3);margin:0;padding:0><span class=_d style=color:light-dark(#780,#bd3);margin:0;padding:0>''</span></span>
<span class=_d style=color:light-dark(#780,#bd3);margin:0;padding:0>			$PSQL -tAc "ALTER ROLE kevincox with SUPERUSER"</span>
<span class=_d style=color:light-dark(#780,#bd3);margin:0;padding:0>			$PSQL -tAc "ALTER ROLE root with SUPERUSER"</span>
<span class=_d style=color:light-dark(#780,#bd3);margin:0;padding:0>		<span class=_d style=color:light-dark(#780,#bd3);margin:0;padding:0>''</span></span>;
		<span class=_a style=color:light-dark(#47a,#fff);font-weight:500;margin:0;padding:0>serviceConfig</span> <span class=_9 style=color:light-dark(#85a,#f90);margin:0;padding:0>=</span> {
			<span class=_a style=color:light-dark(#47a,#fff);font-weight:500;margin:0;padding:0>TimeoutStartSec</span> <span class=_9 style=color:light-dark(#85a,#f90);margin:0;padding:0>=</span> <span class=_d style=color:light-dark(#780,#bd3);margin:0;padding:0><span class=_d style=color:light-dark(#780,#bd3);margin:0;padding:0>"</span>30min<span class=_d style=color:light-dark(#780,#bd3);margin:0;padding:0>"</span></span>;
		};
	};
}
</code></pre><p style=grid-column:2;margin:0;padding:0>Notable features:</p><ol class="_e _f" style=grid-column:2;margin:0;padding:0;padding-inline-start:2em><li style=margin:0;padding:0>I pin the version of Postgres, to ensure that I upgrade when I want to.</li><li style=margin:0;padding:0>I give myself and root superuser.</li></ol><h2 id=preparation class=_4 style="grid-column:2;margin:.4em 0 0;padding:0"><a href=https://kevincox.ca/2025/08/24/nixos-postgres-upgrade/#preparation style=color:inherit!important;margin:0;padding:0;text-decoration:inherit;text-shadow:inherit!important>Preparation</a></h2><ol class="_e _f" style=grid-column:2;margin:0;padding:0;padding-inline-start:2em><li style=margin:0;padding:0>Update your NixOS config but <strong style=margin:0;padding:0>don’t</strong> deploy it.</li><li style=margin:0;padding:0>Build the config to make sure everything is ready to go.</li></ol><h2 id=upgrading class=_4 style="grid-column:2;margin:.4em 0 0;padding:0"><a href=https://kevincox.ca/2025/08/24/nixos-postgres-upgrade/#upgrading style=color:inherit!important;margin:0;padding:0;text-decoration:inherit;text-shadow:inherit!important>Upgrading</a></h2><ol class="_e _f" style=grid-column:2;margin:0;padding:0;padding-inline-start:2em><li style=margin:0;padding:0><p style=margin:0;padding:0>Log in as the <code class="_g language-zsh" style="background:light-dark(#ddd,#000);border:1px solid light-dark(#999,#555);border-radius:.3em;color:light-dark(#000,#fff);margin:0;padding:0 .2em;-moz-tab-size:4;-o-tab-size:4;tab-size:4;white-space:pre-wrap">postgres</code> user. (<code class="_g language-zsh" style="background:light-dark(#ddd,#000);border:1px solid light-dark(#999,#555);border-radius:.3em;color:light-dark(#000,#fff);margin:0;padding:0 .2em;-moz-tab-size:4;-o-tab-size:4;tab-size:4;white-space:pre-wrap">sudo -su postgres</code>)</p></li><li style=margin:0;padding:0><p style=margin:0;padding:0>Define the old version <code class="_g language-zsh" style="background:light-dark(#ddd,#000);border:1px solid light-dark(#999,#555);border-radius:.3em;color:light-dark(#000,#fff);margin:0;padding:0 .2em;-moz-tab-size:4;-o-tab-size:4;tab-size:4;white-space:pre-wrap">old=16</code></p></li><li style=margin:0;padding:0><p style=margin:0;padding:0>Get the old and new versions:</p><pre class=_5 style="background:light-dark(#ddd,#222);border-block:1px solid light-dark(#aaa,#444);display:grid;grid-column:1/4;grid-template-columns:subgrid;justify-content:flex-start;margin:0;overflow:auto hidden;padding:.4em 0"><code class="_g language-sh" style=color:light-dark(#222,#ddd);grid-column:2;margin:0;padding:0;-moz-tab-size:4;-o-tab-size:4;tab-size:4;white-space:pre><span class=_b style=color:light-dark(#85a,#f90);font-weight:500;margin:0;padding:0>cd</span> /var/lib/postgresql/
pg_old=<span class=_d style=color:light-dark(#780,#bd3);margin:0;padding:0><span class=_d style=color:light-dark(#780,#bd3);margin:0;padding:0>$(</span>nix-build --no-out-link -A postgresql_<span class=_8 style=color:light-dark(#47a,#fff);font-weight:700;margin:0;padding:0>${old</span><span class=_9 style=color:light-dark(#85a,#f90);margin:0;padding:0>:?</span><span class=_8 style=color:light-dark(#47a,#fff);font-weight:700;margin:0;padding:0>}</span> <span class=_d style=color:light-dark(#780,#bd3);margin:0;padding:0>'</span>&lt;nixpkgs&gt;<span class=_d style=color:light-dark(#780,#bd3);margin:0;padding:0>')</span></span>
pg_new=<span class=_d style=color:light-dark(#780,#bd3);margin:0;padding:0><span class=_d style=color:light-dark(#780,#bd3);margin:0;padding:0>$(</span>nix-build --no-out-link -A postgresql_<span class=_d style=color:light-dark(#780,#bd3);margin:0;padding:0>$((</span>old<span class=_9 style=color:light-dark(#85a,#f90);margin:0;padding:0>+</span><span class=_b style=color:light-dark(#85a,#f90);font-weight:500;margin:0;padding:0>1</span><span class=_d style=color:light-dark(#780,#bd3);margin:0;padding:0>))</span> <span class=_d style=color:light-dark(#780,#bd3);margin:0;padding:0>'</span>&lt;nixpkgs&gt;<span class=_d style=color:light-dark(#780,#bd3);margin:0;padding:0>')</span></span>
</code></pre><aside class=_h style="background:#333;box-shadow:4px 4px 8px #000;color:#fff;color-scheme:dark;display:grid;gap:.4em;grid-template-columns:0 minmax(0,1fr) 0;margin:0;padding:.4em 0"><b style=grid-column:2;margin:0;padding:0>Note</b><p style=grid-column:2;margin:0;padding:0>If you are using extensions you will need them in <code class="_g language-zsh" style="background:light-dark(#ddd,#000);border:1px solid light-dark(#999,#555);border-radius:.3em;color:light-dark(#000,#fff);margin:0;padding:0 .2em;-moz-tab-size:4;-o-tab-size:4;tab-size:4;white-space:pre-wrap"><span class=_8 style=color:light-dark(#47a,#fff);font-weight:700;margin:0;padding:0>$pg_old</span></code> and <code class="_g language-zsh" style="background:light-dark(#ddd,#000);border:1px solid light-dark(#999,#555);border-radius:.3em;color:light-dark(#000,#fff);margin:0;padding:0 .2em;-moz-tab-size:4;-o-tab-size:4;tab-size:4;white-space:pre-wrap"><span class=_8 style=color:light-dark(#47a,#fff);font-weight:700;margin:0;padding:0>$pg_new</span></code>. If you are using the NixOS module you can make a package with <code class="_g language-zsh" style="background:light-dark(#ddd,#000);border:1px solid light-dark(#999,#555);border-radius:.3em;color:light-dark(#000,#fff);margin:0;padding:0 .2em;-moz-tab-size:4;-o-tab-size:4;tab-size:4;white-space:pre-wrap">nix-build <span class=_d style=color:light-dark(#780,#bd3);margin:0;padding:0><span class=_d style=color:light-dark(#780,#bd3);margin:0;padding:0>"</span>&lt;nixpkgs/nixos&gt;<span class=_d style=color:light-dark(#780,#bd3);margin:0;padding:0>"</span></span> -A config.services.postgresql.finalPackage</code>. If you are using <a href=https://colmena.cli.rs/ class=_3 style=color:inherit;margin:0;padding:0;text-decoration:underline;-webkit-text-decoration-color:gray;text-decoration-color:gray>colmena</a> you can do <code class="_g language-zsh" style="background:light-dark(#ddd,#000);border:1px solid light-dark(#999,#555);border-radius:.3em;color:light-dark(#000,#fff);margin:0;padding:0 .2em;-moz-tab-size:4;-o-tab-size:4;tab-size:4;white-space:pre-wrap">colmena <span class=_b style=color:light-dark(#85a,#f90);font-weight:500;margin:0;padding:0>eval</span> -E <span class=_d style=color:light-dark(#780,#bd3);margin:0;padding:0><span class=_d style=color:light-dark(#780,#bd3);margin:0;padding:0>"</span>{nodes, ...}: nodes.mynode.config.services.postgresql.finalPackage<span class=_d style=color:light-dark(#780,#bd3);margin:0;padding:0>"</span></span></code>.</p></aside></li><li style=margin:0;padding:0><p style=margin:0;padding:0>Initialize the new data directory. Be sure to use the arguments from your config!</p><pre class=_5 style="background:light-dark(#ddd,#222);border-block:1px solid light-dark(#aaa,#444);display:grid;grid-column:1/4;grid-template-columns:subgrid;justify-content:flex-start;margin:0;overflow:auto hidden;padding:.4em 0"><code class="_g language-sh" style=color:light-dark(#222,#ddd);grid-column:2;margin:0;padding:0;-moz-tab-size:4;-o-tab-size:4;tab-size:4;white-space:pre><span class=_8 style=color:light-dark(#47a,#fff);font-weight:700;margin:0;padding:0>$pg_new</span>/bin/initdb --encoding=UTF8 --no-locale <span class=_d style=color:light-dark(#780,#bd3);margin:0;padding:0><span class=_d style=color:light-dark(#780,#bd3);margin:0;padding:0>$((</span>old<span class=_9 style=color:light-dark(#85a,#f90);margin:0;padding:0>+</span><span class=_b style=color:light-dark(#85a,#f90);font-weight:500;margin:0;padding:0>1</span><span class=_d style=color:light-dark(#780,#bd3);margin:0;padding:0>))</span></span>
</code></pre></li><li style=margin:0;padding:0><p style=margin:0;padding:0>Copy the configuration (this is really only needed if you have extensions that need to be loaded, the main value we care about is <code class="_g language-zsh" style="background:light-dark(#ddd,#000);border:1px solid light-dark(#999,#555);border-radius:.3em;color:light-dark(#000,#fff);margin:0;padding:0 .2em;-moz-tab-size:4;-o-tab-size:4;tab-size:4;white-space:pre-wrap">shared_preload_libraries</code>. But copying the whole file is easiest and the NixOS module will overwrite it anyways when starting the new version.).</p><pre class=_5 style="background:light-dark(#ddd,#222);border-block:1px solid light-dark(#aaa,#444);display:grid;grid-column:1/4;grid-template-columns:subgrid;justify-content:flex-start;margin:0;overflow:auto hidden;padding:.4em 0"><code class="_g language-sh" style=color:light-dark(#222,#ddd);grid-column:2;margin:0;padding:0;-moz-tab-size:4;-o-tab-size:4;tab-size:4;white-space:pre>cp -v {<span class=_8 style=color:light-dark(#47a,#fff);font-weight:700;margin:0;padding:0>${old</span><span class=_9 style=color:light-dark(#85a,#f90);margin:0;padding:0>:?</span><span class=_8 style=color:light-dark(#47a,#fff);font-weight:700;margin:0;padding:0>}</span>,<span class=_d style=color:light-dark(#780,#bd3);margin:0;padding:0><span class=_d style=color:light-dark(#780,#bd3);margin:0;padding:0>$((</span>old<span class=_9 style=color:light-dark(#85a,#f90);margin:0;padding:0>+</span><span class=_b style=color:light-dark(#85a,#f90);font-weight:500;margin:0;padding:0>1</span><span class=_d style=color:light-dark(#780,#bd3);margin:0;padding:0>))</span></span>}/postgresql.conf
</code></pre></li><li style=margin:0;padding:0><p style=margin:0;padding:0>Run the check</p><pre class=_5 style="background:light-dark(#ddd,#222);border-block:1px solid light-dark(#aaa,#444);display:grid;grid-column:1/4;grid-template-columns:subgrid;justify-content:flex-start;margin:0;overflow:auto hidden;padding:.4em 0"><code class="_g language-sh" style=color:light-dark(#222,#ddd);grid-column:2;margin:0;padding:0;-moz-tab-size:4;-o-tab-size:4;tab-size:4;white-space:pre><span class=_8 style=color:light-dark(#47a,#fff);font-weight:700;margin:0;padding:0>$pg_new</span>/bin/pg_upgrade \
	--old-bindir=<span class=_8 style=color:light-dark(#47a,#fff);font-weight:700;margin:0;padding:0>$pg_old</span>/bin \
	--new-bindir=<span class=_8 style=color:light-dark(#47a,#fff);font-weight:700;margin:0;padding:0>$pg_new</span>/bin \
	--old-datadir=/var/lib/postgresql/<span class=_8 style=color:light-dark(#47a,#fff);font-weight:700;margin:0;padding:0>${old</span><span class=_9 style=color:light-dark(#85a,#f90);margin:0;padding:0>:?</span><span class=_8 style=color:light-dark(#47a,#fff);font-weight:700;margin:0;padding:0>}</span> \
	--new-datadir=/var/lib/postgresql/<span class=_d style=color:light-dark(#780,#bd3);margin:0;padding:0><span class=_d style=color:light-dark(#780,#bd3);margin:0;padding:0>$((</span>old<span class=_9 style=color:light-dark(#85a,#f90);margin:0;padding:0>+</span><span class=_b style=color:light-dark(#85a,#f90);font-weight:500;margin:0;padding:0>1</span><span class=_d style=color:light-dark(#780,#bd3);margin:0;padding:0>))</span></span> \
	-j16 \
	--clone \
	--check
</code></pre><aside class=_h style="background:#333;box-shadow:4px 4px 8px #000;color:#fff;color-scheme:dark;display:grid;gap:.4em;grid-template-columns:0 minmax(0,1fr) 0;margin:0;padding:.4em 0"><b style=grid-column:2;margin:0;padding:0>Note</b><p style=grid-column:2;margin:0;padding:0>If <code class="_g language-zsh" style="background:light-dark(#ddd,#000);border:1px solid light-dark(#999,#555);border-radius:.3em;color:light-dark(#000,#fff);margin:0;padding:0 .2em;-moz-tab-size:4;-o-tab-size:4;tab-size:4;white-space:pre-wrap">--clone</code> doesn’t work on your system you can try <code class="_g language-zsh" style="background:light-dark(#ddd,#000);border:1px solid light-dark(#999,#555);border-radius:.3em;color:light-dark(#000,#fff);margin:0;padding:0 .2em;-moz-tab-size:4;-o-tab-size:4;tab-size:4;white-space:pre-wrap">--copy-file-range</code> which should basically always work or if not just skip the argument altogether.</p></aside></li><li style=margin:0;padding:0><p style=margin:0;padding:0>Stop the old Postgres. <code class="_g language-zsh" style="background:light-dark(#ddd,#000);border:1px solid light-dark(#999,#555);border-radius:.3em;color:light-dark(#000,#fff);margin:0;padding:0 .2em;-moz-tab-size:4;-o-tab-size:4;tab-size:4;white-space:pre-wrap">systemctl stop postgresql</code></p><aside class="_1 _2" style="background:#600;box-shadow:4px 4px 8px #000;color:#fff;color-scheme:dark;display:grid;gap:.4em;grid-template-columns:0 minmax(0,1fr) 0;margin:0;padding:.4em 0"><b style=grid-column:2;margin:0;padding:0>Warning</b><p style=grid-column:2;margin:0;padding:0>This is the point where we copy the data. If the old Postgres starts running again it will write new data to the old directory that will not be migrated. This will result in data loss. If that happens remove the new state directory and start again.</p></aside></li><li style=margin:0;padding:0><p style=margin:0;padding:0>Run the migration. This is the same command as the check without <code class="_g language-zsh" style="background:light-dark(#ddd,#000);border:1px solid light-dark(#999,#555);border-radius:.3em;color:light-dark(#000,#fff);margin:0;padding:0 .2em;-moz-tab-size:4;-o-tab-size:4;tab-size:4;white-space:pre-wrap">--check</code>.</p></li><li style=margin:0;padding:0><p style=margin:0;padding:0>Start the new PostgreSQL server. (This is typically done by activating the new configuration such as <code class="_g language-zsh" style="background:light-dark(#ddd,#000);border:1px solid light-dark(#999,#555);border-radius:.3em;color:light-dark(#000,#fff);margin:0;padding:0 .2em;-moz-tab-size:4;-o-tab-size:4;tab-size:4;white-space:pre-wrap">sudo nixos-rebuild switch</code>.)</p></li></ol><p style=grid-column:2;margin:0;padding:0>At this point the new version is running. Note that rolling back will cause data loss as new changes are being made only to the new data directory.</p><p style=grid-column:2;margin:0;padding:0>You may wish to rename the old directory so that if you do accidentally roll back it is more likely that things fail rather than losing data. But if you have insecure apps that let the first user be admin that may be a bad idea. Maybe you could do something like put a file there or a directory owned by root? But I haven’t tested any of these things.</p><h2 id=cleanup class=_4 style="grid-column:2;margin:.4em 0 0;padding:0"><a href=https://kevincox.ca/2025/08/24/nixos-postgres-upgrade/#cleanup style=color:inherit!important;margin:0;padding:0;text-decoration:inherit;text-shadow:inherit!important>Cleanup</a></h2><ol class="_e _f" style=grid-column:2;margin:0;padding:0;padding-inline-start:2em><li style=margin:0;padding:0><p style=margin:0;padding:0>As suggested by <code class="_g language-zsh" style="background:light-dark(#ddd,#000);border:1px solid light-dark(#999,#555);border-radius:.3em;color:light-dark(#000,#fff);margin:0;padding:0 .2em;-moz-tab-size:4;-o-tab-size:4;tab-size:4;white-space:pre-wrap">pg_upgrade</code> run <code class="_g language-zsh" style="background:light-dark(#ddd,#000);border:1px solid light-dark(#999,#555);border-radius:.3em;color:light-dark(#000,#fff);margin:0;padding:0 .2em;-moz-tab-size:4;-o-tab-size:4;tab-size:4;white-space:pre-wrap">vacuumdb --all --analyze-in-stages</code>.</p></li><li style=margin:0;padding:0><p style=margin:0;padding:0>At some point in the future once you are confident that the new DB is running fine (a few weeks doesn’t hurt) you can remove the old data.</p><pre class=_5 style="background:light-dark(#ddd,#222);border-block:1px solid light-dark(#aaa,#444);display:grid;grid-column:1/4;grid-template-columns:subgrid;justify-content:flex-start;margin:0;overflow:auto hidden;padding:.4em 0"><code class="_g language-sh" style=color:light-dark(#222,#ddd);grid-column:2;margin:0;padding:0;-moz-tab-size:4;-o-tab-size:4;tab-size:4;white-space:pre><span class=_b style=color:light-dark(#85a,#f90);font-weight:500;margin:0;padding:0>cd</span> /var/lib/postgresql/
./delete_old_cluster.sh
rm -v delete_old_cluster.sh
</code></pre></li></ol></main>]]></content></entry></feed>