On Thu, May 18, 2017 at 5:41 PM, Scott Marlowe <scott.marl...@gmail.com> wrote:
> On Thu, May 18, 2017 at 3:40 PM, Scott Marlowe <scott.marl...@gmail.com> > wrote: > > I would say that the best thing to do is to run 9.6 grab pgadmin4 and do > all > > the examples in the doc page on partitioning. > > > > https://www.postgresql.org/docs/9.6/static/ddl-partitioning.html > > > > If that works well then the question becomes are there any esoteric cases > > where pgadmin4 won't quite get you there? > > Or maybe what can we automate from pgadmin4 that you currently need to > script etc? > *FWIW, It's not much of a problem creating partitioned tables. You simply follow thedocumentation in https://www.postgresql.org/docs/9.6/static/ddl-partitioning.html <https://www.postgresql.org/docs/9.6/static/ddl-partitioning.html>The problem occurs when you are already in production and have data in the mastertable. In that case, you need a function (or script) that reads records from the old masterand inserts them into the appropriate child tables. Verification of success is needed, and then the old master table needs to be truncated. This involves a timeout period in production. One technique which minimizes that, which I personally have done, is to create a new master that is empty, and then create all children from that. When inserts are complete and verified (from old master), then the old master is renamed and the new master is renamed in place of the old master. That means the only downtime is during the renaming, whichis minimal. In the event a problem occurs, you can easily reverse the renames, and since * *the old master will still contain all original records, the risk is minimal.* -- *Melvin Davidson* I reserve the right to fantasize. Whether or not you wish to share my fantasy is entirely up to you.