nixos/postgresql: add upgrade documentation

1 files changed, 45 insertions, 2 deletions

diff --git a/nixos/modules/services/databases/postgresql.xml b/nixos/modules/services/databases/postgresql.xml
index 72d4a8249a32..1754f19fe1eb 100644
--- a/nixos/modules/services/databases/postgresql.xml
+++ b/nixos/modules/services/databases/postgresql.xml
@@ -63,8 +63,51 @@ Type "help" for help.
   <title>Upgrading</title>
 
   <para>
-   FIXME: document dump/upgrade/load cycle.
-  </para>
+   Major PostgreSQL upgrade requires PostgreSQL downtime and a few imperative steps to be called. To simplify this process, use the following NixOS module:
+
+<programlisting>
+  containers.temp-pg.config.services.postgresql = {
+    enable = true;
+    package = pkgs.postgresql_12;
+    ## set a custom new dataDir
+    # dataDir = "/some/data/dir";
+  };
+  environment.systemPackages =
+    let newpg = config.containers.temp-pg.config.services.postgresql;
+    in [
+      (pkgs.writeScriptBin "upgrade-pg-cluster" ''
+        set -x
+        export OLDDATA="${config.services.postgresql.dataDir}"
+        export NEWDATA="${newpg.dataDir}"
+        export OLDBIN="${config.services.postgresql.package}/bin"
+        export NEWBIN="${newpg.package}/bin"
+
+        install -d -m 0700 -o postgres -g postgres "$NEWDATA"
+        cd "$NEWDATA"
+        sudo -u postgres $NEWBIN/initdb -D "$NEWDATA"
+
+        systemctl stop postgresql    # old one
+
+        sudo -u postgres $NEWBIN/pg_upgrade \
+          --old-datadir "$OLDDATA" --new-datadir "$NEWDATA" \
+          --old-bindir $OLDBIN --new-bindir $NEWBIN \
+          "$@"
+      '')
+    ];
+</programlisting>
+    </para>
+  <para>
+      The upgrade process is:</para>
+
+  <orderedlist>
+      <listitem><para>Rebuild nixos configuration with the configuration above added to your <filename>configuration.nix</filename>. Alternatively, add that into separate file and reference it in <literal>imports</literal> list.</para></listitem>
+          <listitem><para>Login as root (<literal>sudo su -</literal>)</para></listitem>
+              <listitem><para>Run <literal>upgrade-pg-cluster</literal>. It will stop old postgresql, initialize new one and migrate old one to new one. You may supply arguments like <literal>--jobs 4</literal> and <literal>--link</literal> to speedup migration process. See <link xlink:href="https://www.postgresql.org/docs/current/pgupgrade.html" /> for details.</para></listitem>
+                  <listitem><para>Change postgresql package in NixOS configuration to the one you were upgrading to, and change <literal>dataDir</literal> to the one you have migrated to. Rebuild NixOS. This should start new postgres using upgraded data directory.</para></listitem>
+                      <listitem><para>After upgrade you may want to <literal>ANALYZE</literal> new db.</para></listitem>
+  </orderedlist>
+  
+
  </section>
  <section xml:id="module-services-postgres-options">
   <title>Options</title>