Questions tagged [pgpool]

middleware between PostgreSQL servers and clients for connection pooling, replication and load balancing

Filter by
Sorted by
Tagged with
0 votes
0 answers
18 views

Pgpool for HA of Postgres with repmgr switchover. Pgpool haw to follow primary

I'm trying to setup a highly available postgres (13) cluster(3 servers) with streaming replication. I first tried native replication and then decided to use repmgr to manage this so I could use the ...
user avatar
  • 1
0 votes
0 answers
5 views

Can I use PGPool-II to manage/replicate to backend databases with different names

I am wanting to use PGPool-II to do some replication/parallelization of queries. In this scenario there are multiple Postgres instances on different systems with different database names which share ...
user avatar
  • 101
0 votes
0 answers
119 views

pgpool2 standby node is in unused status

I've enabled streaming replication in my Postgres 14 installed from the Ubuntu repo. I want to enable load balance for pgpool-2 4 installed from sources. So I have this pgpool.conf: As you can see, I ...
user avatar
0 votes
0 answers
126 views

Is it good practice to use both PGBouncer and PGPool together?

We have setup a streaming replication on Postgres 13 and it all works fine. Current setup is as follows. We have one Primary Postgres and one Secondary Postgres connected via streaming replication. ...
user avatar
  • 101
0 votes
0 answers
60 views

PgBouncer Pool configurations

I have installed PgBouncer because of too many connections on database server. How can i check if BgBouncer pool is working correctly. When i perform SELECT * FROM pg_stat_database order by ...
user avatar
0 votes
0 answers
43 views

postgresql 13 sync replication - does primary complete flushing to local before sending wal to slaves?

In a pgpool-II 4.2 and postgresql 13 environment: 3 servers (each hosting a pgpool and a postgresql) I understand we can set the below parameter for different durability's. synchronous_commit = off, ...
user avatar
0 votes
0 answers
6 views

Can pg_pool send ALL queries to Standby database?

Is there a way to configure pg_pool to send ALL queries to Standby database instead of sending queries between Primary/Standby databases? Thanks in advance!
user avatar
  • 157
1 vote
0 answers
309 views

pgpool 4.2.1 and postgres 11 streaming replication problem

I've setup a 2+2 pgpool and postgresql cluster. two pgpool nodes in active/standby configuration and a streaming replication between databases All seemed work fine until after some failover and ...
user avatar
  • 11
0 votes
1 answer
173 views

support of multiple databases on different hosts by pgpool2

Let's assume We have 2 databases: Database1 is located on host1 and host2 Database2 is located on host3 and host4 If I'll use pgpool2 to connect to these 2 databases with single pgpool config for ...
user avatar
  • 135
0 votes
0 answers
466 views

pgpool-II and Postgres docker image : automated failover and online recovery via rsa key

I've been following this documentation for pgpool-ii https://www.pgpool.net/docs/latest/en/html/example-cluster.html I'm having a hard time setting up rsa on my postgres streaming cluster built in ...
user avatar
0 votes
0 answers
857 views

Postgres Replication - WAL sender throw connection reset by peer

Good morning everyone, I hope containment is going well for you. For my part, I use this time to set up a Postgres 11 cluster on my servers, but I have some problems. Can you help me? I've got ...
user avatar
0 votes
1 answer
445 views

pcp_attach_node not working in pgpool 2

I've been working on pgpool2 setup with 2 DB node and I need to attach on the node, which os currently down. when I run postgres@pg-pool:~$ pcp_attach_node -p 9898 -n 1 -U postgres I get following ...
user avatar
  • 101
0 votes
1 answer
277 views

Master - Master setup on Postgresql 10 + Ubuntu 18.04 + Pgpool II [closed]

Does anybody have experience in configuring Pgpool II with Postgresql 10 on Ubuntu 18.04? I am trying to setup Master - Master setup on Postgresql 10 + Ubuntu. I am trying to use Pgpool II I will ...
user avatar
  • 101
0 votes
1 answer
1k views

.pcppass not working

I run pgpool-II as postgres user so I can replicate exactly what I did in one sequence here in this post. -bash-4.2$ echo "pgpool:$(pg_md5 test)" > /etc/pgpool-II-11/pcp.conf -bash-4.2$ cat /etc/...
user avatar
1 vote
0 answers
2k views

Pgpool status restore/reset

Need help troubleshooting pgpool issue and reset the status. The “show pool_nodes” shows node 1 is down: postgres@node-0:~$ psql -p 9999 -c "show pool_nodes" node_id | hostname | ...
user avatar
  • 157
0 votes
1 answer
255 views

Postgres 9.6 - Taking differenctial backup

I have a few CentOS boxes running Postgres 9.6. I am planning a central server which can make local copies of all DBs on the other servers. These servers are accessible via SSH on Internet. What is ...
user avatar
0 votes
0 answers
254 views

