PostgreSQL: Difference between revisions

← Older edit Newer edit →

VisualWikitext

@@ Line 1: / Line 1: @@
 {{expansion}}
+=== Getting started ===
+To try out Postgresql add the following minimal example to your [https://nixos.wiki/wiki/NixOS_modules NixOS module]:
+<syntaxhighlight lang="nix">
+{
+  # ...
+  config.services.postgresql = {
+    enable = true;
+    ensureDatabases = [ "mydatabase" ];
+    authentication = pkgs.lib.mkOverride 10 ''
+      #type database  DBuser  auth-method
+      local  all             all           trust
+    '';
+  };
+}
+</syntaxhighlight>
+This will setup Postgresql with a database "mydatabase" and let every local Linux user have access to it without a password through a "local" Unix socket "/var/lib/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]
+=== Allow TCP/IP connections ===
+This example shows how to roll out a database with a default user and password:
+<syntaxhighlight lang="nix">
+services.postgresql = {
+  enable = true;
+  ensureDatabases = [ "mydatabase" ];
+  enableTCPIP = true;
+  # port = 5432;
+  authentication = pkgs.lib.mkOverride 10 ''
+    #...
+    #type database  DBuser  origin-address auth-method
+    # ipv4
+    host   all             all          127.0.0.1/32    trust
+    # ipv4
+    host   all             all           ::1/128            trust
+  '';
+  initialScript = pkgs.writeText "backend-initScript" ''
+    CREATE ROLE nixcloud WITH LOGIN PASSWORD 'nixcloud' CREATEDB;
+    CREATE DATABASE nixcloud;
+    GRANT ALL PRIVILEGES ON DATABASE nixcloud TO nixcloud;
+  '';
+};
+</syntaxhighlight>
+This will allow "host" based authentification only from other webservices on the same computer ("127.0.0.1"), although any user will have access to any database.
+=== Set the Postgresql versions ===
+By default, NixOS uses whatever Postgres version that comes with the  [https://search.nixos.org/packages?query=postgresql "pkgs.postgresql" package]. To avoid sudden breaking changes you can fix the Postgres version by using a more specific Nix package:
+<syntaxhighlight lang="nix">
+services.postgresql = {
+  enable = true;
+  package = pkgs.postgresql_15;
+  # ...
+};
+</syntaxhighlight>
+* [https://search.nixos.org/packages?query=postgresql_ Available Nixpkgs Postgresql versions]
 == Using <code>psql</code> ==
@@ Line 41: / Line 105: @@
 </syntaxhighlight>
-=== Deploying databases ===
-This example shows how to roll out a database with a default user and password:
-<pre>
-  services.postgresql = {
-    enable = true;
-    package = pkgs.postgresql_10;
-    enableTCPIP = true;
-    authentication = pkgs.lib.mkOverride 10 ''
-      local all all trust
-      host all all 127.0.0.1/32 trust
-      host all all ::1/128 trust
-    '';
-    initialScript = pkgs.writeText "backend-initScript" ''
-      CREATE ROLE nixcloud WITH LOGIN PASSWORD 'nixcloud' CREATEDB;
-      CREATE DATABASE nixcloud;
-      GRANT ALL PRIVILEGES ON DATABASE nixcloud TO nixcloud;
-    '';
-  };
-</pre>
 To debug the SQL statements futher, one can use '''systemctl cat postgresql''' and see the '''ExecStartPost=/nix/store/rnv1v95bbf2lsy9ncwg7jdwj2s71sqra-unit-script/bin/postgresql-post-start''' line. Then open it with `cat` on the shell and see the '''psql''' command.
@@ Line 78: / Line 122: @@
                                                                  ^
 </pre>
+== See also ==
+* [https://search.nixos.org/options?query=services.postgresql Available NixOS service options]
+[[Category:Applications]]