Thread: could not connect to server

could not connect to server

From
Antonio Silva
Date:
Hello

I'm using postgres 9.5 on Ubuntu 16.04. I have worked with pg in my notebook for almost a month but today postgres did not connected. I tryed both PGAdmin and psql.

I also tried to login using my own user and postgres but I received the messages:

Server doesn't listen
The server doesn't accept connections: the connection library reports
could not connect to server: Conexão recusada (connection refused) Is the server running on host "127.0.0.1" and accepting TCP/IP connections on port 5432?

~$ psql database
WARNING: password file "/home/antonio/.pgpass" has group or world access; permissions should be u=rw (0600) or less
psql: could not connect to server: Arquivo ou diretório não encontrado (File or directory not found)
    Is the server running locally and accepting
    connections on Unix domain socket "/var/run/postgresql/.s.PGSQL.5432"?

$ pg_lsclusters
Ver Cluster Port Status Owner    Data directory               Log file
9.5 main    5432 down   postgres /var/lib/postgresql/9.5/main /var/log/postgresql/postgresql-9.5-main.log

It was the first time I received the warning "password file "/home/antonio/.pgpass" has group or world access; permissions should be u=rw (0600) or less" and I fixed it:
-rw-------  1 antonio antonio      137 Set 30 18:50 .pgpass

I had edited postgresql.conf and pg_hba.conf but this morning they were in their original configuration. So I edited again to:

/etc/postgresql/9.5/main/postgresql.conf

# - Connection Settings -
listen_addresses = '*'
port = 5432

/etc/postgresql/9.5/main/pg_hba.conf

# Database administrative login by Unix domain socket
local   all             postgres                                md5
# TYPE  DATABASE        USER            ADDRESS                 METHOD
# "local" is for Unix domain socket connections only
local   all             all                                     md5
# IPv4 local connections:
host    all             all             127.0.0.1/32            md5

I also restarted the server (/etc/init.d/postgresql restart) and the computer.

I really appreciate any guidance.

All the best,

Antônio Olinto
São Paulo, Brasil

Re: could not connect to server

From
Adrian Klaver
Date:
On 10/15/2016 10:11 AM, Antonio Silva wrote:
> Hello
>
> I'm using postgres 9.5 on Ubuntu 16.04. I have worked with pg in my
> notebook for almost a month but today postgres did not connected. I
> tryed both PGAdmin and psql.

Did you do an apt-get upgrade in the interim?

>
> I also tried to login using my own user and postgres but I received the
> messages:
>
> Server doesn't listen
> The server doesn't accept connections: the connection library reports
> could not connect to server: Conexão recusada (connection refused) Is
> the server running on host "127.0.0.1" and accepting TCP/IP connections
> on port 5432?

This is indication that Postgres server is not running which is
confirmed by your pg_lsclusters output below.

>
> ~$ psql database
> WARNING: password file "/home/antonio/.pgpass" has group or world
> access; permissions should be u=rw (0600) or less
> psql: could not connect to server: Arquivo ou diretório não encontrado
> (File or directory not found)
>     Is the server running locally and accepting
>     connections on Unix domain socket "/var/run/postgresql/.s.PGSQL.5432"?
>
> $ pg_lsclusters
> Ver Cluster Port Status Owner    Data directory               Log file
> 9.5 main    5432 down   postgres /var/lib/postgresql/9.5/main
> /var/log/postgresql/postgresql-9.5-main.log
>
> It was the first time I received the warning "password file
> "/home/antonio/.pgpass" has group or world access; permissions should be
> u=rw (0600) or less" and I fixed it:
> -rw-------  1 antonio antonio      137 Set 30 18:50 .pgpass
>
> I had edited postgresql.conf and pg_hba.conf but this morning they were
> in their original configuration. So I edited again to:
>
> /etc/postgresql/9.5/main/postgresql.conf
>
> # - Connection Settings -
> listen_addresses = '*'
> port = 5432
>
> /etc/postgresql/9.5/main/pg_hba.conf
>
> # Database administrative login by Unix domain socket
> local   all             postgres                                md5
> # TYPE  DATABASE        USER            ADDRESS                 METHOD
> # "local" is for Unix domain socket connections only
> local   all             all                                     md5
> # IPv4 local connections:
> host    all             all             127.0.0.1/32
> <http://127.0.0.1/32>            md5
>
> I also restarted the server (/etc/init.d/postgresql restart) and the
> computer.

