From: Michael Jaskiewicz <mjaskiewicz@ghx.com>
To: "pgsql-general@postgresql.org" <pgsql-general@postgresql.org>
Subject: Postgres Logical Replication - how to see what subscriber is doing
 with received data?
Thread-Topic: Postgres Logical Replication - how to see what subscriber is
 doing with received data?
Thread-Index: AQHa+bZxCLKpzmWpx0KPm1TxQQ8zJg==
Date: Thu, 29 Aug 2024 01:58:06 +0000
Message-ID:
 <BN9PR03MB59965B5087688309C0D79DCEB7962@BN9PR03MB5996.namprd03.prod.outlook.com>
Accept-Language: en-US
Content-Language: en-US
x-ms-exchange-antispam-messagedata-chunkcount: 1
x-ms-exchange-antispam-messagedata-0:
 =?us-ascii?Q?LS1gnQ7+6ziVZfP84kWCa6xB3xdirMXXmwM0dOuSANzR6J91XN2JXzNLZ/9+?=
 =?us-ascii?Q?TE9w039Dehk6LJCN80VWdeQwjZOYIg8ECXHlWL3TxSANYNmHtmQX6kUxhVpS?=
 =?us-ascii?Q?HGD1Z53wWtLIbHqPpotQkFlEQcCn6Cic2/CGw3i0XdrUi3FbpEBLXCMZH5rH?=
 =?us-ascii?Q?VFdGZlWtAxIPhJeHiqDlxakLrg9BZs503OWwLmu5Szj8Faw3RdJ+cBSVbmkw?=
 =?us-ascii?Q?peYRxwZdpnJkBk3q2V54TGqxnHH0QSGpDYz5jpCXCvyrwXt7hqVpNVAfSRmD?=
 =?us-ascii?Q?dNVLgkiXJp6WlKtjuevGL9XI6xcONTiHq91FWr/ThhZ79QnQwVOER8s1azbr?=
 =?us-ascii?Q?1LwE8zWDT7iEr2gWrYIEsIGbc7whMP0z0B0h4rdkRTYMxa2BKKZnyBt5kSCF?=
 =?us-ascii?Q?rwDc0VgILbM/iitWnqrdcjpcZXkMAcnZdn0lnuJqFuMJq0lx3qfIckFh7DPJ?=
 =?us-ascii?Q?zit2LuvNyZHXWDEQirz1dDoXpz/FQ0jin3VUHJLFYzUIITzGiSnqf1XqpkAI?=
 =?us-ascii?Q?ToTLEcpf/BYGtQlKZLgDGPjMA57IOUL8BeAPL7lxLEnbgIBi88KHGJrF0E/t?=
 =?us-ascii?Q?P22D3XDPNCiGCuJM/YYv8fZBI+4TPziC11SZAvOz3/5roKBp69uVDpWCwMex?=
 =?us-ascii?Q?yicwtId6vUZ4AedX5akKoYrWdX77RfyuFsFzaOnsWhxZEQWVq/yV53+Iky2Q?=
 =?us-ascii?Q?Da0FN5dSyB2RXAwj9dEUxAGNXIv7GoEm3F/gAGaqo+0XDbLOLKTq8Z6g7hcA?=
 =?us-ascii?Q?fuqvxQYRVuiRquETxFDrNZ/9QKikhdxtf+B9twWSN/b2X0X07ZlWkznOb3Xu?=
 =?us-ascii?Q?LuqAdk1E4ECujEkR7lGgqzZtFpOIgRNJHYfQKDoe2wRAmzHQTQez2QVtLwRZ?=
 =?us-ascii?Q?4OIvwlcsW7Rjp8qGgSXED+PIef6s83hijkvZgPxbkQHhnctjm+vGTjnh1W41?=
 =?us-ascii?Q?q5ir5QwcBb3irg+/UVaX4gLYFXqj1q/yKYLp6L+JBjdwZ2HVHjalNZw/2Hgo?=
 =?us-ascii?Q?VzcTWGAvES2tNVyNrIW1NcKonxLN4SqOmxM4+s9AIpS4flOah0kfFvadO6ki?=
 =?us-ascii?Q?XN4r30BUFgrpWLvtVfqe3OZ7m/jXb1Du7IH81mfvt0cs+jOB+g3JpstjiyWZ?=
 =?us-ascii?Q?sVG/1I8DsudYbv2caxwH3TrroPoiowIwHp1IgEza4pPAD6CUo5m99VbGNY+e?=
 =?us-ascii?Q?BAUiY6VTc3qT+MBqBTmIJlanbyLIkfkdGIsPrvG1VkLAXVOdM1897AGTXBF9?=
 =?us-ascii?Q?esJH8BzjqLjuyeU3DfzR+jXt9JwAAMXPTnFpoJAAHD9mF696fUOHdhtHCuOB?=
 =?us-ascii?Q?Mz8MQTiUJbTds3RkpTY1intJlL18Dv2ONRVnzh6O0aaPDOe0qyJIzg8nf7L3?=
 =?us-ascii?Q?os9PBKeBZh0++6KlcYXepNJOqqnp684Bf/eyaW7o+J9DvmpNkKCDv4Sb4vTf?=
 =?us-ascii?Q?T3VLTRP0uzEb4GBZR+WVjIuMZlTG4lQ01+VenFwLQnqT5shuqgCyrju95irz?=
 =?us-ascii?Q?W4pex40TqK20w+3hPuLCsSyW4RaUsi1WO2bY4eBtSH4RbF4D5P2IzT6AOK3t?=
 =?us-ascii?Q?oQ/EBHguk/AWhroM9p72hsbbQDhfQ2ez0x/YK1wXSc97y+bktXapxiAB4plI?=
 =?us-ascii?Q?q9XmQU+PEOWE68pPdyTZHdBOStxbpMjK9DojzdVNDHOUfIDNlW1dcbKwX4mX?=
 =?us-ascii?Q?hITFGg=3D=3D?=
