NixOS Wiki

PostgreSQL: Difference between revisions

← Older edit
Visual
imported>Onny
No edit summary
 
(12 intermediate revisions by 10 users not shown)
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 ===
=== 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">
<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/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]
* [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 90: Line 93:
   # port = 5432;
   # port = 5432;
   authentication = pkgs.lib.mkOverride 10 ''
   authentication = pkgs.lib.mkOverride 10 ''
    #...
     #type database DBuser origin-address auth-method
     #type database DBuser origin-address auth-method
    local all      all    trust
    # ... other auth rules ...
     # ipv4
     # ipv4
     host  all      all    127.0.0.1/32  trust
     host  all      all    127.0.0.1/32  trust
     # ipv6
     # ipv6
     host all       all    ::1/128        trust
     host all     all    ::1/128        trust
   '';
   '';
   initialScript = pkgs.writeText "backend-initScript" ''
   initialScript = pkgs.writeText "backend-initScript" ''
Line 109: 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 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">
<syntaxhighlight lang="nix">
Line 119: Line 126:
</syntaxhighlight>
</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 ===
=== 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.
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.
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 ==
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 and permissions are best described in the PG manual under `CREATE ROLE` and `GRANT`
for example:
<syntaxhighlight lang="sql">
CREATE USER tootieapp WITH PASSWORD 'BIGLONGRANDOMSTRINGHERE';
GRANT ALL PRIVILEGES ON DATABASE tootieapp TO tootieapp;
</syntaxhighlight>


== Debugging with <code>psql</code> ==
== Debugging with <code>psql</code> ==
Line 209: Line 268:
<syntaxhighlight lang="nix">
<syntaxhighlight lang="nix">
root$ psql -U postgres
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>
</syntaxhighlight>


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


Line 239: Line 307:
[[Category:Applications]]
[[Category:Applications]]
[[Category:Database]]
[[Category:Database]]
[[Category:NixOS Manual]]
Retrieved from "https://wiki.nixos.org/wiki/PostgreSQL"