PostgreSQL: Difference between revisions
imported>Zie m Add a little about enabling TLS. |
Add upgrade instructions |
||
| (5 intermediate revisions by 4 users not shown) | |||
| Line 1: | Line 1: | ||
[https://www.postgresql.org/ PostgreSQL] also known as Postgres, is a free and open-source relational database management system (RDBMS) emphasizing extensibility and SQL compliance. | |||
This article extends the documentation in the [https://nixos.org/manual/nixos/stable/#module-postgresql NixOS manual]. | |||
=== Getting started === | === Getting started === | ||
To try out Postgresql add the following minimal example to your [ | To try out Postgresql add the following minimal example to your [[NixOS modules | NixOS module]]: | ||
<syntaxhighlight lang="nix"> | <syntaxhighlight lang="nix"> | ||
| Line 19: | Line 21: | ||
</syntaxhighlight> | </syntaxhighlight> | ||
This will setup Postgresql with a default DB superuser/admin "postgres", a database "mydatabase" and let every DB user have access to it without a password through a "local" Unix socket "/var/ | This will setup Postgresql with a default DB superuser/admin "postgres", a database "mydatabase" and let every DB user have access to it without a password through a "local" Unix socket "/var/run/postgresql" (TCP/IP is disabled by default because it's less performant and less secure). | ||
* [https://search.nixos.org/options?query=services.postgresql Available NixOS Postgresql service options] | * [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]. | |||
=== Verify setup === | === Verify setup === | ||
| Line 277: | Line 280: | ||
outline=# ALTER DATABASE outline REFRESH COLLATION VERSION; | outline=# ALTER DATABASE outline REFRESH COLLATION VERSION; | ||
</syntaxhighlight> | </syntaxhighlight> | ||
== Major upgrades == | |||
If you're using NixOS' modules for PostgreSQL and find yourself in a boot/switch after a major bump of it, you'll need to upgrade your cluster. | |||
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> | |||
== See also == | == See also == | ||
| Line 284: | Line 298: | ||
[[Category:Applications]] | [[Category:Applications]] | ||
[[Category:Database]] | [[Category:Database]] | ||
[[Category:NixOS Manual]] | |||