Nixpkgs: postgres initdb doesn't work from command line on Nixos

[nh2]

Issue description

Calling initdb doesn't seem to work from the command line:

# sudo -u postgres initdb -D /tmp/testpostgres2
The files belonging to this database system will be owned by user "postgres".
This user must also own the server process.

initdb: file "/run/current-system/sw/share/postgres.bki" does not exist
This might mean you have a corrupted installation or identified
the wrong directory with the invocation option -L.

But using the full path of the symlink /run/current-system/sw/bin/initdb -> /nix/store/fd05p8jff2avl2jjd505hhllj6n88rad-postgresql-9.5.5/bin/initdb directly, it works:

# sudo -u postgres /nix/store/fd05p8jff2avl2jjd505hhllj6n88rad-postgresql-9.5.5/bin/initdb -D /tmp/testpostgres1
The files belonging to this database system will be owned by user "postgres".
This user must also own the server process.

The database cluster will be initialized with locale "en_US.UTF-8".
The default database encoding has accordingly been set to "UTF8".
The default text search configuration will be set to "english".

Data page checksums are disabled.

creating directory /tmp/testpostgres1 ... ok
creating subdirectories ... ok
...
syncing data to disk ... ok

WARNING: enabling "trust" authentication for local connections
You can change this by editing pg_hba.conf or using the option -A, or
--auth-local and --auth-host, the next time you run initdb.

Success. You can now start the database server using:

    /nix/store/fd05p8jff2avl2jjd505hhllj6n88rad-postgresql-9.5.5/bin/pg_ctl -D /tmp/testpostgres1 -l logfile start

I think it's because which initdb resolves to /run/current-system/sw/bin/initdb and it tries to find postgres.bki by doing ../../share/postgres.bki to argv[0].

Technical details

• Nixpkgs version: 40de598c600dcfce532802066a50f7aaac72b236

[copumpkin]

mutters something about environment.systemPackages being the devil

does it work inside nix-shell?

[nh2]

@copumpkin I'm not sure what to try exactly here, this is a nixops deployed machine.

[rvl]

Not sure if this helps you much, but this seems to work with postgresql 9.6.

[brainrape]

Since it works for 9.6, can we close this issue?

[nh2]

This is not fixed with 9.6; I have just tested it with initdb (PostgreSQL) 9.6.8 on 18.03 and the error exists exactly as before.

@rvl How did you run your commands to see it working?

[rvl]

Hi @nh2, Actually, with further investigation I find that this is due to the environment.pathsToLink setting being different on the two systems that I tested.

Workaround 1: sudo -u postgres nix-shell -p postgresql --run 'initdb -D /tmp/testpostgres2
Workaround 2: add environment.pathsToLink = [ "/share" ]; to the nixos configuration.

[nh2]

Hmm interesting.

So is there some change to the postgresql package that we could make to tell it the equivalent of what pathsToLink does, so that just running initdb works out of the box, as other commands in systemPackages do?

[danbst]

Made a test for this:

  pkgs = import <nixpkgs> { };
in
with import <nixpkgs/nixos/lib/testing.nix> { inherit pkgs; system = builtins.currentSystem; };
with pkgs.lib;

makeTest {
    name = "pg-initdb";

    machine = {...}:
      {
        documentation.enable = false;
        services.postgresql.enable = true;
        services.postgresql.package = pkgs.postgresql_9_6;

        # environment.pathsToLink = [
        #  "/share/postgresql"
        # ];
      };

    testScript = ''
      $machine->start;
      $machine->succeed("sudo -u postgres initdb -D /tmp/testpostgres2");
      $machine->shutdown;
    '';

  }

After https://github.com/NixOS/nixpkgs/pull/62271 it is now possible to link only PG dir (and not whole /share). However I don't quite understand why isn't /share linked by default.

When postgresql is installed through nix-env, all works because /share is at correct place.

I'll prepare PR