PostgreSQL: Difference between revisions

(One intermediate revision by one other user not shown)

Line 338:

Let the service successfully start once, and then stop it. Upon completion, proceed with the following command, substituting the numbers 15 and 16 with the respective versions you previously used and the more recent one:

<syntaxhighlight>sudo -u postgres pg_upgrade -b "$(nix build --no-link --print-out-paths nixpkgs#postgresql_15.out)/bin" -B /run/current-system/sw/bin -d /var/lib/postgresql/15 -D /var/lib/postgresql/16</syntaxhighlight>~~The~~ [https://nixos.org/manual/nixos/stable/#module-postgresql NixOS manual] also ~~has more detailed~~ information about ~~major~~ upgrades.

<syntaxhighlight>sudo -u postgres pg_upgrade -b "$(nix build --no-link --print-out-paths nixpkgs#postgresql_15.out)/bin" -B /run/current-system/sw/bin -d /var/lib/postgresql/15 -D /var/lib/postgresql/16</syntaxhighlight>

If this fails with the <code>Only the install user can be defined in the new cluster</code> message, you might have some luck using <code>initdb</code> to create the data directory by hand instead of relying on the <code>postgresql</code> systemd service to do that.

Following the example above - upgrading to Postgresql 16 - you'd do:

<syntaxhighlight>rm -rf /var/lib/postgresql/16

sudo -u postgres initdb -D /var/lib/postgresql/16

sudo -u postgres pg_upgrade ...</syntaxhighlight>

Triple check you're not actually <code>rm -rf</code>'ing your actual (previous) database! This is meant to remove only the empty database created by the newer Postgresql version, so that <code>initdb</code> starts with a clean slate.

[https://nixos.org/manual/nixos/stable/#module-postgresql NixOS manual] also contains useful information about this kind of upgrades.

=== Upgrading versions <18 to >=18 ===

Starting with v18, <code>initdb</code> defaults to enabling data checksums. This will prevent you from being able to upgrade from previous versions that did not have checksumming enabled, as <code>pg_upgrade</code> requires matching cluster checksum settings<ref>https://www.postgresql.org/docs/release/18.0/</ref>.

To maintain compatibility with a previous database, you may disable checksumming when creating the new database by using the appropriate option, <code>initdb --no-data-checksums ...</code>.

Alternatively, you can enable checksumming on the previous database with relatively little effort using <code>pg_checksums --pgdata={OLD_DATA_DIR} --enable --progress</code>. This must be done using the binary from the current postgres version, not from the version you wish to upgrade to.

== See also ==

@@ Line 338: / Line 338: @@
 Let the service successfully start once, and then stop it. Upon completion, proceed with the following command, substituting the numbers 15 and 16 with the respective versions you previously used and the more recent one:
-<syntaxhighlight>sudo -u postgres pg_upgrade -b "$(nix build --no-link --print-out-paths nixpkgs#postgresql_15.out)/bin" -B /run/current-system/sw/bin -d /var/lib/postgresql/15 -D /var/lib/postgresql/16</syntaxhighlight>The [https://nixos.org/manual/nixos/stable/#module-postgresql NixOS manual] also has more detailed information about major upgrades.
+<syntaxhighlight>sudo -u postgres pg_upgrade -b "$(nix build --no-link --print-out-paths nixpkgs#postgresql_15.out)/bin" -B /run/current-system/sw/bin -d /var/lib/postgresql/15 -D /var/lib/postgresql/16</syntaxhighlight>
+If this fails with the <code>Only the install user can be defined in the new cluster</code> message, you might have some luck using <code>initdb</code> to create the data directory by hand instead of relying on the <code>postgresql</code> systemd service to do that.
+Following the example above - upgrading to Postgresql 16 - you'd do:
+<syntaxhighlight>rm -rf /var/lib/postgresql/16
+sudo -u postgres initdb -D /var/lib/postgresql/16
+sudo -u postgres pg_upgrade ...</syntaxhighlight>
+Triple check you're not actually <code>rm -rf</code>'ing your actual (previous) database! This is meant to remove only the empty database created by the newer Postgresql version, so that <code>initdb</code> starts with a clean slate.
+[https://nixos.org/manual/nixos/stable/#module-postgresql NixOS manual] also contains useful information about this kind of upgrades.
+=== Upgrading versions <18 to >=18 ===
+Starting with v18, <code>initdb</code> defaults to enabling data checksums. This will prevent you from being able to upgrade from previous versions that did not have checksumming enabled, as <code>pg_upgrade</code> requires matching cluster checksum settings<ref>https://www.postgresql.org/docs/release/18.0/</ref>.
+To maintain compatibility with a previous database, you may disable checksumming when creating the new database by using the appropriate option, <code>initdb --no-data-checksums ...</code>.
+Alternatively, you can enable checksumming on the previous database with relatively little effort using <code>pg_checksums --pgdata={OLD_DATA_DIR} --enable --progress</code>. This must be done using the binary from the current postgres version, not from the version you wish to upgrade to.
 == See also ==