Questions tagged [pgpool]
middleware between PostgreSQL servers and clients for connection pooling, replication and load balancing
77
questions
0
votes
0
answers
312
views
PgPool-II can't find the servers where the databases live (even though the PgPool is on a db server)
PgPool-II 4.3.3 Postgresql 13.9 RHEL 8.6
Postgresql is installed on FOOBARa and FOOBARb, and data is successfully being stream replicated from a to b.
Then I installed PgPool-II on a, and configured ...
- 569
0
votes
0
answers
18
views
Postgres 9.6 where two DB
I have a configuration where one system is running the main db another system is the hot standby DB. The connection is managed by pgpool.
The issue I am getting is:
Primary DB node XLOG is [...
0
votes
1
answer
1k
views
How to fix authentication through pgpool?
I have a cluster that consists of:
pgpool
master
slave
Using pgpool-II version 4.3.2 and
Postgres 14.4
Several users have been created, all are authenticated through pgpool.
There was a need to ...
- 131
1
vote
0
answers
473
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 ...
- 11
1
vote
0
answers
2k
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. ...
- 111
0
votes
0
answers
53
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, ...
1
vote
0
answers
442
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 ...
- 11
0
votes
1
answer
345
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 ...
- 135
1
vote
0
answers
1k
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 ...
- 11
0
votes
1
answer
706
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 ...
- 101
0
votes
1
answer
328
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 ...
- 101
0
votes
1
answer
2k
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/...
- 141
1
vote
0
answers
3k
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 | ...
- 165
0
votes
1
answer
333
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 ...
0
votes
0
answers
312
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.
...
- 451
1
vote
0
answers
490
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.
...
- 11
2
votes
0
answers
767
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 ...
- 121
1
vote
0
answers
512
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 ...
- 111
0
votes
2
answers
123
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 ...
- 173
1
vote
0
answers
79
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 ...
1
vote
1
answer
3k
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 ...
- 176
0
votes
1
answer
230
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 ...
- 176
1
vote
2
answers
5k
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 ...
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 ...
1
vote
0
answers
517
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 ...
5
votes
2
answers
2k
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) * ...
- 283
2
votes
1
answer
2k
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 ...
- 141
0
votes
1
answer
2k
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 ...
- 3
2
votes
0
answers
681
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 ...
- 41
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.
...
- 283
3
votes
1
answer
10k
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-...
- 41
2
votes
0
answers
988
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 ...
- 335
2
votes
1
answer
3k
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 ...
- 21
1
vote
0
answers
161
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 ...
0
votes
1
answer
1k
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 ...
user83914
1
vote
1
answer
827
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 ...
- 111
9
votes
2
answers
4k
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 ...
user20459
1
vote
0
answers
335
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 ...
- 480
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 = ...
- 71
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 ...
- 353
4
votes
1
answer
8k
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-...
- 1,912
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 ...
- 131
1
vote
0
answers
339
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 ...
- 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 ...
- 665
0
votes
1
answer
382
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 ...
- 665
1
vote
0
answers
262
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 |-----+-----------...
- 665
1
vote
0
answers
106
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 ?
- 313
4
votes
1
answer
3k
views
timeline 2 of the primary does not match recovery target timeline 1
I want to create a configuration with postgresql and pgpool to get height availability (HA). I want to do this:
.-----. .--------.
| | W | DB |
| APP |---...
- 665
2
votes
2
answers
2k
views
PostgreSQL synchronous replication time out?
I'm using PostgreSQL 9.3 with synchronous replication. I have 2 synchronous replicators and 1 async replicator. My problem is when 2 synchronous replicators are down, transaction will wait ...
- 313
1
vote
0
answers
158
views
Understanding pg-pool in details
I have following problem regarding the pg-pool (3.3.3)
When master fails, automatic failover happens and standby is promoted to master. But this new master don't have any replicators. Other slave ...
- 313