[pgpool-general: 4469] Dynamic configuration of pgpool

[Yuri Niyazov]

How do I manage read replicas that appear and disappear dynamically and
unpredictably in an Amazon Web Services environment? If I understand the
docs correctly at most, 127 read replicas can appear and disappear before
we absolutely must restart pgpool, correct?

Some background:

  We are running PostgreSQL in AWS. We have an always-on master database,
and a number of read-only replicas using streaming replication. At the
moment, we do not use pgpool - we just hardcode the hostnames of the master
and the read-only replicas in our app code.

  We want to dynamically scale up and down our read replicas based on daily
usage patterns, as well as use the cheaper spot EC2 instances that Amazon
provides - the caveat being that the latter can be shut down if the market
price goes above what we are willing to pay. When this happens, we would
either wait for the spot price to come down again, or bring up new
instances with a higher price.

  The following part from the manual for the "backend_hostname"
configuration setting suggests that using pgpool to accomplish this will be
hard: "New nodes can be added in this parameter by reloading a
configuration file. However, values cannot be updated so you must restart
pgpool-II in that case."

So, if we have, in the config:

backend_hostname1=A

and A goes down, then when its replacement B comes up, we can change the
config in the following way:

backend_hostname1=A
backend_hostname2=B

and tell pgpool to reload the config. I guess we can also remove the
backend_hostname1 line completely, like this:

backend_hostname2=B

We *cannot* edit backend_hostname1 like this and expect it work:

backend_hostname1=B

If I understand the code correctly, the biggest number allowed in the
backend_hostname<backend_number> is MAX_NUM_BACKENDS, which is 128.

This means that after we add backend_hostname127=ZZZ, we must drop back to
backend_hostname0=ZZZA and restart pgpool. Is there a way to avoid this?
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://www.sraoss.jp/pipermail/pgpool-general/attachments/20160217/e1922843/attachment-0001.html>