psql cannot connect to local database after changing data directory

Question

I have a PostgreSQL 12 database running on a Linux instance. When I created the user user I forgot to create a same name database. All was working fine, until I change the data_directory in the postgresql.conf file to use a volume in /mnt. Now I cannot access my database.

Any psql command (i.e. createdb) now yields:

root@ubuntu-s-2vcpu-4gb-intel-fra1-01:~# createdb
createdb: error: could not connect to database template1: could not connect to server: No such file or directory
    Is the server running locally and accepting
    connections on Unix domain socket "/var/run/postgresql/.s.PGSQL.5432"?

And even after a restart with sudo systemctl restart postgresql, my cluster doesn't seem to be running:

root@ubuntu-s-2vcpu-4gb-intel-fra1-01:~# pg_lsclusters
Ver Cluster Port Status Owner Data directory                         Log file
12  main    5432 down   root  /mnt/volume_fra1_01/postgresql/12/main /var/log/postgresql/postgresql-12-main.log

The log file seem to suggest that I need to create the user database, but I cannot do it if I the database is not running:

Output of /var/log/postgresql/postgresql-12-main.log

2022-07-13 22:39:20.179 UTC [27361] LOG:  starting PostgreSQL 12.11 (Ubuntu 12.11-0ubuntu0.20.04.1) on x86_64-pc-linux-gnu, compiled by gcc (Ubuntu 9.4.0-1ubuntu1~20.04.1) 9.4.0, 64-bit
2022-07-13 22:39:20.179 UTC [27361] LOG:  listening on IPv6 address "::1", port 5432
2022-07-13 22:39:20.179 UTC [27361] LOG:  listening on IPv4 address "127.0.0.1", port 5432
2022-07-13 22:39:20.181 UTC [27361] LOG:  listening on Unix socket "/var/run/postgresql/.s.PGSQL.5432"
2022-07-13 22:39:20.193 UTC [27362] LOG:  database system was shut down at 2022-07-13 22:39:19 UTC
2022-07-13 22:39:20.198 UTC [27361] LOG:  database system is ready to accept connections
2022-07-15 02:48:45.501 UTC [45714] topcat@topcat FATAL:  database "topcat" does not exist
2022-07-15 03:08:32.272 UTC [27361] LOG:  received fast shutdown request
2022-07-15 03:08:32.274 UTC [27361] LOG:  aborting any active transactions
2022-07-15 03:08:32.275 UTC [27361] LOG:  background worker "logical replication launcher" (PID 27368) exited with exit code 1
2022-07-15 03:08:32.279 UTC [27363] LOG:  shutting down
2022-07-15 03:08:32.295 UTC [27361] LOG:  database system is shut down
~

The same problem arises even when I change to the postgres user. Should I burn it all and start anew? I am getting out of ideas here.

Answer 1

root in pg_lsclusters Owner column looks wrong. The owner of the data directory must be postgres. Also its permissions must be 0700.

The Postgres service won't start if the owner or permissions of the data directory are unexpected. It may complain about it in the server log, but maybe this check happens so soon that it hasn't redirected the output to the log file at this point.

Maybe journalctl -xe would show some details about when you do sudo systemctl restart postgresql and it fails to start.