How to upload local changes to a live server without overriding new posts/pages?

At the moment my current workflow looks like this.

I will take the latest copy of WordPress and its database from the live server and develop it using MAMP on my machine.

On my local server I will make my changes. For example, I might add a new custom post type and some custom fields using the Custom Post Type UI and Advanced Custom Fields Plugins. Then I would code a custom post type template. After that I will change the settings in my wp-config.php, upload the whole WordPress directory to the live server (which takes ages) and do a search and replace on the database.

What if someone has created new posts and pages on the live website while I’ve been developing locally? When I upload my local copy it will erase the latest posts and pages. The only solution I have come up with is manually mimicking the settings on the live server with the local settings before uploading the files.

This doesn’t seem very efficient. Is there another, better way?

Making content (database) changes on a staging environment is never recommended. You should only use a local/staging environment for template changes. Then, update your theme/plugins (the wp-content folder), and push those files to the live site. Once that’s completed, you can then populate the content.

Database merging/synchronization is it’s own specialty/portion of the industry, and it’s very strenuous and fickle. It’s a workflow change, for sure, but you need to really consider the files and the database two separate entities in all cases. The piece you mentioning of “The client has added new content” is the exact use case that will happen every time, and there is no easy way around it except to avoid the conflict in the first place.