Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1otcsm-0002fK-EQ for pgsql-admin@arkaria.postgresql.org; Fri, 11 Nov 2022 22:52:20 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1otcsl-0004kn-1s for pgsql-admin@arkaria.postgresql.org; Fri, 11 Nov 2022 22:52:19 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1otcsk-0004id-KA for pgsql-admin@lists.postgresql.org; Fri, 11 Nov 2022 22:52:18 +0000 Received: from impout008aa.msg.chrl.nc.charter.net ([47.43.20.32] helo=impout008.msg.chrl.nc.charter.net) by magus.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1otcsf-0005z2-KZ for pgsql-admin@lists.postgresql.org; Fri, 11 Nov 2022 22:52:18 +0000 Received: from miniblack.crazybean.net ([71.11.29.254]) by cmsmtp with ESMTP id tcscoT9C4l1q5tcsco1Vz7; Fri, 11 Nov 2022 22:52:11 +0000 Authentication-Results: crazybean.net; none X-Authority-Analysis: v=2.4 cv=X7FBlUfe c=1 sm=1 tr=0 ts=636ed21b a=GkUsAQdcAzAk0MT19QAIRQ==:117 a=GkUsAQdcAzAk0MT19QAIRQ==:17 a=xqWC_Br6kY4A:10 a=9xFQ1JgjjksA:10 a=pGLkceISAAAA:8 a=oqfo20rnbASGB2ohHSoA:9 a=QEXdDO2ut3YA:10 a=hwz32XtP33i_zoUHvPoA:9 a=je9P4m-Y4leBEtDj:21 a=_W_S_7VecoQA:10 a=lqcHg5cX4UMA:10 Received: from smtpclient.apple ( [2001:470:e117:3:bc:f928:4492:2ca4]) by miniblack.crazybean.net (OpenSMTPD) with ESMTPSA id 4f8c8048 (TLSv1.2:ECDHE-RSA-AES256-GCM-SHA384:256:NO); Fri, 11 Nov 2022 17:52:10 -0500 (EST) From: Rui DeSousa Message-Id: <8AFEF66F-6289-4455-A0EC-C1FB76F3B0BF@crazybean.net> Content-Type: multipart/alternative; boundary="Apple-Mail=_913C69BB-41B6-4EE6-A12D-AB5E28287A62" Mime-Version: 1.0 (Mac OS X Mail 16.0 \(3696.120.41.1.1\)) Subject: Re: How to test replication without doing a failover Date: Fri, 11 Nov 2022 17:52:09 -0500 In-Reply-To: Cc: pgsql-admin@lists.postgresql.org To: Edwin UY References: X-Mailer: Apple Mail (2.3696.120.41.1.1) X-CMAE-Envelope: MS4xfFyCmv3cFBG8SFeD1iDUsGgqRwUVYSSwyD8tNi2mgCZ3Xm6/d+hLf436/aVFFHjZMCL76Vr+bofQqZXp8Zzk1BEPwirPjmhDDNu3lfcqjqVW3Eoquyav 2wNcfRe6H8ScwouCFCzQNIObkPIWWJ0P/cOqMulj/Sq86QAARZu19VeUzHTw69LRMO81Ia2RklFLniFWcSaToRbpRMua1w/vbUZPJSXNQZvh9B31CLnIvhe8 Q8UsiRzUfGmXW0uOWnOlxw== List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --Apple-Mail=_913C69BB-41B6-4EE6-A12D-AB5E28287A62 Content-Transfer-Encoding: quoted-printable Content-Type: text/plain; charset=utf-8 > On Nov 10, 2022, at 9:59 AM, Edwin UY wrote: >=20 >=20 > Is it possible to force/initiate a log transaction and see if that = gets replicated or not? I mean for example on Oracle we can do a switch = logfile and check if that log gets shipped across and applied on the = standby, is there something similar in PostgreSQL? Yes, if you are using log shipping instead of replication you can use: = select pg_switch_wal(); However, replication doesn=E2=80=99t wait for a WAL file switch to = replicate data; thus you can monitor it on the upstream server (master = or cascading replication server) using the pg_stat_replication and = pg_replication_slots views. =20 I create and use the following views to monitor replication; viewing the = lag by data size instead of lsn values. create or replace view dba.replication_status as select pg_stat_replication.client_addr , pg_stat_replication.application_name , pg_stat_replication.sync_priority , pg_stat_replication.sync_state , pg_stat_replication.state , case pg_is_in_recovery() when true then = pg_size_pretty(pg_wal_lsn_diff(pg_last_wal_receive_lsn(), sent_lsn)) else pg_size_pretty(pg_wal_lsn_diff(pg_current_wal_lsn(), = sent_lsn)) end as sent_lag , case pg_is_in_recovery() when true then = pg_size_pretty(pg_wal_lsn_diff(pg_last_wal_receive_lsn(), flush_lsn)) else pg_size_pretty(pg_wal_lsn_diff(pg_current_wal_lsn(), = flush_lsn))=20 end as flush_lag , case pg_is_in_recovery() when true then = pg_size_pretty(pg_wal_lsn_diff(pg_last_wal_receive_lsn(), replay_lsn))=20= else pg_size_pretty(pg_wal_lsn_diff(pg_current_wal_lsn(), = replay_lsn)) end as replay_lag from pg_stat_replication ; create or replace view dba.replication_slot_status as select slot_name , slot_type , temporary , active , active_pid , xmin , catalog_xmin , restart_lsn , case pg_is_in_recovery() when true then = pg_size_pretty(pg_wal_lsn_diff(pg_last_wal_receive_lsn(), restart_lsn)) else pg_size_pretty(pg_wal_lsn_diff(pg_current_wal_lsn(), = restart_lsn)) end as restart_lag , confirmed_flush_lsn as flush_lsn , case pg_is_in_recovery() when true then = pg_size_pretty(pg_wal_lsn_diff(pg_last_wal_receive_lsn(), = confirmed_flush_lsn)) else pg_size_pretty(pg_wal_lsn_diff(pg_current_wal_lsn(), = confirmed_flush_lsn)) end as flush_lag from pg_replication_slots ;= --Apple-Mail=_913C69BB-41B6-4EE6-A12D-AB5E28287A62 Content-Transfer-Encoding: quoted-printable Content-Type: text/html; charset=utf-8

