How to use postgresql in nixos

PierreolivierGendraud
December 9, 2022
262 views
1 vote
2 Answers

I’ve installed PostgreSQL:

  nix-env -iA nixos.postgresql

Now when I use psql, I get:

psql: error: connection to server on socket "/run/postgresql/.s.PGSQL.5432" failed: No such file or directory
    Is the server running locally and accepting connections on that socket?

Must I add something in /etc/nixos/configuration.nix?

update

I’ve tried to do like this link (i.e., to install it in nix-shell and not in nix-env), but I still get the same error:

$ nix-shell --pure shell.nix
done
server started

$ psql
psql: error: could not connect to server: could not connect to server: No such file or directory
    Is the server running locally and accepting
    connections on Unix domain socket "/mnt/c/Users/Pierre-Olivier/nix/psql/.pg/.s.PGSQL.5432"?

Answers

- DinkoPehar
- January 2, 2023 at 9:03 pm
- 0 votes
0
psql is the PostgreSQL interactive terminal.

What you are trying to do is connect to database without PostgreSQL running in first place.

Follow these steps:
1. Initialize the database with initdb -D .data
2. Start a PostgreSQL server with pg_ctl -D .data -l logfile start
3. Make sure it’s running pg_ctl -D .data status
4. Connect to database with psql -d postgres (by default, postgres database is created)
At the end, make sure to stop database with pg_ctl -D .data stop when leaving the nix shell.
Login or Signup to reply.

1. `nix-env -iA nixos.postgresql`

psql won’t connect after nix-env because it just puts the PostgreSQL package into the /nix/store, but doesn’t start it. You can check it on the terminal with

ps ax | grep postgres

2. `nix-shell --pure` `shell.nix`

This puts the PostgreSQL package in the /nix/store, starts and initializes the database cluster (with pg_ctl start and initdb, respectively). The relevant lines in the shell.nix:

  # ENV Variables
  LD_LIBRARY_PATH = "${geos}/lib:${gdal}/lib";
  PGDATA = "${toString ./.}/.pg";

  # Post Shell Hook
  shellHook = ''
    echo "Using ${postgresql_12.name}."

    # Setup: other env variables
    export PGHOST="$PGDATA"
    # Setup: DB
    [ ! -d $PGDATA ] 
      && pg_ctl initdb -o "-U postgres" 
      && cat "$postgresConf" >> $PGDATA/postgresql.conf
    pg_ctl -o "-p 5555 -k $PGDATA" start

So, in this case, the PostgreSQL instance is running, but you’ll need to specify a couple of things, such as --host= ("_ the host name of the machine on which the server is running_") and --dbname (the name of the database in the cluster you would like to connect):

psql --host=localhost --dbname=postgres
# or
psql --host=$PGDATA --dbname=$(whoami)

By default, there are only the following databases present in a newly created PostgreSQL cluster:

$ psql --host=localhost -l
                                   List of databases
   Name    |   Owner   | Encoding |   Collate   |    Ctype    |    Access privileges
-----------+-----------+----------+-------------+-------------+-------------------------
 postgres  | toraritte | UTF8     | en_US.UTF-8 | en_US.UTF-8 |
 template0 | toraritte | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/toraritte           +
           |           |          |             |             | toraritte=CTc/toraritte
 template1 | toraritte | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/toraritte           +
           |           |          |             |             | toraritte=CTc/toraritte
(3 rows)

My constant companion is the PostgreSQL documentation; some of the relevant pages:

initdb man page
pg_ctl man page
psql man page
PGDATA

The replies to the How to configure PostgreSQL declaratively (NixOS and non-NixOS)? NixOS forum thread also helped me to get started. (There are fairly advanced examples in there that I’m still trying to understand.)

A basic (and insecure) shell.nix example that I use for development is this (from here; fine-tuned a bit since then, but the gist is the same):

# Quickstart with PostgreSQL
# ==========================
#
#     createdb $(whoami) --host=$PGDATA --port=5432
#     psql --host=$PGDATA --username=$(whoami) --dbname=$(whoami) --port=5432
#
# `--port` is  only needed  when used  something other
# than the default port of 5432.

# ISSUES {{-
#
# + `pga_hba.conf` in here is super-insecure; tighten it
#   up (or, at least, add notes for best practices).
#
# + QUESTION Currently,   after    running   this   Nix
#            expression,  the  PostgreSQL superuser  is
#            whoever the current user is.
#   +-> Is this normal?
#   +-> How is it set?
#   +-> Did I influence this  with  any of the settings
#      below?
#
# }}-

