7

I am running Ubuntu 16. I have installed Postgresql. Postgresql used to work, but then I rebooted.

nmap commands show port 5432 is open. Postgres seems to be working correctly:

service postgresql status

postgresql.service - PostgreSQL RDBMS Loaded: loaded (/lib/systemd/system/postgresql.service; enabled; vendor preset: enabled) Active: active (exited) since Sat 2017-07-29 18:42:59 EDT; 1min 4s ago Process: 201 ExecStart=/bin/true (code=exited, status=0/SUCCESS) Main PID: 201 (code=exited, status=0/SUCCESS)
Memory: 0B CGroup: /system.slice/postgresql.service

I ran this: psql

But I got this:

psql: 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"?

The file listed above does not seem to exist.

How do I get into Postgresql? Normally I'd run psql or sudo -i -u postgres then psql. But these commands are not working. I keep getting an error about "could not connect to server." Several reboots have not helped.

Update:

I ran this command: dpkg -l | grep postgres

rc postgresql-9.5                9.5.6-0ubuntu0.16.04                   amd64        object-relational SQL database, version 9.5 server
ii postgresql-client             9.5+173                                all          front-end programs for PostgreSQL (supported version)
ii postgresql-client-9.5         9.5.7-0ubuntu0.16.04                   amd64        front-end programs for PostgreSQL 9.5
ii postgresql-client-common      173                                    all          manager for multiple PostgreSQL client versions
ii postgresql-common             173                                    all          PostgreSQL database-cluster manager
|improve this question
  • What is the result of ps aux | grep '[b]in/postgres'? – Abelisto Jul 29 '17 at 23:37
  • I get no output at all from that command. – Victor Jul 29 '17 at 23:53
  • 1
    Show the output of pg_lsclusters, please. Also try using psql -h 127.0.0.1. It may be a configuration issue (disabled Unix sockets). – ypercubeᵀᴹ Jul 30 '17 at 9:41
  • 5
    The dpkg status of postgresql-9.5 shoud be ii, not rc, which means it has been uninstalled. The configuration files and data should still be there, so simply doing apt-get install postgresql-9.5 has a good chance to work. – Daniel Vérité Jul 30 '17 at 10:29
  • Also unfortunately the service postgresql status is useless since the switch to systemd. Apparently it can report active when the service is actually down, probably depending on the reason why it's down. – Daniel Vérité Jul 30 '17 at 10:37
5

psql: 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"?

This error generally means that the server is not running. Based on dpkg -l output and the thread of comments, it was due to the postgresql-9.5 main package being somehow uninstalled. Since the uninstall hasn't been called with the --purge option to dpkg, the data and configuration files are still there, so apt-get install postgresql-9.5 can fix the problem.

|improve this answer
  • I have been working through this issue for half a day (though Im using postgresql-10). There are so many reasons for why someone could get the error in the OP. Its very misleading when the problem is that the packages were uninstalled (somehow?) but the system lists them as installed. Are you able to explain how this could happen? Im really confused, and wouldnt think to just apt-get install package-that-appears-installed. Also I am baffled as to how a simple apt-get update/upgrade could cause this, as its all I did this morning before encountering this problem. Pls halp understands? – Todd Oct 29 '18 at 20:35
  • Also, tysm for this solution, totally saved me :-) – Todd Oct 29 '18 at 20:50
7

You probably have multiple PostgreSQL versions installed. If so, the other version probably defaults to unix_socket_directories = '/tmp/' but the libpq your psql is linked to probably defaults to /var/run/postgresql/.

Try

psql -h /tmp

If that works, the above is the problem. You can add export PGHOST=/tmp to your .bashrc to change the default locally for your user.

If that doesn't work, make sure PostgreSQL is actually running

ps aux |grep postgres

and if not, start it. How depends on how you installed it, but it'll be via the service or systemctl command(s) if you installed using packages.

