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 1sewjU-00Ec99-OU for pgsql-admin@arkaria.postgresql.org; Fri, 16 Aug 2024 13:11:08 +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 1sewjT-005Hqy-DW for pgsql-admin@arkaria.postgresql.org; Fri, 16 Aug 2024 13:11:07 +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 1sewh3-005CWM-Ve for pgsql-admin@lists.postgresql.org; Fri, 16 Aug 2024 13:08:38 +0000 Received: from mail-oa1-x2c.google.com ([2001:4860:4864:20::2c]) by magus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.94.2) (envelope-from ) id 1sewh1-0059b7-Sf for pgsql-admin@lists.postgresql.org; Fri, 16 Aug 2024 13:08:37 +0000 Received: by mail-oa1-x2c.google.com with SMTP id 586e51a60fabf-260f033fda3so1166942fac.3 for ; Fri, 16 Aug 2024 06:08:35 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20230601; t=1723813714; x=1724418514; darn=lists.postgresql.org; h=cc:to:subject:message-id:date:from:in-reply-to:references :mime-version:from:to:cc:subject:date:message-id:reply-to; bh=LPMvIqpc9ZLxQhv0njyyS43PBYm+wZgCy+dELt1aC+M=; b=ByFD4YtafZY/N3LFihTvO9goPcZT7JuIC4ZrJrbnV4eBiljnfd/2rGvBRyAnOUkTpf 2BDEF+Gl7gQhLooSQuxDL05OdoThJoYwOZd7WLD3g/H3TDNCnD82o/QdbYYucsHx6p+N ctQ/jgjjoQyOrjDWbfpfJlEGgO5Li+iaylW8EjRBqGSGwGwoylwFx/NigML+IWypHMNM j8OGeN6rF5PbDcYrxGV72Dyh9RM46kbDNGRabNqp3IxDptnF975Fp4TYb021mjlf8WFa Az1cAHQ6rfb6PtYcuGxarO1hM+M8sM5a46WyOogp/Jg+KEA1kXaLWq/vacz+FKfHgbUH 2X1Q== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20230601; t=1723813714; x=1724418514; h=cc: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=LPMvIqpc9ZLxQhv0njyyS43PBYm+wZgCy+dELt1aC+M=; b=clBruzn05OItIQo5CSbz3j5hA0QNAaGQzqZnl8Ufu+GhXa8EXlboLSQHRpql+AqpOX 7ygNPdDBEms9D0Ah2m7OXwnZ3wguLNOKq3Ohtpe5yGyayP1MOZ3K6dBEaZKY07FIVsfj QLnijUVagxA64yQAZn5Vu1l3nMSPaBIoIryNAXjFaVkgzGMFc58/CvzyB6iI3xCs7xM6 oKK0HOCD+Xm8KO/PU9SJXUR6l54l8RHViaA+rR028ZRBjN5e0TdKjnxstfPhSfMY6nwp ivKBP06RrauslbuzZ6N/lMHRrDXYfNHZYfHEa77rtzi7tTG+Nc16CecHlb6h87qB0E/A 5KlA== X-Forwarded-Encrypted: i=1; AJvYcCV1oYgq8uny/aTZG0MSO52Qm8FyMbfgMI63b3tJYLtZkGhtgRvvf5aHPV/wNw3T3NPQJyAXdCLvk8D9G1mp0VKSg/zFHoejP5pPDl13FrRJwA== X-Gm-Message-State: AOJu0Yw+DLRQ08vNVCmFWAUOLcB6/SnAy1bEat/KaYcG/rJRVoAxwNXq mG0iUeiajRgOE0ucgUJqJMHQoSnGG7Zo9y0PhoQUyar5r5QUM6c75fZeIZ4Woqnxnas0ijLSVa4 X7Lqo0v3tCjznIuw+493beIXFYSm8wA== X-Google-Smtp-Source: AGHT+IH7F54jIARAh0Q25QL5rJA8Xrq4VB+Dfc4phPpLgoksmq2tYVYZVLqke6WoHERzZy7zWrmzqqxUwbalLgymKQA= X-Received: by 2002:a05:6870:71c3:b0:25e:fb:af8c with SMTP id 586e51a60fabf-2701c39f1ecmr3233442fac.18.1723813714318; Fri, 16 Aug 2024 06:08:34 -0700 (PDT) MIME-Version: 1.0 References: In-Reply-To: From: Mohammed Afsar Date: Fri, 16 Aug 2024 18:38:22 +0530 Message-ID: Subject: =?UTF-8?Q?Re=3A_Reindex_doesn=E2=80=99t_not_working_replica_nodes?= To: Peter Gram Cc: khan Affan , pgsql-admin@lists.postgresql.org, pgsql-admin Content-Type: multipart/alternative; boundary="00000000000019dcdb061fcca853" List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --00000000000019dcdb061fcca853 Content-Type: text/plain; charset="UTF-8" Content-Transfer-Encoding: quoted-printable Hi Peter, We are using streaming replications but still reindex is not working source db has 11.15replica has 11.11 PostgreSQL version. Regards, Mohammed Afsar On Fri, 16 Aug 2024 at 6:33=E2=80=AFPM, Peter Gram = wrote: > Hi Mohammed > > If we are talking physical replication then the wall includes changes to > index=E2=80=99s data. If we are talking logical replication the wall does= not > include index changes. > > Med venlig hilsen > > Peter Gram > S=C3=A6byholmsvej 18 > 2500 Valby > > Mobile: (+45) 5374 7107 > Email: peter.m.gram@gmail.com > > > > On Fri, 16 Aug 2024 at 13.20, khan Affan wrote: > >> Hi Mohammed, >> >> PostgreSQL replication is based on replicating WAL (Write-Ahead Log) >> records, which capture changes to data files such as inserts, updates, >> deletes, and the creation of new tables. However, >> >> WAL does not include the physical structure of indexes. >> >> If you're encountering issues like zero rows being fetched from the >> replica, it could be due to index inconsistencies or replication lag. To >> ensure your read replica has updated indexes, you can promote the replic= a >> to be the primary node, perform the REINDEX operation, and then revert t= he >> roles, making the original primary node the primary again. >> >> Regards >> Muhammad Affan >> >> On Wed, Aug 14, 2024 at 5:58=E2=80=AFPM Mohammed Afsar >> wrote: >> >>> Dear experts, >>> >>> >>> We have initiated reindex on source db but it not replicated to replica >>> db and we have having select query fetching with zero rows but data exi= ting >>> on the table. >>> Postgres 11.10 streaming replication >>> >>> Regards, >>> Mohammed Afsar >>> >> --00000000000019dcdb061fcca853 Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable
Hi Peter,

