Hot answers tagged

9

Generally you would not install Pgpool on the backend servers. What you see in your picture is the most common configuration. Pgpool is a standalone server which essentially sits in front of the databases. The two Postgres servers are often configured with streaming replication; with one being the master and the other the slave. This allows Pgpool to load ...


6

In our shop we selected repmgr and pgbouncer instead of pgpool. repmgr has some nice tooling to setup and maintain the cluster of replicated database servers. In our case 1 master and 2 slaves (one failover and one live read performance test that can become the failover of the new master). pgpool has issues with changes in the configuration, in most cases ...


4

The documentation on PITR recovery that comes with pgpool is really incomplete, and downright misleading at points. It suggests keeping all the archive files on the master than then having a recovery_command that uses scp to copy from there to each standby. That's worthless. Each standby needs to have its own copy of the archive, or there's no real ...


4

The issue is covered in this detailed blog. In short, the master and the standby servers must all have archive_mode = on and an archive_command, until 9.3 which removes this requirement.


3

Instead of compiling PgPool, why not just install it from package management? yum install postgresql-pgpool-II I don't use CentOS, but that's it's package name in Fedora 17, and the two are usually consistent. Since you're using a PostgreSQL 9.2 installed via Yum on CentOS 5, you're obviously using a 3rd party packaging of PostgreSQL, so you're likely ...


3

I'm not sure about pgPool, but pgBouncer (another connection pool) can use SSL by using Stunnel. Check your functional requirements to see if pgBouncer can solve your problem. Maybe pgPool can also use Stunnel, you could give it a try.


3

Wow, surprised nobody answered this question in the past two years! The problem is almost certainly these two lines: log_statement = on # Log all statements log_per_node_statement = on # Log all statements # with node and backend informations They result in a lot ...


2

That isn't a problem, it's by design. If you don't want transactions to wait until a synchronous replica acknowledges a commit, then you don't want synchronous replication. You want async replication and should simply remove the synchronous_standby_names option. What good would synchronous replication be if it gave up when no replica was reachable? "I ...


2

No, pgpool is an all or nothing solution. All you can control is the precentage of read queries going to each host, not what kinds of (read) queries.


2

Pgpool is a kind of a layer between client and postgresql. Pgpool receive a query from client and ask Postgresql for data. Postgresql answers to Pgpool and pgpool send data to client like on the picture below. There is not direct connection between client and Postgres when you use Pgpool to connect to database. By the way Pgpool provides connection pooling, ...


2

You're increasing num_init_children, but there's no sign you've set the PostgreSQL server backend's max_connections high enough to allow that number of connections from PgPool. Please see the PgPool-II documentation to learn more about configuring PgPool-II. You might find this article useful. You really don't want thousands of parallel queries to the ...


2

Answer per the question: Script pgpool_recvery_pitr: psql -t -c 'SELECT datname FROM pg_database WHERE NOT datistemplate AND datallowconn' template1| while read i do if [ "$i" != "" ];then psql -c "SELECT setval(oid, nextval(oid)) FROM pg_class WHERE relkind = 'S'" $i fi done After recovery all sequences in database advance 1. There is no information ...


2

Of PgBouncer and PgPool-II, only PgPool-II supports load balancing. PgPool-II's load balancing supports only read-only queries that meet certain requirements; see the pgpool docs. Additionally, if you start load-balancing queries you are no longer guaranteed to get a consistent view of the database because the replica might be lagging behind the master. ...


2

It looks like your default authentication mode for TCP/IP connections (host) is md5. So at a guess, you have PgPool connecting over TCP/IP. Change the auth mode for loopback connections on tcp/ip, or use unix sockets with pgpool (usually by omitting the host setting). If you're still stuck after reading: 19.1. The pg_hba.conf File and Chapter 19. Client ...


1

I messed around with this problem for several hours and then finally found the solution. Read the pgpool-II FAQ and look at the table: You cannot leave the default entries of trust in pool_hba.conf. I commented out the trust entries and added my md5 entries.


1

I don't think these are issued by PGPool. I think they are instigated by your application (which I am guessing is written in PHP and running over some sort of ORM). The first entry is DEALLOCATE which means effectively to tell the server to free up memory from a prepared statement. These look like they are issued through some PDO module. The second and ...


1

Solution founded: To repair this issue I must execute this comand: chcon -t httpd_user_rw_content_t pgpool.conf and chcon -t httpd_user_rw_content_t pcp.conf then pgpooladmin will show that those files are writable and will allow to click "Next" to install pgpoolAdmin. Yay Cheers :D


1

Well ... check the pgpool logs and your servlet container logs. I'd also try psql 'host=localhost port=9999 dbname=postgres username=postgres password=whatever' Most likely the PgPool configuration has issues. If it works from psql, you'd need to check out the server logs to see what's up.


1

There really isn't enough to go on here. 10 concurrent connections isn't a lot for most setups. The best advice I can give is start simple and when you start to run into cases where performance is starting to become an issue then look at more complex solutions unless you know it will be an issue (say 1000 concurrent users and millions of queries from the ...


1

To my knowledge it does not allow that level of parallelism. If that's what you are trying to do the best approach is to go with Postgres-XC or GridSQL, but Postgres-XC while more complex is also more flexible. http://postgres-xc.sourceforge.net/ is the project page.


1

AS per my comment this ended up being a misunderstanding between pgppool and pgpool-II being separate open source projects. When correctly using pgpoolII and it's documentation this error was not a problem.


1

We are using two different solutions in combination at the same time... Pgpool-II for synchronous replication and Slony2 for asynchronous (triggered) replication. Performance is excellent


1

Ok so. By design, pgpool does the above. The follow_master_command in the pgpool.conf file is what you need to use to resolve this issue. I use the following script (follow_master.sh which i place in the /etc/pgpool-II folder) #!/bin/sh ################ ## ## $1 = node id ## $2 = Old master node id ## $3 = node hostname ## ############### ...


1

Initially, I have set sr_check_period in pgpool.conf to 0 (zero) to disable SR checking. I thought of avoiding this check and invariably balance the load. But this has caused the problem. After I have given a non-zero value to this attribute, load balancing started working.


1

Yes you can define in pgbouncer your 2 servers (or more) masetr and slave and connect to each of them. There is different kind of topology you can install pgbouncer on each www, as is the connection will be the fastest (www process can use unix socket to connect), on install a pgbouncer on each server (master and slave) or install it on a new host to be ...



Only top voted, non community-wiki answers of a minimum length are eligible