psql cannot connect to local database after changing data directory

All we need is an easy explanation of the problem, so here it is.

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:

[email protected]:~# 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:

[email protected]-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] [email protected] 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.

How to solve :

I know you bored from this bug, So we are here to help you! Take a deep breath and look at the explanation of your problem. We have many solutions to this problem, But we recommend you to use the first method because it is tested & true method that will 100% work for you.

Method 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.

Note: Use and implement method 1 because this method fully tested our system.
Thank you 🙂

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

Leave a Reply