我觉得我的商店有一个漏洞,因为我们没有一个可靠的过程来控制数据库模式更改的版本。我们做了很多备份,所以我们或多或少地得到了保护,但以这种方式依赖于最后一道防线是一种糟糕的做法。
令人惊讶的是,这似乎是一个共同的主线。与我交谈过的许多商店都忽略了这个问题,因为他们的数据库不会经常更改,他们基本上只是尽量做到一丝不苟。
不过,我知道这个故事是怎么发展的。这只是时间问题,迟早会出问题,会有东西丢失。
在这方面有什么最佳实践吗?你有哪些行之有效的策略?
我觉得我的商店有一个漏洞,因为我们没有一个可靠的过程来控制数据库模式更改的版本。我们做了很多备份,所以我们或多或少地得到了保护,但以这种方式依赖于最后一道防线是一种糟糕的做法。
令人惊讶的是,这似乎是一个共同的主线。与我交谈过的许多商店都忽略了这个问题,因为他们的数据库不会经常更改,他们基本上只是尽量做到一丝不苟。
不过,我知道这个故事是怎么发展的。这只是时间问题,迟早会出问题,会有东西丢失。
在这方面有什么最佳实践吗?你有哪些行之有效的策略?
当前回答
必须阅读将数据库置于版本控制之下。查看K. Scott Allen的系列帖子。
When it comes to version control, the database is often a second or even third-class citizen. From what I've seen, teams that would never think of writing code without version control in a million years-- and rightly so-- can somehow be completely oblivious to the need for version control around the critical databases their applications rely on. I don't know how you can call yourself a software engineer and maintain a straight face when your database isn't under exactly the same rigorous level of source control as the rest of your code. Don't let this happen to you. Get your database under version control.
其他回答
我对创建脚本进行了版本控制,并在其中使用了svn版本标记。然后,每当我得到一个将要使用的版本时,我就在dbpatches/目录中创建一个脚本,命名为要上卷到的版本。该脚本的任务是在不破坏数据的情况下修改当前数据库。例如,Dbpatches /可能有名为201、220和240的文件。如果数据库当前处于201级别,则应用补丁220,然后再应用补丁240。
DROP TABLE IF EXISTS `meta`;
CREATE TABLE `meta` (
`property` varchar(255),
`value` varchar(255),
PRIMARY KEY (`property`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
INSERT INTO `meta` VALUES ('version', '$Rev: 240 $');
在考虑一个好的补丁之前,不要忘记测试你的代码。购者自慎!
The most successful scheme I've ever used on a project has combined backups and differential SQL files. Basically we would take a backup of our db after every release and do an SQL dump so that we could create a blank schema from scratch if we needed to as well. Then anytime you needed to make a change to the DB you would add an alter scrip to the sql directory under version control. We would always prefix a sequence number or date to the file name so the first change would be something like 01_add_created_on_column.sql, and the next script would be 02_added_customers_index. Our CI machine would check for these and run them sequentially on a fresh copy of the db that had been restored from the backup.
我们还准备了一些脚本,开发人员可以用一个命令将本地db重新初始化到当前版本。
Sadly, I've seen more than one team developing PL/SQL programs (stored procedures in Oracle) - sometimes ten thousands LOC - just by editing the code in TOAD (a database tool), without even saving the source to files (except for deployment). Even if the database is backuped regulary (wouldn't take that for granted, though), the only way to retrieve an old version of a stored procedure is to restore the whole database, which is many GB large. And of course sometimes concurrent changes in one file lead to loss of work, when more than one developer works on the same project.
我们对所有dabase创建的对象进行源代码控制。为了让开发人员保持诚实(因为你可以在没有源代码控制的情况下创建对象),我们的dba定期寻找不在源代码控制中的任何东西,如果他们找到了什么,他们就会丢弃它,而不询问它是否ok。
Your project team can have a DBA to whom every developer would forward their create alter, delete, insert/update (for master data) sql statements. DBAs would run those queries and on successfully making the required update would add those statements to a text file or a spreadsheet. Each addition can be labeled as a savepoint. Incase you revert back to a particular savepoint, just do a drop all and run the queries uptil the labelled savepoint. This approach is just a thought... a bit of fine tuning here would work for your development environment.