PostgreSQL issues: 1) seemingly packaging issue, then 2) server connection problem

[jpprost]

Hi,

I am trying to install on a fresh Ubuntu 18.04, with postgres 10.9.

1. first (well, kind of) I was stuck at step sudo -u postgres psql -c "CREATE USER ..., with the following message: === begin output 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"? === end output

Following suggestions found at https://askubuntu.com/questions/50621/cannot-connect-to-postgresql-on-port-5432 that there was a packaging problem with postgres and that the version number had to be explicitely specified at install time, I removed the different postgres-related packages already installed, then ran the following: # sudo apt install -y postgresql-10 libpq-dev

It seemed to fix the problem, until the next one.

2. now I am stuck at the following step: $ bin/rails db:create RAILS_ENV=production which gives me the following output: === begin output could not connect to server: Connection refused Is the server running on host "localhost" (::1) and accepting TCP/IP connections on port 5432? 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? Couldn't create 'decidim_prod' database. Please check your configuration. rails aborted! PG::ConnectionBad: could not connect to server: Connection refused Is the server running on host "localhost" (::1) and accepting TCP/IP connections on port 5432? 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? bin/rails:4:in `' Tasks: TOP => db:create (See full trace by running task with --trace) === end output

I have applied all kinds of suggestions found on various postgres forums but nothing seems to work.

Any suggestions?

Cheers, JP

[microstudi]

Can you confirm that postgresql is up and running? you should be able to connect it outside rails to be sure. Things to look at:

1. Check if postgresql is up:

ps aux|grep postgresql
postgres  9854  0.0  0.3 323988 27396 ?        S    11:24   0:00 /usr/lib/postgresql/10/bin/postgres -D /var/lib/postgresql/10/main -c config_file=/etc/postgresql/10/main/postgresql.conf

If it's down, you need to solve the problem of getting up postgresql, check the logs in postgresql,

2. Check any firewalls (try to use the command nc). Check it out:

$ nc -zv localhost 5432
Connection to localhost 5432 port [tcp/postgresql] succeeded!

If this doesn't work, it might be the firewall (in ubuntu try sudo ufw status).

3. try to connect directly to postgresql:

sudo -u postgres psql 
psql (10.9 (Ubuntu 10.9-0ubuntu0.18.04.1))
Type "help" for help.

postgres=# \dS

[jpprost]

Hi,

thank you for your reply. Yes, I agree that something was wrong about getting the server up and running. But even starting the service wouldn't make any difference.

Today I decided to try with the latest version of postgreSQL, to see if it made any difference. So I installed the v. 11 (after purging the v. 10). The installation process ends with the recommendation to a) create a cluster, then b) launch the service, with the following commands:

$ sudo pg_createcluster 11 main --start $ sudo systemctl start postgresql@11-main

After that everything was fine. So the problem was most likely that I had not created any cluster.

I am (obviously) not familiar at all with postgreSQL and the creation of a so-called "cluster" is not intuitive to me. If I may, I would like to recommend upgrading the otherwise extremely well designed instructions with an additionnal step for setting up postgres -- after installing the package, I would say.

Thank you anyway for your help.

Cheers, JP

[microstudi]

That wasn't necessary when I made the guide but I'll include these recommendations in the next iteration of the guide, probably by middle-summer. This feedback is very helpful, thanks!