Thread: Please Help me with connecting my PostgreSQL
It's the first time that I can't find the answer about some tech problems
Here's my problems:
>>conn=psycopg2.connect(database="mydb", user="postgres", password="123",port=5433)
Traceback (most recent call last):
File "<stdin>", line 1, in <module>
psycopg2.OperationalError: 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"?
1.my postgreSQL is running
2.my listeningport is 5432 for sure
3.
root@lanston-laptop:~# psql -l
Password:
List of databases
Name | Owner | Encoding | Collation | Ctype | Access privileges
---------------+----------+----------+------------+------------+-----------------------
checkdatabase | postgres | UTF8 | en_US.utf8 | en_US.utf8 |
mydb | postgres | UTF8 | en_US.utf8 | en_US.utf8 |
postgres | postgres | UTF8 | en_US.utf8 | en_US.utf8 |
template0 | postgres | UTF8 | en_US.utf8 | en_US.utf8 | =c/postgres +
| | | | | postgres=CTc/postgres
template1 | postgres | UTF8 | en_US.utf8 | en_US.utf8 | =c/postgres +
| | | | | postgres=CTc/postgres
(5 rows)
This is the problem that so many people asked,but there's no answers.
Thanks a lot
> >>conn=psycopg2.connect(database="mydb", user="postgres", > password="123",port=5433) ... > 2.my listeningport is 5432 for sure That does not help if you ask to connect on port 5433. Karsten -- GMX DSL Doppel-Flat ab 19,99 Euro/mtl.! Jetzt mit gratis Handy-Flat! http://portal.gmx.net/de/go/dsl
On Thursday, March 31, 2011 5:54:34 am Lasntonpeng wrote: > It's the first time that I can't find the answer about some tech problems > > Here's my problems: > >>conn=psycopg2.connect(database="mydb", user="postgres", > > password="123",port=5433) > > Traceback (most recent call last): > File "<stdin>", line 1, in <module> > psycopg2.OperationalError: 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"? Two problems. The first is the one Karsten already mentioned about the difference on ports. Second you have not specified a host so Psycopg is trying the local socket. If you are trying to connect from a machine other than the other one with the Postgres server it is not going to find the socket. Also the socket connection may have a restriction in pg_hba.conf that would prevent a connection. > > > > 1.my postgreSQL is running > 2.my listeningport is 5432 for sure > 3. > root@lanston-laptop:~# psql -l > Password: > List of databases > Name | Owner | Encoding | Collation | Ctype | Access > privileges > ---------------+----------+----------+------------+------------+----------- > ------------ checkdatabase | postgres | UTF8 | en_US.utf8 | en_US.utf8 > | > mydb | postgres | UTF8 | en_US.utf8 | en_US.utf8 | > postgres | postgres | UTF8 | en_US.utf8 | en_US.utf8 | > template0 | postgres | UTF8 | en_US.utf8 | en_US.utf8 | > =c/postgres + > > postgres=CTc/postgres > template1 | postgres | UTF8 | en_US.utf8 | en_US.utf8 | > =c/postgres + > > postgres=CTc/postgres > (5 rows) > > > *This is the problem that so many people asked,but there's no answers.* > *Thanks a lot* -- Adrian Klaver adrian.klaver@gmail.com
On Thu, Mar 31, 2011 at 2:33 PM, Adrian Klaver <adrian.klaver@gmail.com> wrote: > On Thursday, March 31, 2011 5:54:34 am Lasntonpeng wrote: >> It's the first time that I can't find the answer about some tech problems >> >> Here's my problems: >> >>conn=psycopg2.connect(database="mydb", user="postgres", >> >> password="123",port=5433) >> >> Traceback (most recent call last): >> File "<stdin>", line 1, in <module> >> psycopg2.OperationalError: 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"? > > Two problems. The first is the one Karsten already mentioned about the difference > on ports. Second you have not specified a host so Psycopg is trying the local > socket. If you are trying to connect from a machine other than the other one > with the Postgres server it is not going to find the socket. Also the socket > connection may have a restriction in pg_hba.conf that would prevent a > connection. To connect to a database on the local machine, the quick answer is: add host=127.0.0.1 to the connection string, which makes it connect to the network socket instead of the unix socket. And I mean "127.0.0.1", not "localhost". All the gory details are in the pg_hba.conf documentation. -- Daniele
On Thursday, March 31, 2011 6:42:35 am Daniele Varrazzo wrote: > > To connect to a database on the local machine, the quick answer is: > add host=127.0.0.1 to the connection string, which makes it connect to > the network socket instead of the unix socket. And I mean "127.0.0.1", > not "localhost". localhost works also:) No host specified: [unknown]-2011-03-31 07:00:26.140 PDT-0LOG: connection received: host=[local] aklaver-2011-03-31 07:00:26.142 PDT-0LOG: connection authorized: user=aklaver database=test localhost specified: [unknown]-2011-03-31 07:00:52.809 PDT-0LOG: connection received: host=127.0.0.1 port=51196 aklaver-2011-03-31 07:00:52.864 PDT-0LOG: connection authorized: user=aklaver database=test 127.0.0.1 specified: [unknown]-2011-03-31 07:01:29.853 PDT-0LOG: connection received: host=127.0.0.1 port=51197 aklaver-2011-03-31 07:01:29.922 PDT-0LOG: connection authorized: user=aklaver database=test > > All the gory details are in the pg_hba.conf documentation. > > -- Daniele -- Adrian Klaver adrian.klaver@gmail.com
On Thursday, March 31, 2011 7:12:22 am David Blewett wrote: > On Thu, Mar 31, 2011 at 10:07 AM, Adrian Klaver <adrian.klaver@gmail.com> wrote: > > On Thursday, March 31, 2011 6:42:35 am Daniele Varrazzo wrote: > >> To connect to a database on the local machine, the quick answer is: > >> add host=127.0.0.1 to the connection string, which makes it connect to > >> the network socket instead of the unix socket. And I mean "127.0.0.1", > >> not "localhost". > > > > localhost works also:) > > > > No host specified: > > [unknown]-2011-03-31 07:00:26.140 PDT-0LOG: connection received: > > host=[local] aklaver-2011-03-31 07:00:26.142 PDT-0LOG: connection > > authorized: user=aklaver database=test > > The host=[local] bit means it accepted the connection over the UNIX > socket. This won't work if you are trying to connect remotely. Just to be clear I am not the OP:) -- Adrian Klaver adrian.klaver@gmail.com
On Thu, Mar 31, 2011 at 10:07 AM, Adrian Klaver <adrian.klaver@gmail.com> wrote: > On Thursday, March 31, 2011 6:42:35 am Daniele Varrazzo wrote: > >> >> To connect to a database on the local machine, the quick answer is: >> add host=127.0.0.1 to the connection string, which makes it connect to >> the network socket instead of the unix socket. And I mean "127.0.0.1", >> not "localhost". > > localhost works also:) > > No host specified: > [unknown]-2011-03-31 07:00:26.140 PDT-0LOG: connection received: host=[local] > aklaver-2011-03-31 07:00:26.142 PDT-0LOG: connection authorized: user=aklaver > database=test The host=[local] bit means it accepted the connection over the UNIX socket. This won't work if you are trying to connect remotely. -- Thanks, David Blewett
On Thu, Mar 31, 2011 at 3:07 PM, Adrian Klaver <adrian.klaver@gmail.com> wrote: > On Thursday, March 31, 2011 6:42:35 am Daniele Varrazzo wrote: > >> >> To connect to a database on the local machine, the quick answer is: >> add host=127.0.0.1 to the connection string, which makes it connect to >> the network socket instead of the unix socket. And I mean "127.0.0.1", >> not "localhost". > > localhost works also:) Sometimes I've been bitten by a badly configured /etc/hosts and/or pg_hba.conf, and if localhost doesn't resolve to 127.0.0.1 in both directions (not granted when both IPv4 and IPv6 enabled) the connection will fail, or the wrong pg_hba rule may be used. Of course it doesn't happen on a well configured server, but because the OP is in "shotgun debugging" mode, as the 5433 typo shows, avoiding the use of a name to be solved is an element less that may fail. More details are in the box at http://developer.postgresql.org/pgdocs/postgres/auth-pg-hba-conf.html#AEN30140 -- Daniele
On 03/31/2011 07:56 AM, Daniele Varrazzo wrote: > On Thu, Mar 31, 2011 at 3:07 PM, Adrian Klaver<adrian.klaver@gmail.com> wrote: >> On Thursday, March 31, 2011 6:42:35 am Daniele Varrazzo wrote: >> >>> >>> To connect to a database on the local machine, the quick answer is: >>> add host=127.0.0.1 to the connection string, which makes it connect to >>> the network socket instead of the unix socket. And I mean "127.0.0.1", >>> not "localhost". >> >> localhost works also:) > > Sometimes I've been bitten by a badly configured /etc/hosts and/or > pg_hba.conf, and if localhost doesn't resolve to 127.0.0.1 in both > directions (not granted when both IPv4 and IPv6 enabled) the > connection will fail, or the wrong pg_hba rule may be used. Got you, so it not that localhost is not allowed, but that an explicit IP address eliminates a possible bug. > > Of course it doesn't happen on a well configured server, but because > the OP is in "shotgun debugging" mode, as the 5433 typo shows, > avoiding the use of a name to be solved is an element less that may > fail. > > More details are in the box at > http://developer.postgresql.org/pgdocs/postgres/auth-pg-hba-conf.html#AEN30140 > > -- Daniele -- Adrian Klaver adrian.klaver@gmail.com