Thread: 'pgaccess' - Can't Connect to Backend

Configuration
-------------
  MkLinux
  postgresql-6.3.2
  pgaccess-{0.86,0.91,0.93}


Problem
-------
I can connect to the backend via 'psql' but not 'pgaccess'.
Both 'pgaccess' and 'postgreSQL' are running on the same machine.

Pgaccess gives the message:

    Error connecting database
    Connection to database failed
    connectDB() failed: Is the postmaster running
    and accepting TCP/IP (with -i) connections
    at 'localhost' on port '5432'?


Observations
------------
Here's the relevant process info (in particular, note the '-i'
option):

    /usr/bin/postmaster -i -S -D/var/lib/pgsql
    /usr/bin/postgres -p -Q -P5 -v 65536 collection

Also, the following 'netstat' output (corresponding to the 'psql'
connection) shows that port '5432' is, in fact, being used:

    Proto Recv-Q Send-Q Local Address           Foreign Address         State
    tcp        0      2 cs:3123                 localhost:5432          CLOSE


I've used 'pgaccess' successfully before, but I must have changed
something (permissions, etc.) during an OS upgrade.

Any help will be appreciated!

-Prof Kenneth H Jacker       khj@cs.appstate.edu
-Computer Science Dept       www.cs.appstate.edu/~khj
-Appalachian State Univ
-Boone, NC  28608  USA

Kenneth Jacker wrote:
>
> Configuration
> -------------
>   MkLinux
>   postgresql-6.3.2
>   pgaccess-{0.86,0.91,0.93}

Pay attention to the fact that pgaccess 0.93 is working fine with
PostgreSQL 6.4.x due to some changes in libpgtcl
PgAccess can work with older PostgreSQL versions but there is a single
line that have to be changed.

Upgrading to 6.4.1 might be a good solution!


> I can connect to the backend via 'psql' but not 'pgaccess'.
> Both 'pgaccess' and 'postgreSQL' are running on the same machine.
> Pgaccess gives the message:
>         Error connecting database
>         Connection to database failed
>         connectDB() failed: Is the postmaster running
>         and accepting TCP/IP (with -i) connections
>         at 'localhost' on port '5432'?
>
> Observations
> ------------
> Here's the relevant process info (in particular, note the '-i'
> option):
>
>         /usr/bin/postmaster -i -S -D/var/lib/pgsql
>         /usr/bin/postgres -p -Q -P5 -v 65536 collection

STRANGE !!! VERY STRANGE !!!

Let's find first if libpgtcl is working good!

enter the following commands in a shell ($ is the prompter)
collection is the database name, people a table name

$ tclsh
tcl> load libpgtcl.so
tcl> set dbc [pg_connect collection]
tcl> pg_select $dbc "select * from people" tup {puts [array get tup]}
tcl> pg_disconnect $dbc
tcl> exit

If something from above does not work, that means that you have a
problem with libpgtcl, not with pgaccess !
Send me the error message!

send me please also the result of

grep -v "^#" /where_it_is/pg_hba.conf

Constantin Teodorescu
FLEX Consulting Braila, ROMANIA

Kenneth Jacker <khj@cs.appstate.edu> writes:
> I can connect to the backend via 'psql' but not 'pgaccess'.
> Both 'pgaccess' and 'postgreSQL' are running on the same machine.

Probably, access to the backend works OK via a Unix socket, but does
not work via TCP/IP.  Try
    psql -h localhost your-database-name
and I'll bet it fails just like pgaccess does.  (Unless Constantin
has changed something recently, pgaccess always tries to connect
via TCP/IP, which is why it fails even for the default case.)

> Pgaccess gives the message:
>     Error connecting database
>     Connection to database failed
>     connectDB() failed: Is the postmaster running
>     and accepting TCP/IP (with -i) connections
>     at 'localhost' on port '5432'?

My first thought was that your pg_hba.conf file is messed up,
but on second thought I don't like that theory.  The particular
error you quote only comes out if libpq's connect() call fails,
and that would mean failure even to contact the postmaster ---
authorization checks don't happen until later.

> Here's the relevant process info (in particular, note the '-i'
> option):
>     /usr/bin/postmaster -i -S -D/var/lib/pgsql

OK, silly mistake #1 is eliminated.

> Also, the following 'netstat' output (corresponding to the 'psql'
> connection) shows that port '5432' is, in fact, being used:

>   Proto Recv-Q Send-Q Local Address           Foreign Address         State
>   tcp        0      2 cs:3123                 localhost:5432          CLOSE

I think this line has nothing to do with your psql session --- unless
you ran psql with -h switch or PGHOST environment setting, it would've
connected via Unix socket and not via TCP at all.  (You might find its
connection in the "Unix domain" part of netstat's listing.)  My guess
is that this line reflects the failed attempt to connect from pgaccess.
That guess is reinforced by the fact that the socket state is shown
as CLOSE (ie, connection in process of closing down) not ESTABLISHED.

So now we have to figure out how it's getting that way.  Does anything
get added to the postmaster's log file (typically ~postgres/server.log)
when the connect attempt fails?  I'd look for lines mentioning
StreamConnection in particular.

Beyond that I'm fresh out of ideas.  It's clearly an oversight that
libpq doesn't report the kernel error code when the connect() call fails
--- that would be very useful to know right now ...  I'd suggest
modifying src/interfaces/libpq/fe-connect.c so that strerror(errno) is
included in the error report when connect() fails inside the connectDB()
routine.  (Make it look more like the handling of failures from the
other kernel calls nearby...)  Armed with that, we can try to make a
better guess about the problem.

            regards, tom lane