PostgreSQL: Difference between revisions
Edits the current example of adjusting `services.postgresql.authentication` to allow IP/TCP connections.If you (like me) aren't familiar with configuring authentication rules for a postgres instance and opt to blindly copy-and-paste the examples here, doing that for the IP/TCP example won't work because it removes the auth rule that allows the postgres user to connect via the peer authentication mode, which the systemd service will use. This |
|||
| (2 intermediate revisions by 2 users not shown) | |||
| Line 114: | Line 114: | ||
=== Set the Postgresql versions === | === Set the Postgresql versions === | ||
By default, NixOS uses whatever Postgres version shipped as default for your [https://search.nixos.org/options?show=system.stateVersion system.stateVersion]. To use a different or more recent version, you can set | By default, NixOS uses whatever Postgres version shipped as default for your [https://search.nixos.org/options?show=system.stateVersion system.stateVersion]. | ||
To use a different or more recent version, you can manually set one of the [https://search.nixos.org/packages?query=postgresql_ available Nixpkgs Postgresql versions]: | |||
<syntaxhighlight lang="nix"> | <syntaxhighlight lang="nix"> | ||
| Line 124: | Line 126: | ||
</syntaxhighlight> | </syntaxhighlight> | ||
Note that changing the package version does not trigger any automatic migrations of your existing databases | Note that changing the package version does not trigger any automatic migrations of your existing databases — follow [[#Major upgrades]] to migrate existing databases. | ||
=== Security === | === Security === | ||
| Line 298: | Line 298: | ||
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: | 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> | <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. | ||
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> | |||
== See also == | == See also == | ||