# NOTE **Default `nixpkgs_commit` {{-
#
# The Nixpkgs commit used for pinning below is quite old,
#
#     Oct 1, 2021, 8:37 PM EDT
#     https://github.com/NixOS/nixpkgs/tree/751ce748bd1ebac94442dfeaa8bc3f100d73a9f6
#
# but they can be overridden using `nix-shell`'s `--argstr`
# (never figured out how to use `--arg`):
#
#     nix-shell 
#       -v      
#       -E 'import (builtins.fetchurl "https://raw.githubusercontent.com/toraritte/shell.nixes/main/_composables/postgres_shell.nix")' 
#       --argstr "nixpkgs_commit" "3ad7b8a7e8c2da367d661df6c3742168c53913fa"
#
#  (And all that on one line:
#  nix-shell  -v -E 'import (builtins.fetchurl "https://raw.githubusercontent.com/toraritte/shell.nixes/main/_composables/postgres_shell.nix")' --argstr "nixpkgs_commit" "3ad7b8a7e8c2da367d661df6c3742168c53913fa"
#  )
#
# The rules to compose "raw" GitHub links from the regular view page seems straightforward:
#
#      https://github.com/               toraritte/shell.nixes/blob/main/elixir-phoenix-postgres/shell.nix
#      https://raw.githubusercontent.com/toraritte/shell.nixes/     main/elixir-phoenix-postgres/shell.nix
#
# }}-

{ nixpkgs_commit ? "751ce748bd1ebac94442dfeaa8bc3f100d73a9f6" }:

let
  pkgs =
    import
      # The downloaded archive will be (temporarily?) housed in the Nix store
      # e.g., "/nix/store/gk9x7syd0ic6hjrf0fs6y4bsd16zgscg-source"
      # (Try any of the `fetchTarball` commands  below  in `nix repl`, and it
      #  will print out the path.)
      ( builtins.fetchTarball "https://github.com/nixos/nixpkgs/tarball/${nixpkgs_commit}" )
      { config = {}; overlays = []; }
  ;
in


