I'm new to PostgreSQL. Although I could access a localhost database either with PGAdminIII or psql (eg
sudo -u postgres psql mydb), I've not been able to connect from Dart. I think my difficulties are with Postgres.
Previously when I've used Postgres (Windows 10) I could start the server with
pg_ctl start -l ~/pglog.txt
Now (ubuntu 16.04), this same command gives me this error:
pg_ctl: could not open PID file "/var/lib/postgresql/9.5/main/postmaster.pid": Permission denied*
(PGDATA is set to /var/lib/postgresql/9.5/main)
Folder main has user/group of postgres/postgres as does it's parent, postgresql. /var/lib has owner/group root/root.
If I run the same command as the postgres user (su -c 'pg_ctl start -l ~/pglog' postgres), after entering the password, the error message is:
su: Authentication failure
(I'm sure I have the pw correct, but is there some way to check/change/confirm that…bearing in mind that I now can't get the server running.)
The bin folder is in my path. There are postmaster.pid and postgresql.conf files in the data directory.
I have changed authentication for local users in pg_hba.conf to md5 rather than peer.
I think I installed postgresql with this command:
sudo apt-get install postgresql postgresql-contrib
Having changed a few permissions, then, as far as I can tell, changed them back again, the server no longer runs on bootup. That is, I can now no longer connect with either PGAdminIII or psql. I've not been able to find out how it would have started previously.
I feel sure that I have some permissions incorrect? Other suggested topics on StackOverflow have not been sufficient to allow me to resolve this. One answer is the following:
Sounds like a permission problem on the data directory. Did you check if the postgres (operating system) user has full access to /usr/local/pgsql/data and all sub-directories?*
Another answer included:
"Permission denied" is usually a problem with the access rights on the data directory. Make sure the postgres user account has full access to /usr/local/pgsql/data
Thanks Roman. Using your suggested command I get
sudo: pg_ctl: command not found
But running without sudo I got the following promising message (which is weird given that that's essentially what I was using before but with no response):
pg_ctl start -l /tmp/pglog.txt server starting
Notwithstanding this, I still don't think the server is running – this is the message from either PGAdmin3 or psql.
Evan, thank you also. The latter gives no error and returns to the prompt after perhaps 2 seconds – so it appears to be doing something. But trying to connect with PGAdmin3 or psql still indicates that no server is listening. Eg:
psql postgres 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"?
I've checked postgres.conf and see that the port seems to be correct.