PostgreSQL promotes itself as the most advanced open-source database application platform, and Debian Linux certainly has plenty of packages that make it even more complex. You can also find boatloads of packages for PostgreSQL if you’re working with Ubuntu Server or any of the various Ubuntu spins, since they’re based on Debian’s core. This level of complexity and development makes “could not connect to server” and “no such file or directory” warnings that much more annoying.
Fortunately, these are usually simple cases of permission problems caused by the fact that PostgreSQL wants a user named postgres to hold onto these directories. By using a simple command line trick, you can fix this almost instantly. You’ll want to go through a few basic diagnostic checks beforehand, though, just to make sure that this is actually the problem that you’re up against.
Fixing PostgreSQL Could Not Connect to Server Errors
First, try restarting the PostgreSQL system manually. Sometimes this is enough to fix things, and if not then you’ll at least get an error message to work from. More than likely, you simply restart the system by issuing the psql command as the postgres user.
You might find that this cleared everything up. Otherwise, you might get a line that reads “psql: could not connect to server: No such file or directory,” which means you’re having permission problems. If you receive this error message, then you’ll get some other text spit out as well.
Check service status if you’ve gotten this message to make sure that the modules are loaded. They should be, but if they’re not you might want to restart. If you get a message that reads “Loaded: loaded (/lib/systemd/system/postgresql.service; enabled)”, then they are running. Try sudo service postgresql restart just to do a brief restart and see if that corrects anything. It usually doesn’t, but it might be worth a try depending.
Assuming that it hasn’t helped, have a look inside of th e PostgreSQL log to look for errors. In the unlikely case you find something about package errors, then you might be missing one of the SQL modules. This isn’t usually the cause of these problems, but it certainly can’t hurt to at least have a look. More than likely, you’ll actually come across something that warns you that “Permissions should be u=rwx (0700)” and
that “data directory “/var/lib/postgresql/9.6/main” has group or world access”, though you might see a different version number depending on which SQL server you’re running.
This is because Debian and similar distributions expect that the postgres user and group control these directories with 0700 permissions and all files through 0600 permissions for security’s sake. All you have to do is run the following command at the terminal to fix permissions:
sudo chown -R postgres:postgres /var/lib/postgresql/9.6/ && sudo chmod -R u=rwX,go= /var/lib/postgresql/9.6/
Keep in mind that should be an upper-case X and not the more common lower-case x you’re probably more familiar with due to the specific way that you’re going to want to set these file permission options. While you do need root access to do this, the inclusion of those two sudo marks should be more than enough to give yourself the proper permissions when running as a regular user. This is important since Ubuntu and the various Linux implementations spun off from Ubuntu hash out the main root account so you need to do things this way.
Once this command finishes, you can restart the service again by sudo service postgresql restart from the terminal and you shouldn’t have any errors this time. If you were to take a look at the log, then those warnings regarding permissions problems shouldn’t be there any longer either.
This is an error that happens as the result of fairly specific conditions, so you shouldn’t experience it again after you’ve corrected it the first time provided that you don’t try to manually do anything involving permissions manipulation of the PostgreSQL directories. There’s no situation where this should really be necessary anyway, outside of correcting this problem in the first place.