Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I've got Postgres 9.2 and PgBouncer (The version that came with the 'stack builder'). I have a .net application connecting via Npgsql version 2.0.12 on a win7 machine.

My application can happily connect straight to the postgres server, but always fails to connect via the pgbouncer.

My connection string to npgsql (after some substitution is)

Sslmode=Prefer;Timeout=40;Server={0};Port={3};User Id={4};Password={1};Database={2};Pooling=False;

(I've also tried to connect to PgBounce with Protocol=3 explicitly and that didn't work.)

The database line from pgBounce ini is:

[databases]
something = host=localhost port=5433 dbname=somethingelse user=someone password=aaa

and in the userlist.txt I have "someone" "aaa" as required.

When my app runs the npgsql says "connection forcibly closed" and the pgbouncer error log says:

2013-02-06 20:16:57.594 2444 LOG File descriptor limit: -1 (H:-1), max_client_conn: 1000, max fds possible: 1030
2013-02-06 20:16:57.649 2444 LOG listening on ::/6433
2013-02-06 20:16:57.657 2444 LOG listening on 0.0.0.0:6433
2013-02-06 20:16:57.659 2444 LOG process up: pgbouncer 1.5.2, libevent 2.0.19-stable (win32), adns: evdns2
2013-02-06 20:17:22.066 2444 LOG C-01819410: something/someone@fe80::997b:396e:eacc:dd2b:3013 login attempt: db=somethingelse user=someone
2013-02-06 20:17:22.130 2444 LOG S-01836648: something/someone@127.0.0.1:5433 new connection to server
2013-02-06 20:17:24.069 2444 LOG C-01819410: something/someone@fe80::997b:396e:eacc:dd2b:3013 closing because: client close request (age=2)
2013-02-06 20:17:26.319 2444 LOG C-01819410: (nodb)/(nouser)@fe80::997b:396e:eacc:dd2b:3023 closing because: bad packet header (age=0)
2013-02-06 20:17:26.322 2444 WARNING C-01819410: (nodb)/(nouser)@fe80::997b:396e:eacc:dd2b:3023 Pooler Error: bad packet header

The npgsql exception says: (from a log file in app, the topmost exception follows down the nested exceptions):

Exception message:  Unable to read data from the transport connection: An established connection was aborted by the software in your host machine.
Exception source:  Npgsql
Exception stacktrace:     at Npgsql.NpgsqlClosedState.Open(NpgsqlConnector context) in Z:\Projectdirectory\Npgsql2.0.12.0.src\src\Npgsql\NpgsqlClosedState.cs:line 232
at Npgsql.NpgsqlConnector.Open() in Z:\Projectdirectory\Npgsql2.0.12.0.src\src\Npgsql\NpgsqlConnector.cs:line 695
at Npgsql.NpgsqlConnectorPool.GetNonPooledConnector(NpgsqlConnection Connection) in Z:\Projectdirectory\Npgsql2.0.12.0.src\src\Npgsql\NpgsqlConnectorPool.cs:line 346
at Npgsql.NpgsqlConnectorPool.RequestConnector(NpgsqlConnection Connection) in Z:\Projectdirectory\Npgsql2.0.12.0.src\src\Npgsql\NpgsqlConnectorPool.cs:line 185
at Npgsql.NpgsqlConnection.Open() in Z:\Projectdirectory\Npgsql2.0.12.0.src\src\Npgsql\NpgsqlConnection.cs:line 543
at *snip* the thing in my code that goes and opens the connection *snip*
-----------------------------------------------------
Exception message:  Unable to read data from the transport connection: An established connection was aborted by the software in your host machine.
Exception source:  System
Exception stacktrace:     at System.Net.Sockets.NetworkStream.Read(Byte[] buffer, Int32 offset, Int32 size)
at System.IO.Stream.ReadByte()
at Npgsql.NpgsqlClosedState.Open(NpgsqlConnector context) in Z:\Projectdirectory\Npgsql2.0.12.0.src\src\Npgsql\NpgsqlClosedState.cs:line 184
-----------------------------------------------------
Exception message:  An established connection was aborted by the software in your host machine
Exception source:  System
Exception stacktrace:     at System.Net.Sockets.Socket.Receive(Byte[] buffer, Int32 offset, Int32 size, SocketFlags socketFlags)
at System.Net.Sockets.NetworkStream.Read(Byte[] buffer, Int32 offset, Int32 size)

Also, window firewall is off and anti virus I turned off temporarily.

The HBA conf file for postgres has:

host    all             all             127.0.0.1/32            md5
host    all             all           192.168.0.1/16            md5
# IPv6 local connections:
host    all             all             ::1/128                 md5
host    all             all         fe80::1/16                  md5

I've googled around and found no solution. Help! Thanks.

Peter.

share|improve this question
add comment

2 Answers

The userlist.txt file contains the accounts for connecting from your client to PgBouncer. The [databases] section contains the login information for connection from PgBouncer to the backend PostgreSQL server. You appear to be mixing these two up.

When you connect from your client to PgBouncer you need to provide a user name and password from userlist.txt and a database name of something in this case.

share|improve this answer
    
I was being lazy and using the same username for pgbouncer and the database. Likewise the same password. Would this be a problem? –  Peter pete Feb 5 '13 at 21:45
    
@Peter pete, what's the pgbouncer log says? –  francs Feb 6 '13 at 5:48
    
@francs: I've updated the original question with additional information: * hba conf file * a full log file for pgbouncer from service start to application connect attempt. * Exception trace of npgsql –  Peter pete Feb 6 '13 at 9:27
    
@Peterpete - omg, thanks. Finding this was such a help - been working on this for 3 hours :/ Documentation for pgbouncer is crap. I wish that Postgres shipped with a connection pooler. –  orokusaki Jun 7 '13 at 5:15
add comment
up vote 3 down vote accepted

Ah! Darn! I found the answer.

My connection string had SslMode=Prefer

I removed this, and voila! the pgBouncer worked!! My connection string is now:

Timeout=40;Server={0};Port={3};User Id=someone;Password={1};Database={2};Pooling=False;Protocol=3;

Thanks everyone for your time and effort.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.