Thread: could not connect to server
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.
$ 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
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 .pgpassI 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/postg
# - Connection Settings -
listen_addresses = '*'
port = 5432
/etc/postgresql/9.5/main/pg_hb
# Database administrative login by Unix domain socket
local all postgres
# TYPE DATABASE USER ADDRESS METHOD
# "local" is for Unix domain socket connections only
local all all
# 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./etc/postgresql/9.5/main/postg
# - Connection Settings -
listen_addresses = '*'
port = 5432
/etc/postgresql/9.5/main/pg_hb
# Database administrative login by Unix domain socket
local all postgres
# TYPE DATABASE USER ADDRESS METHOD
# "local" is for Unix domain socket connections only
local all all
# IPv4 local connections:
host all all 127.0.0.1/32 md5
I really appreciate any guidance.
Antônio Olinto
São Paulo, Brasil
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
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
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).
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.<http://127.0.0.1/32> md5
~$ 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
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
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
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: 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