Re: How to test replication without doing a failover

On 10/11/2022 14:59, Edwin UY wrote:
> 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?

You can (usually) access the replica in read-only mode

So, what I do is do a transaction on the master and see if it appears on
the replica. On a live database that often happens automatically, so I
just look at, for instance, the latest entry in a busy table on the
master, and check it's the same on the replica. If you do this a couple
of times as things change on the master, then you'll know whether or not
it's replicating properly

Paul

--

Paul Smith Computer Services
Tel: 01484 855800
Vat No: GB 685 6987 53

Sign up for news & updates at http://www.pscs.co.uk/go/subscribe