Home - News ( United States | United Kingdom | Italy | Germany ) - Football scores

Showing content from https://learn.microsoft.com/en-us/azure/postgresql/flexible-server/concepts-pgbouncer below:

PgBouncer in Azure Database for PostgreSQL flexible server - Azure Database for PostgreSQL

APPLIES TO: Azure Database for PostgreSQL - Flexible Server

Azure Database for PostgreSQL flexible server offers PgBouncer as a built-in connection pooling solution. PgBouncer is an optional feature that you can enable on a per-database-server basis. It's supported on General Purpose and Memory Optimized compute tiers in both public access and private access networks.

PgBouncer runs on the same virtual machine (VM) as the database server for Azure Database for PostgreSQL flexible server. Postgres uses a process-based model for connections, so maintaining many idle connections is expensive. Postgres runs into resource constraints when the server runs more than a few thousand connections. The primary benefit of PgBouncer is to improve idle connections and short-lived connections at the database server.

PgBouncer uses a lightweight model that utilizes asynchronous I/O. It uses Postgres connections only when needed--that is, when inside an open transaction or when a query is active. This model allows scaling to up to 10,000 connections with low overhead.

PgBouncer runs on port 6432 on your database server. You can change your application's database connection configuration to use the same host name, but change the port to 6432 to start using PgBouncer and benefit from improved scaling of idle connections.

PgBouncer in Azure Database for PostgreSQL flexible server supports Microsoft Entra authentication (Azure AD).

PgBouncer is also available for elastic clusters. Each node in an elastic cluster has its own instance of PgBouncer. After being enabled, 6432 port leads to the PgBouncer running on the currently designated coordinator node. You can also use port 8432 to load balance your connections against PgBouncer instances running on all nodes in the cluster.

PgBouncer is especially useful in transactional applications where frequent connection churn can degrade performance. It is enabled by default on port 6432, and you can connect to it using the same host name as your PostgreSQL server, but with port 6432 instead of the default port 5432.

To enable PgBouncer, go to the Server parameters pane in the Azure portal, search for PgBouncer, and change the pgbouncer.enabled setting to true. There's no need to restart the server.

You can configure PgBouncer settings by using these parameters.

Note

The following list of PgBouncer server parameters is visible on the Server parameters pane only if the pgbouncer.enabled server parameter is set to true. Otherwise, they're deliberately hidden.

For more information about PgBouncer configurations, see the pgbouncer.ini documentation.

Currently, the version of PgBouncer deployed on all supported major versions of the engine (17, 16, 15, 14, 13, 12, 11), in Azure Database for PostgreSQL flexible server, is 1.22.1.

By using the built-in PgBouncer feature with Azure Database for PostgreSQL flexible server, you can get these benefits:

• Convenience of simplified configuration: Because PgBouncer is integrated with Azure Database for PostgreSQL flexible server, there's no need for a separate installation or complex setup. You can configure it directly from the server parameters.

• Reliability of a managed service: PgBouncer offers the advantages of Azure managed services. For example, Azure manages updates of PgBouncer. Automatic updates eliminate the need for manual maintenance and ensure that PgBouncer stays up to date with the latest features and security patches.

• Support for various connection types: PgBouncer in Azure Database for PostgreSQL flexible server provides support for both public and private connections. You can use it to establish secure connections over private networks or connect externally, depending on your specific requirements.

• High availability in failover scenarios: If a standby server is promoted to the primary role during a failover, PgBouncer seamlessly restarts on the newly promoted standby. You don't need to make any changes to the application connection string. This ability helps ensure continuous availability and minimizes disruption to the application's connection pool.

You can use PgBouncer metrics to monitor the performance of the PgBouncer process, including details for active connections, idle connections, total pooled connections, and the number of connection pools. Each metric is emitted at a 1-minute interval and has up to 93 days of history. Customers can configure alerts on the metrics and also access the new metrics dimensions to split and filter metrics data by database name.

• To monitor PgBouncer metrics, ensure that pgbouncer feature is enabled via the server parameter pgbouncer.enabled and metrics parameter metrics.pgbouncer_diagnostics is enabled.
• These parameters are dynamic and don't require an instance restart.
• PgBouncer metrics are disabled by default.

• PgBouncer metrics that use the DatabaseName dimension have a 30-database limit.
• On the Burstable SKU, the limit is 10 databases that have the DatabaseName dimension.
• The DatabaseName dimension limit is applied to the OID column, which reflects the order of creation for the database.

To learn more, see PgBouncer metrics.

PgBouncer also provides an internal database called pgbouncer. When you connect to that database, you can run SHOW commands that provide information on the current state of PgBouncer.

To connect to the pgbouncer database:

1. Set the pgBouncer.stats_users parameter to the name of an existing user (for example, myUser), and apply the changes.

2. Connect to the pgbouncer database as this user and set the port as 6432:

   psql "host=myPgServer.postgres.database.azure.com port=6432 dbname=pgbouncer user=myUser password=<password> sslmode=require"
   

After you're connected to the database, use SHOW commands to view PgBouncer statistics:

