Re: PostgreSQL 9.2 and PGBOUNCER

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
>
>
>