[pgpool-general: 8636] Re: PgPool-II throws 'kind mismatch among backends'

[Tatsuo Ishii]

> Greetings everyone,
> 
> I’m running a 3 replica PostgreSQL cluster with Repmgr and each a PgPool-II in front in streaming replication mode.
> 
> This database is used by a GitLab deployment which comes with its own backup tool that snapshots via pg_dump and irregularly when this operation is executed I’m getting the following error:
> 
> 2023-03-05 00:00:38 UTC -- Dumping database ...
> pg_dump: error: query failed: FATAL:  failed to read kind from backend
> DETAIL:  kind mismatch among backends. Possible last query was: "SET TRANSACTION SNAPSHOT '00000059-0001C517-1'" kind details are: 0[E: invalid snapshot identifier: "00000059-0001C517-1"] 1[C]

pg_dump issues SET TRANSACTION SNAPSHOT command in parallel dump
mode. The argument to SET TRANSACTION SNAPSHOT command is "snapshot
id", which is different from server to server. The snapshot id had
been acquired by pg_export_snapshot() function beforehand (I think the
call to the function was made in pg_dump). Unfortunately snapshot id
is differ server to server. In this case probably the snapshot id was
acquired on node 1, which is not valid on node 0. That's why node 0
complained "invalid snapshot identifier: "00000059-0001C517-1". There
are some workarounds:

1) Let pg_dump connect to PostgreSQL directly (not via pgpool). This
is the most recommended way. Not only to avoid the issue, but in
general, since pg_dump involves lots of packets flying over wire,
running pg_dump via pgpool will be slow.

If this is not possible, try #2 or #3.

2) Let pgpool redirect to the primary node while running
pg_dump. You can set below in pgpool.conf.

app_name_redirect_preference_list = 'pg_dump:primary'

You need to reload pgpool.conf or restart pgpool.

3) Do not use parallel dump mode.

> postgres=# show pool_nodes;
>  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
> ---------+----------------------------------------+------+--------+-----------+-----------+---------+---------+------------+-------------------+-------------------+-------------------+------------------------+---------------------
>  0       | pg-postgresql-0.pg-postgresql-headless | 5432 | up     | up        | 0.333333  | primary | primary | 93         | false             | 0                 |                   |                        | 2023-02-14 13:02:35
>  1       | pg-postgresql-1.pg-postgresql-headless | 5432 | up     | up        | 0.333333  | standby | standby | 62         | false             | 0                 |                   |                        | 2023-02-14 13:03:15
>  2       | pg-postgresql-2.pg-postgresql-headless | 5432 | up     | up        | 0.333333  | standby | standby | 86         | true              | 0                 |                   |                        | 2023-02-14 13:03:15
> (3 rows)
> 
> 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.
> Now my confusion is, what I’m facing currently, is this an aftermath of the above mentioned fields not being populated?

Definitely no.

> I’m not really familiar with how replication is tackled on PgPool-II, does anyone have any thoughts as what could cause such issues?
> Best regards,
> ~Daniel M.

Pool-II corrects necessary information by using daemon process called
"streaming replication check process".  In order for those fields to
display correctly, you need to properly set streaming replication
related parameters used by the process (sr_check_period, sr_check_user
sr_check_password and sr_check_database).
https://www.pgpool.net/docs/44/en/html/runtime-streaming-replication-check.html#GUC-SR-CHECK-USER

If sr_check_user is not postgres super user, you need to join the user to pg_monitor group.
See the note at:
https://www.pgpool.net/docs/44/en/html/pcp-node-info.html

Also you need to set backend_application_name properly.
https://www.pgpool.net/docs/44/en/html/runtime-config-backend-settings.html#RUNTIME-CONFIG-BACKEND-DATA

Best reagards,
--
Tatsuo Ishii
SRA OSS LLC
English: http://www.sraoss.co.jp/index_en/
Japanese:http://www.sraoss.co.jp