Content-Type: multipart/alternative;
	boundary="_000_BN9PR03MB59965B5087688309C0D79DCEB7962BN9PR03MB5996namp_"
MIME-Version: 1.0
X-OriginatorOrg: ghx.com
X-MS-Exchange-CrossTenant-AuthAs: Internal
X-MS-Exchange-CrossTenant-AuthSource: BN9PR03MB5996.namprd03.prod.outlook.com
X-MS-Exchange-CrossTenant-Network-Message-Id: 6adc1641-3fec-45dc-17cb-08dcc7ce0663
X-MS-Exchange-CrossTenant-originalarrivaltime: 29 Aug 2024 01:58:06.3594
 (UTC)
X-MS-Exchange-CrossTenant-fromentityheader: Hosted
X-MS-Exchange-CrossTenant-id: 3c2088fe-3969-4873-ac0d-dc9f122866b9
X-MS-Exchange-CrossTenant-mailboxtype: HOSTED
X-MS-Exchange-CrossTenant-userprincipalname: wKEe1d9irW8h0NBb3EuAi8zJM75cKZLOy5X7pO87q3KBy91lfES/8NOxPnC95kZsDMPeaUY7j7ua1nEXKd8EYQ==
X-MS-Exchange-Transport-CrossTenantHeadersStamped: SJ0PR03MB5678
List-Id: <pgsql-general.lists.postgresql.org>
List-Help: <https://lists.postgresql.org/manage/>
List-Subscribe: <https://lists.postgresql.org/manage/>
List-Post: <mailto:pgsql-general@lists.postgresql.org>
List-Owner: <mailto:pgsql-general-owner@lists.postgresql.org>
List-Archive: <https://www.postgresql.org/list/pgsql-general>
Archived-At: <https://www.postgresql.org/message-id/BN9PR03MB59965B5087688309C0D79DCEB7962%40BN9PR03MB5996.namprd03.prod.outlook.com>
Precedence: bulk

--_000_BN9PR03MB59965B5087688309C0D79DCEB7962BN9PR03MB5996namp_
Content-Type: text/plain; charset="us-ascii"
Content-Transfer-Encoding: quoted-printable

I've got two Postgres 13 databases on AWS RDS.

  *   One is a master, the other a slave using logical replication.
  *   Replication has fallen behind by about 350Gb.
  *   The slave was maxed out in terms of CPU for the past four days becaus=
