I have a PostgreSQL db that was running on my server.
I tried to do a db_dump
and got a weird error message. I eventually thought I would just restart PostgreSQL. However its now stopped and won't restart.
Error: /var/lib/postgresql/9.3/main is not accessible or does not exist
I get the error above when I try and run:
sudo service postgresql start
Can anyone give me some clue please?
I'm running on PostgreSQL 9.3 on Ubuntu 14.04
-
And what was the weird error message? How did you take the dump? Is your installation in the directory in the title?András Váczi– András Váczi2014年11月08日 19:30:20 +00:00Commented Nov 8, 2014 at 19:30
1 Answer 1
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?
-
Thanks for the help. My installation (its a Digital Ocean server) is /etc/postgres/9.3/main However starting it as postgres didn't work. I got the same error message? Is there maybe some env setting that is out of whack?Bernard– Bernard2014年11月08日 21:01:37 +00:00Commented Nov 8, 2014 at 21:01
-
@Bernard: I clarified a bit. I know nothing about
Digital Ocean
. Obviously a cloud service. If that means a modified Postgres installation all bets are off. Consult their help.Erwin Brandstetter– Erwin Brandstetter2014年11月09日 01:23:03 +00:00Commented Nov 9, 2014 at 1:23 -
Thanks - DO is like Heroku. Its a standard postgres install as they don't have custom installs. They just give you a server and you choose the standard linux distros and install everything yourself. /var/lib/postgresql/9.3/main is my data directory according to postgresql.conf. However I have nothing in the /var/lib/postgresql/ folder except .ssh, .bash_history, .psql_history ? Its like its the postgres user's home folder or something as I tried to do a pg_dump as the postgres user and that backup is also in the folder - 0 bytes long though :(Bernard– Bernard2014年11月09日 09:37:54 +00:00Commented Nov 9, 2014 at 9:37
-
root@myserver:~# pg_lsclusters Ver Cluster Port Status Owner Data directory Log file 9.3 main 5432 down <unknown> /var/lib/postgresql/9.3/main /var/log/postgresql/postgresql-9.3-main.logBernard– Bernard2014年11月09日 09:39:04 +00:00Commented Nov 9, 2014 at 9:39
-
Its totally weird - like my db has just disappeared?Bernard– Bernard2014年11月09日 09:40:00 +00:00Commented Nov 9, 2014 at 9:40