Message-ID: <51397c23-b806-eb24-3bd0-986ef9c593c9@jakobs.com>
Date: Thu, 10 Nov 2022 16:52:57 +0100
MIME-Version: 1.0
User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:102.0) Gecko/20100101
 Thunderbird/102.4.1
Subject: Re: How to test replication without doing a failover
To: pgsql-admin@lists.postgresql.org
References: 
 <CA+wokJ8FQ0c5NwhPL1Bv2_fcyndwZNEqoJFORnORvVRxSX+61w@mail.gmail.com>
From: Holger Jakobs <holger@jakobs.com>
In-Reply-To: 
 <CA+wokJ8FQ0c5NwhPL1Bv2_fcyndwZNEqoJFORnORvVRxSX+61w@mail.gmail.com>
Content-Type: multipart/signed; micalg=pgp-sha256;
 protocol="application/pgp-signature";
 boundary="------------TseTWN4SLm0NDOMZTPoDymk4"
Archived-At: 
 <https://www.postgresql.org/message-id/51397c23-b806-eb24-3bd0-986ef9c593c9%40jakobs.com>
Precedence: bulk

This is an OpenPGP/MIME signed message (RFC 4880 and 3156)
--------------TseTWN4SLm0NDOMZTPoDymk4
Content-Type: multipart/mixed; boundary="------------gnTEejdh5MzRv9h61WmLNBNQ";
 protected-headers="v1"
From: Holger Jakobs <holger@jakobs.com>
To: pgsql-admin@lists.postgresql.org
Message-ID: <51397c23-b806-eb24-3bd0-986ef9c593c9@jakobs.com>
Subject: Re: How to test replication without doing a failover
References: <CA+wokJ8FQ0c5NwhPL1Bv2_fcyndwZNEqoJFORnORvVRxSX+61w@mail.gmail.com>
In-Reply-To: <CA+wokJ8FQ0c5NwhPL1Bv2_fcyndwZNEqoJFORnORvVRxSX+61w@mail.gmail.com>

--------------gnTEejdh5MzRv9h61WmLNBNQ
Content-Type: multipart/alternative;
 boundary="------------Bn2LSn5UH5CjIxu6AX7Ujbk0"

--------------Bn2LSn5UH5CjIxu6AX7Ujbk0
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: base64

QW0gMTAuMTEuMjIgdW0gMTU6NTkgc2NocmllYiBFZHdpbiBVWToNCj4gTGlzdA0KPg0KPiBW
ZXJ5IG5ld2JpZSBoZXJlIG9uIFBvc3RncmVTUUwgY29taW5nIGZyb20gYW4gT3JhY2xlIERC
QSBiYWNrZ3JvdW5kIG1vc3RseQ0KPg0KPiBIb3cgZG8gSSB0ZXN0IGFuZCBjb25maXJtIHdo
ZXRoZXIgcmVwbGljYXRpb24gaXMgd29ya2luZyBvciBub3QgDQo+IGJlc2lkZXMgZGVwZW5k
aW5nIG9uIGp1c3QgcnVubmluZyB0aGUgU1FMIGJlbG93Pw0KPg0KPiBzZWxlY3QgcGdfaXNf
aW5fcmVjb3ZlcnkoKSA7DQo+DQo+IHNlbGVjdCAqIGZyb20gcGdfc3RhdF9yZXBsaWNhdGlv
biA7DQo+DQo+DQo+IElzIGl0IHBvc3NpYmxlIHRvIGZvcmNlL2luaXRpYXRlIGEgbG9nIHRy
YW5zYWN0aW9uIGFuZCBzZWUgaWYgdGhhdCANCj4gZ2V0cyByZXBsaWNhdGVkIG9yIG5vdD8g
SSBtZWFuIGZvciBleGFtcGxlIG9uIE9yYWNsZSB3ZSBjYW4gZG8gYSANCj4gc3dpdGNoIGxv
Z2ZpbGUgYW5kIGNoZWNrIGlmIHRoYXQgbG9nIGdldHMgc2hpcHBlZCBhY3Jvc3MgYW5kIGFw
cGxpZWQgDQo+IG9uIHRoZSBzdGFuZGJ5LCBpcyB0aGVyZSBzb21ldGhpbmcgc2ltaWxhciBp
biBQb3N0Z3JlU1FMPw0KPiBNYXliZSBzb21lb25lIGNhbiByZWZlciBtZSB0byBhbiBleGFt
cGxlIGlmIHRoZXJlIGlzIG9uZSBzb21ld2hlcmU/DQo+DQo+IFJlZ2FyZHMsDQo+IEVkDQoN
Cg0KKk1vbml0b3JpbmcgdGhlIHJlcGxpY2F0aW9uKg0KDQpDb21wYXJlIHRoZSBXQUwgcG9z
aXRpb25zIG9mIHByaW1hcnkgYW5kIHNlY29uZGFyeTogc2VsZWN0IA0KcGdfY3VycmVudF93
YWxfbHNuKCk7IG9uIHRoZSBwcmltYXJ5DQpzZWxlY3QgcGdfbGFzdF93YWxfcmVjZWl2ZV9s
c24oKTsgb24gdGhlIHN0YW5kYnkgb3IgaW4gdGhlIHBzIG91dHB1dCBvZiANCldBTCByZWNl
aXZlciBwcm9jZXNzLg0KDQpVc2luZyB0aGUgdmlldyBwZ19zdGF0X3JlcGxpY2F0aW9uDQoN
CkNoZWNrIHRoZSBkaWZmZXJlbmNlIGJldHdlZW4gcGdfY3VycmVudF93YWxfbHNuKCkgYW5k
IHRoZSBjb2x1bW4gDQpzZW50X2xzbiBvZiB0aGUgdmlldyBzaG93cyBoaWdoIGxvYWQgb2Yg
dGhlIHByaW1hcnkuDQpEaWZmZXJlbmNlIGJldHdlZW4gdGhlIGNvbHVtbiBzZW50X2xzbiBv
ZiB0aGUgdmlldyBhbmQgDQpwZ19sYXN0X3dhbF9yZWNlaXZlX2xzbigpIG9uIHRoZSBzdGFu
ZGJ5IHNob3dzIG5ldHdvcmsgY29uZ2VzdGlvbiBvciANCnRoYXQgdGhlIHN0YW5kYnkgaGFz
IGhpZ2ggbG9hZC4NCg0KDQoNCg0KLS0gDQpIb2xnZXIgSmFrb2JzLCBCZXJnaXNjaCBHbGFk
YmFjaCwgVGVsLiArNDktMTc4LTk3NTkwMTINCg0K
--------------Bn2LSn5UH5CjIxu6AX7Ujbk0
Content-Type: text/html; charset=UTF-8
Content-Transfer-Encoding: quoted-printable