On = Nov 10, 2022, at 9:59 AM, Edwin UY <edwin.uy@gmail.com> = wrote:

Is it = possible to force/initiate a log transaction and see if that gets = replicated or not? I mean for example on Oracle we can do a switch = logfile and check if that log gets shipped across and applied on the = standby, is there something similar in = PostgreSQL?

Yes, if you are using log shipping instead of = replication you can use: select pg_switch_wal();

However, replication doesn=E2=80=99t wait for a = WAL file switch to replicate data; thus you can monitor it on the = upstream server (master or cascading replication server) using = the pg_stat_replication and pg_replication_slots views.

I create and use the following views to monitor = replication; viewing the lag by data size instead of lsn= values.

create = or replace view dba.replication_status

as

select = pg_stat_replication.client_addr

= , pg_stat_replication.application_name

, pg_stat_replication.sync_priority

, = pg_stat_replication.sync_state

= , pg_stat_replication.state

= , case pg_is_in_recovery()

= when true then = pg_size_pretty(pg_wal_lsn_diff(pg_last_wal_receive_lsn(), = sent_lsn))

= else pg_size_pretty(pg_wal_lsn_diff(pg_current_wal_lsn(), = sent_lsn))

= end as sent_lag

= , case pg_is_in_recovery()

= when true then = pg_size_pretty(pg_wal_lsn_diff(pg_last_wal_receive_lsn(), = flush_lsn))

= else pg_size_pretty(pg_wal_lsn_diff(pg_current_wal_lsn(), = flush_lsn))

= end as flush_lag

= , case pg_is_in_recovery()

= when true then = pg_size_pretty(pg_wal_lsn_diff(pg_last_wal_receive_lsn(), = replay_lsn))

= else pg_size_pretty(pg_wal_lsn_diff(pg_current_wal_lsn(), = replay_lsn))

= end as replay_lag

from = pg_stat_replication

;

create = or replace view dba.replication_slot_status

as

select slot_name

= , slot_type

= , temporary

= , active

= , active_pid

= , xmin

= , catalog_xmin

= , restart_lsn

= , case pg_is_in_recovery()

= when true then = pg_size_pretty(pg_wal_lsn_diff(pg_last_wal_receive_lsn(), = restart_lsn))

= else pg_size_pretty(pg_wal_lsn_diff(pg_current_wal_lsn(), = restart_lsn))

= end as restart_lag

= , confirmed_flush_lsn as flush_lsn

, case pg_is_in_recovery()

when true then = pg_size_pretty(pg_wal_lsn_diff(pg_last_wal_receive_lsn(), = confirmed_flush_lsn))

= else pg_size_pretty(pg_wal_lsn_diff(pg_current_wal_lsn(), = confirmed_flush_lsn))

= end as flush_lag

from = pg_replication_slots

;

= --Apple-Mail=_913C69BB-41B6-4EE6-A12D-AB5E28287A62--