1

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

ref (https://help.ubuntu.com/community/PostgreSQL).

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

8 March: 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.

  • sudo pg_ctl start -l /tmp/pglog.txt – Roman Tkachuk Mar 5 '17 at 21:38
  • @Trevor you need to comment on the answer not just silently update the question so we can't see it. Try running ls /var/run/postgresql/*PGSQL* – Evan Carroll Oct 27 '17 at 1:46
1

In Ubuntu you don't start PostgreSQL with pg_ctl. You can, but don't. Instead, use this

sudo service postgresql start

su -c will only work if you enable the root account. Don't do that. Instead, use sudo.

See my answer here for how to get up and running on Ubuntu

  • 1
    on the latest ubuntu you need to run sudo service postgresql start (different order of arguments) – Cristian May 18 '17 at 17:20
  • Typoed that. It's Fixed. – Evan Carroll May 18 '17 at 17:24
0

You have to be postgres user to be able to start postgres with pg_ctl. So I guess your command should be something like that (remember that it's possible that postgres user $PATH is not set properly, that's why I added the complete binary path) :

sudo -u postgres /usr/lib/postgresql/9.5/bin/pg_ctl start -l ~/pglog.txt

(If you're using Ubuntu, you can use the Debian Postgres perl wrappers which are really great. https://manpages.debian.org/jessie/postgresql-common/index.html)

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.