MIME-Version: 1.0
References: <CAKna9VZ=b6f-zei3RY4uiKq7xjt0Ox_oftySPRq8t0YRWDN+4g@mail.gmail.com>
 <CAEzWdqeWG2Ld7Y=_kdjBL0CdbRCSGzt+L4p3vzmHoNsxDk+POw@mail.gmail.com>
In-Reply-To: <CAEzWdqeWG2Ld7Y=_kdjBL0CdbRCSGzt+L4p3vzmHoNsxDk+POw@mail.gmail.com>
From: Lok P <loknath.73@gmail.com>
Date: Sat, 27 Jul 2024 14:17:26 +0530
Message-ID: <CAKna9VbvovPsOibzPVNxw88vP+=fCZpQ8A=Vvacpw4=8+-X6mQ@mail.gmail.com>
Subject: Re: Partition boundary messed up
To: yudhi s <learnerdatabase99@gmail.com>
Cc: pgsql-general <pgsql-general@lists.postgresql.org>
Content-Type: multipart/alternative; boundary="0000000000000fa9e3061e36ae97"
Archived-At: <https://www.postgresql.org/message-id/CAKna9VbvovPsOibzPVNxw88vP%2B%3DfCZpQ8A%3DVvacpw4%3D8%2B-X6mQ%40mail.gmail.com>
Precedence: bulk

--0000000000000fa9e3061e36ae97
Content-Type: text/plain; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

Thank you. I understand that is going to take a lot of time as we
already have billions of rows in the main table spread across 40+
partitions.
Also this table is child to another parent partition table and so,  it will
take a lot of time to validate the FK constraint back for the new table.
Is there a less disruptive way(zero to minimal downtime) possible to fix
this mess?

On Sat, Jul 27, 2024 at 2:08=E2=80=AFPM yudhi s <learnerdatabase99@gmail.co=
m> wrote:

> If you were thinking of some ALTER command which will just alter the
> boundaries of the incorrect partition and make it correct ,  I don't thin=
k
> there exists any such. You may have to create a whole new table and run t=
he
> partman.create_parent block with the UTC time zone set so that all the
> subsequent partitions will be created with correct boundaries and then mo=
ve
> the data into it from the existing table.
>
>
>

--0000000000000fa9e3061e36ae97
Content-Type: text/html; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

<div dir=3D"ltr"><div dir=3D"ltr">Thank you. I understand that is going to =
take a lot of time as we already=C2=A0have billions of=C2=A0rows in the mai=
n table spread across 40+ partitions.=C2=A0</div><div dir=3D"ltr">Also this=
 table is child to another parent partition table and so,=C2=A0 it will tak=
e a lot of time to validate the FK constraint back for the new table.</div>=
<div dir=3D"ltr">Is there a less disruptive way(zero to minimal downtime)=
=C2=A0possible=C2=A0to fix this mess?<br></div><br><div class=3D"gmail_quot=
e"><div dir=3D"ltr" class=3D"gmail_attr">On Sat, Jul 27, 2024 at 2:08=E2=80=
=AFPM yudhi s &lt;<a href=3D"mailto:learnerdatabase99@gmail.com">learnerdat=
abase99@gmail.com</a>&gt; wrote:<br></div><blockquote class=3D"gmail_quote"=
 style=3D"margin:0px 0px 0px 0.8ex;border-left:1px solid rgb(204,204,204);p=
adding-left:1ex"><div dir=3D"ltr"><div>If you were thinking of some ALTER c=
ommand which will just alter the boundaries=C2=A0of the incorrect=C2=A0part=
ition and make it correct=C2=A0,=C2=A0 I don&#39;t think there exists any s=
uch. You may have to create a whole new table and run the partman.create_pa=
rent block with the UTC time zone=C2=A0set so that all the subsequent parti=
tions will be created with correct=C2=A0boundaries and then move the data i=
nto it from the existing=C2=A0table.<br></div><div><br></div><div class=3D"=
gmail_quote"><div>=C2=A0</div></div></div>
</blockquote></div></div>

--0000000000000fa9e3061e36ae97--