PostgreSQL: Difference between revisions

(11 intermediate revisions by 9 users not shown)

Line 1:

~~{{expansion}}~~

[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 ===

To try out Postgresql add the following minimal example to your [~~https://nixos.wiki/wiki/NixOS_modules~~ NixOS module]:

To try out Postgresql add the following minimal example to your [[NixOS modules | NixOS module]]:

Line 19:

Line 21:

</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/~~lib~~/postgresql" (TCP/IP is disabled by default because it's less performant and less secure).

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]

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 ===

Line 90:

Line 93:

# port = 5432;

authentication = pkgs.lib.mkOverride 10 ''

~~#...~~

#type database DBuser origin-address auth-method

local all all trust

# ... other auth rules ...

# ipv4

host all all 127.0.0.1/32 trust

# ipv6

host all all ::1/128 trust

'';

initialScript = pkgs.writeText "backend-initScript" ''

Line 109:

Line 114:

=== 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 ~~it manually~~:

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]:

Line 119:

Line 126:

</syntaxhighlight>

Note that changing the package version does not trigger any automatic migrations of your existing databases~~: if you update Postgres you should be ready~~ to migrate ~~any~~ existing databases ~~manually.~~

Note that changing the package version does not trigger any automatic migrations of your existing databases — follow [[#Major upgrades]] to migrate existing databases.

* [https://search.nixos.~~org/packages?query=postgresql_ Available Nixpkgs Postgresql versions]~~

=== Security ===

Line 175:

Line 180:

With "sameuser" Postgres will allow DB user access only to databases of the same name. E.g. DB user "mydatabase" will get access to database "mydatabase" and nothing else. The part <code>map=superuser_map</code> is optional.

One exception is the DB user "postgres", which by default is a superuser/admin with access to everything.

=== Monitoring ===

A [[Prometheus]] exporter is available to export metrics to Prometheus-compatible storage.<syntaxhighlight lang="nix">

services.prometheus.exporters.postgres = {

enable = true;

listenAddress = "0.0.0.0";

port = 9187;

};

</syntaxhighlight>[https://search.nixos.org/options?show=services.prometheus.exporters.postgres.dataSourceName&from=0&size=50&sort=relevance&type=packages&query=services.prometheus.exporters.postgres See all available options for services.prometheus.exporters.postgres]

== TLS ==

Line 254:

Line 268:

root$ psql -U postgres

psql: error: connection to server on socket "/run/postgresql/.s.PGSQL.5432" failed: FATAL: Peer authentication failed for user "~~postgress~~"

psql: error: connection to server on socket "/run/postgresql/.s.PGSQL.5432" failed: FATAL: Peer authentication failed for user "postgres"

</syntaxhighlight>

Line 277:

Line 291:

outline=# ALTER DATABASE outline REFRESH COLLATION VERSION;

</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>The [https://nixos.org/manual/nixos/stable/#module-postgresql NixOS manual] also has more detailed information about major upgrades.

== See also ==

Line 284:

Line 307:

[[Category:Applications]]

[[Category:Database]]

[[Category:NixOS Manual]]

@@ Line 1: / Line 1: @@
-{{expansion}}
+[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 ===
-To try out Postgresql add the following minimal example to your [https://nixos.wiki/wiki/NixOS_modules NixOS module]:
+To try out Postgresql add the following minimal example to your [[NixOS modules | NixOS module]]:
 <syntaxhighlight lang="nix">
@@ Line 19: / Line 21: @@
 </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/lib/postgresql" (TCP/IP is disabled by default because it's less performant and less secure).
+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]
+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 ===
@@ Line 90: / Line 93: @@
    # port = 5432;
    authentication = pkgs.lib.mkOverride 10 ''
-    #...
      #type database DBuser origin-address auth-method
+    local all      all     trust
+    # ... other auth rules ...
      # ipv4
      host  all      all     127.0.0.1/32   trust
      # ipv6
-     host all       all     ::1/128        trust
+     host  all      all     ::1/128        trust
    '';
    initialScript = pkgs.writeText "backend-initScript" ''
@@ Line 109: / Line 114: @@
 === 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 it manually:
+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">
@@ Line 119: / Line 126: @@
 </syntaxhighlight>
-Note that changing the package version does not trigger any automatic migrations of your existing databases: if you update Postgres you should be ready to migrate any existing databases manually.
+Note that changing the package version does not trigger any automatic migrations of your existing databases — follow [[#Major upgrades]] to migrate existing databases.
-* [https://search.nixos.org/packages?query=postgresql_ Available Nixpkgs Postgresql versions]
 === Security ===
@@ Line 175: / Line 180: @@
 With "sameuser"  Postgres will allow DB user access only to databases of the same name. E.g. DB user "mydatabase" will get access to database "mydatabase" and nothing else. The part <code>map=superuser_map</code> is optional.
 One exception is the DB user "postgres", which by default is a superuser/admin with access to everything.
+=== Monitoring ===
+A [[Prometheus]] exporter is available to export metrics to Prometheus-compatible storage.<syntaxhighlight lang="nix">
+services.prometheus.exporters.postgres = {
+    enable = true;
+    listenAddress = "0.0.0.0";
+    port = 9187;
+};
+</syntaxhighlight>[https://search.nixos.org/options?show=services.prometheus.exporters.postgres.dataSourceName&from=0&size=50&sort=relevance&type=packages&query=services.prometheus.exporters.postgres See all available options for services.prometheus.exporters.postgres]
 == TLS ==
@@ Line 254: / Line 268: @@
 <syntaxhighlight lang="nix">
 root$ psql -U postgres
-psql: error: connection to server on socket "/run/postgresql/.s.PGSQL.5432" failed: FATAL:  Peer authentication failed for user "postgress"
+psql: error: connection to server on socket "/run/postgresql/.s.PGSQL.5432" failed: FATAL:  Peer authentication failed for user "postgres"
 </syntaxhighlight>
@@ Line 277: / Line 291: @@
 outline=# ALTER DATABASE outline REFRESH COLLATION VERSION;
 </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>The [https://nixos.org/manual/nixos/stable/#module-postgresql NixOS manual] also has more detailed information about major upgrades.
 == See also ==
@@ Line 284: / Line 307: @@
 [[Category:Applications]]
 [[Category:Database]]
+[[Category:NixOS Manual]]