Set up a simple Postgres instance

Help
1

The goal is to set up a basic Postgres 17 instance as back-end for the development of an application. All local and basic, the simplest thing possible. This guide seems to provide exactly that, however, none of the client programmes are able to connect to the server. Both the log and isof report a running instance, but it is unreachable.

The full log is below. Any help appreciated.

~/git/test$ cat shell.nix
# Obtained from the guide:
# https://mgdm.net/weblog/postgresql-in-a-nix-shell/
with import <nixpkgs> {};
mkShell {
  buildInputs = [
    postgresql
  ];
}

~/git/test$ nix-shell

[nix-shell:~/git/test]$ initdb -D .pg
The files belonging to this database system will be owned by user "lads".
This user must also own the server process.

The database cluster will be initialized with this locale configuration:
  locale provider:   libc
  LC_COLLATE:  en_GB.UTF-8
  LC_CTYPE:    pt_PT.UTF-8
  LC_MESSAGES: en_GB.UTF-8
  LC_MONETARY: pt_PT.UTF-8
  LC_NUMERIC:  pt_PT.UTF-8
  LC_TIME:     pt_PT.UTF-8
The default database encoding has accordingly been set to "UTF8".
The default text search configuration will be set to "portuguese".

Data page checksums are disabled.

creating directory .pg ... ok
creating subdirectories ... ok
selecting dynamic shared memory implementation ... posix
selecting default "max_connections" ... 100
selecting default "shared_buffers" ... 128MB
selecting default time zone ... Europe/Lisbon
creating configuration files ... ok
running bootstrap script ... ok
performing post-bootstrap initialization ... ok
syncing data to disk ... ok

initdb: warning: enabling "trust" authentication for local connections
initdb: hint: 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/xwbdn2l153f3lxd4f09ilgpngbbhvvlb-postgresql-17.2/bin/pg_ctl -D .pg -l logfile start


[nix-shell:~/git/test]$ pg_ctl -D .pg -l pg_log -o "--unix_socket_directories='$PWD'" start
waiting for server to start.... done
server started

[nix-shell:~/git/test]$ createdb test
createdb: 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?

[nix-shell:~/git/test]$ psql -p 5432 -U postgres
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?

[nix-shell:~/git/test]$ cat pg_log
2025-02-14 09:23:12.401 WET [65273] LOG:  starting PostgreSQL 17.2 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 14.2.1 20241116, 64-bit
2025-02-14 09:23:12.402 WET [65273] LOG:  listening on IPv6 address "::1", port 5432
2025-02-14 09:23:12.402 WET [65273] LOG:  listening on IPv4 address "127.0.0.1", port 5432
2025-02-14 09:23:12.403 WET [65273] LOG:  listening on Unix socket "/home/lads/git/test/.s.PGSQL.5432"
2025-02-14 09:23:12.405 WET [65276] LOG:  database system was shut down at 2025-02-14 09:22:30 WET
2025-02-14 09:23:12.408 WET [65273] LOG:  database system is ready to accept connections

[nix-shell:~/git/test]$ lsof -i :5432
COMMAND    PID USER   FD   TYPE DEVICE SIZE/OFF NODE NAME
postgres 65273 lads    6u  IPv6 140005      0t0  TCP localhost:postgresql (LISTEN)
postgres 65273 lads    7u  IPv4 140006      0t0  TCP localhost:postgresql (LISTEN)
2

It is evident from your log that createdb and psql try to connect on a Unix socket at /run/postgresql/.s.PGSQL.5432, but your Postgres instance is listening on Unix socket "/home/lads/git/test/.s.PGSQL.5432". Either pass the directory where the socket resides like psql -h /home/lads/git/test/, or force connecting via IPv4 instead: AFAIK to do that, you need to explicitly specify a host like psql -h 127.0.0.1.

1 Like
3

For the sake of completion, this set up does not seem to create database users. There is thus an extra step:

[nix-shell:~/git/cro]$ createuser postgres -h 127.0.0.1

[nix-shell:~/git/cro]$ createdb test -h 127.0.0.1

[nix-shell:~/git/cro]$ psql -h 127.0.0.1 -U postgres
psql (17.2)
Type "help" for help.

postgres=> \q