Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1u293w-00Di2O-86 for pgsql-general@arkaria.postgresql.org; Tue, 08 Apr 2025 13:32:24 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.94.2) (envelope-from ) id 1u293u-008fum-FD for pgsql-general@arkaria.postgresql.org; Tue, 08 Apr 2025 13:32:22 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1u293u-008fua-1W for pgsql-general@lists.postgresql.org; Tue, 08 Apr 2025 13:32:22 +0000 Received: from mail-oo1-xc2b.google.com ([2607:f8b0:4864:20::c2b]) by magus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.96) (envelope-from ) id 1u293r-0048Yj-2m for pgsql-general@lists.postgresql.org; Tue, 08 Apr 2025 13:32:22 +0000 Received: by mail-oo1-xc2b.google.com with SMTP id 006d021491bc7-6021e3daeabso2708159eaf.3 for ; Tue, 08 Apr 2025 06:32:19 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20230601; t=1744119137; x=1744723937; darn=lists.postgresql.org; h=to:subject:message-id:date:from:in-reply-to:references:mime-version :from:to:cc:subject:date:message-id:reply-to; bh=Qfb7Yw73vXcD0rq3SQnVFRRAElwXIGk+qHq7faG1Lvs=; b=fsMSitkemRgJvK3xO1on05jpQOJ2Nt1E3eSDGDvLSiABScNWZWzLItGJWWBBq84VnC n+uPOFq2WCUaG5uaPmW0Pwy48QzRztb0kGDP+yuzX8LmuwUW3ahwb7+kHMWTMPwKq21q JjSpBVXQtWS1IKQ93ofE6sK/oppzpRwVsADmcx+ZzOHDzsiRUMrwojHpTTGdFBQJ+2nO 1MXB7D4ptKUibRXCoY8Ttcl2mfqjyIEcW3Dsma1uP3bpWue/EayrUhRv3T93swYYC4d+ 5LXA+hWxpFWmp97QDbL/QsFqx4iQjRy/d3GPAbEj2m/itA2gRlfC5xG8514p5XDINrPy CaIw== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20230601; t=1744119137; x=1744723937; h=to:subject:message-id:date:from:in-reply-to:references:mime-version :x-gm-message-state:from:to:cc:subject:date:message-id:reply-to; bh=Qfb7Yw73vXcD0rq3SQnVFRRAElwXIGk+qHq7faG1Lvs=; b=YsOyF4vNvhSCB/PbrQX/VNSBEXDXER5FlVHjW/FrDdPUxzwoM2gkzO3OigxQGHOIh6 PtcHhm6DnTt/aZLdqKLSCA0ICd9HK0Wi/DCVrqPU/GsSJ0olEGf+wVDRPZPLf9lbVdIU /g920cZbLhfxZi8W/SkyZZZyNbaE5MdHDaRxm18cEd1/o/tfOmvZtzQwRArpmlSW89cx 2ueLIUDMsUrCyGhRisPYumbj69NYv6rBgmY63Oq96SKS5fyGAjDsDHwfTehCxa1gGIoT IrKou6NK4gcv5GLueG3RvBGCmwKR9t0IuPsqsnIu6foj7XoQ1aaz1c4gBX2MnI1+5mT8 6V8A== X-Gm-Message-State: AOJu0YxEm1CaNDOCCawTbQq8Bu4EBbWruupim9hDBiRlV32HLX8wdsGg BYPhLpP9aPeOoDcs3CfbwGsTlN44dmWjnxhcHAiZioA9Uss1QtsCsxcCr6yR1vnrqnKZBqTm4p1 slMCEuKsSF2v2K643vNVoUZkdfTdq+Ask X-Gm-Gg: ASbGncuKe2LN4LuXS+FPo3FE5Jy4tcQvbAxsqQiwGL8QAzbxW7JCyMt8In4WG6c6jQM T+W/9XdaEtim+CLYP6h8xOG6bA5snw35yICNczkzmpHARW6Kkz+YiGgftpA9zTDgDDErbJCitWJ D0yiwRtzUNqqB9j9aiH440whu0s0OlR17y/jIIeBKLQH92Od1aUWNQO0fU/0Ln X-Google-Smtp-Source: AGHT+IEG1gYpCQsMrUT0n08vzNX0FX5fZ2VHBfMmuY5cXLMpjdwE+8SaVu7JL+wQKyI2uMnI0SjUziGFh4qzlq+HyAg= X-Received: by 2002:a05:6808:200b:b0:3f6:a86e:721a with SMTP id 5614622812f47-4004d94965amr7787946b6e.1.1744119137293; Tue, 08 Apr 2025 06:32:17 -0700 (PDT) MIME-Version: 1.0 References: In-Reply-To: From: Ron Johnson Date: Tue, 8 Apr 2025 09:32:06 -0400 X-Gm-Features: ATxdqUGTey5EovQ5GzKgLK965YOjma6tBto84_lsXRDuWBNm-IG5aEG_PGpTCZk Message-ID: Subject: Re: Wal file query To: pgsql-general Content-Type: multipart/alternative; boundary="0000000000009fdcc6063244610b" List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --0000000000009fdcc6063244610b Content-Type: text/plain; charset="UTF-8" Content-Transfer-Encoding: quoted-printable You cannot connect to the Primary while connected to the Replica, except via postgres_fdw. Even then, it might not work, since the replica replicates _fdw definitions. These exist on the primary: pg_current_wal_lsn() pg_replication_slots pg_stat_replication These exist on the replica: pg_last_wal_receive_lsn() pg_last_wal_replay_lsn() pg_stat_wal_receiver pg_get_wal_replay_pause_state() On Tue, Apr 8, 2025 at 9:09=E2=80=AFAM Atul Kumar w= rote: > I got the error: > > Wal control functions cannot be executed during recovery. > > I need the solution that should in when streaming replication is > configured. > > My postgres version is 13.20 > > > Regards. > > On Tue, 8 Apr 2025, 17:32 Kashif Zeeshan, wrote= : > >> Hi Atul >> >> Start by looking at the current WAL LSN and insert LSN. The >> pg_current_wal_lsn is the location of the last write. The >> pg_current_wal_insert_lsn is the logical location and reflects data in >> the buffer that has not been written to disk. There is also a flush valu= e >> that shows what has been written to durable storage. >> >> [postgres] # select pg_current_wal_lsn(), pg_current_wal_insert_lsn(); >> pg_current_wal_lsn | pg_current_wal_insert_lsn >> --------------------+--------------------------- >> 76/7D000000 | 76/7D000028 >> (1 row) >> >> Although you can guess the name of the WAL file based on the above outpu= t, it is best to use the pg_walfile_name function. >> >> >> [postgres] # select pg_walfile_name('76/7D000028'); >> pg_walfile_name >> -------------------------- >> 00000001000000760000007D >> (1 row) >> >> >> For details visit following link : https://www.crunchydata.com/blog/post= gres-wal-files-and-sequuence-numbers >> >> >> Thanks >> >> Kashif Zeeshan >> >> >> >> On Tue, Apr 8, 2025 at 4:44=E2=80=AFPM Atul Kumar wrote: >> >>> In streaming replication What is the way to check which "WAL file" is >>> currently in use in primary and in standby ? >>> >>> >>> Regards. >>> >> --=20 Death to , and butter sauce. Don't boil me, I'm still alive. lobster! --0000000000009fdcc6063244610b Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable
You cannot connect to the Primary while connected to = the Replica, except via postgres_fdw.=C2=A0 Even then, it might not work, s= ince the replica replicates _fdw definitions.