|improve this answer
  • psql -h /tmp psql: could not connect to server: No such file or directory Is the server running locally and accepting connections on Unix domain socket "/tmp/.s.PGSQL.5432"? So that did not work. I then tried this: # ps aux | grep postgres root 3627 0.0 0.0 11224 876 pts/0 S+ 22:37 0:00 grep --color=auto postgres I may have multiple versions installed. I am not sure what is wrong. – Victor Jul 30 '17 at 2:38
  • 2
    Ok, so PostgreSQL isn't running (no postgres processes). You have to start it. It should usually autostart on boot so either the way you installed it won't autostart, you might have to start it with pg_ctl. Otherwise, check the logs. If you don't know what you installed or where, try find / -name postgres 2>/dev/null and/or rpm -qa|grep postgres or dpkg -l |grep postgres – Craig Ringer Jul 30 '17 at 4:01
  • "service postgresql start" goes to the next line. There is no error. But no postgres daemon starts. "pg_ctl" is a "command not found. The command "find / -name postgres 2>/dev/null" returns /root/.linuxbrew/Library/Aliases/postgres. – Victor Jul 30 '17 at 4:32
3

If your Postgres service is up and running without any error or there is no error in starting the Postgres service and still you are getting the mentioned error, follow these steps

Step1: Running pg_lsclusters will list all the postgres clusters running on your device

eg: 

Ver Cluster Port Status Owner    Data directory               Log file
9.6 main    5432 online postgres /var/lib/postgresql/9.6/main /var/log/postgresql/postgresql-9.6-main.log

most probably the status will be down in your case . Try restarting Postgres clusters and service

Step 2: Restart the pg_ctlcluster

#format is pg_ctlcluster <version> <cluster> <action>
sudo pg_ctlcluster 9.6 main start

#restart postgres
sudo service postgres restart

Step 3: Step 2 failed and threw an error

If this process is not successfull it will throw the error. My error was(You can see the error log on /var/log/postgresql/postgresql-9.6-main.log) 

FATAL: could not access private key file "/etc/ssl/private/ssl-cert-snakeoil.key": Permission denied
Try adding `postgres` user to the group `ssl-cert`

Step 4: check ownership of postgres

Make sure that postgres is the owner of /var/lib/postgresql/version_no/main eg: sudo chown postgres -R /var/lib/postgresql/9.6/main/

Step 5: Check Postgres user belongs to ssl-cert user group

It happened to me and it turned out that I removed erroneously the Postgres user from "ssl-cert" group. Run the below code to fix the user group issue and fixing the permissions

#set user to group back with
sudo gpasswd -a postgres ssl-cert

# Fixed ownership and mode
sudo chown root:ssl-cert  /etc/ssl/private/ssl-cert-snakeoil.key
sudo chmod 740 /etc/ssl/private/ssl-cert-snakeoil.key

sudo service postgres restart
|improve this answer
1

You're under Ubuntu. You can use Debian wrappers. Try pg_ctlcluster 9.6 main start (you can change main for your cluster's name.

To check if another PostgreSQL version is running, try ps -ef | grep postgres.

If it' won't start with pg_ctlcluster. Look at the logs. For Debian-based, default logging is in /var/log/postgresql.

|improve this answer
  • 1
    You're assuming that they're using Ubuntu's packages, or those like the apt.postgresql.org ones that integrate with pg_wrapper. Not necessarily a safe assumption. Many people don't know to use them and install from EDB or some other binary installer distro. – Craig Ringer Jul 30 '17 at 11:12
  • Oh, I didn't think people would use the more complicated way, but I suppose "complicated" isn't objective. – Arkhena Jul 31 '17 at 7:46
0

I solved this problem with a few checks

root@biber-OptiPlex-380:/home/biber/kailas/RoR/demo# su - postgres
postgres@biber-OptiPlex-380:~$ psql
psql: 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"?
postgres@biber-OptiPlex-380:~$ service postgresql status
9.5/main (port 5432): down
postgres@biber-OptiPlex-380:~$ service postgresql start
 * Starting PostgreSQL 9.5 database server                                                                                                        [ OK ] 
postgres@biber-OptiPlex-380:~$ service postgresql status
9.5/main (port 5432): online
postgres@biber-OptiPlex-380:~$ psql
psql (9.5.19)
Type "help" for help.

postgres=#
|improve this answer
  • 1
    Welcome to Database Administrators. Please, check the Markdown help to make your answer easier to read. – Ronaldo Dec 20 '19 at 13:29

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.