PostgreSQL: Difference between revisions

(2 intermediate revisions by 2 users not shown)

Line 25:

* [https://search.nixos.org/options?query=services.postgresql Available NixOS Postgresql service options]

It's also possible to setup PostgreSQL with [[Nix Darwin]] similar to how you would on NixOS, see the [https://daiderd.com/nix-darwin/manual/index.html#opt-services.postgresql.enable options].

====== Beginner's Note: ======

If you are studying Postgres by following its [https://www.postgresql.org/docs/current/tutorial.html official tutorial], you would find that the <code>pg_config</code> executable is missing [https://github.com/NixOS/nixpkgs/issues/408785]. To obtain <code>pg_config</code>, use the <code>pg_config</code> attribute of the <code>postgresql</code> package as in:

{

# ...

config.services.postgresql.package = pkgs.postgresql.pg_config;

}

</syntaxhighlight>

=== Verify setup ===

Line 328:

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 25: / Line 25: @@
 * [https://search.nixos.org/options?query=services.postgresql Available NixOS Postgresql service options]
 It's also possible to setup PostgreSQL with [[Nix Darwin]] similar to how you would on NixOS, see the [https://daiderd.com/nix-darwin/manual/index.html#opt-services.postgresql.enable options].
+====== Beginner's Note: ======
+If you are studying Postgres by following its [https://www.postgresql.org/docs/current/tutorial.html official tutorial], you would find that the <code>pg_config</code> executable is missing [https://github.com/NixOS/nixpkgs/issues/408785]. To obtain <code>pg_config</code>, use the <code>pg_config</code> attribute of the <code>postgresql</code> package as in:
+<syntaxhighlight lang="nix">
+{
+  # ...
+  config.services.postgresql.package = pkgs.postgresql.pg_config;
+}
+</syntaxhighlight>
 === Verify  setup ===
@@ Line 328: / 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 ==