What does  pg_lsclusters show?

How did you try to connect?

>
> I really appreciate any guidance.
>
> All the best,
>
> Antônio Olinto
> São Paulo, Brasil


--
Adrian Klaver
adrian.klaver@aklaver.com


Re: could not connect to server

From
Tom Lane
Date:
Antonio Silva <aolinto.lst@gmail.com> writes:
> $ pg_lsclusters
> Ver Cluster Port Status Owner    Data directory               Log file
> 9.5 main    5432 down   postgres /var/lib/postgresql/9.5/main
> /var/log/postgresql/postgresql-9.5-main.log

Looking into the mentioned log file might offer some insight as to
why the server's not running.

            regards, tom lane


Re: could not connect to server

From
Antonio Silva
Date:
Dear Adrian and Tom, thanks for your attention.

I did an upgrade on Friday. I use to do this every week.

Log file informed:

2016-10-15 06:15:20 BRT [995-1] FATAL:  data directory "/var/lib/postgresql/9.5/main" has group or world access
2016-10-15 06:15:20 BRT [995-2] DETAIL:  Permissions should be u=rwx (0700).

The problem with permissions was already indicated to  .pgpass

So I did a "sudo chmod 0700 main" at /var/lib/postgresql/9.5/ directory and I could connect postgres again!

A cannot understand what happened. How an upgrade changes the permissions of files an folders? I work with more two desktops with the same framework (I7, 8 GB, Ubuntu 16.04, PostgreSQL, ...) and it did not happen.

Well, but I have my database available now. Thanks.

Best regards,

Antônio Olinto

2016-10-15 14:28 GMT-03:00 Adrian Klaver <adrian.klaver@aklaver.com>:
On 10/15/2016 10:11 AM, Antonio Silva wrote:
Hello

I'm using postgres 9.5 on Ubuntu 16.04. I have worked with pg in my
notebook for almost a month but today postgres did not connected. I
tryed both PGAdmin and psql.

Did you do an apt-get upgrade in the interim?


I also tried to login using my own user and postgres but I received the
messages:

Server doesn't listen
The server doesn't accept connections: the connection library reports
could not connect to server: Conexão recusada (connection refused) Is
the server running on host "127.0.0.1" and accepting TCP/IP connections
on port 5432?

This is indication that Postgres server is not running which is confirmed by your pg_lsclusters output below.


~$ psql database
WARNING: password file "/home/antonio/.pgpass" has group or world
access; permissions should be u=rw (0600) or less
psql: could not connect to server: Arquivo ou diretório não encontrado
(File or directory not found)
    Is the server running locally and accepting
    connections on Unix domain socket "/var/run/postgresql/.s.PGSQL.5432"?

$ pg_lsclusters
Ver Cluster Port Status Owner    Data directory               Log file
9.5 main    5432 down   postgres /var/lib/postgresql/9.5/main
/var/log/postgresql/postgresql-9.5-main.log

It was the first time I received the warning "password file
"/home/antonio/.pgpass" has group or world access; permissions should be
u=rw (0600) or less" and I fixed it:
-rw-------  1 antonio antonio      137 Set 30 18:50 .pgpass

I had edited postgresql.conf and pg_hba.conf but this morning they were
in their original configuration. So I edited again to:

/etc/postgresql/9.5/main/postgresql.conf

# - Connection Settings -
listen_addresses = '*'
port = 5432

/etc/postgresql/9.5/main/pg_hba.conf

# Database administrative login by Unix domain socket
local   all             postgres                                md5
# TYPE  DATABASE        USER            ADDRESS                 METHOD
# "local" is for Unix domain socket connections only
local   all             all                                     md5
# IPv4 local connections:
host    all             all             127.0.0.1/32
<http://127.0.0.1/32>            md5

I also restarted the server (/etc/init.d/postgresql restart) and the
computer.

What does  pg_lsclusters show?

How did you try to connect?



I really appreciate any guidance.

All the best,

