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

**URL:** <https://forums.percona.com/t/partitioned-a-table-and-change-primary-key-using-pt-online-schema-change/7657>\
**Category:** Percona Toolkit\
**Created:** [May 27, 2020, 5:25am UTC](https://forums.percona.com/t/partitioned-a-table-and-change-primary-key-using-pt-online-schema-change/7657 "2020-05-27T05:25:31Z")\
**Posts on this page:** 2\
**Page:** 1

<div class="post-metadata">

**Author:** ![AntiLapa](https://avatars.discourse-cdn.com/v4/letter/a/977dab/32.png) [@AntiLapa](https://forums.percona.com/u/AntiLapa)\
**Post date:** [May 27, 2020, 5:25am UTC](https://forums.percona.com/t/partitioned-a-table-and-change-primary-key-using-pt-online-schema-change/7657/1 "2020-05-27T05:25:31Z")

</div>

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) )  
(&nbsp;   
&nbsp; PARTITION p20190901 VALUES LESS THAN ( UNIX\_TIMESTAMP('2019-09-01 00:00:00') ),  
&nbsp; PARTITION p20191001 VALUES LESS THAN ( UNIX\_TIMESTAMP('2019-10-01 00:00:00') ))”

---

<div class="post-metadata">

**Author:** ![vaibhav\_upadhyay40](https://sea1.discourse-cdn.com/flex019/user_avatar/forums.percona.com/vaibhav_upadhyay40/32/9_2.png) [@vaibhav\_upadhyay40](https://forums.percona.com/u/vaibhav_upadhyay40)\
**Post date:** [June 25, 2020, 11:58am UTC](https://forums.percona.com/t/partitioned-a-table-and-change-primary-key-using-pt-online-schema-change/7657/2 "2020-06-25T11:58:13Z")

</div>

Hi @AntiLapa   
  
I would suggest not to use ''no-nocheck-unique-key-change" to avoid disaster.&nbsp;  
Also try to split the operation into 2 parts.  
1) Altering PK  
2) Creating partition  
  
  
Sample query : (Test it in non-prod / Lower environment)&nbsp;  
–dry-run&nbsp;  
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&nbsp;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)&nbsp;  
–critical-load&nbsp;  
–max-load&nbsp;  
–chunk-size (explicit value to copy rows)  
–max-lag&nbsp; &nbsp; (set as per acceptable delay on slave)  
  
&nbsp;Hope this helps