Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1tYAjC-0055dZ-7x for pgsql-general@arkaria.postgresql.org; Wed, 15 Jan 2025 21:15:06 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.94.2) (envelope-from ) id 1tYAjA-0003Zy-54 for pgsql-general@arkaria.postgresql.org; Wed, 15 Jan 2025 21:15:04 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1tYAj9-0003Zp-NW for pgsql-general@lists.postgresql.org; Wed, 15 Jan 2025 21:15:04 +0000 Received: from mail-il1-x12a.google.com ([2607:f8b0:4864:20::12a]) by magus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.96) (envelope-from ) id 1tYAj7-000cke-2X for pgsql-general@lists.postgresql.org; Wed, 15 Jan 2025 21:15:03 +0000 Received: by mail-il1-x12a.google.com with SMTP id e9e14a558f8ab-3ce34c6872aso1747375ab.0 for ; Wed, 15 Jan 2025 13:15:01 -0800 (PST) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20230601; t=1736975700; x=1737580500; darn=lists.postgresql.org; h=cc:to:subject:message-id:date:from:in-reply-to:references :mime-version:from:to:cc:subject:date:message-id:reply-to; bh=J3jWtOGHumGcJqIeudAYmj0L5fg7UhXkT6P8s4ZvGCM=; b=YG5L8LGqVOeHFf67P1Xu4VaW85DK1CPpfp/JMcZdwP+BOGM9GcjJNTFOtkv1Tw0QoS TfYDN2yNAG0tjU2qzgtRzVE0bC0sFrkC+y1Jf/iWHnfEQu3aZCrUv/N4S5Oup7LTy9Sq P+zjvMK6dEwWJMApRrdWGLLRZXnsOCrLtpBsmjA6G+FOMUVX19wZmEhtg/taY5Ng07jF JBpykM9FtmiZNQW8v2i7gme4FvEd+sL2uzZF3u75waByBH18PuCkgKBJTHh+eLwjjn9+ NnrSx4q0myLcIHdOneTSpItMqbQUKAtp0qR/a5roNCK2XmkrzstBuhLxcM6Bm0LoEP7U QoEQ== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20230601; t=1736975700; x=1737580500; h=cc:to:subject:message-id:date:from:in-reply-to:references :mime-version:x-gm-message-state:from:to:cc:subject:date:message-id :reply-to; bh=J3jWtOGHumGcJqIeudAYmj0L5fg7UhXkT6P8s4ZvGCM=; b=UfuIV1GdxTF+KL9olQH7hmCotMyDEH4kMYuS9FB4tFq4vJS7mj3tyzzA8PnFqNMQ2+ mwYlyi1NjDU4kReXbQcYP4kr4+tzRCnRQCLaucoTIOoDH0pyIfYWUXf43nh7OnfAdqvu wSDhBASRLxRbb0zljVQdbUvyIeiuIoTb4+MzWXGnSOo6b26YC4vndOoH29h+wkmNhV50 RMDO2wJQT3hxByjfy4mQPSqjtjh0RJn6EB8b4Ny2FE+TZHLcQpvzpsQOAnOAJEk74rGe BiBB1x9lVhOv7V6oMvSzioHE0vXF7Iya9if4mLfHMVb5DT6aGrKna4xcePVaRU0dQ235 85GA== X-Gm-Message-State: AOJu0YxgTwS2m76da5s9lzD02wcY4QK57ol8LJ1UJkqIIHAjb6Cn+dxM AZgML7/tBoSfwtugSfcfrIDRX7SuDZBLd5FmJ9KBTnn8f6+SBPKjZ5SzZO/yhwX1pkl1URxMxOO SNSVb53hrKQOKu2OsLG5IShm1tMxLpDaP X-Gm-Gg: ASbGncs/6odX1NUiv24uZPD/qTg4Erf4/+WsoA8DUC406o1Dj2VawgDjU/8+GT5NK69 lP2UC/DsfhHsuekRfJIm9xGWO9KQEp8elihN7EQ== X-Google-Smtp-Source: AGHT+IFlQ5HxsMlV2UIwIrzeyBAW4mJ176WzGaVIICfTU2gmFpdTc13eRc0aDnhKXkZzJ47WFt3K8CQMzL8jXfRhQdM= X-Received: by 2002:a05:6e02:1a47:b0:3ce:6828:8955 with SMTP id e9e14a558f8ab-3ce68288f5bmr143308645ab.5.1736975699917; Wed, 15 Jan 2025 13:14:59 -0800 (PST) MIME-Version: 1.0 References: <37ae75bc-6385-4e41-9a48-e03e7884bcf3@vondra.me> In-Reply-To: <37ae75bc-6385-4e41-9a48-e03e7884bcf3@vondra.me> From: Tim Gerber Date: Wed, 15 Jan 2025 15:14:49 -0600 X-Gm-Features: AbW1kvaGMthkhsugESKtclyd0cm_fEs5TVFGt4nbPqJdwKkO5VnOGvMleJOi-S0 Message-ID: Subject: Re: Data Out of Sync with Physical Streaming Replication To: Tomas Vondra Cc: pgsql-general@lists.postgresql.org Content-Type: multipart/alternative; boundary="00000000000093c3c5062bc52b36" List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --00000000000093c3c5062bc52b36 Content-Type: text/plain; charset="UTF-8" Content-Transfer-Encoding: quoted-printable Thanks for the thoughts, Tomas. I suspected storage only because data was missing. Everything I've looked at is not showing any indication of this. Here's the tuple data for each. Appears to look normal to me. ISSUE table: PRIMARY: issuedb=3D#=3D# SELECT ctid, xmin, xmax, issue_id as issue_id from issue; ctid | xmin | xmax | issue_id -------+------+------+-------------------------------------- (0,1) | 3439 | 3443 | 0192d4c1-7220-7780-be76-e67f955a719a (0,2) | 3439 | 3439 | 0192d4c1-7233-725c-a97a-0acc82fa2c2a (0,5) | 3724 | 3724 | 01934472-b052-7ce2-9c7d-35a809bcb3f6 (3 rows) issuedb=3D#=3D# SELECT t_xmin, t_xmax, tuple_data_split('issue'::regclass, t_data, t_infomask, t_infomask2, t_bits) issuedb=3D#-# FROM heap_page_items(get_raw_page('issue', 0)); t_xmin | t_xmax | tuple_data_split --------+--------+---------------------------------------------------------= ---------------------------------------------------------------------------= ----------------------------------------------------------------------- 3439 | 3443 | {"\\x0192d4c172207780be76e67f955a719a","\\x0d3534333231","\\x1b3031322d3131= 3131312d3031","\\x01",NULL,NULL,"\\x01",NULL,NULL,NULL,"\\xbc6d6ed88dc80200= ","\\x1d62756b6174796e6963686f6c61",NULL,NULL} 3439 | 3439 | {"\\x0192d4c17233725ca97a0acc82fa2c2a","\\x0f363534333231","\\x1b3031322d31= 313131312d3032","\\x01",NULL,NULL,"\\x00",NULL,NULL,NULL,"\\xb5986ed88dc802= 00","\\x1d62756b6174796e6963686f6c61",NULL,NULL} 3698 | 3711 | {"\\x019342bde8217af59ebbb1315860c475","\\x0531","\\x0532","\\x01",NULL,NUL= L,"\\x00","\\x01",NULL,NULL,"\\xf5f19a7a3bca0200","\\x1767696c6c696c616e646= a",NULL,NULL} 3717 | 3719 | {"\\x0193446fa7de79c6a6c4bc508ab31f30","\\x15334231323334353637","\\x1b3031= 322d30303030302d3030","\\x01",NULL,NULL,"\\x00","\\x01",NULL,NULL,"\\x66eee= f1842ca0200","\\x1767696c6c696c616e646a",NULL,NULL} 3724 | 3724 | {"\\x01934472b0527ce29c7d35a809bcb3f6","\\x133141313233343536","\\x1b303132= 2d30303030302d3030","\\x01",NULL,NULL,"\\x00","\\x01",NULL,NULL,"\\x67f1c82= 442ca0200","\\x1767696c6c696c616e646a",NULL,NULL} (5 rows) STANDBY: issuedb=3D# SELECT ctid, xmin, xmax, issue_id as issue_id from issue; ctid | xmin | xmax | issue_id -------+------+------+-------------------------------------- (0,1) | 3439 | 3443 | 0192d4c1-7220-7780-be76-e67f955a719a (0,2) | 3439 | 3439 | 0192d4c1-7233-725c-a97a-0acc82fa2c2a (2 rows) issuedb=3D# SELECT t_xmin, t_xmax, tuple_data_split('issue'::regclass, t_data, t_infomask, t_infomask2, t_bits) issuedb-# FROM heap_page_items(get_raw_page('issue', 0)); t_xmin | t_xmax | tuple_data_split --------+--------+---------------------------------------------------------= ---------------------------------------------------------------------------= -------------------------------------------------------------------- 3439 | 3443 | {"\\x0192d4c172207780be76e67f955a719a","\\x0d3534333231","\\x1b3031322d3131= 3131312d3031","\\x01",NULL,NULL,"\\x01",NULL,NULL,NULL,"\\xbc6d6ed88dc80200= ","\\x1d62756b6174796e6963686f6c61",NULL,NULL} 3439 | 3439 | {"\\x0192d4c17233725ca97a0acc82fa2c2a","\\x0f363534333231","\\x1b3031322d31= 313131312d3032","\\x01",NULL,NULL,"\\x00",NULL,NULL,NULL,"\\xb5986ed88dc802= 00","\\x1d62756b6174796e6963686f6c61",NULL,NULL} (2 rows) COMMENT table: PRIMARY: issuedb=3D# select ctid, xmin, xmax, comment_id from comment; ctid | xmin | xmax | comment_id -------+------+------+-------------------------------------- (0,1) | 3448 | 0 | 0192d528-f0f8-7ab9-b709-035bf31b5a0c (0,2) | 3737 | 0 | 019345d3-7451-7483-a8e4-ec60a4c12442 (0,3) | 3738 | 0 | 019345d4-2a00-7e18-874c-1c8b9e4f36f9 (0,4) | 3739 | 0 | 019345d5-2c5a-7aac-8d58-614272fe0e60 (4 rows) issuedb=3D# SELECT t_xmin, t_xmax, tuple_data_split('comment'::regclass, t_data, t_infomask, t_infomask2, t_bits) issuedb-# FROM heap_page_items(get_raw_page('comment', 0)); t_xmin | t_xmax | tuple_data_split --------+--------+---------------------------------------------------------= ---------------------------------------------------------------------------= ---------------------------------------------------------------------------= ------ ---------------------------------------------------------------------------= ---- 3448 | 0 | {"\\x0192d528f0f87ab9b709035bf31b5a0c","\\x2554686973206973206120636f6d6d65= 6e74","\\x4b30313932633535612d646533372d373064642d626263312d353337366232346= 130373036","\\x0b554e4954","\\x1d62756b6174796e6963686f6c61"," \\xc1acb56c8fc80200",NULL} 3737 | 0 | {"\\x019345d374517483a8e4ec60a4c12442","\\x39546869732069732061206469666665= 72656e7420636f6d6d656e74","\\x4b30313932633535612d646533372d373064642d62626= 3312d353337366232346130373036","\\x0b554e4954","\\x1d62756b617 4796e6963686f6c61","\\x5a8ac68647ca0200",NULL} 3738 | 0 | {"\\x019345d42a007e18874c1c8b9e4f36f9","\\x3d54686973206973206120636f6d6d65= 6e74206f6e206120726570616972","\\x4b30313932643463312d373232302d373738302d6= 26537362d653637663935356137313961","\\x21415353454d424c595f524 550414952","\\x1d62756b6174796e6963686f6c61","\\x52328c8947ca0200",NULL} 3739 | 0 | {"\\x019345d52c5a7aac8d58614272fe0e60","\\x4954686973206973206120636f6d6d65= 6e74206f6e20612074726f75626c6573686f6f74","\\x4b30313932643364332d313131662= d376330302d623031382d366161636639363739386336","\\x1b54524f554 24c4553484f4f54","\\x1d62756b6174796e6963686f6c61","\\xa76c7d8d47ca0200",NU= LL} (4 rows) STANDBY: issuedb=3D# select ctid, xmin, xmax, comment_id from comment; ctid | xmin | xmax | comment_id -------+------+------+-------------------------------------- (0,1) | 3448 | 0 | 0192d528-f0f8-7ab9-b709-035bf31b5a0c (1 row) issuedb=3D# SELECT t_xmin, t_xmax, tuple_data_split('comment'::regclass, t_data, t_infomask, t_infomask2, t_bits) issuedb-# FROM heap_page_items(get_raw_page('comment', 0)); t_xmin | t_xmax | tuple_data_split --------+--------+---------------------------------------------------------= ---------------------------------------------------------------------------= ---------------------------------------------------------------------------= ------ --------------------------- 3448 | 0 | {"\\x0192d528f0f87ab9b709035bf31b5a0c","\\x2554686973206973206120636f6d6d65= 6e74","\\x4b30313932633535612d646533372d373064642d626263312d353337366232346= 130373036","\\x0b554e4954","\\x1d62756b6174796e6963686f6c61"," \\xc1acb56c8fc80200",NULL} (1 row) TROUBLESHOOT_ISSUE table: PRIMARY: issuedb=3D# SELECT ctid, xmin, xmax, troubleshoot_issue_id from troubleshoot_issue; ctid | xmin | xmax | troubleshoot_issue_id -------+------+------+-------------------------------------- (0,1) | 3439 | 0 | d80a394a-9568-11ef-90aa-5bb5a149f782 (0,2) | 3439 | 0 | d80a5dda-9568-11ef-90aa-5b4cba555710 (0,5) | 3724 | 0 | d38a3624-a673-11ef-bb77-c3e784e70250 (3 rows) issuedb=3D# SELECT t_xmin, t_xmax, tuple_data_split('troubleshoot_issue'::regclass, t_data, t_infomask, t_infomask2, t_bits) issuedb-# FROM heap_page_items(get_raw_page('troubleshoot_issue', 0)); t_xmin | t_xmax | tuple_data_split --------+--------+---------------------------------------------------------= ------------------------------------------------------------ 3439 | 0 | {"\\xd80a394a956811ef90aa5bb5a149f782","\\x0192d3d3111f7c00b0186aacf96798c6= ","\\x0192d4c172207780be76e67f955a719a"} 3439 | 0 | {"\\xd80a5dda956811ef90aa5b4cba555710","\\x0192d3d3111f7c00b0186aacf96798c6= ","\\x0192d4c17233725ca97a0acc82fa2c2a"} 3698 | 3711 | {"\\x2dbecb16a63111efa2a30f7da92c7fb4","\\x019342bdc3617d28ae08db06da805986= ","\\x019342bde8217af59ebbb1315860c475"} 3717 | 3719 | {"\\x5d106810a67311ef84c19fcba7f0ea8d","\\x0193446c0c297714a349af8702c16dd5= ","\\x0193446fa7de79c6a6c4bc508ab31f30"} 3724 | 0 | {"\\xd38a3624a67311efbb77c3e784e70250","\\x019344723f7778b6b34305157fb9a339= ","\\x01934472b0527ce29c7d35a809bcb3f6"} (5 rows) STANDBY: issuedb=3D# SELECT ctid, xmin, xmax, troubleshoot_issue_id as issue_id fro= m troubleshoot_issue; ctid | xmin | xmax | troubleshoot_issue_id -------+------+------+-------------------------------------- (0,1) | 3439 | 0 | d80a394a-9568-11ef-90aa-5bb5a149f782 (0,2) | 3439 | 0 | d80a5dda-9568-11ef-90aa-5b4cba555710 (2 rows) issuedb=3D# SELECT t_xmin, t_xmax, tuple_data_split('troubleshoot_issue'::regclass, t_data, t_infomask, t_infomask2, t_bits) issuedb-# FROM heap_page_items(get_raw_page('troubleshoot_issue', 0)); t_xmin | t_xmax | tuple_data_split --------+--------+---------------------------------------------------------= ------------------------------------------------------------ 3439 | 0 | {"\\xd80a394a956811ef90aa5bb5a149f782","\\x0192d3d3111f7c00b0186aacf96798c6= ","\\x0192d4c172207780be76e67f955a719a"} 3439 | 0 | {"\\xd80a5dda956811ef90aa5b4cba555710","\\x0192d3d3111f7c00b0186aacf96798c6= ","\\x0192d4c17233725ca97a0acc82fa2c2a"} (2 rows) Thanks, Tim On Wed, Jan 15, 2025 at 2:27=E2=80=AFPM Tomas Vondra wrot= e: > On 1/15/25 19:50, Tim Gerber wrote: > > Hi All, > > > >... > > > > Archive mode is on. I ran pg_amcheck and everything came back clean. = I > > know synchronous replication is an option, but it feels like something > > else is going on and would like to get to the bottom of it. > > > > Any ideas on what could be causing this? I=E2=80=99m planning on turni= ng on > > data_checksums and running pg_checksums to see if any corruption is > found. > > > > No idea. If the storage has some issues (which is the only thing data > checksums could catch, in some cases), then anything is possible. > > I think it'd be interesting to compare the tuples returned on primary > vs. standby, including system columns etc. Ideally using pageinspect. > That might tell you what exactly is the difference, and perhaps also say > which XID to look at. > > regards > > -- > Tomas Vondra > > --00000000000093c3c5062bc52b36 Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable


