[ACCEPTED]-How to synchronize development and production database-synchronization

You asked for a tool or application answer, but 10 what you really need is a a process answer. The 9 underlying theme here is that you should 8 be versioning your database DDL (and DML, when 7 needed) and providing change scripts to 6 be able to update any version of your database 5 to a higher version.

This set of links provided 4 by Jeff Atwood and written by K. Scott Allen 3 explain in detail what this ought to look 2 like - and they do it better than I can 1 possibly write up here: http://www.codinghorror.com/blog/2008/02/get-your-database-under-version-control.html

For PostgreSQL you could use Another PostgreSQL Diff Tool . It can diff 11 two SQL Dumps very fast (a few seconds on 10 a db with about 300 tables, 50 views and 9 500 stored procedures). So you can find 8 your changes easily and get a sql diff which 7 you can execute.

From the APGDiff Page:

Another 6 PostgreSQL Diff Tool is simple PostgreSQL 5 diff tool that is useful for schema upgrades. The 4 tool compares two schema dump files and 3 creates output file that is (after some 2 hand-made modifications) suitable for upgrade 1 of old schema.

Have scripts (under source control of course) that 6 you only ever add to the bottom off. That 5 combined with regular restores from your 4 production database to dev you should be 3 golden. If you are strict about it, this 2 works very well.

Otherwise I know lots of 1 people use redgate stuff for SQLServer.

Another vote for RedGate SQL Compare

http://www.red-gate.com/products/SQL_Compare/index.htm

Wouldn't 4 want to live without it!

Edit: Sorry, it 3 seems this is only for SQL Server. Still 2 - if any SQL Server users have the same 1 question I'd definitely recommend this tool.

If you write your SQL statements for your 5 development database (which are, I imagine, series 4 of DDL instructions such as CREATE, ALTER 3 and DROP), why don't you keep track of them 2 by recording them in a table, with a "version" index? You 1 will then be able to:

1. track your version changes
2. make a small routine allowing the "automatic" update of your production database by sending the recorded instructions to the database.

I really like the EMS tools.

There tools are available 3 for all popular DB's and you have the same 2 user experience for every type of DB.

One 1 of the tools is the DB Comparer.

TOAD

saved many an ass several times in the 2 past. Why do people run sql with no exit 1 strategy?

the redgate one is good also.

Siebel (CRM, Sales, etc. management product) has 3 a built-in tool to align the production 2 database with the development one (dev2prod).

Otherwise, you've 1 got to stick with manually executed scripts.

Navicat has a structure synchronisation wizard 1 that handles this.

I solve this by using Hibernate. It can detect and 1 autocreate missing tables, columns, etc.

You could add some automation to your current 4 way of doing things by using dbDeploy or a similar 3 script. This will allow you to keep track 2 of your schema changes and to upgrade/rollback 1 your schema as you see fit.

Here's a straight linux bash script I wrote 2 for syncing Magento databases... but you 1 can easily modify it for other uses :)

http://markshust.com/2011/09/08/syncing-magento-instance-production-development

DBV - "Database version control, made 1 easy!" (PHP)