![]() ![]() You can probably do the rename earlier since it's in a transaction but this is just how I ended up doing it. attach the existing data as a partitionįOR VALUES FROM (''::timestamptz) TO (''::timestamptz) ĪLTER TABLE events RENAME TO "events_legacy" ĪLTER TABLE events_partioned RENAME TO events validate it (no table exclusive lock, works row-wise instead) pick an upper bound for a timestamp in the future while this transaction occurs, say a few days from nowĬHECK (created_at >= ''::timestamptz AND created_at ''::timestamptz) NOT VALID It's easy as long as there aren't any events with timestamps in the future. ![]() My goal with was to quickly migrate over to the no table without any changes in client code. You eventually want the events_partioned to be the new events and the existing events to become a child partition, with minimal downtime I'm assuming here you have a table called events for old data and a new table events_partitioned that's structured the same by partitioned by created_at with no child tables. Just the cost of around 1 sequence scan with some overhead It happened fairly quickly and I didn't need to move any data out to a new table. Here's how my transaction looked given a table called events with a field called created_at used for timing that eventually became partitioned. I actually did it aittle differently because we never migrated data itself since that comes with a maintenance cost. CREATE TABLE tmptestb (num integer) ALTER TABLE tmptesta RENAME TO tmptestc ALTER TABLE tmptestb RENAME TO tmptesta ALTER TABLE tmptestc RENAME TO tmptestb - Now look at what the VIEW is using again (now is using tmptestb with an alias of tmptesta) SELECT pggetviewdef ('vtmptesta'::regclass, false) - Cleanup DROP. I've done this before in production and it worked great. ![]()
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |