2

I've been working on this for about a day and I can't figure it out, I really don't know where else to look. I have a directory, /psql/data/ and I'm trying to run postgres -D /pgsql/data to start the database server. However, I get 

postgres cannot access the server configuration file "/psql/data/postgresql.conf": Permission denied

Here's some background. Where I work we have an application using Flask framework that uses a Postgres database. We are running this application on an Amazon server. This application works! I know that because I've used it before. However, the person who wrote it left the company and I'm in charge of it now. And another one of my colleagues inadvertently stopped the server, so upon restart I had to set it back up.

Here's what I've tried: postgres -D /psql/data which I read about here From that link it also says you have to be logged in to the postgresql user account. (I just started using ubuntu about a month ago and I'm not extremely familiar with it, so forgive me if I'm making dumb statements). So I looked around and found out I could login to the postgres account with sudo su postgres. Which works! And I can see files I previously had no permissions to see. So then when I try to run postgres -D /psql/data again I get:

The program 'postgres' is currently not installed. To run 'postgres' please ask your administrator to install the package 'postgres-xc'

First I tried to install postgres-xc, but that had a missing dependency, then when I went searching I read that if I'm getting this message it's likely a greater bug, and I shouldn't have to install postgres-xc. So I stopped trying.

Ultimately, I am trying to run the application again. But before it can run it needs the database running, because without the database running when I attempt to start the flask app with python [myapp].py I get:

sqlalchemy.exc.OperationalError: (psycopg2.OperationalError) could not connect to server: Connection refused
Is the server running on host "localhost" (127.0.0.1) and accepting
TCP/IP connections on port 5432?

I've figured out that that's because the database is not running. And I can't get the database server to start. I've tried to give as much relevant information as I can, any suggestions on where I should look next will be greatly appreciated. I can also provide more information if needed.

UPDATE: The error is occurring here

engine = sqlalchemy.create_engine(conf['db'])
insp = reflection.Inspector.from_engine(engine)

the engine is created from a value in the config file, namely,

db: postgresql://work:app@localhost:5432/work

But once the second line tries to execute it errors out.

UPDATE:

VICTORY! Thank you to everyone who helped me, all of your answers combined pointed me in the right directions (but only one person can receive the bounty unfortunately).

Firstly, whenever I tried sudo -u postgres pg_ctl -D /psql/data it would error and tell me no such command. So I had to find exactly where the pg_ctl command was. It was in /usr/lib/postgres/9.3/bin/pg_ctl. So then I called sudo -u postgres /usr/lib/postgresql/9.3/bin/pg_ctl start -D /psql/data. This gave me an error that my configuration file had an error. Which was weird since I never touched it, but I commented that error out and then I got the real error.

The database was made using postgres 9.4 but I was using postgres 9.3.

So I had to install 9.4. Instruction can be found here. Once I did that I had to stop the previously started postgres servers with sudo service postgresql stop.

Finally I was able to start the server from the database on file with sudo -u postgres /usr/lib/postgresql/9.4/bin/pg_ctl start -D /psql/data.

Now I can access the database with sudo -su postgres then psql.

Thank you all!

Improve this question
12
  • 1
    You might want to look this as an example: pypi.python.org/pypi/testing.postgresql Commented Jun 16, 2015 at 22:35
  • 1
    Check if in /etc/init.d there is a file with a name postgres, pgsql or something similar. Commented Jul 3, 2015 at 0:43
  • 1
    After start it should say something like "PostgreSQL 9.3 started successfully" or "Cannot start..." What did it say? Commented Jul 3, 2015 at 1:13
  • 1
    So, server is running. Next step is to check authentication parameters. They are in pg_hba.conf in postgres data directory. Commented Jul 3, 2015 at 1:27
  • 1
    I am afraid that I cannot help you here. You have to read about it in the documentation. Commented Jul 3, 2015 at 1:29

2 Answers 2

