NixOS paperless with postgres

Help
1

Hi, I’m new to Nixos and I am trying to set up paperless with postgres, but I can’t get paperless to connect to the database.
I’ve tried to setup the postgres to listen on TCP/IP so paperless can connect from localhost, but unfortunatelly the connection is refused.

This is my postgres.nix

services.postgresql = {
    enable = true;
    package = pkgs.postgresql_16;
    dataDir = "${homeDir}/data";
    ensureDatabases = [ "paperless" "nextcloud" ];
    ensureUsers = [
      {
        name = "paperless";
        ensureDBOwnership = true;
      }
      {
        name = "nextcloud";
        ensureDBOwnership = true;
      }
    ];
    enableTCPIP = true;
    authentication = pkgs.lib.mkForce ''
      # TYPE  DATABASE        USER            ADDRESS                 METHOD
      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 paperless WITH LOGIN PASSWORD 'paperless' CREATEDB;
      CREATE DATABASE paperless;
      GRANT ALL PRIVILEGES ON DATABASE paperless TO paperless;
    '';
    settings = {
      listen_addresses = pkgs.lib.mkForce "*";
    };
  };

and this is the paperless config:

  services.paperless = {
    enable = true;
    address = "192.168.0.2";
    port = 58080;
    mediaDir = "${homeDir}/media";
    dataDir = "${homeDir}/data";
    user = "${user}";
    passwordFile = "${homeDir}/data/password";
    consumptionDir = "${homeDir}/import";
    extraConfig = {
      PAPERLESS_DBHOST = "localhost";
      PAPERLESS_DBNAME = "paperless";
      PAPERLESS_DBUSER = "paperless";
      PAPERLESS_DBPASS = "paperless";
      PAPERLESS_TRASH_DIR = "${homeDir}/media/trash";
      PAPERLESS_OCR_LANGUAGE = "deu+eng+pol";
      PAPERLESS_TIME_ZONE = "Europe/Berlin";
      PAPERLESS_CONSUMER_RECURSIVE = true;
      PAPERLESS_CONSUMER_SUBDIRS_AS_TAGS = true;
      PAPERLESS_CONSUMER_ENABLE_BARCODES = true;
    };
  };

The paperless-scheduler service tries to connect but ends with an exception:

Jun 22 20:40:31 nixos paperless-scheduler-pre-start[146239]: psycopg2.OperationalError: connection to server at “localhost” (::1), port 5432 failed: Connection refused
Jun 22 20:40:31 nixos paperless-scheduler-pre-start[146239]: Is the server running on that host and accepting TCP/IP connections?
Jun 22 20:40:31 nixos paperless-scheduler-pre-start[146239]: connection to server at “localhost” (127.0.0.1), port 5432 failed: Connection refused
Jun 22 20:40:31 nixos paperless-scheduler-pre-start[146239]: Is the server running on that host and accepting TCP/IP connections?

But I clearly can see, it’s listening on this port:

$netstat -tulpen
tcp        0      0 0.0.0.0:5432            0.0.0.0:*               LISTEN      71         749012     145847/postgres
tcp6       0      0 :::5432                 :::*                    LISTEN      71         749013     145847/postgres

and the connection from cli works too:

psql -d paperless -U paperless -h 127.0.0.1   
psql (16.2)
Type "help" for help.

paperless=>

Does anyone has an Idea what is causing this issue?

@Edit
this happens with other services too, so it seems that the postgres configuration here is invalid

Thank you

1 Like
2

I just set up Paperless on Postgres a couple of days ago using a Unix socket instead of localhost TCP. It’s a pretty simple config so it might be worth looking at if using a socket is appropriate for your environment.

  services.postgresql = {
    enable = true;
    package = pkgs.postgresql_16;
    ensureDatabases = [ "paperless" ];
    ensureUsers = [
      {
        name = "paperless";
        ensureDBOwnership = true;
      }
    ];
  };

  services.paperless = {
    enable = true;
    # ...
    settings = {
      PAPERLESS_DBHOST = "/run/postgresql";
     # ...
    };
  }
2 Likes