Postgresql – How to access Postgres when I get an error about “/var/run/postgresql/.s.PGSQL.5432”

linuxpostgresqlUbuntu

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

Best Answer

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.

Related Solutions

Postgresql – How to run a PostgreSQL outside the standard directories on Linux, ie outside /var/run/postgresql etc

pg_ctl allows for a -D option which allows you to point at your data wherever it is.

I do this all the time for development environments and fun things like setting up intracomputer replication environments for testing. It works fine.

The one thing you really need to be careful about is the configuration. After unzipping you should probably edit the postgresql.conf appropriately.

Postgresql – Error: /var/lib/postgresql/9.3/main is not accessible or does not exist

If you suspect there's something wrong with our database (cluster), make a copy of your database directory before you continue. Just to be sure. On Ubuntu the default directory would be: /var/lib/postgresql/9.3/main/ - but your installation may differ.

Actually, your command should work (in default Ubuntu installations), because the service command starts the postgres server process with appropriate privileges (as system user postgres)

sudo service postgresql start

Translates to something like:

sudo -u postgres /usr/lib/postgresql/9.3/bin/pg_ctl -D /var/lib/postgresql/9.3/main/ -o "-c config_file=/etc/postgresql/9.3/main/postgresql.conf" start

That starts all db clusters you may have. To start a particular one, use the wrapper pg_ctlcluster:

sudo -u postgres pg_ctlcluster 9.3 main start

To get the status of all currently installed db clusters use pg_lsclusters:

sudo -u postgres pg_lsclusters

You must be aware that the data directory is not the same as the config directory in default Debian or Ubuntu Postgres installations. For Postgres 9.3 that would be:

data dir: /var/lib/postgresql/9.3/main/
config dir: /etc/postgresql/9.3/main/

You can check the /etc/postgresql/9.3/main/postgrsql.conf for the actual data dir. It's the line starting with

data_directory =

The error message indicates that Postgres tries to start with the default data dir, but it is not there or the privileges have been changed. Did you check?

Or are you running a non-default installation?

Best Answer

Related Solutions

Postgresql – How to run a PostgreSQL outside the standard directories on Linux, ie outside /var/run/postgresql etc

Postgresql – Error: /var/lib/postgresql/9.3/main is not accessible or does not exist

Related Question