If there are hundreds of users who browse the live WordPress website, and you need to do a major update to the website, what should you do? The answer is clone the website into a staging environment, update and then test the usability of the entire website again, make sure the usability is smooth and the designs don’t break. Once everything is fine, you need to merge the staging site with the live site. What should you be aware of?
Certainly it is always difficult to merge the old website and the new website, especially if it is an eCommerce site. I had the opportunity to merge a website and I must say it was a painful process and I had to spend the whole working day doing it. It was very tricky and we couldn’t afford to fail the process.
Before you start the entire process, make sure your client agree to put the website on maintenance to ensure there is no new orders coming in during the merging.
Understand changes in database
First of all, you need to understand the database structure of WordPress. Make sure you are familiar with the plugins installed in the website and check if there are new tables created for the plugins. If there are additional tables, make sure you migrate over correctly. In my case, I did not have any plugins that create custom tables so the tables that might have been updated are:
1. wp_posts and wp_postmeta: You might have added new images and blog posts on staging and the live site might have new WooCommerce orders.
2. wp_users and wp_usermeta: It is very common to have new user registrations on the live site while you are updating the staging.
3. wp_woocommerce_order and wp_woocommerce_ordermeta: There might be new WooCommerce orders on the live site.
Start merging!
1. I cloned the entire staging site to my localhost. Firstly, I replaced the entire wp_users and wp_usermeta tables from the live site.
2. I compared the wp_posts table from both databases, got the post_id (id) of the last row that had the exact same data.
3. In my staging database, I ran these queries to increase the id by 1000 (assuming the row I noted in step 2 was 4294), assuming there are less than 1000 rows added in the staging site:
update wp_posts set id = id + 1000 where id > 4294; update wp_posts set post_parent = post_parent + 1000 where post_parent > 4294; update wp_postmeta set post_id = post_id + 1000 where post_id > 4294;
4. Next, I exported the new rows from the staging to SQL:
select * from wp_posts where id > 5294;
To export the query result to an SQL file, I clicked on the Export button under ‘Query Results Operations’. Note: export the Insert statements only.
5. Then, I realised my staging and live site database has duplicated meta_ids, therefore I had to make sure my meta id is not duplicated. In phpmyadmin, I ran these queries:
select * from wp_postmeta where post_id > 5294;
6. Again, export the query result (Insert statements only) to an SQL file by clicking on the Export button under ‘Query Results Operations’. I manually changed the first 4 digits of the IDs to something large (assuming my postmeta only have hundreds of rows). In my case, the meta_ids were 1998xxx and I increased them to 2001xxx as the largest id in my live site table was 2000xxx. I also used CTRL+H to find all (1998 and replaced them with (2001 which made the process a lot faster.
7. Then, delete the wp_posts and wp_postmeta table of staging and replaced them with the ones from the live site. After that, I imported the SQL files I exported from step 4 and 6 to the database.
8. Because I did a major WooCommerce update, I had to also changed all the post_status of the shop orders by running this query:
update wp_posts set post_status = 'wc-completed' where post_type = 'shop_order' and post_status = 'publish';
9. There was no change in my wp_orders and wp_ordermeta table, therefore I just replaced the entire tables from the live site.
Things to be aware of:
1. Site should be put under maintenance mode
2. Make sure the live site database is the most updated version and the live site doesn’t have new WooCommerce orders
This might not be the best method but this is the method I have used to merge sites. Comment below if you found a more efficient method!