How should databases be configured for different environments to not lose data

Question

How to configure databases for development, staging and production environments so that there will be no any data loss. Many Applications such as Wordpress, Django, etc... use some database to store data. Now, if I am about to implement such a setup. The complicated thing would be the database. Because there will be difference in the data in staging environment and production environment. So when I try to merge both in some way can there be a problem or data loss. What should I consider when I try to host different environments.

Answer 1

You don't need to do any special config for a database to make it not lose data. You do want to have the appropriate setup for backups and possibly HA/DR depending on a given environment's required uptime, RTO and RPO, to guard against data loss due to hardware failure or operator error.

If that doesn't make a lot of sense to you, you're going to want to hire a qualified sysadmin and/or DBA.

Edit - it also seems like you've got some concerns about data loss when integrating new code changes to your software? That's really a data-engineering and software-engineering topic, not the sort of thing that a sysadmin-focused site is well suited for. Stack Overflow is where you'll want to have that conversation, with more details about your language, framework, and database.

Answer 2

"So when I try to merge both in some way can there be a problem or data loss."

Ah, then you are misunderstanding the concept of a staging directory in terms of development. Staging is a final test to test the system before deploying in production. I.e. you would take a backup of production, deploy all the updates to staging, so users can test on a copy of the production system, THEN at the end do in one transaction a backup of production and the same (automated) deployment into production.

AT NO POINT IN TIME IS THERE ANY MERGING. Any merging is a fundamental misunderstanding of how you move database changes. Any updates to the database should be handled by automatic update scripts as part of the deployment. Which are tested in multiple steps - including at the end in staging against a copy of the production database. If anything goes wrong during deploy, you roll back to the last backup.

The problem you are afraid of just cannot appear in a professionally managed setup - if they may, rework the setup.