Drop Down MenusCSS Drop Down MenuPure CSS Dropdown Menu

PostgreSQL PgBouncer Installation,Monitoring,Prerequest And Postrequest

Table of Contents
1.What is The PgBouncer And  Pooling Modes
2.Installation Of PgBouncer
3.Make Directories
4.Make Sure pgbouncer whether running or not
5.Configuring pgbouncer.ini
6.Authentication file named userlist.txt
7. Encrypt the passwords in the authentication file
8. Launch pgbouncer
9.Connect client applications to PgBouncer.
10.Use the Admin Console to observe the state of the connection pools.

1.What is The PgBouncer And  Pooling Modes
What is the PgBouncer:-
PgBouncer is a lightweight connection pooler for PostgreSQL.
pgbouncer is a PostgreSQL connection pooler. Any target application can be connected to pgbouncer as if it were a PostgreSQL server, and pgbouncer will create a connection to the actual server, or it will reuse one of its existing connections.
The aim of pgbouncer is to lower the performance impact of opening new connections to PostgreSQL.

Pooling Modes:-
pool_mode =Values may be session, transaction, or statement. The default is session.

In order not to compromise transaction semantics for connection pooling, pgbouncer supports several types of pooling when rotating connections:

a)Session pooling
Most polite method. When client connects, a server connection will be assigned to it for the whole duration the client stays connected. When the client disconnects, the server connection will be put back into the pool. This is the default method.
b)Transaction pooling
A server connection is assigned to client only during a transaction. When PgBouncer notices that transaction is over, the server connection will be put back into the pool.
c)Statement pooling
Most aggressive method. The server connection will be put back into pool immediately after a query completes. Multi-statement transactions are disallowed in this mode as they would break.
The administration interface of pgbouncer consists of some new SHOW commands available when connected to a special ‘virtual’ database pgbouncer.

2.Installation Of PgBouncer:
PgBouncer depends on few things to get compiled:
a)Download the latest version of Pgbouncer from http://pgfoundry.org/frs/?group_id=1000258
b) Extract the compressed file and run
c) When dependencies are installed just run
$ ./configure --prefix=/usr/local --with-libevent=libevent-prefix
$ make
$ make install 
If you are building from git, or are building for Windows, please see separate build instructions below.

3.Make Directories
mkdir -p /var/log/pgbouncer
mkdir -p /var/run/pgbouncer
chown postgres: postgres  /var/log/pgbouncer
chown postgres: postgres  /var/run/pgbouncer  
4.Make Sure pgbouncer whether running or not
ps aux | grep pgbouncer
This is shown by the following:
$ ps aux | grep pgbouncer 
postgres 3162 0.0 0.0 2472 856 ? S 12:45 0:00 
./pgbouncer -d /opt/Postgres/8.4SS/pgbouncer/share/pgbouncer.ini
postgres 22540 0.0 0.0 2456 668 ? S 12:58 0:00
/opt/Postgres/8.4SS/pgbouncer/bin/pgbouncer-d /opt/Postgres/8.4SS/pgbouncer/share/pgbouncer.ini
5.Configuring pgbouncer.ini
vi /etc/pgbouncer/pgbouncer.ini
 
[databases]
postgres = host=127.0.0.1 port=5432
example = host=127.0.0.1 port=5432 dbname=sample
remote = host=192.168.10.101 port=5432 user=postgres password=my_password
 
[pgbouncer]
logfile = /var/log/pgbouncer/pgbouncer.log
pidfile = /var/run/pgbouncer/pgbouncer.pid
listen_addr = *
listen_port = 6432
 
; any, trust, plain, crypt, md5
auth_type = md5
 
 
; Users allowed into database 'pgbouncer'
auth_file = /opt/Postgres/8.4SS/pgbouncer/etc/userlist.txt
 
; comma-separated list of users, who are allowed to change settings
admin_users = postgres
 
; When server connection is released back to pool:
;   session      - after client disconnects
;   transaction  - after transaction finishes
;   statement    - after statement finishes
pool_mode = session
6. Authentication file named userlist.txt
When PgBouncer is installed, an authentication file named userlist.txt is created in the pgbouncer/etc subdirectory with the following content: "postgres" "my_password"
7. Encrypt the passwords in the authentication file
Generation of the MD5-encyrpted password is shown by the following:
postgres=# select 'md5' || md5 ('my_password' || 'postgres');
 ?column?
-------------------------------------
 md5bd865b5255aa3a11062eb552bc89fc9c
(1 row)
Copy the resulting string into the userlist.txt file:
"postgres" "md5bd865b5255aa3a11062eb552bc89fc9c"
If you use MD5-encrypted passwords in the authentication file, be sure the auth_type
parameter in the configuration file is set to md5
8. Launch pgbouncer:
If a change to the PgBouncer configuration file was made, restart PgBouncer.
$ ps aux | grep pgbouncer
postgres 8185 0.0 0.0 2472 680 ? S 16:58 0:00 ./pgbouncer
-d ../share/demobouncer.ini
postgres 8289 0.0 0.0 3064 732 pts/4 S+ 17:01 0:00 grep
pgbouncer
 
$ kill 8185 
Change to the pgbouncer/bin subdirectory and start the pgbouncer daemon as follows:
$ cd /opt/Postgres/8.4SS/pgbouncer/bin
$ ./pgbouncer -d /opt/Postgres/8.4SS/pgbouncer/share/pgbouncer.ini
2010-01-20 14:47:47.350 24109 LOG File descriptor limit: 1024 (H:8192),
max_client_conn: 100, max fds possible: 170
9.Connect client applications to PgBouncer.
$ /opt/Postgres/8.4SS/bin/psql -d postgres -U postgres -p 6432
Password for user postgres:
psql (8.4.1)
Type "help" for help.
postgres=#
The following example shows a connection to the example database alias:
$ /opt/Postgres/8.4SS/bin/psql -d example -U postgres -p 6432
Password for user postgres:
psql (8.4.1)
Type "help" for help.
example=# 
The following example shows a connection to the remote database alias:
$ /opt/Postgres/8.4SS/bin/psql -d remote -U postgres -p 6432
Password for user postgres:
psql (8.4.1)
Type "help" for help.
remote=#
10.Use the Admin Console to observe the state of the connection pools.
Log in to the psql program and connect to PgBouncer using database pgbouncer, username postgres, and port number 6432. If PgBouncer is running on a different hostthan the psql program you are using, also specify the IP address of the host runningPgBouncer as shown by the following:

The following are examples of the output of some of the Admin Console commands. The SHOW POOLS command lists the connection pools:
The SHOW CLIENTS command lists the clients and the state of their connections to PgBouncer database aliases:
The SHOW SERVERS command lists the PgBouncer connections to the database servers:
The SHOW DATABASES command lists the PgBouncer database aliases and their associated Postgres  databases:

Toubleshooting:-

1. psql: ERROR: Unknown startup
Ensure that the “ignore_startup_parameters = application_name” entry is set in pgbouncer.ini
2. ERROR broken auth file
Ensure the username and password defined in users.txt are in quotes. E.g. “postgres” instead of postgres.

Comments

Popular posts from this blog

How to Get Table Size, Database Size, Indexes Size, schema Size, Tablespace Size, column Size in PostgreSQL Database

PostgreSQL Pgbadger Installation On Linux

PostgreSQL pgBadger

How to configure Replication Manager (repmgr) ?

Migrating From Oracle to PostgreSQL using ora2pg open source tools