We are using streaming replications but still reindex is not working so= urce db has 11.15replica has 11.11 PostgreSQL version.

Regards,
Mohammed Afsar


On Fri, 16 Aug 2024 at 6:33=E2=80=AFPM, Peter Gram <peter.m.gram@gmail.com> wrote:
Hi Mohammed

If we are talking physical repl= ication then the wall includes changes to index=E2=80=99s data. If we are t= alking logical replication the wall does not include index changes.=C2=A0

Med venlig hilsen
<= br>Peter Gram
S=C3=A6byholmsvej 18=C2=A0
2500 Valby

Mobile: (+45) 5374 7107
=

=


On Fri, 16 Aug 2024 at 13.20, khan Affan <bawag773@gmail.com>= wrote:
Hi Mohamme= d,

PostgreSQL replication is based on replicating WAL (Write-Ahead L= og) records, which capture changes to data files such as inserts, updates, = deletes, and the creation of new tables. However,

WAL does not incl= ude the physical structure of indexes.

If you're encountering is= sues like zero rows being fetched from the replica, it could be due to inde= x inconsistencies or replication lag. To ensure your read replica has updat= ed indexes, you can promote the replica to be the primary node, perform the= REINDEX operation, and then revert the roles, making the original primary = node the primary again.

Regards
Muhammad Affan

On Wed, Aug 14, 202= 4 at 5:58=E2=80=AFPM Mohammed Afsar <vmdapsar@gmail.com> wrote:
Dear experts,


= We have initiated reindex on source db but it not replicated to replica db = and we have having select query fetching with zero rows but data exiting on= the table.
Postgres 11.10 streaming replication=C2=A0
=

Regards,
= Mohammed Afsar
--00000000000019dcdb061fcca853--