e of some jobs that were ongoing so I'm not sure what logical replication w=
as able to replicate during that time.
  *   I killed those jobs and now CPU on the master and slave are both low.
  *   I look at the subscriber via `select * from pg_stat_subscription;` an=
d see that latest_end_lsn is advancing albeit very slowly.
  *   The publisher says write/flush/replay lags are all 13 minutes behind =
but it's been like that for most of the day.
  *   I see no errors in the logs on either the publisher or subscriber out=
side of some simple SQL errors that users have been making.
  *   CloudWatch reports low CPU utilization, low I/O, and low network.

Is there anything I can do here? Previously I set wal_receiver_timeout time=
out to 0 because I had replication issues, and that helped things. I wish I=
 had some visibility here to get any kind of confidence that it's going to =
pull through, but other than these lsn values and database logs, I'm not su=
re what to check.

Sincerely,
mj

--_000_BN9PR03MB59965B5087688309C0D79DCEB7962BN9PR03MB5996namp_
Content-Type: text/html; charset="us-ascii"
Content-Transfer-Encoding: quoted-printable

<html xmlns:o=3D"urn:schemas-microsoft-com:office:office" xmlns:w=3D"urn:sc=
hemas-microsoft-com:office:word" xmlns:m=3D"http://schemas.microsoft.com/of=
fice/2004/12/omml" xmlns=3D"http://www.w3.org/TR/REC-html40">
<head>
<meta http-equiv=3D"Content-Type" content=3D"text/html; charset=3Dus-ascii"=
>
<meta name=3D"Generator" content=3D"Microsoft Word 15 (filtered medium)">
<style><!--
/* Font Definitions */
@font-face
	{font-family:Wingdings;
	panose-1:5 0 0 0 0 0 0 0 0 0;}
@font-face
	{font-family:"Cambria Math";
	panose-1:2 4 5 3 5 4 6 3 2 4;}
@font-face
	{font-family:Aptos;
	panose-1:2 11 0 4 2 2 2 2 2 4;}
/* Style Definitions */
p.MsoNormal, li.MsoNormal, div.MsoNormal
	{margin:0in;
	font-size:11.0pt;
	font-family:"Aptos",sans-serif;
	mso-ligatures:standardcontextual;}
span.EmailStyle17
	{mso-style-type:personal-compose;
	font-family:"Aptos",sans-serif;
	color:windowtext;}
.MsoChpDefault
	{mso-style-type:export-only;
	font-size:11.0pt;}
@page WordSection1
	{size:8.5in 11.0in;
	margin:1.0in 1.0in 1.0in 1.0in;}
div.WordSection1
	{page:WordSection1;}
/* List Definitions */
@list l0
	{mso-list-id:96875845;
	mso-list-template-ids:-1637472940;}
@list l0:level1
	{mso-level-number-format:bullet;
	mso-level-text:\F0B7 ;
	mso-level-tab-stop:.5in;
	mso-level-number-position:left;
	text-indent:-.25in;
	mso-ansi-font-size:10.0pt;
	font-family:Symbol;}
@list l0:level2
	{mso-level-number-format:bullet;
	mso-level-text:o;
	mso-level-tab-stop:1.0in;
	mso-level-number-position:left;
	text-indent:-.25in;
	mso-ansi-font-size:10.0pt;
	font-family:"Courier New";
	mso-bidi-font-family:"Times New Roman";}
@list l0:level3
	{mso-level-number-format:bullet;
	mso-level-text:\F0A7 ;
	mso-level-tab-stop:1.5in;
	mso-level-number-position:left;
	text-indent:-.25in;
	mso-ansi-font-size:10.0pt;
	font-family:Wingdings;}
@list l0:level4
	{mso-level-number-format:bullet;
	mso-level-text:\F0A7 ;
	mso-level-tab-stop:2.0in;
	mso-level-number-position:left;
	text-indent:-.25in;
	mso-ansi-font-size:10.0pt;
	font-family:Wingdings;}
@list l0:level5
	{mso-level-number-format:bullet;
	mso-level-text:\F0A7 ;
	mso-level-tab-stop:2.5in;
	mso-level-number-position:left;
	text-indent:-.25in;
	mso-ansi-font-size:10.0pt;
	font-family:Wingdings;}
