Thread: Partitioning an existing table - pg10.6
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
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
Hello, I didn’t test it myself but maybe using logical réplication could help ... See https://www.postgresql.org/docs/10/logical-replication.html Operations - create parttable - Feed it using réplication - when sync : stop app, stop réplication, rename tables - maybe you can réplicate from parttable to nonpart as a rollback plan - ... To be tested carrefully Regards PAscal -- Sent from: http://www.postgresql-archive.org/PostgreSQL-general-f1843780.html
Hello, Trying to do what I suggested, I understood it doesn't work ;o( Logical replication can only work between two distinct databases, and it seems that the replicated table name is the same as its source ... Regards PAscal -- Sent from: http://www.postgresql-archive.org/PostgreSQL-general-f1843780.html
I have not personally used this, but the write-up seems solid to minimize downtime to help you to shift data gradually. Be sure you understand the limitations of partitioning, particularly when you are still on 10x not yet on v11 where updates will shift a row to a new partition if the partition key is updated.