From: Holger Jakobs <holger@jakobs.com>
To: pgsql-admin@lists.postgresql.org
Subject: Re: How to test replication without doing a failover
Date: Thu, 10 Nov 2022 16:52:57 +0100
Message-ID: <51397c23-b806-eb24-3bd0-986ef9c593c9@jakobs.com> (raw)
In-Reply-To: <CA+wokJ8FQ0c5NwhPL1Bv2_fcyndwZNEqoJFORnORvVRxSX+61w@mail.gmail.com>
References: <CA+wokJ8FQ0c5NwhPL1Bv2_fcyndwZNEqoJFORnORvVRxSX+61w@mail.gmail.com>
Am 10.11.22 um 15:59 schrieb Edwin UY:
> List
>
> Very newbie here on PostgreSQL coming from an Oracle DBA background mostly
>
> How do I test and confirm whether replication is working or not
> besides depending on just running the SQL below?
>
> select pg_is_in_recovery() ;
>
> select * from pg_stat_replication ;
>
>
> 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?
> Maybe someone can refer me to an example if there is one somewhere?
>
> Regards,
> Ed
*Monitoring the replication*
Compare the WAL positions of primary and secondary: select
pg_current_wal_lsn(); on the primary
select pg_last_wal_receive_lsn(); on the standby or in the ps output of
WAL receiver process.
Using the view pg_stat_replication
Check the difference between pg_current_wal_lsn() and the column
sent_lsn of the view shows high load of the primary.
Difference between the column sent_lsn of the view and
pg_last_wal_receive_lsn() on the standby shows network congestion or
that the standby has high load.
--
Holger Jakobs, Bergisch Gladbach, Tel. +49-178-9759012
Attachments:
[application/pgp-signature] OpenPGP_signature (202B, ../51397c23-b806-eb24-3bd0-986ef9c593c9@jakobs.com/3-OpenPGP_signature)
download
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: holger@jakobs.com, pgsql-admin@lists.postgresql.org
Subject: Re: How to test replication without doing a failover
In-Reply-To: <51397c23-b806-eb24-3bd0-986ef9c593c9@jakobs.com>
* 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