Re: How to test replication without doing a failover

Hi Rui

If using log shipping, the SQL below will return nothing, is that correct?

select * from pg_stat_replication ;

On Sat, Nov 12, 2022 at 11:52 AM Rui DeSousa <rui(at)crazybean(dot)net> wrote:

>
> On Nov 10, 2022, at 9:59 AM, Edwin UY <edwin(dot)uy(at)gmail(dot)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
> ;
>