Re: Partitioning an existing table - pg10.6

On 3/7/19 10:01 π.μ., Ayub M wrote:
> 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.

Regarding 2) you could setup a trigger on the original table which would also replicate INSERTS, DELETES and UPDATES to the new table.

>
> Any other approaches/alternatives?
>
> --
> Regards,
> Ayub

--
Achilleas Mantzios
IT DEV Lead
IT DEPT
Dynacom Tankers Mgmt