Thanks for the thoughts, Tomas.

I suspected= storage only because data was missing.=C2=A0 Everything I've looked at= is not showing any indication of this.=C2=A0

Here's the tuple = data for each.=C2=A0 Appears to look normal to me.

=C2=A0
ISSUE = table:=C2=A0

PRIMARY:

issuedb=3D#=3D# =C2= =A0 SELECT ctid, xmin, xmax, issue_id as issue_id from issue;
=C2=A0ctid= =C2=A0| xmin | xmax | =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 iss= ue_id
-------+------+------+--------------------------------------
= =C2=A0(0,1) | 3439 | 3443 | 0192d4c1-7220-7780-be76-e67f955a719a
=C2=A0(= 0,2) | 3439 | 3439 | 0192d4c1-7233-725c-a97a-0acc82fa2c2a
=C2=A0(0,5) | = 3724 | 3724 | 01934472-b052-7ce2-9c7d-35a809bcb3f6
(3 rows)

issue= db=3D#=3D# =C2=A0SELECT t_xmin, t_xmax, tuple_data_split('issue'::r= egclass, t_data, t_infomask, t_infomask2, t_bits)
issuedb=3D#-# =C2=A0FR= OM heap_page_items(get_raw_page('issue', 0));
=C2=A0t_xmin | t_x= max | =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0= =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 = =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 tuple_data_split
--------+--------+-----= ---------------------------------------------------------------------------= ---------------------------------------------------------------------------= ------------------------------------------------
=C2=A0 =C2=A03439 | =C2= =A0 3443 | {"\\x0192d4c172207780be76e67f955a719a","\\x0d3534= 333231","\\x1b3031322d31313131312d3031","\\x01",NU= LL,NULL,"\\x01",NULL,NULL,NULL,"\\xbc6d6ed88dc80200",&q= uot;\\x1d62756b6174796e6963686f6c61",NULL,NULL}
=C2=A0 =C2=A03439 |= =C2=A0 3439 | {"\\x0192d4c17233725ca97a0acc82fa2c2a","\\x0f= 363534333231","\\x1b3031322d31313131312d3032","\\x01&qu= ot;,NULL,NULL,"\\x00",NULL,NULL,NULL,"\\xb5986ed88dc80200&qu= ot;,"\\x1d62756b6174796e6963686f6c61",NULL,NULL}
=C2=A0 =C2=A0= 3698 | =C2=A0 3711 | {"\\x019342bde8217af59ebbb1315860c475","= ;\\x0531","\\x0532","\\x01",NULL,NULL,"\\x00&= quot;,"\\x01",NULL,NULL,"\\xf5f19a7a3bca0200","\\x= 1767696c6c696c616e646a",NULL,NULL}
=C2=A0 =C2=A03717 | =C2=A0 3719 = | {"\\x0193446fa7de79c6a6c4bc508ab31f30","\\x153342313233343= 53637","\\x1b3031322d30303030302d3030","\\x01",NUL= L,NULL,"\\x00","\\x01",NULL,NULL,"\\x66eeef1842ca0= 200","\\x1767696c6c696c616e646a",NULL,NULL}
=C2=A0 =C2=A0= 3724 | =C2=A0 3724 | {"\\x01934472b0527ce29c7d35a809bcb3f6","= ;\\x133141313233343536","\\x1b3031322d30303030302d3030",&quo= t;\\x01",NULL,NULL,"\\x00","\\x01",NULL,NULL,"= ;\\x67f1c82442ca0200","\\x1767696c6c696c616e646a",NULL,NULL}=
(5 rows)

STANDBY:

=C2=A0issuedb=3D# SELECT ctid, xmi= n, xmax, issue_id as issue_id from issue;
=C2=A0ctid =C2=A0| xmin= | xmax | =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 issue_id
----= ---+------+------+--------------------------------------
=C2=A0(0,1) | 3= 439 | 3443 | 0192d4c1-7220-7780-be76-e67f955a719a
=C2=A0(0,2) | 3439 | 3= 439 | 0192d4c1-7233-725c-a97a-0acc82fa2c2a
(2 rows)

issuedb=3D# = =C2=A0SELECT t_xmin, t_xmax, tuple_data_split('issue'::regclass, t_= data, t_infomask, t_infomask2, t_bits)
issuedb-# =C2=A0FROM heap_page_it= ems(get_raw_page('issue', 0));
=C2=A0t_xmin | t_xmax | =C2=A0 = =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 = =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 = =C2=A0 =C2=A0 =C2=A0tuple_data_split
--------+--------+-----------------= ---------------------------------------------------------------------------= ---------------------------------------------------------------------------= ---------------------------------
=C2=A0 =C2=A03439 | =C2=A0 3443 | {&qu= ot;\\x0192d4c172207780be76e67f955a719a","\\x0d3534333231",&q= uot;\\x1b3031322d31313131312d3031","\\x01",NULL,NULL,"\= \x01",NULL,NULL,NULL,"\\xbc6d6ed88dc80200","\\x1d62756b= 6174796e6963686f6c61",NULL,NULL}
=C2=A0 =C2=A03439 | =C2=A0 3439 | = {"\\x0192d4c17233725ca97a0acc82fa2c2a","\\x0f363534333231&qu= ot;,"\\x1b3031322d31313131312d3032","\\x01",NULL,NULL,&= quot;\\x00",NULL,NULL,NULL,"\\xb5986ed88dc80200","\\x1d= 62756b6174796e6963686f6c61",NULL,NULL}
(2 rows)




=
COMMENT table:=C2=A0

PRIMARY:

<= /div>
issuedb=3D# =C2=A0select ctid, xmin, xmax, comment_id from commen= t;
=C2=A0ctid =C2=A0| xmin | xmax | =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 = =C2=A0 =C2=A0comment_id
-------+------+------+--------------------------= ------------
=C2=A0(0,1) | 3448 | =C2=A0 =C2=A00 | 0192d528-f0f8-7ab9-b7= 09-035bf31b5a0c
=C2=A0(0,2) | 3737 | =C2=A0 =C2=A00 | 019345d3-7451-7483= -a8e4-ec60a4c12442
=C2=A0(0,3) | 3738 | =C2=A0 =C2=A00 | 019345d4-2a00-7= e18-874c-1c8b9e4f36f9
=C2=A0(0,4) | 3739 | =C2=A0 =C2=A00 | 019345d5-2c5= a-7aac-8d58-614272fe0e60
(4 rows)

issuedb=3D# =C2=A0 SELECT t_xmi= n, t_xmax, tuple_data_split('comment'::regclass, t_data, t_infomask= , t_infomask2, t_bits)
issuedb-# =C2=A0 FROM heap_page_items(get_raw_pag= e('comment', 0));
=C2=A0t_xmin | t_xmax | =C2=A0 =C2=A0 =C2=A0 = =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 = =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 = =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 = =C2=A0 =C2=A0 =C2=A0tuple_data_split

--------+--------+-------------= ---------------------------------------------------------------------------= ---------------------------------------------------------------------------= --------------------------------------------------
---------------------= ----------------------------------------------------------
=C2=A0 =C2=A0= 3448 | =C2=A0 =C2=A0 =C2=A00 | {"\\x0192d528f0f87ab9b709035bf31b5a0c&q= uot;,"\\x2554686973206973206120636f6d6d656e74","\\x4b3031393= 2633535612d646533372d373064642d626263312d353337366232346130373036",&qu= ot;\\x0b554e4954","\\x1d62756b6174796e6963686f6c61","\\xc1acb56c8fc80200",NULL}
=C2=A0 =C2=A03737 | =C2=A0 =C2=A0 =C2= =A00 | {"\\x019345d374517483a8e4ec60a4c12442","\\x3954686973= 206973206120646966666572656e7420636f6d6d656e74","\\x4b30313932633= 535612d646533372d373064642d626263312d353337366232346130373036","\= \x0b554e4954","\\x1d62756b617
4796e6963686f6c61","\\= x5a8ac68647ca0200",NULL}
=C2=A0 =C2=A03738 | =C2=A0 =C2=A0 =C2=A00 = | {"\\x019345d42a007e18874c1c8b9e4f36f9","\\x3d5468697320697= 3206120636f6d6d656e74206f6e206120726570616972","\\x4b303139326434= 63312d373232302d373738302d626537362d653637663935356137313961","\\= x21415353454d424c595f524
550414952","\\x1d62756b6174796e696368= 6f6c61","\\x52328c8947ca0200",NULL}
=C2=A0 =C2=A03739 | = =C2=A0 =C2=A0 =C2=A00 | {"\\x019345d52c5a7aac8d58614272fe0e60",&q= uot;\\x4954686973206973206120636f6d6d656e74206f6e20612074726f75626c6573686f= 6f74","\\x4b30313932643364332d313131662d376330302d623031382d36616= 1636639363739386336","\\x1b54524f554
24c4553484f4f54",&qu= ot;\\x1d62756b6174796e6963686f6c61","\\xa76c7d8d47ca0200",NU= LL}
(4 rows)

STANDBY:

issuedb=3D# = =C2=A0select ctid, xmin, xmax, comment_id from comment;
=C2=A0ctid =C2= =A0| xmin | xmax | =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0comment_= id
-------+------+------+--------------------------------------
=C2= =A0(0,1) | 3448 | =C2=A0 =C2=A00 | 0192d528-f0f8-7ab9-b709-035bf31b5a0c
= (1 row)

issuedb=3D# =C2=A0 SELECT t_xmin, t_xmax, tuple_data_split(&= #39;comment'::regclass, t_data, t_infomask, t_infomask2, t_bits)
iss= uedb-# =C2=A0 FROM heap_page_items(get_raw_page('comment', 0));
= =C2=A0t_xmin | t_xmax | =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 = =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 = =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 = =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0tuple_data_split

---= -----+--------+------------------------------------------------------------= ---------------------------------------------------------------------------= ---------------------------------------------------------------------------= ---
---------------------------
=C2=A0 =C2=A03448 | =C2=A0 =C2=A0 =C2= =A00 | {"\\x0192d528f0f87ab9b709035bf31b5a0c","\\x2554686973= 206973206120636f6d6d656e74","\\x4b30313932633535612d646533372d373= 064642d626263312d353337366232346130373036","\\x0b554e4954",&= quot;\\x1d62756b6174796e6963686f6c61","
\\xc1acb56c8fc80200&qu= ot;,NULL}
(1 row)



T= ROUBLESHOOT_ISSUE table:=C2=A0

PRIMARY:
=
issuedb=3D# =C2=A0SELECT ctid, xmin, xmax, troubleshoot_issu= e_id from troubleshoot_issue;
=C2=A0ctid =C2=A0| xmin | xmax |=C2= =A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0troubleshoot_issue_id-------+------+------+--------------------------------------
=C2=A0(0,= 1) | 3439 | =C2=A0 =C2=A00 | d80a394a-9568-11ef-90aa-5bb5a149f782
=C2=A0= (0,2) | 3439 | =C2=A0 =C2=A00 | d80a5dda-9568-11ef-90aa-5b4cba555710
=C2= =A0(0,5) | 3724 | =C2=A0 =C2=A00 | d38a3624-a673-11ef-bb77-c3e784e70250
= (3 rows)


issuedb=3D# =C2=A0SELECT t_xmin, t_xmax, tuple_data_spl= it('troubleshoot_issue'::regclass, t_data, t_infomask, t_infomask2,= t_bits)
issuedb-# =C2=A0FROM heap_page_items(get_raw_page('troubles= hoot_issue', 0));
=C2=A0t_xmin | t_xmax | =C2=A0 =C2=A0 =C2=A0 =C2= =A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 = =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A0tuple_data_split
--------+--------+----------------------------------= ---------------------------------------------------------------------------= --------
=C2=A0 =C2=A03439 | =C2=A0 =C2=A0 =C2=A00 | {"\\xd80a394a9= 56811ef90aa5bb5a149f782","\\x0192d3d3111f7c00b0186aacf96798c6&quo= t;,"\\x0192d4c172207780be76e67f955a719a"}
=C2=A0 =C2=A03439 | = =C2=A0 =C2=A0 =C2=A00 | {"\\xd80a5dda956811ef90aa5b4cba555710",&q= uot;\\x0192d3d3111f7c00b0186aacf96798c6","\\x0192d4c17233725ca97a= 0acc82fa2c2a"}
=C2=A0 =C2=A03698 | =C2=A0 3711 | {"\\x2dbecb16= a63111efa2a30f7da92c7fb4","\\x019342bdc3617d28ae08db06da805986&qu= ot;,"\\x019342bde8217af59ebbb1315860c475"}
=C2=A0 =C2=A03717 |= =C2=A0 3719 | {"\\x5d106810a67311ef84c19fcba7f0ea8d","\\x01= 93446c0c297714a349af8702c16dd5","\\x0193446fa7de79c6a6c4bc508ab31= f30"}
=C2=A0 =C2=A03724 | =C2=A0 =C2=A0 =C2=A00 | {"\\xd38a362= 4a67311efbb77c3e784e70250","\\x019344723f7778b6b34305157fb9a339&q= uot;,"\\x01934472b0527ce29c7d35a809bcb3f6"}
(5 rows)
=


STANDBY:

issuedb= =3D# =C2=A0SELECT ctid, xmin, xmax, troubleshoot_issue_id as issue_id from = troubleshoot_issue;
=C2=A0ctid =C2=A0| xmin | xmax |=C2=A0 =C2=A0 =C2=A0= =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0troubleshoot_issue_id
-------+------+= ------+--------------------------------------
=C2=A0(0,1) | 3439 | =C2= =A0 =C2=A00 | d80a394a-9568-11ef-90aa-5bb5a149f782
=C2=A0(0,2) | 3439 | = =C2=A0 =C2=A00 | d80a5dda-9568-11ef-90aa-5b4cba555710
(2 rows)

issuedb=3D# =C2=A0SELECT t_xmin, t_xmax, tuple_data_split('troublesho= ot_issue'::regclass, t_data, t_infomask, t_infomask2, t_bits)
issued= b-# =C2=A0FROM heap_page_items(get_raw_page('troubleshoot_issue', 0= ));
=C2=A0t_xmin | t_xmax | =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 = =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0tuple_data_split=
--------+--------+-----------------------------------------------------= ----------------------------------------------------------------
=C2=A0 = =C2=A03439 | =C2=A0 =C2=A0 =C2=A00 | {"\\xd80a394a956811ef90aa5bb5a149= f782","\\x0192d3d3111f7c00b0186aacf96798c6","\\x0192d4c= 172207780be76e67f955a719a"}
=C2=A0 =C2=A03439 | =C2=A0 =C2=A0 =C2= =A00 | {"\\xd80a5dda956811ef90aa5b4cba555710","\\x0192d3d311= 1f7c00b0186aacf96798c6","\\x0192d4c17233725ca97a0acc82fa2c2a"= ;}
(2 rows)


Thanks,
Tim

On Wed, Jan 15, 2025 at 2:27=E2=80=AFPM To= mas Vondra <tomas@vondra.me> w= rote:
On 1/15/25= 19:50, Tim Gerber wrote:
> Hi All,
>
>...
>
> Archive mode is on.=C2=A0=C2=A0 I ran pg_amcheck and everything came b= ack clean. I
> know synchronous replication is an option, but it feels like something=
> else is going on and would like to get to the bottom of it.
>
> Any ideas on what could be causing this?=C2=A0 I=E2=80=99m planning on= turning on
> data_checksums and running pg_checksums to see if any corruption is fo= und.
>

No idea. If the storage has some issues (which is the only thing data
checksums could catch, in some cases), then anything is possible.

I think it'd be interesting to compare the tuples returned on primary vs. standby, including system columns etc. Ideally using pageinspect.
That might tell you what exactly is the difference, and perhaps also say which XID to look at.

regards

--
Tomas Vondra

--00000000000093c3c5062bc52b36--