Postgresql – pgpool-ii show server as down, but server is running fine

postgresqlruby-on-rails-3

I am using postgres 9.2 with streaming replication. I have 1 master DB and 2 standby DBs, it's hosted on amazon EC2, each has it's own server. On separate server i have pgpool(3.2.3) that does load balancing

In the pgpool config I have

num_init_children=290, 
max_pool- 2, 
child_life_time - 0
child_max_connections - 100, 
connection_life_time- 100, 
client_idle_limit - 0
do not cache onnections

Sometimes pgpool shows server status as "down", even though the server is up & running fine, due to this, i am getting a lot of issues.
Also i have in pgpool config:

health_check_timeout - 60
health_check_period - 30
health_check_max_retries - 3
health_check_retry_delay -5

Also pgpool is not writing log 🙁
I set the logging up as

log_destination "stderr"
debug_level 0
logdir /var/tmp

I checked permissions it has 777, so it's not the case.
when i start pgpool i start it with "-n" option to let it write log.

Any ideas why server shown as down & where is my logs ?

Best Answer

Here's a response about the logs.

First, the log_directory is the place where pgpool_status is written, not the actual logs, which is why you're not finding the logs there.

Logging can be done in either of two ways. You've got it set to send logs to STDERR, i.e. the file descriptor for error messages. In the script you're using to start pgpool, you could add a redirect for STDERR to a file, which would then be your logfile:

pgpool [whatever options you use] > /var/tmp/pgpool.log 2>&1

Another way is to set logging to go via the regular syslog daemon. In that case, change the log_destination like this:

log_destination 'syslog'

This will send the logs on facility local7. If you want it sent to another facility, use

syslog_facility 'local0'

or whatever facility you like. Of course, you also need to configure your syslog daemon to handle that facility.

Once that part is done, chances are it'll be easier for you to figure out the other problems.

Related Solutions

Postgresql – PG pool failover leads to two independent masters with network failure

First, I think that pgpool2 does have a failback command, but this wouldn't help you much in that case. The problem is that chaos will result if both machines think they are the master. What's more, here you had a simple case: network went down. What if the network is partitioned? I.e., both machines are connected, but they somehow lose connectivity to each other. In that case, both machines will become the master, and they will serve different clients, and you will have a forked database. It's a rarer case, but are you certain it's so unlikely that you are prepared to risk the resulting chaos?

An alternative would be this:

                                    +- master db
                                    |
                ------ pgpool ------+
                                    |
                                    +- hot standby

In that case, however, you have a single point of failure, pgpool, which you probably don't want. I know only two ways to address this problem. The easiest is to only promote a standby to master manually, and this is applicable with your architecture. Your applications will need to go to read-only mode until human intervention.

The second way is to have quorums. One architecture that could work is this:

                +--- pgpool standing by -+    +- master db
                |                        |    |
   failover ip -+--- active pgpool      -+----+- hot standby 1
                |                        |    |
                +--- pgpool standing by -+    +- hot standby 2
                                              |
                                              +- hot standby 3

                                              (as many standby servers as
                                              you want, so that you have
                                              read-only load balancing)

The three pgpools are running on three different machines, each with its own IP address, but they also provide an additional failover IP address, taken only by the active machine, and it is the one used by clients. If the active pgpool fails, a standby pgpool takes it over. This can be accomplished with heartbeat.

In order to promote a hot standby to master, a quorum of pgpools (i.e. at least two of the three) must decide so; and they will implement the decision only after a delay of, say, 10 seconds after they decide. In addition, the active pgpool may not continue to use the existing master db for more than 10 seconds without getting confirmation from at least another pgpool (this is to guard against the case that the two standby pgpools lose their connection to the active pgpool and to the master at the same time, promote a hot standby to master, but the active pgpool continues to use the old master).

Actually the third pgpool need not participate in the failover IP, and just be there in order to help the quorum. In addition, I don't know if pgpool has enough features to do this. Maybe you need another daemon. A more general architecture is this:

              +--- active pgpool      -+          +- master db
              |                        |          | 
 failover ip -+                       -+----------+- hot standby 1
              |                        |          | 
              +--- pgpool standing by -+      +---+- hot standby 2
                                              |   | 
                                              |   +- hot standby 3
                monitoring daemon 1 ---+      |
                                       |      |
                monitoring daemon 2 ---+------+
                                       |
                monitoring daemon 3 ---+

In this case the load balancing done by pgpool is separated from the monitoring and promotion of standby to a master. Note that you can put pgpools, database servers, and monitoring daemons on the same machine, but the two pgpools must be on two different machines and the three monitoring daemons must be on three different machines. Note that I don't know whether a ready-made monitoring daemon with all necessary features exist.

The details can be changed, but I think that if you do automatic standby promotion to master without using a quorum, you are asking for trouble.

Postgresql – Best PostgreSQL replication solution that minimizes network costs

After a week of research I believe Streaming Log Shipping and Hot Standby configuration of PostgreSQL instances serves my needs of instant replication (minimum window of data loss), yet low network traffic. I've written a detailed blog post on how I set it up.

There might be other solutions too, using 3rd party tools like pgpool, but I didn't have much success with them.

Best Answer

Related Solutions

Postgresql – PG pool failover leads to two independent masters with network failure

Postgresql – Best PostgreSQL replication solution that minimizes network costs

Related Topic