Partitioned a table and change primary key using pt-online-schema-change

Polyglot Projects Percona Toolkit
1

Hi,
I have a table which is mostly inserts (no updates, very few deletes) with a primary key on id which is auto_increase.I want to use pt-online-schema-change to partitioned the table by date (another column).For that I need to do 2 things - change the primary key to be (id,dateTS) and to add partitions.Reading the documentation I saw --nocheck-unique-key-change is not advisable as data may be lost.
Is that indeed the case if I don’t have updates (but inserts still uses 0 or null in the id column for the auto-increase) ? Do I have other option?

This is what I had in mind:
pt-online-schema-change --nocheck-unique-key-change --user $USER --password $PASS --host $WRITER D=db_name,t=table_name–alter “
drop primary key, add PRIMARY KEY (id,dateTS), PARTITION BY RANGE ( UNIX_TIMESTAMP(dateTS) )
( 
  PARTITION p20190901 VALUES LESS THAN ( UNIX_TIMESTAMP('2019-09-01 00:00:00') ),
  PARTITION p20191001 VALUES LESS THAN ( UNIX_TIMESTAMP('2019-10-01 00:00:00') ))”




2

Hi @AntiLapa

I would suggest not to use ''no-nocheck-unique-key-change" to avoid disaster. 
Also try to split the operation into 2 parts.
1) Altering PK
2) Creating partition


Sample query : (Test it in non-prod / Lower environment) 
–dry-run 
pt-online-schema-change D=schema,t=tablename --dry-run–user=user --password=‘password’ --alter “DROP PRIMARY KEY,ADD PRIMARY KEY(id,dateTS)”

–execute
pt-online-schema-change D=schema,t=tablename --execute --user=user --password=‘password’ --alter “DROP PRIMARY KEY,ADD PRIMARY KEY(id,dateTS)”

There are few other options worth exploring. These options work on different areas during execution. For more details and suitable value as per your production load and requirement refer to the documentation and test thoroughly non-prod / Lower environment.

–recursion-method
–no-drop-old-table ----(keep the old table (with new name) as well as new table with newly desired table, hence you need to be careful with current disk space usage, expected data growth till the execution completes and additional requirement of disk is sufficient) 
–critical-load 
–max-load 
–chunk-size (explicit value to copy rows)
–max-lag    (set as per acceptable delay on slave)

 Hope this helps