no performance improvement with pgpool query result caching

I'm trying to add query result caching to a PostGIS database that's frequently hit with the same (expensive) spatial queries. Looks like pgpool-II is currently the only well-developed solution. ...
user avatar
1 vote
0 answers
425 views

PGPool II + Django w/ psycopg2 load balancing

I've a pool of applications running django 1.6 with psycopg2 on top of a PGPool II with two backends PostgreSQL servers. But all queries (read and/or write) are going to master PostgreSQL even SELECT. ...
user avatar
  • 11
2 votes
0 answers
721 views

Pgpool executes queries on standby nodes instead of master when replication is behind in standby

I have a postgresql 10 master db with 2 hot standby servers with streaming replication, and the replication is working correctly. The synchronous_commit is setted to remote_write Also I have a pgpool ...
user avatar
  • 121
1 vote
0 answers
414 views

PGpool not properly detecting which postgres server is master

I am in the process of building a resilient database cluster and currently have 3 database servers running postgres 10 and repmgr. repmgr is taking care of the streaming replication including ...
user avatar
0 votes
2 answers
96 views

In pgPool-II 3.7.5 configuration pgpool.conf, is recovery_user a role or a system user?

I'm having trouble configuing pgPool-II 3.7.5 for online recovery. In pgpool.conf, there is a parameter called recovery_user along with a recovery_password that are somehow used to run ...
user avatar
  • 163
1 vote
0 answers
48 views

Disconnect specific database from pgpool II

I'm using PostgreSQL 9.5 with pgpool II version 3.4.13 on Ubuntu 16.04.3 I want to drop a specific database so I must disconnect the database from pgpool. How can I do this without disconnecting the ...
user avatar
1 vote
1 answer
2k views

Connecting to Pgpool

Apologies for the long post but i'm stumped on getting Pgpool to work for me. Running Centos7 i have two instances of Postgres running on different ports in master replication mode. I have ...
user avatar
0 votes
1 answer
213 views

PgPool: distro vs upstream?

I'm running PostgreSQL 9.6 and would like to trial pgpool. The yum repository shows the following options: pgpool-II-96 postgresql-pgpool-II Which version should i use ? The documentation on PgPool ...
user avatar
1 vote
2 answers
4k views

Pgpool2 doesn't use the slave for load balancing

I've enabled streaming replication in my postgres 9.5.10 installed from ubuntu xenial repo. I want to enable load balance for pgpool-2 (3.7.0 amefuriboshi) installed from sources. So I have this ...
user avatar
0 votes
1 answer
1k views

PostgreSQL pgpool-II replication mode doesn't work?

I'm experimenting with PostgreSQL and pgpool-II and trying to setup native replication mode on pgpool. The pgpool documentation says In the native replication mode, Pgpool-II is responsible for ...
user avatar
0 votes
0 answers
290 views

Postgresql - Is this possible to setup a Zero downtime PostgreSQL Cluster?

I have already asked here about PostgreSQL HA setup, and I got many possible answers. But the bad thing is, even though if I use any third party software like PGpool, Repmgr, pacemaker are asking for ...
user avatar
  • 1,698
1 vote
0 answers
452 views

pgpool-II configuring multiple masters and slaves

Actualy I did create a replication system using Pgpool2, with master/slave servers, now i need to add multiple master servers and I need them to be related and updated across each other. Is it ...
user avatar
5 votes
2 answers
1k views

PGPool memory requirements

What amount of physical memory is recommended for a dedicated PGPool machine running connection pooling & load balancing, but no query cache? I see num_init_children(96) * max_pool(2) * ...
user avatar
  • 283
2 votes
1 answer
1k views

Troubleshooting PgPool Query Lags

I have been using PGpool-II for quite a while but just noticed an intermittent lag (slow request taking about 20s to execute) which I am able to replicate consistently. Before I explain here is a ...
user avatar
0 votes
1 answer
1k views

PgPool II: Write-Only and Read-Only DBs

I'd like to setup the following infrastructure: PSQL 9.6 in USA (master) PSQL 9.6 in Europe (slave) PgPool-II in Europe App Server in Europe (it reads the DB in 90% of time). My ...
user avatar
2 votes
0 answers
586 views

Pgpool II and Postgresql upgrade scenario using different major versions

Pgpool FAQ states that: You cannot mix different major versions of PostgreSQL, for example 8.4.x and 9.0.x And the reason that I understand very well is: Pgpool-II assumes messages from ...
user avatar
0 votes
2 answers
3k views

PGPool II - Failover when current master node dies

I have a 2 node high-availability postgresql 9.5 cluster. I'm using streaming replication, PGPool II (3.5.4 [ekieboshi]), and a virtual (or floating) secondary IP all hosted on aws to accomplish this. ...
user avatar
  • 283