Antônio Olinto
São Paulo, Brasil


--
Adrian Klaver
adrian.klaver@aklaver.com

Re: could not connect to server

From
Adrian Klaver
Date:
On 10/15/2016 09:09 PM, Antonio Silva wrote:
> Dear Adrian and Tom, thanks for your attention.
>
> I did an upgrade on Friday. I use to do this every week.
>
> Log file informed:
>
> 2016-10-15 06:15:20 BRT [995-1] FATAL:  data directory
> "/var/lib/postgresql/9.5/main" has group or world access
> 2016-10-15 06:15:20 BRT [995-2] DETAIL:  Permissions should be u=rwx (0700).
>
> The problem with permissions was already indicated to  .pgpass
>
> So I did a "sudo chmod 0700 main" at /var/lib/postgresql/9.5/ directory
> and I could connect postgres again!
>
> A cannot understand what happened. How an upgrade changes the
> permissions of files an folders? I work with more two desktops with the
> same framework (I7, 8 GB, Ubuntu 16.04, PostgreSQL, ...) and it did not
> happen.

I would look in the the /var/log/apt/history.log.* files that cover
Friday and see what upgraded. That may offer a clue.

>
> Well, but I have my database available now. Thanks.
>
> Best regards,
>
> Antônio Olinto
>
> 2016-10-15 14:28 GMT-03:00 Adrian Klaver <adrian.klaver@aklaver.com
> <mailto:adrian.klaver@aklaver.com>>:
>
>     On 10/15/2016 10:11 AM, Antonio Silva wrote:
>
>         Hello
>
>         I'm using postgres 9.5 on Ubuntu 16.04. I have worked with pg in my
>         notebook for almost a month but today postgres did not connected. I
>         tryed both PGAdmin and psql.
>
>
>     Did you do an apt-get upgrade in the interim?
>
>
>         I also tried to login using my own user and postgres but I
>         received the
>         messages:
>
>         Server doesn't listen
>         The server doesn't accept connections: the connection library
>         reports
>         could not connect to server: Conexão recusada (connection
>         refused) Is
>         the server running on host "127.0.0.1" and accepting TCP/IP
>         connections
>         on port 5432?
>
>



--
Adrian Klaver
adrian.klaver@aklaver.com


Re: could not connect to server

From
Adrian Klaver
Date:
On 10/15/2016 09:09 PM, Antonio Silva wrote:
> Dear Adrian and Tom, thanks for your attention.
>
> I did an upgrade on Friday. I use to do this every week.
>
> Log file informed:
>
> 2016-10-15 06:15:20 BRT [995-1] FATAL:  data directory
> "/var/lib/postgresql/9.5/main" has group or world access
> 2016-10-15 06:15:20 BRT [995-2] DETAIL:  Permissions should be u=rwx (0700).
>
> The problem with permissions was already indicated to  .pgpass
>
> So I did a "sudo chmod 0700 main" at /var/lib/postgresql/9.5/ directory
> and I could connect postgres again!
>
> A cannot understand what happened. How an upgrade changes the
> permissions of files an folders? I work with more two desktops with the
> same framework (I7, 8 GB, Ubuntu 16.04, PostgreSQL, ...) and it did not
> happen.

In my previous post I should have also mentioned checking syslog for the
period in question.

>
> Well, but I have my database available now. Thanks.
>
> Best regards,
>
> Antônio Olinto


--
Adrian Klaver
adrian.klaver@aklaver.com


Re: could not connect to server

From
Christoph Berg
Date:
Re: Antonio Silva 2016-10-16 <CAE8g1gO_0Ym8Y0yEQ41Hi-T_GGUoEDNAFYF81SPgqrusODNFaQ@mail.gmail.com>
> 2016-10-15 06:15:20 BRT [995-1] FATAL:  data directory
> "/var/lib/postgresql/9.5/main" has group or world access
> 2016-10-15 06:15:20 BRT [995-2] DETAIL:  Permissions should be u=rwx (0700).
>
> The problem with permissions was already indicated to  .pgpass

Hi,

upgrades do not modify data directory permissions. Something or
someone must have modified that manually. And given it also happened
to your home directory, that might have affected the whole system...

Christoph