MIME-Version: 1.0
References: 
 <CA+FWGK6pT2TKQ0iNH0=6SYOpi560KdLy4kWzKps_B8GMsM4QwA@mail.gmail.com>
In-Reply-To: 
 <CA+FWGK6pT2TKQ0iNH0=6SYOpi560KdLy4kWzKps_B8GMsM4QwA@mail.gmail.com>
From: Keith Fiske <keith.fiske@crunchydata.com>
Date: Fri, 22 Apr 2022 11:38:08 -0400
Message-ID: 
 <CAODZiv6XDLy3n=WPU3a=Ra27M=sgiwQP=FX=2mW7FzJnVChLBw@mail.gmail.com>
Subject: Re: Partitioning without downtime
To: Loles <lolesft@gmail.com>
Cc: pgsql-admin <pgsql-admin@postgresql.org>
Content-Type: multipart/alternative; boundary="000000000000ec12bb05dd400411"
Archived-At: 
 <https://www.postgresql.org/message-id/CAODZiv6XDLy3n%3DWPU3a%3DRa27M%3DsgiwQP%3DFX%3D2mW7FzJnVChLBw%40mail.gmail.com>
Precedence: bulk

--000000000000ec12bb05dd400411
Content-Type: text/plain; charset="UTF-8"

On Fri, Apr 22, 2022 at 11:28 AM Loles <lolesft@gmail.com> wrote:

> Hi!
>
> Is there a way to partition a table in production without downtime? Or
> extension that allows it.
>
> Thanks!
>

Zero downtime (or without a table lock at some point) is not possible with
native partitioning since you cannot convert an existing table into a
natively partitioned table. However, it is possible to do it with very
little downtime if the circumstances allow. I've laid out a method of doing
this with pg_partman here -
https://github.com/pgpartman/pg_partman/blob/master/doc/pg_partman_howto_native.md

-- 
Keith Fiske
Senior Database Engineer
Crunchy Data - http://crunchydata.com

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

<div dir=3D"ltr"><div dir=3D"ltr"><br></div><br><div class=3D"gmail_quote">=
<div dir=3D"ltr" class=3D"gmail_attr">On Fri, Apr 22, 2022 at 11:28 AM Lole=
s &lt;<a href=3D"mailto:lolesft@gmail.com">lolesft@gmail.com</a>&gt; wrote:=
<br></div><blockquote class=3D"gmail_quote" style=3D"margin:0px 0px 0px 0.8=
ex;border-left:1px solid rgb(204,204,204);padding-left:1ex"><div dir=3D"aut=
o">Hi!<div dir=3D"auto"><br></div><div dir=3D"auto">Is there a way to parti=
tion a table in production without downtime? Or extension that allows it.</=
div><div dir=3D"auto"><br></div><div dir=3D"auto">Thanks!</div></div>
</blockquote></div><div><br></div><div>Zero downtime (or without a table lo=
ck at some point) is not possible with native partitioning since you cannot=
 convert an existing table into a natively partitioned table. However, it i=
s possible to do it with very little downtime if the=20
circumstances allow. I&#39;ve laid out a method of doing this with=20
pg_partman here -=C2=A0<a href=3D"https://github.com/pgpartman/pg_partman/b=
lob/master/doc/pg_partman_howto_native.md">https://github.com/pgpartman/pg_=
partman/blob/master/doc/pg_partman_howto_native.md</a><br></div><div><br></=
div>-- <br><div dir=3D"ltr" class=3D"gmail_signature"><div dir=3D"ltr"><div=
><div dir=3D"ltr">Keith Fiske<br>Senior Database Engineer<br>Crunchy Data -=
 <a href=3D"http://crunchydata.com" target=3D"_blank">http://crunchydata.co=
m</a></div></div></div></div></div>

--000000000000ec12bb05dd400411--