3 votes
1 answer
9k views

Why PgPool showing all the nodes as standby even though pg_is_in_recovery() shows correctly? [closed]

I have a set up where I have 1 pgpool, 1 primary postgres and 2 standby postgres. And I am seeing following issues: Issue 1 : Pgpool is not able to detect primary and says all are standby. [root@ip-...
user avatar
2 votes
0 answers
931 views

Using PGPool with RDS Slave replicas, any advice?

I have a Postgres 9.4 RDS instance with Multi AZ, and there's a slave, read-only replica. Up to this point the load balancing was made in the business layer of my app, but it's inneficient and I was ...
user avatar
  • 305
2 votes
1 answer
2k views

Automatic recovery of the failed postgresql master node is not working with pgpool II

I am new to Postgresql and Pgpool II setup. I have configured the Postgresql HA/Load balancing using Pgpool II and Repmgr. I have followed the link to do the setup. The setup consist of 3 nodes and ...
user avatar
1 vote
0 answers
138 views

Pgpool slave node limit

I've started a new database architecture using pgpool-II 3.4.5, I have 3 servers by now, 1 master and 2 slaves. They are configured to use Master/Slave conf and LoadBalancing. The problem is: When I ...
user avatar
0 votes
1 answer
991 views

Pooler (pgbouncer) + MasterDB + Slaves

During a spike, my connections go from 30 to over 140, as you can see below: I'm willing to set-up a pooler like pgbouncer, but have some questions as my scenario is not that easy: 1 Load Balancer ...
user avatar
1 vote
1 answer
808 views

PGPool for Postgres load balancing, where to sit it

So I've been doing a stack of reading. I'm still not 100% clear of the best way to go about it. Let's take a simple setup. 3 nodes to server UWSGI apps (in random), 3 nodes for PGSQL, 1 nominal ...
user avatar
9 votes
2 answers
3k views

Postgres with pgpool architecture

Below is an example pgpool architecture: This implies that you only need to have pgpool on a single server; is this true? When I look at the configuration I also see that you configure backends ...
user avatar
1 vote
0 answers
328 views

Connecting Tableau and PgPool

I'm trying to connect Tableau to our database Postgresql via PgPool, for load Balance. I have Postgresql configured with Streaming Replication between one master and two slaves. And have another ...
user avatar
2 votes
2 answers
6k views

pgpool load balancing is sending all queries only to master

My two postgresql servers are configured for streaming replication, which is working fine. Pgpool is configured for Master Slave mode / Load Balance Mode. pgpool.conf: listen_addresses = '*' port = ...
user avatar
1 vote
0 answers
1k views

Master/Slave - streaming replication restrict function to master

How can I run a function a on master only? I don't want to connect directly to my master server, because the master server might change. Or can I change my function to work on this setup? Because ...
user avatar
  • 353
4 votes
1 answer
7k views

Pgpool II : unable to read message length between two network interfaces

Start log file: 2015-03-06 01:57:56: pid 2760: LOG: Setting up socket for 0.0.0.0:9999 2015-03-06 01:57:56: pid 2760: LOG: Setting up socket for :::9999 2015-03-06 01:57:56: pid 2760: LOG: pgpool-...
user avatar
  • 1,802
0 votes
1 answer
2k views

PGpool Promote to master and Slaves wont replicate on failure

Sorry for the copy paste from stackoverflow, but this is probably a better place to post this. Version pgpool-II-pg93-3.4.0-3pgdg.rhel6.x86_64.rpm I’ve seen some chat online about this from a ...
user avatar
  • 131
1 vote
0 answers
334 views

Pgpool load-balancing vs. consistency

In what ways is consistency weakened when using pgpool-II load-balancing on Postgresql servers running streaming replication? Obviously with asynchronous replication you can insert a row on the ...
user avatar
  • 111
1 vote
1 answer
3k views

pgpool-II doesn't execute my failover script

I have been installed pgpool-II 3.3.4 from source. I have two pgpool nodes and I also used the watchdog configuration to get a pgpool services with High Availability. When I stop one, the other wake ...
user avatar
  • 615
0 votes
1 answer
357 views

Using pgpool and PostgreSQL in differents hosts.

I want to use pgpool but I did some research and I have a doubt, if neccesary install PostgreSQL and pgpool in the same machine? When I did my first configuration and installation in a same host. But ...
user avatar
  • 615
1 vote
0 answers
259 views

how to get pgpool2 HA

I am using pgpool2 and PostgreSQL to get a HA of postgresql service. I have been working in this: .-----. .--------. | | W | DB | | PGP |-----+-----------...
user avatar
  • 615
1 vote
0 answers
101 views

Does pg-pool-II support two phase commit?

When pg-pool II is used with sharding, what happens if I want to do a transaction which involves two nodes ? Does pgpool support two phase commit ?
user avatar
  • 293