From: Rui DeSousa <rui@crazybean.net>
To: Edwin UY <edwin.uy@gmail.com>
Cc: pgsql-admin@lists.postgresql.org
Subject: Re: How to test replication without doing a failover
Date: Fri, 11 Nov 2022 17:52:09 -0500
Message-ID: <8AFEF66F-6289-4455-A0EC-C1FB76F3B0BF@crazybean.net> (raw)
In-Reply-To: <CA+wokJ8FQ0c5NwhPL1Bv2_fcyndwZNEqoJFORnORvVRxSX+61w@mail.gmail.com>
References: <CA+wokJ8FQ0c5NwhPL1Bv2_fcyndwZNEqoJFORnORvVRxSX+61w@mail.gmail.com>
> 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’t 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
;=
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-admin@postgresql.org
Cc: rui@crazybean.net, edwin.uy@gmail.com, pgsql-admin@lists.postgresql.org
Subject: Re: How to test replication without doing a failover
In-Reply-To: <8AFEF66F-6289-4455-A0EC-C1FB76F3B0BF@crazybean.net>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox