https://progress.opensuse.org/https://progress.opensuse.org/themes/openSUSE/favicon/favicon.ico?15829177842019-08-09T07:32:41ZopenSUSE Project Management ToolopenQA Project - action #55262: Install Pgpool-II or PgBouncer before PostgreSQL for openQA instances, e.g. to be used on OSDhttps://progress.opensuse.org/issues/55262?journal_id=2336932019-08-09T07:32:41Zokurzokurz@suse.com
<ul></ul><p>so you are suggesting this to be installed but so far there is no reference to it within the packages or so, shouldn't we try to handle that upstream to e.g. for example "recommend" this?</p>
<p>Also, I have seen the message you gave as well. However shouldn't we rather investigate why this suddenly recently appeared and not "jump to conclusions"?</p>
openQA Project - action #55262: Install Pgpool-II or PgBouncer before PostgreSQL for openQA instances, e.g. to be used on OSDhttps://progress.opensuse.org/issues/55262?journal_id=2441362019-09-17T15:31:50Zokurzokurz@suse.com
<ul></ul><p>On 2019-09-17 we had a similar incidence – see discussion in <a href="https://chat.suse.de/group/openqa-dev" class="external">https://chat.suse.de/group/openqa-dev</a> – which might be preventable by the suggestion mentioned in this ticket.</p>
openQA Project - action #55262: Install Pgpool-II or PgBouncer before PostgreSQL for openQA instances, e.g. to be used on OSDhttps://progress.opensuse.org/issues/55262?journal_id=3154172020-07-29T07:12:58Zokurzokurz@suse.com
<ul><li><strong>Priority</strong> changed from <i>Normal</i> to <i>Low</i></li></ul> openQA Project - action #55262: Install Pgpool-II or PgBouncer before PostgreSQL for openQA instances, e.g. to be used on OSDhttps://progress.opensuse.org/issues/55262?journal_id=3157542020-07-30T09:46:09Zokurzokurz@suse.com
<ul><li><strong>Related to</strong> <i><a class="issue tracker-4 status-3 priority-3 priority-lowest closed child" href="/issues/69355">action #69355</a>: [spike] redundant/load-balancing webui deployments of openQA</i> added</li></ul> openQA Project - action #55262: Install Pgpool-II or PgBouncer before PostgreSQL for openQA instances, e.g. to be used on OSDhttps://progress.opensuse.org/issues/55262?journal_id=3393852020-10-22T13:36:13Zokurzokurz@suse.com
<ul><li><strong>Target version</strong> set to <i>Ready</i></li></ul> openQA Project - action #55262: Install Pgpool-II or PgBouncer before PostgreSQL for openQA instances, e.g. to be used on OSDhttps://progress.opensuse.org/issues/55262?journal_id=3450372020-10-30T05:39:00Zokurzokurz@suse.com
<ul><li><strong>Target version</strong> changed from <i>Ready</i> to <i>future</i></li></ul> openQA Project - action #55262: Install Pgpool-II or PgBouncer before PostgreSQL for openQA instances, e.g. to be used on OSDhttps://progress.opensuse.org/issues/55262?journal_id=3531042020-11-21T18:50:02Zokurzokurz@suse.com
<ul><li><strong>Project</strong> changed from <i>openQA Infrastructure</i> to <i>openQA Project</i></li><li><strong>Subject</strong> changed from <i>Install Pgpool-II or PgBouncer before PostgreSQL for OSD</i> to <i>Install Pgpool-II or PgBouncer before PostgreSQL for openQA instances, e.g. to be used on OSD</i></li><li><strong>Category</strong> set to <i>Feature requests</i></li><li><strong>Parent task</strong> set to <i>#80142</i></li></ul> openQA Project - action #55262: Install Pgpool-II or PgBouncer before PostgreSQL for openQA instances, e.g. to be used on OSDhttps://progress.opensuse.org/issues/55262?journal_id=5387172022-07-19T16:44:42Zkraihsebastian.riedel@suse.com
<ul></ul><p>Today i've tried both pgbouncer and pgpool-II in production with the legaldb. Here are some notes:</p>
<ul>
<li><p><strong>pgbouncer</strong>: Not in Factory yet. Easy to install from package in <a href="https://build.opensuse.org/package/show/server:database:postgresql/pgbouncer" class="external">server:database:postgresql</a>. Simple configuration file with 3 basic modes of operation. Transaction mode pretty much just works for CRUD queries. Each transaction gets forwarded round robin to a backend connection in the pool as they become available. This would work great for the webui itself. Only problem is that pgbouncer does not support listen/notify, which the Minion job queue uses as an optimization to dequeue jobs faster. So it cannot be used for Gru. It has only minimal log output, but should be enough to track down issues.</p></li>
<li><p><strong>pgpool-II</strong>: Also not in Factory yet. Much harder to install, because the package doesn't appear to build currently for SLE/Leap 15.4, had to use the binary package from <a href="https://build.opensuse.org/package/show/server:database:postgresql/pgpool-II" class="external">server:database:postgresql</a>. Hard to configure, and the official documentation is close to useless. The operating modes here are very focused on replication with multiple replication strategies. Lots of options for external services to do failover and performance monitoring. Theoretically it supports listen/notify, but i ran into a problem where the job queue just froze, with queries not getting results anymore. Probably a misconfiguration on my part, but pgpool-II is clearly not a component you just add to your setup without serious preparation. The main focus seems to be failover for multiple replicated backend servers.</p></li>
</ul>
<p>On the plus side, both packages from the devel project have .service files and example configs.</p>
<p>Update: After some more experimentation it seems pgpool-II works pretty well with the Minion job queue when these settings are used with one configured backend server: <code>backend_clustering_mode = 'raw'</code>, <code>load_balance_mode = off</code>, <code>use_watchdog = off</code>, <code>memory_cache_enabled = off</code>.</p>