PostgreSQL: Difference between revisions

(20 intermediate revisions by 16 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].

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

Line 103:

# 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 124:

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

</syntaxhighlight>

Note that changing the package version does not trigger ~~and~~ 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 190:

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]

== Remote Access ==

=== TLS ===

To turn TLS on in recent versions of postgres it's pretty easy. Their [https://www.postgresql.org/docs/current/ssl-tcp.html docs] are pretty good.

Create a simple cert just to make it work. If you are doing this in production, you need to provide your own server.crt and server.key in the main PGDATA dir (~postgres).

In a shell:

cd ~postgres

sudo -u postgres openssl req -new -x509 -days 365 -nodes -text -out server.crt -keyout server.key -subj "/CN=dbhost.yourdomain.com"

chmod og-rwx server.key

</syntaxhighlight>

Then in your nix configuration:

<syntaxhighlight lang="nix">services.postgresql = {

enable = true;

package = pkgs.postgresql_16;

enableTCPIP = true;

ensureDatabases = [ "tootieapp" ];

settings = {

ssl = true;

};

authentication = pkgs.lib.mkOverride 10 ''

#type database DBuser auth-method

local all all trust

host sameuser all 127.0.0.1/32 scram-sha-256

host sameuser all ::1/128 scram-sha-256

'';

};</syntaxhighlight>

the `sameuser` mentioned in the authentication section requires the database name be the same as the username, which you may not want, you can change that to `all` to allow an authenticated user the ability to connect to any database.

`scram-sha-256` is the require a password option, but you can authenticate a variety of different ways, see the official docs for other options as part of pg_hba.conf.

=== User creation ===

NixOS can declaratively create or manage Postgres users with passwords.

Postgres allows specifying the password as plaintext, MD5 hash or SCRAM-SHA-256 hash. For declarative configuration, the recommended format is SCRAM-SHA-256 and a hash can be obtained from a valid password for example using the following script:<syntaxhighlight lang="python3" line="1" start="1">

#!/usr/bin/env nix-shell

#! nix-shell -i python3 -p "python3.withPackages (ps: [ps.scramp])"

import scramp, base64

PASSWORD = 'secure_password123!'

m = scramp.ScramMechanism()

salt, stored_key, server_key, iteration_count = m.make_auth_info(PASSWORD)

print(f"SCRAM-SHA-256${iteration_count}:{base64.b64encode(salt).decode()}${base64.b64encode(stored_key).decode()}:{base64.b64encode(server_key).decode()}")

</syntaxhighlight>Using this hash, we can then extend the above configuration as follows:<syntaxhighlight lang="nix">services.postgresql = {

enable = true;

package = pkgs.postgresql_16;

enableTCPIP = true;

ensureDatabases = [ "tootieapp" ];

settings = {

ssl = true;

};

authentication = pkgs.lib.mkOverride 10 ''

#type database DBuser auth-method

local all all trust

host sameuser all 127.0.0.1/32 scram-sha-256

host sameuser all ::1/128 scram-sha-256

'';

ensureUsers = [

{

name = "tootieapp";

ensureDBOwnership = true;

ensureClauses = {

login = true;

password = "SCRAM-SHA-256$4096:lB4tguN+gvNVSqk0zGRPHQ==$zh48o1bb9tuRjvGQHh/CeobEyUI4u91rp0K9who8m3I=:mHxc6obGad8/g65+V3C84UQGHIK41Gfx32+xXSZiOss=";

};

}

];

};</syntaxhighlight>For more details on how to create hashes from valid passwords also refer to https://gist.github.com/jkatz/e0a1f52f66fa03b732945f6eb94d9c21

== Debugging with <code>psql</code> ==

Line 194:

Line 293:

</pre>

== ~~FAQ~~ ==

== Troubleshooting ==

=== Connection rejected with "Role does not exist" ===

Line 209:

Line 308:

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>

You are trying to login as a DB user ("postgres" in this example) for which your current system user ("root" in this example) has no permission to switch to. Check your "user name map" in the <code>identMap</code> section.

=== WARNING: database "XXX" has a collation version mismatch ===

The complete error which appears in the system log might look similar to this

WARNING: database "outline" has a collation version mismatch

DETAIL: The database was created using collation version 2.35, but the operating system provides version 2.38.

HINT: Rebuild all objects in this database that use the default collation and run ALTER DATABASE outline REFRESH COLLATION VERSION, or build PostgreSQL with the right library version.

</syntaxhighlight>

To fix it, run following commands in the psql console. Replace the database name <code>outline</code> with the name of the database which you want to migrate

sudo -u postgres psql

postgres=# \c outline;

outline=# REINDEX DATABASE outline;

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>

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

Line 365:

[[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].
+====== 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 90: / Line 103: @@
    # 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 124: @@
 === 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 136: @@
 </syntaxhighlight>
-Note that changing the package version does not trigger and 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 190: @@
 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]