Reset to default
3
+50

It is highly likely that the postgres server was being run under a different user account, probably by an init script.

To get started quickly, you'll probably want to:

sudo -u postgres pg_ctl -D /psql/data -w start

to run as the postgres user (sudo -u postgres) the pg_ctl command with an instruction to start PostgreSQL from /psql/data and wait until it has started.

You should then look into whether PostgreSQL is set to be managed by your OS init scripts. Generally you should be able to just do something like:

sudo service postgresql start

sudo service postgresql-9.4 start

sudo service postgresql94

sudo systemctl start postgresql-9.4

depending on your OS/version and how PostgreSQL was installed. If it was compiled by hand, none of these may work until you set up a service, which you should do promptly. Read your operating system documentation to find out how.

Improve this answer
Sign up to request clarification or add additional context in comments.

3 Comments

Thanks for you reply. So I ran that command and got the error "sudo: pg_ctl: command not found". So then I tried to run it without the sudo -u postgres, and I got back to the permission denied error. Which I understand, since I'm not under the postgres user. Then I ran sudo su postgres, to switch to postgres user and tried to run "pg_ctl -D /psql/data -w start" and it says the program is not currently installed, and that I need to install postgres_xc.
As for your last comment about setting up a service, what's the difference between setting up a service, and starting the database server?
@Gabriel an OS service manages the db server startup and shutdown - e.g. sysv init scripts, systemd service etc
1

Gabriel, first of all. What are the file permissions of "/psql/data/postgresql.conf"?

ls -l /psql/data/*.conf

With the super user, try:

chmod 744 /psql/data/postgresql.conf

It would allow you to read this file, but by your problem's description I believe only this step should not solve it.

Then, which O.S. are you using?

I had similar problems when I was migrating from PostgreSQL 8 with PostGIS to PostgreSQL 9 in a Linux Ubuntu (11 LTS) environment. In the end I gave up opening the file directly (pg_start) and, instead, I used pg_dump to dump the files from the old DB and then I imported the database to PG9.

If you can't do this. May I ask if you have administrative privileges on the server AND this is the only database you are using in PostgreSQL?

If this is your case, I would uninstall the server (apt-get remove, purge, etc if you're using apt) and install it again. Since you even don't have the pg_ctl executable working, something is missing, (lib, bin, etc???) and to find this out sometimes takes too long and the best solution is uninstall/install.

If you cannot do this in your server, install a postgresql server in any other one and execute the pg_start -D the way you're doing. It should open the DB. Then you can dump the database and import it to your production server.

Improve this answer

7 Comments

Thanks for your reply. Changing the permissions got me to a new error: FATAL: data directory "/psql/data" has wrong ownership HINT: The server must be started by the user that owns the data directory. I will investigate this error now. To answer your other question, the app sits on an Amazon server running Ubuntu 14.04. I also have full rights to do anything I want to the server. As for pg_dump, the data to my database is in /psql/data. I don't see any options where I can dump a database that's in a file. Is that what you mean for me to do?
@Gabriel, yes. For me, dumping the file from the original DB would be the best solution, if you have access to it. If you don't have, then we should try to open the database in the <b>exact same version<b> of the original DB. Do you know from which version the file comes from? (One day, I managed to open a DB from an older version the way you're doing, but I needed even to change the postgreSQL original source in C and compile it again, I don't want you to go this far (if you're curious the solution was pointed here: postgresql.nabble.com/Recuperacao-postgreSQL-8-0-td2044539.html
do: ls -l /psql to check the owner of the data directory. If Then do: chown -R your_postgre_user /psql/data
The database should be generated from a file. /psql/data has all of the information. So right now the database is not running because I can't start it using that file. The postgres version is 9.4
The owner is postgres. I never created a postgres user. I thought owner being postgres was sufficient, It seems like I need to start the database as postgres, but when I can't figure out how to even do that.
|

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.