<html><head><meta http-equiv="content-type" content="text/html; charset=utf-8"></head><body style="overflow-wrap: break-word; -webkit-nbsp-mode: space; line-break: after-white-space;">Greetings everyone,<div><br></div><div>I’m running a 3 replica PostgreSQL cluster with Repmgr and each a PgPool-II in front in streaming replication mode.</div><div><br></div><div>This database is used by a GitLab deployment which comes with its own backup tool that snapshots via <i style="font-weight: bold;">pg_dump</i> and irregularly when this operation is executed I’m getting the following error:</div><div><br></div><div><pre class="code highlight" lang="shell"><span id="LC1" class="line" lang="shell">2023-03-05 00:00:38 UTC <span class="nt">--</span> Dumping database ...</span>
<span id="LC2" class="line" lang="shell">pg_dump: error: query failed: FATAL: failed to <span class="nb">read </span>kind from backend</span>
<span id="LC3" class="line" lang="shell">DETAIL: kind mismatch among backends. Possible last query was: <span class="s2">"SET TRANSACTION SNAPSHOT '00000059-0001C517-1'"</span> kind details are: 0[E: invalid snapshot identifier: <span class="s2">"00000059-0001C517-1"</span><span class="o">]</span> 1[C]</span>
<span id="LC4" class="line" lang="shell">HINT: check data consistency among db nodes</span>
<span id="LC5" class="line" lang="shell">server closed the connection unexpectedly</span>
<span id="LC6" class="line" lang="shell"> This probably means the server terminated abnormally</span>
<span id="LC7" class="line" lang="shell"> before or <span class="k">while </span>processing the request.</span>
<span id="LC8" class="line" lang="shell">pg_dump: detail: Query was: SET TRANSACTION SNAPSHOT <span class="s1">'00000059-0001C517-1'</span></span>
<span id="LC9" class="line" lang="shell">Dumping PostgreSQL database gitlabhq_production ... 2023-03-05 00:00:38 UTC <span class="nt">--</span> Dumping database failed: Failed to create compressed file <span class="s1">'/srv/gitlab/tmp/backups/db/database.sql.gz'</span> when trying to backup the main database:</span>
<span id="LC10" class="line" lang="shell"> - host: <span class="s1">'pg-pgpool.gitlab-postgres.svc'</span></span>
<span id="LC11" class="line" lang="shell"> - port: <span class="s1">'5432'</span></span>
<span id="LC12" class="line" lang="shell"> - database: <span class="s1">‘gitlabhq_production’</span></span>
</pre><pre class="code highlight" lang="shell"><span class="line" lang="shell"><br></span></pre><pre class="code highlight" lang="shell"><span class="line" lang="shell"><span class="s1"><font face="Helvetica">My initial thought was that something is not right with the replication, so I’ve executed on PgPool-II the following command to check for replication issues:</font></span></span></pre><pre class="code highlight" lang="shell"><span class="line" lang="shell"><pre class="code highlight" lang="plaintext"><span class="line" id="LC1" lang="plaintext">psql (14.5)</span>
<span class="line" id="LC2" lang="plaintext">Type "help" for help.</span>
<span class="line" id="LC3" lang="plaintext"></span>
<span class="line" id="LC4" lang="plaintext">postgres=# show pool_nodes;</span>
<span class="line" id="LC5" lang="plaintext"> node_id | hostname | port | status | pg_status | lb_weight | role | pg_role | select_cnt | load_balance_node | replication_delay | replication_state | replication_sync_state | last_status_change</span>
<span class="line" id="LC6" lang="plaintext">---------+----------------------------------------+------+--------+-----------+-----------+---------+---------+------------+-------------------+-------------------+-------------------+------------------------+---------------------</span>
<span class="line" id="LC7" lang="plaintext"> 0 | pg-postgresql-0.pg-postgresql-headless | 5432 | up | up | 0.333333 | primary | primary | 93 | false | 0 | | | 2023-02-14 13:02:35</span>
<span class="line" id="LC8" lang="plaintext"> 1 | pg-postgresql-1.pg-postgresql-headless | 5432 | up | up | 0.333333 | standby | standby | 62 | false | 0 | | | 2023-02-14 13:03:15</span>
<span class="line" id="LC9" lang="plaintext"> 2 | pg-postgresql-2.pg-postgresql-headless | 5432 | up | up | 0.333333 | standby | standby | 86 | true | 0 | | | 2023-02-14 13:03:15</span>
<span class="line" id="LC10" lang="plaintext">(3 rows)</span>
</pre><pre class="code highlight" lang="plaintext"><span class="line" lang="plaintext"><br></span></pre><pre class="code highlight" lang="plaintext"><font face="Helvetica">Further searches on the Internet took me to the point that, in streaming replication mode, the columns replication_state and replication_sync_state have to be populated, as per this support document.</font></pre><font face="Helvetica">Now my confusion is, what I’m facing currently, is this an aftermath of the above mentioned fields not being populated? I’m not really familiar with how replication is tackled on PgPool-II, does anyone have any thoughts as what could cause such issues?</font><br></span></pre><pre class="code highlight" lang="shell"><font face="Helvetica">Best regards,</font></pre><pre class="code highlight" lang="shell"><font face="Helvetica">~Daniel M.</font></pre></div></body></html>