+== Remote Access ==
+=== TLS ===
+To turn TLS on in recent versions of postgres it's pretty easy. Their [https://www.postgresql.org/docs/current/ssl-tcp.html docs] are pretty good.
+Create a simple cert just to make it work. If you are doing this in production, you need to provide your own server.crt and server.key in the main PGDATA dir (~postgres).
+In a shell:
+<syntaxhighlight lang="nix">
+cd ~postgres
+sudo -u postgres openssl req -new -x509 -days 365 -nodes -text -out server.crt  -keyout server.key -subj "/CN=dbhost.yourdomain.com"
+chmod og-rwx server.key
+</syntaxhighlight>
+Then in your nix configuration:
+<syntaxhighlight lang="nix">services.postgresql = {
+    enable = true;
+    package = pkgs.postgresql_16;
+    enableTCPIP = true;
+    ensureDatabases = [ "tootieapp" ];
+    settings = {
+        ssl = true;
+    };
+    authentication = pkgs.lib.mkOverride 10 ''
+      #type database  DBuser  auth-method
+      local all       all     trust
+      host  sameuser    all     127.0.0.1/32 scram-sha-256
+      host  sameuser    all     ::1/128 scram-sha-256
+    '';
+  };</syntaxhighlight>
+the `sameuser` mentioned in the authentication section requires the database name be the same as the username, which you may not want, you can change that to `all` to allow an authenticated user the ability to connect to any database.
+`scram-sha-256` is the require a password option, but you can authenticate a variety of different ways, see the official docs for other options as part of pg_hba.conf.
+=== User creation ===
+NixOS can declaratively create or manage Postgres users with passwords.
+Postgres allows specifying the password as plaintext, MD5 hash or SCRAM-SHA-256 hash. For declarative configuration, the recommended format is SCRAM-SHA-256 and a hash can be obtained from a valid password for example using the following script:<syntaxhighlight lang="python3" line="1" start="1">
+#!/usr/bin/env nix-shell
+#! nix-shell -i python3 -p "python3.withPackages (ps: [ps.scramp])"
+import scramp, base64
+PASSWORD = 'secure_password123!'
+m = scramp.ScramMechanism()
+salt, stored_key, server_key, iteration_count = m.make_auth_info(PASSWORD)
+print(f"SCRAM-SHA-256${iteration_count}:{base64.b64encode(salt).decode()}${base64.b64encode(stored_key).decode()}:{base64.b64encode(server_key).decode()}")
+</syntaxhighlight>Using this hash, we can then extend the above configuration as follows:<syntaxhighlight lang="nix">services.postgresql = {
+  enable = true;
+  package = pkgs.postgresql_16;
+  enableTCPIP = true;
+  ensureDatabases = [ "tootieapp" ];
+  settings = {
+      ssl = true;
+  };
+  authentication = pkgs.lib.mkOverride 10 ''
+    #type database  DBuser  auth-method
+    local all       all     trust
+    host  sameuser    all     127.0.0.1/32 scram-sha-256
+    host  sameuser    all     ::1/128 scram-sha-256
+  '';
+  ensureUsers = [
+    {
+      name = "tootieapp";
+      ensureDBOwnership = true;
+      ensureClauses = {
+        login = true;
+        password = "SCRAM-SHA-256$4096:lB4tguN+gvNVSqk0zGRPHQ==$zh48o1bb9tuRjvGQHh/CeobEyUI4u91rp0K9who8m3I=:mHxc6obGad8/g65+V3C84UQGHIK41Gfx32+xXSZiOss=";
+      };
+    }
+  ];
+};</syntaxhighlight>For more details on how to create hashes from valid passwords also refer to https://gist.github.com/jkatz/e0a1f52f66fa03b732945f6eb94d9c21
 == Debugging with <code>psql</code> ==
@@ Line 194: / Line 293: @@
 </pre>
-== FAQ ==
+== Troubleshooting ==
 === Connection rejected with "Role does not exist" ===
@@ Line 209: / Line 308: @@
 <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>
 You are trying to login as a DB user ("postgres" in this example) for which your current system user ("root" in this example) has no permission to switch to. Check your "user name map" in the <code>identMap</code> section.
+=== WARNING: database "XXX" has a collation version mismatch ===
+The complete error which appears in the system log might look similar to this
+<syntaxhighlight>
+WARNING:  database "outline" has a collation version mismatch
+DETAIL:  The database was created using collation version 2.35, but the operating system provides version 2.38.
+HINT:  Rebuild all objects in this database that use the default collation and run ALTER DATABASE outline REFRESH COLLATION VERSION, or build PostgreSQL with the right library version.
+</syntaxhighlight>
+To fix it, run following commands in the psql console. Replace the database name <code>outline</code> with the name of the database which you want to migrate
+<syntaxhighlight>
+sudo -u postgres psql
+postgres=# \c outline;
+outline=# REINDEX DATABASE outline;
+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>
+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 220: / Line 365: @@
 [[Category:Applications]]
+[[Category:Database]]
+[[Category:NixOS Manual]]