Thes= e exist on the primary:=C2=A0
pg_current_wal_lsn()
pg_replication_= slots
pg_stat_replication
These exist on t= he replica:=C2=A0
pg_last_wal_receive_lsn()=C2=A0
pg_last_wal_repl= ay_lsn()
pg_stat_wal_receiver
pg_get_wal_replay_pause_s= tate()

On Tue, Apr 8, 2025 at 9:09=E2=80= =AFAM Atul Kumar <akumar14871@g= mail.com> wrote:
I got the error:

Wal control functions cannot be executed during recovery.

I need the solution that sho= uld in when streaming replication is configured.=C2=A0

My postgres version is 13.20


Regards.= =C2=A0

On Tue, 8 Apr 2025, 17:32 Kashif Zeeshan, <kashi.zeeshan@gmail.com>= wrote:
Hi=C2=A0Atul

Start by looking at the current WA= L LSN and insert LSN. The pg_current_wal_lsn is the location o= f the last write. The pg_current_wal_insert_lsn is the logical location and reflects data in the buffer that has not=20 been written to disk. There is also a flush value that shows what has=20 been written to durable storage.
[postgres] # select pg_current_wal_lsn(), pg_current_wal_inse=
rt_lsn();
 pg_current_wal_lsn | pg_current_wal_insert_lsn
--------------------+---------------------------
 76/7D000000        | 76/7D000028
(1 row)

Although you can guess the name of the WAL file bas=
ed on the above output, it is best to use the pg_walfile_name =
function.

[po=
stgres] # select pg_walfile_name('76/7D000028');
     pg_walfile_name
--------------------------
 00000001000000760000007D
(1 row)

For details visi=
t following link : https://=
www.crunchydata.com/blog/postgres-wal-files-and-sequuence-numbers

Thanks
Kashif Zeeshan


On Tue, Apr 8= , 2025 at 4:44=E2=80=AFPM Atul Kumar <akumar14871@gmail.com> w= rote:
In streaming replication=C2=A0What is the way to check which &quo= t;WAL file" is currently in use in primary=C2=A0 and in standby ?


Regard= s.


--
Death to <Redacted>, and butter sauce.Don't boil me, I'm still alive.
<Redacted> lobs= ter!
--0000000000009fdcc6063244610b--