Re: PostgreSQL 9.2 and PGBOUNCER

From: "ac(at)hsk(dot)hk" <ac(at)hsk(dot)hk>
To: Birta Levente <blevi(dot)linux(at)gmail(dot)com>
Cc: "ac(at)hsk(dot)hk" <ac(at)hsk(dot)hk>, pgsql-general(at)postgresql(dot)org
Subject: Re: PostgreSQL 9.2 and PGBOUNCER
Date: 2013-01-08 08:21:04
Message-ID: 15399726-0A20-4882-8DDB-4D22E61C6ED5@hsk.hk
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

Hi,

Thanks for your reply.

Below is the pgbouncer.ini
===
[databases]
; use db:postgres for connection testing
postgres = port=5432 dbname=postgres

[pgbouncer]
listen_port = 6543
listen_addr = 127.0.0.1
admin_users = postgres
auth_type = md5
auth_file = 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?

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

In response to

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Birta Levente 2013-01-08 09:04:14 Re: PostgreSQL 9.2 and PGBOUNCER
Previous Message Birta Levente 2013-01-08 07:13:46 Re: PostgreSQL 9.2 and PGBOUNCER