github.com/1aal/kubeblocks@v0.0.0-20231107070852-e1c03e598921/docs/user_docs/kubeblocks-for-postgresql/postgresql-connection-pool/postgresql-connection-pool.md (about)

     1  ---
     2  title: PostgreSQL connection pool
     3  description: Connect to PgBouncer to reduce too many PostgreSQL connections and to improve the throughput and stability of the database.
     4  keywords: [postgresql, connection pool, pgbouncer]
     5  sidebar_position: 1
     6  sidebar_label: Connection pool
     7  ---
     8  
     9  # PostgreSQL connection pool
    10  
    11  PostgreSQL adopts a multi-process architecture, which creates a separate backend process for each user connection. When there are too many user connections, it occupies a large amount of memory, which reduces the throughput and stability of the database. To solve these problems, KubeBlocks introduces a connection pool, PgBouncer, for PostgreSQL database clusters.
    12  
    13  When creating a PostgreSQL cluster with KubeBlocks, PgBouncer is installed by default.
    14  
    15  ## Steps
    16  
    17  1. View the status of the created PostgreSQL cluster and ensure this cluster is `Running`.
    18  
    19     ```bash
    20     kbcli cluster list mycluster
    21     ```
    22  
    23  2. Describe this cluster and there are two connection links in Endpoints.
    24  
    25      Port `5432` is used to connect to the primary pod of this database and port `6432` is used to connect to PgBouncer.
    26  
    27      ```bash
    28      kbcli cluster describe mycluster
    29      >
    30      Endpoints:
    31      COMPONENT    MODE        INTERNAL                                              EXTERNAL   
    32      postgresql   ReadWrite   mycluster-postgresql.default.svc.cluster.local:5432   <none>     
    33                               mycluster-postgresql.default.svc.cluster.local:6432         
    34      ```
    35  
    36  3. Connect the cluster with PgBouncer.
    37  
    38     This command shows how to connect to a cluster with CLI. The default example uses port `5432` and you can replace it with port `6432`.
    39  
    40      ```bash
    41      kbcli cluster connect --client=cli --show-example mycluster
    42      >
    43      kubectl port-forward service/mycluster-postgresql 6432:6432
    44      PGPASSWORD=***** psql -h127.0.0.1 -p 6432 -U postgres postgres
    45      ```
    46  
    47  4. Run `port-forward`.
    48  
    49     ```bash
    50     kubectl port-forward service/mycluster-postgresql 6432:6432
    51     ```
    52  
    53  5. Open a new terminal window and run the `psql` command to connect to PgBouncer.
    54  
    55     ```bash
    56     PGPASSWORD=***** psql -h127.0.0.1 -p 6432 -U postgres postgres
    57     ```
    58  
    59  6. Run the following command in `psgl` to verify the connection.
    60  
    61     If you can connect to `pgbouncer` and execute `show help` with the expected results below, this cluster connects to PgBouncer successfully.
    62  
    63     ```bash
    64     postgres=# \c pgbouncer
    65     ```
    66  
    67     ```bash
    68     pgbouncer=# show help;
    69     >
    70     NOTICE:  Console usage
    71     DETAIL:  
    72             SHOW HELP|CONFIG|DATABASES|POOLS|CLIENTS|SERVERS|USERS|VERSION
    73             SHOW PEERS|PEER_POOLS
    74             SHOW FDS|SOCKETS|ACTIVE_SOCKETS|LISTS|MEM|STATE
    75             SHOW DNS_HOSTS|DNS_ZONES
    76             SHOW STATS|STATS_TOTALS|STATS_AVERAGES|TOTALS
    77             SET key = arg
    78             RELOAD
    79             PAUSE [<db>]
    80             RESUME [<db>]
    81             DISABLE <db>
    82             ENABLE <db>
    83             RECONNECT [<db>]
    84             KILL <db>
    85             SUSPEND
    86             SHUTDOWN
    87             WAIT_CLOSE [<db>]
    88     SHOW
    89     ```