public inbox for [email protected]help / color / mirror / Atom feed
Partitioning without downtime 3+ messages / 3 participants [nested] [flat]
* Partitioning without downtime @ 2022-04-22 15:27 Loles <[email protected]> 2022-04-22 15:38 ` Re: Partitioning without downtime Keith Fiske <[email protected]> 0 siblings, 1 reply; 3+ messages in thread From: Loles @ 2022-04-22 15:27 UTC (permalink / raw) To: pgsql-admin Hi! Is there a way to partition a table in production without downtime? Or extension that allows it. Thanks! ^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Partitioning without downtime 2022-04-22 15:27 Partitioning without downtime Loles <[email protected]> @ 2022-04-22 15:38 ` Keith Fiske <[email protected]> 2022-04-22 16:42 ` Re: Partitioning without downtime ravi k <[email protected]> 0 siblings, 1 reply; 3+ messages in thread From: Keith Fiske @ 2022-04-22 15:38 UTC (permalink / raw) To: Loles <[email protected]>; +Cc: pgsql-admin On Fri, Apr 22, 2022 at 11:28 AM Loles <[email protected]> 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 ^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Partitioning without downtime 2022-04-22 15:27 Partitioning without downtime Loles <[email protected]> 2022-04-22 15:38 ` Re: Partitioning without downtime Keith Fiske <[email protected]> @ 2022-04-22 16:42 ` ravi k <[email protected]> 0 siblings, 0 replies; 3+ messages in thread From: ravi k @ 2022-04-22 16:42 UTC (permalink / raw) To: Keith Fiske <[email protected]>; +Cc: Loles <[email protected]>; pgsql-admin If you want update historical data while cutover it's difficult else you can load historical data as chunks and then during switchover apply access exclusive lock on table and load latest few hours data and rename table in single transaction. Best, Ravindra On Fri, 22 Apr, 2022, 9:08 pm Keith Fiske, <[email protected]> wrote: > > > On Fri, Apr 22, 2022 at 11:28 AM Loles <[email protected]> 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 > ^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2022-04-22 16:42 UTC | newest] Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed) -- links below jump to the message on this page -- 2022-04-22 15:27 Partitioning without downtime Loles <[email protected]> 2022-04-22 15:38 ` Keith Fiske <[email protected]> 2022-04-22 16:42 ` ravi k <[email protected]>
This inbox is served by agora; see mirroring instructions for how to clone and mirror all data and code used for this inbox