pkgs.mkShell {

  buildInputs = with pkgs; [
    postgresql
  ];

  shellHook = ''

    ######################################################################
    # Create a diretory for the generated artifacts                      #
    ######################################################################

    mkdir .nix-shell
    export NIX_SHELL_DIR=$PWD/.nix-shell

    ######################################################################
    # Put the PostgreSQL databases in the project diretory.              #
    ######################################################################

    export PGDATA=$NIX_SHELL_DIR/db

    ######################################################################
    # Clean up after exiting the Nix shell using `trap`.                 #
    # ------------------------------------------------------------------ #
    # Idea taken from                                                    #
    # https://unix.stackexchange.com/questions/464106/killing-background-processes-started-in-nix-shell
    # and the answer provides a way more sophisticated solution.         #
    #                                                                    #
    # The main syntax is `trap ARG SIGNAL` where ARG are the commands to #
    # be executed when SIGNAL crops up. See `trap --help` for more.      #
    ######################################################################

    trap 
      "
        ########################################################
        # Stop PostgreSQL                                      #
        ########################################################

        pg_ctl -D $PGDATA stop

        ########################################################
        # Delete `.nix-shell` directory                        #
        # ----------------------------------                   #
        # The first  step is going  back to the  project root, #
        # otherwise `.nix-shell`  won't get deleted.  At least #
        # it didn't for me when exiting in a subdirectory.     #
        ########################################################

        cd $PWD
        rm -rf $NIX_SHELL_DIR
      " 
      EXIT

    ######################################################################
    # If database is  not initialized (i.e., $PGDATA  directory does not #
    # exist), then set  it up. Seems superfulous given  the cleanup step #
    # above, but handy when one had to force reboot the iron.            #
    ######################################################################

    if ! test -d $PGDATA
    then

      ########################################################
      # Init PostgreSQL                                      #
      #                                                      #
      # NOTE `initdb` vs `createdb`                          #
      # https://stackoverflow.com/questions/50210158/whats-the-difference-between-initdb-usr-local-var-db-and-createdb-db
      # https://www.postgresql.org/docs/current/app-initdb.html
      ########################################################

      pg_ctl initdb -D  $PGDATA

      ########################################################
      # PORT ALREADY IN USE                                  #
      ########################################################
      # If another `nix-shell` is  running with a PostgreSQL #
      # instance,  the logs  will show  complaints that  the #
      # default port 5432  is already in use.  Edit the line #
      # below with  a different  port number,  uncomment it, #
      # and try again.                                       #
      ########################################################

      # sed -i "s|^#port.*$|port = 5433|" $PGDATA/postgresql.conf

    fi

    ########################################################################
    # Configure and start PostgreSQL                                       #
    # ==================================================================== #
    # Setting all  necessary configuration  options via  `pg_ctl` (which   #
    # is  basically  a wrapper  around  `postgres`)  instead of  editing   #
    # `postgresql.conf` directly with `sed`. See docs:                     #
    #                                                                      #
    # + https://www.postgresql.org/docs/current/app-pg-ctl.html            #
    # + https://www.postgresql.org/docs/current/app-postgres.html          #
    #                                                                      #
    # See more on the caveats at                                           #
    # https://discourse.nixos.org/t/how-to-configure-postgresql-declaratively-nixos-and-non-nixos/4063/1
    # but recapping out of paranoia:                                       #
    #                                                                      #
    # > use `SHOW`  commands to  check the  options because  `postgres -C` #
    # > "_returns values  from postgresql.conf_" (which is  not changed by #
    # > supplying  the  configuration options  on  the  command line)  and #
    # > "_it does  not reflect  parameters supplied  when the  cluster was #
    # > started._"                                                         #
    #                                                                      #
    # OPTION SUMMARY                                                       #
    # -------------------------------------------------------------------- #
    #                                                                      #
    #  + `unix_socket_directories`                                         #
    #                                                                      #
    #    PostgreSQL  will  attempt  to create  a  pidfile  in              #
    #    `/run/postgresql` by default, but it will fail as it              #
    #    doesn't exist. By  changing the configuration option              #
    #    below, it will get created in $PGDATA.                            #
    #                                                                      #
    #   + `listen_addresses`                                               #
    #                                                                      #
    #     In   tandem  with   edits   in  `pg_hba.conf`   (see             #
    #     `HOST_COMMON`  below), it  configures PostgreSQL  to             #
    #     allow remote connections (otherwise only `localhost`             #
    #     will get  authorized  and  the  rest  of the traffic             #
    #     will be discarded).                                              #
    #                                                                      #
    #     NOTE: the  edit  to  `pga_hba.conf`  needs  to  come             #
    #           **before**  `pg_ctl  start`  (or  the  service             #
    #           needs to be restarted otherwise), because then             #
    #           the changes are not being reloaded.                        #
    #                                                                      #
    #     More info  on setting up and  troubleshooting remote             #
    #     PosgreSQL connections (these are  all mirrors of the             #
    #     same text; again, paranoia):                                     #
    #                                                                      #
    #       + https://stackoverflow.com/questions/24504680/connect-to-postgres-server-on-google-compute-engine
    #       + https://stackoverflow.com/questions/47794979/connecting-to-postgres-server-on-google-compute-engine
    #       + https://medium.com/scientific-breakthrough-of-the-afternoon/configure-postgresql-to-allow-remote-connections-af5a1a392a38
    #       + https://gist.github.com/toraritte/f8c7fe001365c50294adfe8509080201#file-configure-postgres-to-allow-remote-connection-md
    #
    #   + `log*`                                                           #
    #                                                                      #
    #     Setting up basic logging,  to see remote connections             #
    #     for example.                                                     #
    #                                                                      #
    #     See the docs for more:                                           #
    #     https://www.postgresql.org/docs/current/runtime-config-logging.html
    ########################################################################

    # !!!!!!!!!!!! These are only suitable for development.
    # ! INSECURE ! (Not sure if running a production server
    # !!!!!!!!!!!!  from `nix-shell` is a good idea anyway:)

    HOST_COMMON="hosts+alls+all"
    sed -i "s|^$HOST_COMMON.*127.*$|host all all 0.0.0.0/0 trust|" $PGDATA/pg_hba.conf
    sed -i "s|^$HOST_COMMON.*::1.*$|host all all ::/0 trust|"      $PGDATA/pg_hba.conf

    pg_ctl                                                  
      -D $PGDATA                                            
      -l $PGDATA/postgres.log                               
      -o "-c unix_socket_directories='$PGDATA'"             
      -o "-c listen_addresses='*'"                          
      -o "-c log_destination='stderr'"                      
      -o "-c logging_collector=on"                          
      -o "-c log_directory='log'"                           
      -o "-c log_filename='postgresql-%Y-%m-%d_%H%M%S.log'" 
      -o "-c log_min_messages=info"                         
      -o "-c log_min_error_statement=info"                  
      -o "-c log_connections=on"                            
      start
  '';

  ######################################################################
  # Without  this, almost  everything  fails with  locale issues  when #
  # using `nix-shell --pure` (at least on NixOS).                      #
  # See                                                                #
  # + https://github.com/NixOS/nix/issues/318#issuecomment-52986702    #
  # + http://lists.linuxfromscratch.org/pipermail/lfs-support/2004-June#023900.html
  ######################################################################

  LOCALE_ARCHIVE = if pkgs.stdenv.isLinux then "${pkgs.glibcLocales}/lib/locale/locale-archive" else "";
}

# vim: set foldmethod=marker foldmarker={{-,}}- foldlevelstart=0 tabstop=2 shiftwidth=2 expandtab:

Please signup or login to give your own answer.

Click here to cancel reply.

How to use postgresql in nixos

Answers

1. nix-env -iA nixos.postgresql

2. nix-shell --pure shell.nix

1. `nix-env -iA nixos.postgresql`

2. `nix-shell --pure` `shell.nix`