TutorialDBA - Support | Training | Consultant

I have multiple PostgreSQL servers for a web application. Typically one master and multiple slaves in hot standby mode (asynchronous streaming replication). I use PGBouncer for connection pooling: one instance installed on each PG server (port 6432) connecting to database on localhost. I use transaction pool mode. In order to load-balance my read-only connections on slaves, I use HAProxy (v1.5) with a conf more or less like this: listen pgsql_pool 0.0.0.0:10001 mode tcp option pgsql-check user ha balance roundrobin server master 10.0.0.1:6432 check backup server slave1 10.0.0.2:6432 check server slave2 10.0.0.3:6432 check server slave3 10.0.0.4:6432 check So, my web application connects to haproxy (port 10001), that load-balance connections on multiple pgbouncer configured on each PG slave. Here is a representation graph of my current architecture: This works quite well like this, but I realize that some implements ...

Replication Introduction. PostreSQL, or postgres , is a popular database management system that can organize and manage the data associated with websites or applications. Replication is a means of copying database information to a second system in order to create high availability and redundancy repmgr 3.0:- repmgr is an open-source tool suite to  monitor replication, and perform administrative tasks such as failover or manual switchover operations. PgBouncer:- PgBouncer is connection pooler for PostgreSQL databases and acts as gateway for clients to connect to the databases.pgBouncer allows for backend database configuration changes by just editing its configuration file and reloading the process. Setup:- host1: first database host running on 192.168.1.100 host2: second database host running on 192.168.1101 bouncer-host: host running pgbouncer on 192.168.1.102 Instructions:- 1.Install dependencies. 2.Configure ssh access between db nodes. 3...

Determining if your current database connection is using PgBouncer, or going directly to Postgres itself, can be challenging, as PgBouncer is a very low-level, transparent interface. It is possible, and here are some detection methods you can use. This was inspired by someone asking on the Perl DBD IRC channel if it was possible to easily tell if your current database handle (usually "$dbh") is connected to PgBouncer or not. Since I've seen this question asked in other venues, I decided to take a crack at it. There are actually two questions to be answered: (1) are we connected to PgBouncer, and if so, (2) what pool_mode is being run? The quickest and easiest way I found to answer the first question is to try and connect to a non-existent database. Normally, this is a FATAL message, as seen here: $ psql testdb -p 5432 testdb=# \c ghostdb FATAL:  database "ghostdb" does not exist Previous connection kept testdb=# However, a slightly different ERROR message is ret...