@list l0:level6
	{mso-level-number-format:bullet;
	mso-level-text:\F0A7 ;
	mso-level-tab-stop:3.0in;
	mso-level-number-position:left;
	text-indent:-.25in;
	mso-ansi-font-size:10.0pt;
	font-family:Wingdings;}
@list l0:level7
	{mso-level-number-format:bullet;
	mso-level-text:\F0A7 ;
	mso-level-tab-stop:3.5in;
	mso-level-number-position:left;
	text-indent:-.25in;
	mso-ansi-font-size:10.0pt;
	font-family:Wingdings;}
@list l0:level8
	{mso-level-number-format:bullet;
	mso-level-text:\F0A7 ;
	mso-level-tab-stop:4.0in;
	mso-level-number-position:left;
	text-indent:-.25in;
	mso-ansi-font-size:10.0pt;
	font-family:Wingdings;}
@list l0:level9
	{mso-level-number-format:bullet;
	mso-level-text:\F0A7 ;
	mso-level-tab-stop:4.5in;
	mso-level-number-position:left;
	text-indent:-.25in;
	mso-ansi-font-size:10.0pt;
	font-family:Wingdings;}
ol
	{margin-bottom:0in;}
ul
	{margin-bottom:0in;}
--></style>
</head>
<body lang=3D"EN-US" link=3D"#467886" vlink=3D"#96607D" style=3D"word-wrap:=
break-word">
<div class=3D"WordSection1">
<p class=3D"MsoNormal">I've got two Postgres 13 databases on AWS RDS.<o:p><=
/o:p></p>
<ul style=3D"margin-top:0in" type=3D"disc">
<li class=3D"MsoNormal" style=3D"mso-list:l0 level1 lfo1">One is a master, =
the other a slave using logical replication.<o:p></o:p></li><li class=3D"Ms=
oNormal" style=3D"mso-list:l0 level1 lfo1">Replication has fallen behind by=
 about 350Gb.<o:p></o:p></li><li class=3D"MsoNormal" style=3D"mso-list:l0 l=
evel1 lfo1">The slave was maxed out in terms of CPU for the past four days =
because of some jobs that were ongoing so I'm not sure what logical replica=
tion was able to replicate during that time.<o:p></o:p></li><li class=3D"Ms=
oNormal" style=3D"mso-list:l0 level1 lfo1">I killed those jobs and now CPU =
on the master and slave are both low.<o:p></o:p></li><li class=3D"MsoNormal=
" style=3D"mso-list:l0 level1 lfo1">I look at the subscriber via `select * =
from pg_stat_subscription;` and see that latest_end_lsn is advancing albeit=
 very slowly.<o:p></o:p></li><li class=3D"MsoNormal" style=3D"mso-list:l0 l=
evel1 lfo1">The publisher says write/flush/replay lags are all 13 minutes b=
ehind but it's been like that for most of the day.<o:p></o:p></li><li class=
=3D"MsoNormal" style=3D"mso-list:l0 level1 lfo1">I see no errors in the log=
s on either the publisher or subscriber outside of some simple SQL errors t=
hat users have been making.<o:p></o:p></li><li class=3D"MsoNormal" style=3D=
"mso-list:l0 level1 lfo1">CloudWatch reports low CPU utilization, low I/O, =
and low network.
<o:p></o:p></li></ul>
<p class=3D"MsoNormal" style=3D"margin-left:.5in"><o:p>&nbsp;</o:p></p>
<p class=3D"MsoNormal">Is there anything I can do here? Previously I set wa=
l_receiver_timeout timeout to 0 because I had replication issues, and that =
helped things. I wish I had
<i>some</i> visibility here to get any kind of confidence that it's going t=
o pull through, but other than these lsn values and database logs, I'm not =
sure what to check.<o:p></o:p></p>
<p class=3D"MsoNormal"><o:p>&nbsp;</o:p></p>
<p class=3D"MsoNormal">Sincerely,<o:p></o:p></p>
<p class=3D"MsoNormal">mj<o:p></o:p></p>
</div>
</body>
</html>

--_000_BN9PR03MB59965B5087688309C0D79DCEB7962BN9PR03MB5996namp_--