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.
https://www.depesz.com/2019/03/19/migrating-simple-table-to-partitioned-how/