Partitioning an existing table - pg10.6

From: Ayub M <hiayub(at)gmail(dot)com>
To: "pgsql-general(at)lists(dot)postgresql(dot)org" <pgsql-general(at)lists(dot)postgresql(dot)org>
Subject: Partitioning an existing table - pg10.6
Date: 2019-07-03 07:01:24
Message-ID: CAOS0qEsT0E3KqWTeGOU-=e38PYwpTXmgzyuEvHG4-yUhTAoMzA@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

Hello, I am using postgres 10.6 and have a huge table with ~30m recs which
needs to be partitioned. Whats the best strategy for it with minimal
downtime?

1. Rename existing table and create new partitioned tables with orig name
and keep inserting data from the old renamed to new table. This will incur
downtime for the apps.
2. Create partitioned table with new name and move the data from current
table (move oldest to newest) and when moving current month paritition
shutdown down the app and move the last set of data. This will not incur
downtime but if any data is changed which was already copied over then it
might be lost.

Any other approaches/alternatives?

--
Regards,
Ayub

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Laurenz Albe 2019-07-03 07:18:05 Re: multiple nodes in FDW create server statement
Previous Message Vijaykumar Jain 2019-07-03 05:27:43 multiple nodes in FDW create server statement