Postgres Service doesn't Run

[benkio]

Hi, I just added in my darwin-configuration.nix the basic configuration about postgres service:

services = {
    postgresql = {
      enable = true;
      package = pkgs.postgresql;
      dataDir = "/var/lib/postgresql";
    };
};

But I can't see any folder in /var/lib/postgresql and the server isn't running in background. Here a full pastebin of the commands as they are right now. https://pastebin.com/fteqZ77g

[vmsp]

I have the same issue. You can see what's going on by taking a look at postgres' logs:

launchd.user.agents.postgresql.serviceConfig = {
  StandardErrorPath = "/Users/vitor/postgres.error.log";
  StandardOutPath = "/Users/vitor/postgres.log";
};

Postgres is trying to write to /var/lib/postgresql -- the default dataDir -- but doesn't have permission to do so.

I created /usr/local/var/postgres and made my user own it.

$ sudo mkdir -p /usr/local/var/postgres
$ sudo chown -R vitor:staff /usr/local/var

Then I configured postgres to use this directory:

postgresql = {
  enable = true;
  dataDir = "/usr/local/var/postgres";
  package = pkgs.postgresql_13;
};

[benkio]

I just added a home-manager activation for creating the postgres data directory and the related reference in nix config, everything seems to work. I close this issue then.

• https://github.com/benkio/nix-config/blob/master/MacOs/home/home.nix#L40-L44
• https://github.com/benkio/nix-config/blob/master/MacOs/system/darwin-configuration.nix#L12-L16

[vmsp]

I'd suggest keeping the issue open. We solved it but the default behavior is broken.

[cbzehner]

For what it's worth, I compiled @vmsp & @benkio's suggestions together into a postgres.nix file that's working nicely. It depends on having both nix-darwin and home-manager available with home-manager.users.<user>.xdg.enable = true; but works with just nix-darwin if you don't mind outputting the logs to your user directory.

# ❯ cat .nixpkgs/services/postgres.nix
{ config, pkgs, ... }:

{
  services = {
    postgresql = {
      enable = true;
      package = pkgs.postgresql;
      # dataDir = /. + "/var/lib/postgresql/"; # Default value
    };
  };

  # Create the PostgreSQL data directory, if it does not exist.
  system.activationScripts.preActivation = {
    enable = true;
    text = ''
      if [ ! -d "/var/lib/postgresql/" ]; then
        echo "creating PostgreSQL data directory..."
        sudo mkdir -m 775 -p /var/lib/postgresql/
        chown -R cbzehner:staff /var/lib/postgresql/
      fi
    '';
  };

  # Direct log output to $XDG_DATA_HOME/postgresql for debugging.
  launchd.user.agents.postgresql.serviceConfig = {
    # Un-comment these values instead to avoid a home-manager dependency.
    # StandardErrorPath = "/Users/cbzehner/postgres.error.log";
    # StandardOutPath = "/Users/cbzehner/postgres.out.log";
    StandardErrorPath = "/Users/cbzehner/.local/share/postgresql/postgres.error.log";
    StandardOutPath = "/Users/cbzehner/.local/share/postgresql/postgres.out.log";
  };

  home-manager.users = {
    cbzehner = {
      # Create the directory ~/.local/share/postgresql/
      xdg.dataFile."postgresql/.keep".text = "";
    };
  };
}

[dbaynard]

I'd like to set UserName in the serviceConfig but according to the documentation, that only works if the service is run by root.

Am I right in thinking that the postgres service is run as the logged in user?

(The idea is I have a dedicated postgres user which owns the /var/lib/postgresql directory and then runs postgres, but at the moment I get a launch error that the server must be started by the user that owns the data directory, and if I call postgresql-start as the correct user it works.)

[dminuoso]

As of 511177ffe8226c78c9cf6a92a7b5f2df3684956b the postgresql module appears very unfinished and broken.

Here is a list of my findings:

1. ensureDatabases, ensureUsers and initialScript are available, documented but at evaluation only produce a warning. The documentation suggests this should work.
2. Data directory is not managed at all. This should become a mandatory option with a documentation hint that the user must create it with the correct permissions.
3. The bootstrap superuser is pinned to a readonly postgres user, but no such user is actually created via users.users, meaning you are left with an unusable database cluster (because you cannot bootstrap any roles).
4. postgresl uses per-user LaunchAgents, I feel system-wide LaunchDaemon would make more sense. Then we could just create a system-wide users.users.postgres, with createHome, and the postgresql data directory would just live in its home directory. That would address points 2 and 3 above.

[fesplugas]

With latest nix-darwin postgresql service will fail because database cannot be created. Bug was introduced about a year ago by https://github.com/LnL7/nix-darwin/commit/c9fcec4b6befa1dce991b71be63550eb17b8fc9b.

Until the bug is not fixed you can create the database in the activationScript section:

  system.activationScripts.preActivation = {
    enable = true;
    text = ''
      if [ ! -d "/var/lib/postgresql/" ]; then
        echo "creating PostgreSQL data directory..."
        sudo mkdir -m 750 -p /var/lib/postgresql/
        chown -R fesplugas:staff /var/lib/postgresql/
      fi
    '';
  };

  services.postgresql.initdbArgs = ["-U fesplugas" "--pgdata=/var/lib/postgresql/15" "--auth=trust" "--no-locale" "--encoding=UTF8"];
  services.postgresql.package = pkgs.postgresql_15;
  services.postgresql.enable = true;

  launchd.user.agents.postgresql.serviceConfig = {
    StandardErrorPath = "/tmp/postgres.error.log";
    StandardOutPath = "/tmp/postgres.log";
  };

A PR https://github.com/LnL7/nix-darwin/pull/794 has been created.

A few considerations:

• launchd.user.agents.postgresql will run process as fesplugas that's why you need to change the folder permissions
• As fesplugas owns the process, it's better to initialise the database with -U fesplugas

[virusdave]

As of 511177f the postgresql module appears very unfinished and broken.

Here is a list of my findings:

1. ensureDatabases, ensureUsers and initialScript are available, documented but at evaluation only produce a warning. The documentation suggests this should work.
2. Data directory is not managed at all. This should become a mandatory option with a documentation hint that the user must create it with the correct permissions.
3. The bootstrap superuser is pinned to a readonly postgres user, but no such user is actually created via users.users, meaning you are left with an unusable database cluster (because you cannot bootstrap any roles).
4. postgresl uses per-user LaunchAgents, I feel system-wide LaunchDaemon would make more sense. Then we could just create a system-wide users.users.postgres, with createHome, and the postgresql data directory would just live in its home directory. That would address points 2 and 3 above.

Has anyone taken a stab at this at all? These seem like sensible steps to take to make this not-terrible and even almost usable!