Re: PostgreSQL 9.2 and PGBOUNCER

!! Please do not top post !!

On 08/01/2013 10:21, ac(at)hsk(dot)hk wrote:
> Hi,
>
> Thanks for your reply.
>
> Below is the pgbouncer.ini
> ===
> [databases]
> ; use db:postgres for connection testing
> postgres = port=5432 dbname=postgres
>
As I said before, with this you can only connect database postgres.

Maybe you want:
* = port=5432
With these you can connect all databases in your cluster.

> [pgbouncer]
> listen_port = 6543
> listen_addr = 127.0.0.1
watch this: maybe you want to connect from the outside: listen_addr = *

> admin_users = postgres
> auth_type = md5
> auth_file = users.txt
I don't know if on ubuntu need or not specify the full path to the
users.txt.

> server_reset_query = DISCARD ALL;
> ignore_startup_parameters = application_name
> logfile = pgbouncer.log
> pidfile = pgbouncer.pid
> pool_mode = session
> default_pool_size = 20
> log_pooler_errors = 0
> ===
>
> If I want to test the connection from my application to DB postgres via pgbouncer, which port should I use, post 6543 or port 5432?

The pooler is between client and postgresql server. So, if you want to
connect through the pooler you need to connect on 6543.

But I think you really need to read some documentation about pgbouncer.

>
> Thanks
>
>
>
>
> On 8 Jan 2013, at 3:13 PM, Birta Levente wrote:
>
>> On 08/01/2013 08:40, ac(at)hsk(dot)hk wrote:
>>> Hi,
>>>
>>>
>>> My PostgreSQL is 9.2.1 in Ubuntu 12.04, I need to set up a connection
>>> pool by using pgbouncer.
>>>
>>>
>>> I used "apt-get install pgbouncer", after configuring it, I can now
>>> connect to pgbouncer and can use all pgbouncer SHOW commands, however:
>>>
>>> Q1) Is version "1.4.2/bouncer" the right one for PostgreSQL 9.2?
>>> $ psql -U postgres -p 6543 pgbouncer
>>> psql.bin (9.2.1, server 1.4.2/bouncer)
>>> WARNING: psql.bin version 9.2, server version 1.4.
>>> Some psql features might not work.
>>> Type "help" for help.
>>> No entry for terminal type "xterm-color";
>>> using dumb terminal settings.
>>>
>>
>> I think the best is the latest: v1.5.4
>>
>>
>>>
>>> Q2) if I try the general psql commands, I got errors
>>> for example:
>>> $ psql -U postgres -p 6543 pgbouncer
>>> pgbouncer=# \l
>>> ERROR: invalid command 'SELECT d.datname as "Name",
>>> pg_catalog.pg_get_userbyid(d.datdba) as "Owner",
>>> pg_catalog.pg_encoding_to_char(d.encoding) as "Encoding",
>>> pg_catalog.array_to_string(d.datacl, '\n') AS "Access privileges"
>>> FROM pg_catalog.pg_database d
>>> ORDER BY 1;', use SHOW HELP;
>>
>> If you connect to the pgbouncer, which is special, not really a database, you only can show pgbouncer stats and change pgbouncer settings
>>
>> # SHOW help;
>> show you available commands.
>>
>>>
>>>
>>> Q3) I can connect to pgbouncer by using "psql -U postgres -p 6543
>>> pgbouncer", however if I try to use "-d postgres", I got error:
>>> $ psql -U postgres -p 6543 pgbouncer -d postgres
>>> psql.bin: warning: extra command-line argument "pgbouncer" ignored
>>> psql.bin: ERROR: no working server connection
>>
>> You need to show us pgbouncer.ini.
>> And maybe read this before
>> http://get.enterprisedb.com/docs/Tutorial_All_PPSS_pgBouncer.pdf
>>
>>
>>>
>>> Q4) Which port should I use in my application in order to connect to
>>> PostgreSQL via pgbouncer, port 6543 or port 5432?
>>> the port value in pgbounce.ini:
>>> postgres = port=5432 dbname=postgres
>>> listen_port = 6543
>>> the port value in postgresql.conf:
>>> port=5432
>>
>> With this you make connection pooling only for the database named postgres. This is what you really want?
>>
>> Read this minihowto too:
>> http://filip.rembialkowski.net/pgbouncer-mini-howto-benchmark/
>>
>>>
>>>
>>>
>>> Please help!
>>> Thanks
>>>
>>>
>>>
>>
>>
>>
>> --
>> Sent via pgsql-general mailing list (pgsql-general(at)postgresql(dot)org)
>> To make changes to your subscription:
>> http://www.postgresql.org/mailpref/pgsql-general
>