<html>
  <head>
    <meta http-equiv=3D"Content-Type" content=3D"text/html; charset=3DUTF=
-8">
  </head>
  <body>
    <div class=3D"moz-cite-prefix">Am 10.11.22 um 15:59 schrieb Edwin UY:=
<br>
    </div>
    <blockquote type=3D"cite"
cite=3D"mid:CA+wokJ8FQ0c5NwhPL1Bv2_fcyndwZNEqoJFORnORvVRxSX+61w@mail.gmai=
l.com">
      <meta http-equiv=3D"content-type" content=3D"text/html; charset=3DU=
TF-8">
      <div dir=3D"ltr">List
        <div><br>
        </div>
        <div>Very newbie here on PostgreSQL coming from an Oracle DBA
          background mostly</div>
        <div><br>
        </div>
        <div>How do I test and confirm whether replication is working or
          not besides depending on just running the SQL below?</div>
        <div><br>
        </div>
        <div>
          <p style=3D"margin:0in;font-family:Consolas;font-size:11pt"
            lang=3D"en-US">select
            pg_is_in_recovery() ;</p>
          <p style=3D"margin:0in;font-family:Consolas;font-size:11pt"
            lang=3D"en-US">select *
            from pg_stat_replication ;</p>
        </div>
        <div><br>
        </div>
        <div>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?</div>
        <div>Maybe someone can refer me to an example if there is one
          somewhere?</div>
        <div><br>
        </div>
        <div>Regards,</div>
        <div>Ed</div>
      </div>
    </blockquote>
    <p><br>
    </p>
    <p><b>Monitoring the replication</b></p>
    <p>Compare the WAL positions of primary and secondary: select
      pg_current_wal_lsn(); on the primary<br>
      select pg_last_wal_receive_lsn(); on the standby or in the ps
      output of WAL receiver process.<br>
    </p>
    <p>Using the view pg_stat_replication <br>
    </p>
    <p>Check the difference between pg_current_wal_lsn() and the column
      sent_lsn of the view shows high load of the primary.<br>
      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.</p>
    <p><br>
    </p>
    <p><br>
    </p>
    <p><br>
    </p>
    <pre class=3D"moz-signature" cols=3D"72">--=20
Holger Jakobs, Bergisch Gladbach, Tel. +49-178-9759012</pre>
  </body>
</html>

--------------Bn2LSn5UH5CjIxu6AX7Ujbk0--

--------------gnTEejdh5MzRv9h61WmLNBNQ--

--------------TseTWN4SLm0NDOMZTPoDymk4
Content-Type: application/pgp-signature; name="OpenPGP_signature.asc"
Content-Description: OpenPGP digital signature
Content-Disposition: attachment; filename="OpenPGP_signature"

-----BEGIN PGP SIGNATURE-----

wmMEABEIACMWIQScuJCxWJK1p5BOebGKV5yTsxlhugUCY20eWQUDAAAAAAAKCRCKV5yTsxlhumzJ
AKC59WCZWMTTjLe4hu6QYmULnLpK/wCgoWpIgfefL83QKYnpefa/cBOlZDk=
=YGaK
-----END PGP SIGNATURE-----

--------------TseTWN4SLm0NDOMZTPoDymk4--