pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedHow to test replication without doing a failover
6+ messages / 4 participants
[nested] [flat]
* How to test replication without doing a failover
@ 2022-11-10 14:59 Edwin UY <edwin.uy@gmail.com>
0 siblings, 3 replies; 6+ messages in thread
From: Edwin UY @ 2022-11-10 14:59 UTC (permalink / raw)
To: pgsql-admin@lists.postgresql.org
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
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: How to test replication without doing a failover
@ 2022-11-10 15:43 Paul Smith <paul@pscs.co.uk>
parent: Edwin UY <edwin.uy@gmail.com>
2 siblings, 0 replies; 6+ messages in thread
From: Paul Smith @ 2022-11-10 15:43 UTC (permalink / raw)
To: pgsql-admin@lists.postgresql.org
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
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: How to test replication without doing a failover
@ 2022-11-10 15:52 Holger Jakobs <holger@jakobs.com>
parent: Edwin UY <edwin.uy@gmail.com>
2 siblings, 0 replies; 6+ messages in thread
From: Holger Jakobs @ 2022-11-10 15:52 UTC (permalink / raw)
To: pgsql-admin@lists.postgresql.org
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:
OpenPGP_signature (application/pgp-signature, 202B) download
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: How to test replication without doing a failover
@ 2022-11-11 22:52 Rui DeSousa <rui@crazybean.net>
parent: Edwin UY <edwin.uy@gmail.com>
2 siblings, 1 reply; 6+ messages in thread
From: Rui DeSousa @ 2022-11-11 22:52 UTC (permalink / raw)
To: Edwin UY <edwin.uy@gmail.com>; +Cc: pgsql-admin@lists.postgresql.org
> 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
;=
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: How to test replication without doing a failover
@ 2022-11-12 06:24 Edwin UY <edwin.uy@gmail.com>
parent: Rui DeSousa <rui@crazybean.net>
0 siblings, 1 reply; 6+ messages in thread
From: Edwin UY @ 2022-11-12 06:24 UTC (permalink / raw)
To: Rui DeSousa <rui@crazybean.net>; +Cc: pgsql-admin@lists.postgresql.org
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@crazybean.net> wrote:
>
> 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
> ;
>
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: How to test replication without doing a failover
@ 2022-11-12 18:08 Rui DeSousa <rui@crazybean.net>
parent: Edwin UY <edwin.uy@gmail.com>
0 siblings, 0 replies; 6+ messages in thread
From: Rui DeSousa @ 2022-11-12 18:08 UTC (permalink / raw)
To: Edwin UY <edwin.uy@gmail.com>; +Cc: pgsql-admin@lists.postgresql.org
> On Nov 12, 2022, at 1:24 AM, Edwin UY <edwin.uy@gmail.com> wrote:
>
> If using log shipping, the SQL below will return nothing, is that correct?
>> select * from pg_stat_replication ;
Correct, as you would have the replica consuming WAL files rather than connecting directly to an upstream/master server and consuming the WAL online. That will only show the active connections currently replicating from the given server.
^ permalink raw reply [nested|flat] 6+ messages in thread
end of thread, other threads:[~2022-11-12 18:08 UTC | newest]
Thread overview: 6+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2022-11-10 14:59 How to test replication without doing a failover Edwin UY <edwin.uy@gmail.com>
2022-11-10 15:43 ` Paul Smith <paul@pscs.co.uk>
2022-11-10 15:52 ` Holger Jakobs <holger@jakobs.com>
2022-11-11 22:52 ` Rui DeSousa <rui@crazybean.net>
2022-11-12 06:24 ` Edwin UY <edwin.uy@gmail.com>
2022-11-12 18:08 ` Rui DeSousa <rui@crazybean.net>
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