46

Do you know any applications to synchronize two databases - during development sometimes it's required to add one or two table rows or new table or column. Usually I write every sql statement in some file and during uploading path I evecute those lines on my production database (earlier backing it up). I work with mySQL and postreSQL databases.

What is your practise and what applications helps you in that.

pbrodka
  • 1,137
  • 1
  • 11
  • 23

13 Answers13

31

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

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

whaley
  • 16,075
  • 10
  • 57
  • 68
  • 1
    That's were the problem lies. Typo3 runs it's own scripts upon updates/modifications, and I want to extract changes when it does this. That's why I need a DB diff tool – demonkoryu Oct 12 '10 at 13:24
  • 1
    If you would go the versioning route, I recommend to use http://www.liquibase.org/. It can manage your schema forward and backward in time. – Neeme Praks Oct 13 '10 at 02:35
  • @Neeme Thank you, that's about what I'm looking for. If this becomes a mature product, it will be perfect. – demonkoryu Oct 14 '10 at 09:37
  • What is not mature about liquibase? I know several companies that are using it to version the database changes in their products. – Neeme Praks Oct 14 '10 at 11:29
6

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

From the APGDiff Page:

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

Patryk Kordylewski
  • 1,263
  • 1
  • 7
  • 11
5

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

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

Iain Holder
  • 14,172
  • 10
  • 66
  • 86
5

Another vote for RedGate SQL Compare

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

Wouldn't want to live without it!

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

AlexJReid
  • 1,601
  • 1
  • 13
  • 17
2

If you write your SQL statements for your development database (which are, I imagine, series of DDL instructions such as CREATE, ALTER and DROP), why don't you keep track of them by recording them in a table, with a "version" index? You 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.
Philippe Grondier
  • 10,900
  • 3
  • 33
  • 72
  • This is essentially database versioning, but self-implemented. Good approach, but probably better to use an existing tool. – sleske Oct 12 '10 at 11:50
1

I really like the EMS tools.

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

One of the tools is the DB Comparer.

bob
  • 6,465
  • 1
  • 30
  • 26
1

TOAD

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

the redgate one is good also.

John Nicholas
  • 4,778
  • 4
  • 31
  • 50
0

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

AKi
  • 170
  • 1
  • 7
0

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

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

friol
  • 6,996
  • 4
  • 44
  • 81
0

Navicat has a structure synchronisation wizard that handles this.

Colonel Sponsz
  • 1,701
  • 1
  • 12
  • 21
0

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

Black
  • 5,022
  • 2
  • 22
  • 37
0

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

wimvds
  • 12,790
  • 2
  • 41
  • 42
0

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

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

Mark Shust at M.academy
  • 6,300
  • 4
  • 32
  • 50