• SHOW HELP: List all the available SHOW commands.
• SHOW POOLS: Show the number of connections in each state for each pool.
• SHOW DATABASES: Show the current applied connection limits for each database.
• SHOW STATS: Show statistics on requests and traffic for every database.

For more information on the PgBouncer SHOW commands, see Admin console.

To start using PgBouncer, follow these steps:

1. Connect to your database server, but use port 6432 instead of the regular port 5432. Verify that this connection works.

   psql "host=myPgServer.postgres.database.azure.com port=6432 dbname=postgres user=myUser password=<password> sslmode=require"
   

2. Test your application in a QA environment against PgBouncer, to make sure you don't have any compatibility problems. The PgBouncer project provides a compatibility matrix, and we recommend transaction pooling for most users.

3. Change your production application to connect to port 6432 instead of 5432. Monitor for any application-side errors that might point to compatibility issues.

In zone-redundant, high-availability (HA) servers, the primary server runs PgBouncer. You can connect to PgBouncer on the primary server over port 6432. After a failover, PgBouncer is restarted on the newly promoted standby, which is now the primary server. So your application connection string remains the same after failover.

In some cases, you might already have an application-side connection pool or have PgBouncer set up on your application side (for example, an Azure Kubernetes Service sidecar). In these cases, the built-in PgBouncer feature can still be useful because it provides the benefits of idle connection scaling.

Using an application-side pool together with PgBouncer on the database server can be beneficial. Here, the application-side pool brings the benefit of reduced initial connection latency (because the roundtrip to initialize the connection is much faster), and the database-side PgBouncer provides idle connection scaling.

• The PgBouncer feature is currently not supported with the Burstable server compute tier. If you change the compute tier from General Purpose or Memory Optimized to Burstable, you lose the built-in PgBouncer capability.

• Whenever the server is restarted during scale operations, HA failover, or a restart, PgBouncer and the VM are also restarted. You then have to re-establish the existing connections.

• The portal doesn't show all PgBouncer parameters. After you enable PgBouncer and save the parameters, you have to close the Server parameters pane (for example, select Overview) and then go back to the Server parameters pane.

• You can't use statement pool modes along with prepared statements. Current version of PgBouncer added support for prepared statements inside of transaction mode. This support can be enabled and configured via max_prepared_statements parameter. Setting this parameter above default value of 0 will turn on support for prepared statements. This support only applies to protocol-level prepared statements. For most programming languages, this means that we are using the libpq function PQprepare on the client, sending protocol level commands that PgBouncer can intercept, rather than issuing a dynamic SQL command similar to PREPARE proc AS, which is sending text that PgBouncer will not interpret correctly. To check other limitations of your chosen pool mode, refer to the PgBouncer documentation.

• If PgBouncer is deployed as a feature, it becomes a potential single point of failure. If the PgBouncer feature is down, it can disrupt the entire database connection pool and cause downtime for the application. To mitigate the single point of failure, you can set up multiple PgBouncer instances behind a load balancer for high availability on Azure VMs.

• Token Size Restriction with Azure AD Authentication - Users with a large number of group memberships won't be able to connect through PgBouncer due to a token size restriction. Applications, services, and users with a small number of groups work.

• PgBouncer is a lightweight application that uses a single-threaded architecture. This design is great for most application workloads. But in applications that create a large number of short-lived connections, this design might affect pgBouncer performance and limit your ability to scale your application. You might need to try one of these approaches:

  • Distribute the connection load across multiple PgBouncer instances on Azure VMs.
  • Consider alternative solutions, including multithreaded solutions like PgCat, on Azure VMs.

Important

The parameter pgbouncer.client_tls_sslmode for the built-in PgBouncer feature has been deprecated in Azure Database for PostgreSQL flexible server.

When TLS/SSL for connections to Azure Database for PostgreSQL flexible server is enforced via setting the require_secure_transport server parameter to ON, TLS/SSL is automatically enforced for connections to the built-in PgBouncer feature. This setting is on by default when you create a new Azure Database for PostgreSQL flexible server instance and enable the built-in PgBouncer feature. For more information, see Secure connectivity with TLS and SSL in Azure Database for PostgreSQL flexible server.

For customers who want simplified management, built-in high availability, easy connectivity with containerized applications, and the ability to use the most popular configuration parameters, the built-in PgBouncer feature is a good choice. For customers who want multithreaded scalability, full control of all parameters, and a debugging experience, setting up PgBouncer on Azure VMs might be an alternative.

• Connection target: Users may be connected to the PostgreSQL server directly instead of the PgBouncer instance.
• Port misconfiguration: PgBouncer listens on port 6432. If users connect via the default PostgreSQL port (5432), PgBouncer commands like SHOW POOLS doesn't work.
• PgBouncer not running: The service may not be properly initialized or enabled.

• Overview of Azure Database for PostgreSQL flexible server
• Public access and private endpoints in Azure Database for PostgreSQL flexible server
• Virtual network integration in Azure Database for PostgreSQL flexible server

RetroSearch is an open source project built by @garambo | Open a GitHub Issue

Search and Browse the WWW like it's 1997 | Search results from DuckDuckGo

HTML: 3.2 | Encoding: UTF-8 | Version: 0.7.4