Live site, customer has 100,000's of records of data. Has been doing their own thing like editing message templates, users configuring dashboards, editing and adding reports and probably a whole bunch of things I don't know about.

In the meantime, I've been developing some new enhancements which include additional custom data, both in existing tables and add-on tables. Some more message templates, a new dashlet and editing/adding reports. Plus about 80 other items, some in code some in the database.

I happen to know where the dashboard table is, I know where the message template table is - but I don't know which version of each template to keep.

So the code part is easy, but how the hell are you supposed to synchronise the two databases? There is no timestamps or other identifies in the records. There is no way of identifying what changes have been made on the live site There is no way of identifying what changes have been made on the development site.

Seriously - how are you guys doing this in the real world?

Have I missed something?

  • I'd like to thank all those who have given advice. It is all useful. Sadly, it does not change my view that any system which effectively relies on human source code control is not fit for purpose. God I wish my client had chosen something else. Jun 2, 2020 at 18:05

6 Answers 6


So a few things, if you turn on logging then you'll get a full timestamped history of changes to all tables in the database. If you had this on both environments then you would now be able to see all the changes.

However what your describing is a workflow issue, i.e. having changes happening in two environments without being clear on how changes will be released from Dev -> Prod seems like an oversight and not one CiviCRM will solve (Sorry - not trying to be rude).

In our pre-prod implementations we always define where customers are doing their prod configurations, where we're doing our dev and which environment is for playing. This way we have a clear release process from Dev -> Prod. We also introduce staging environments at the right time.

Hope that helps!

  • OK, that's kind of fair, but TBH I never expected to walk into this in the first place. But this is a month's worth of Dev work after just going live. Doesn't matter what sort of staging there is, I don't see a way round it. With no clear separation of system vs data, no proper timestamping/checksum in the tables themselves civi makes things much harder than they ought to be. May 30, 2020 at 7:10

It sounds like you have quite a mission. There is no smooth path for synching between the two. We have tried building such a process but it was not robust enough to release.

Our general approach is that if anything is created on live then replicate it on dev, and do not create anything on dev without having first created it on live.

This is the only way we can ensure we keep the gid and field id and all the other entities which might then be applied in customisations, reports etc, in synch. In other words, before doing anything on dev, grab a copy of live. If you need a new field, or some new field options, or a new relationship type etc etc, then create it on both at same time.

If that can't be done, which is your case, then there are some sql inserts (eg relationship type) that are "safe" to do but the IDs may not be consistent and hence may mean that moving a customisation/custom report/etc from dev to live will mean having those IDs updated for it to work on live.

HTH. it isn't a nice situation to be in.

  • Genuinely thanks for the sympathy. I've been a coder for 40 years; civi is the worst system I've ever had to use and to my mind is absolutely not ready for real world use. No record locking, no dev->production control, no security for add-ons, a great API but buggy, no timestamped records, no unique field names between tables, a data structure and API that are appallingly slow. But the customer chose it, despite my protestations they are sticking with it, they've spent a 5 figure some to have it fixed to sort of do the job they want. May 30, 2020 at 7:16
  • I'd be interested to hear why you feel CiviCRM is the worst system you've ever had to use. Please feel free to reach out to me parvez at vedaconsulting.co.uk May 30, 2020 at 16:50

As with the others, I don't know of any great way to sort out the current situation easily, or an out of the box way to make it work super smoothly in the future although of course it does become somewhat smoother if you plan around this from the start when doing your changes.

As far as doing this again in the future or "what to do differently next time", as @Aidan says one way is to package changes in an extension, which allows management of changes to the db schema and data. Another way is to simply track those changes in whatever way you prefer so that you can replicate them in production. So script it, take notes, whatever it is... yes that's sort of dumb, but there you go. And as @Parvez Saleh says you can turn on logging as well if that helps with this (gives you a copy of every table, adding the log_ prefix in the name and a few new fields in each including a timestamp).

In general here the approach is always that production is the master copy and that anything done in dev needs to them be merged into prod, not the other way around. That's what works best in my experience, at least.

As for the current situation:

As you said everything in the file system shouldn't be too bad. Hopefully you have some sort of revisioning (git?) and you can simply deal with the changes that way. But watch that you don't override some things on prod like user uploads and the main settings files, etc. In Drupal for example these are usually in sites/default but it depends how you're set up.

As for the db this is tougher of course. If you have logging turned on (as above) this helps somewhat. I'm going to assume you have a copy of the database from before you started the changes (i.e. from when you set up the dev site). I would probably start by creating a new database and loading the original data in there. Then you can progressively try exports from both using the same queries in both and diff those export files to start building up a record of the changes. If nothing else going through this should help to remind you / build up a list of what changes were made, even if doesn't give you exact db update queries that you can simply apply.

Once you know what has changed in the db you have a few ways to deploy this to prod. In many cases it may be smarter to simply recreate the steps there, i.e. go through the admin pages, for example re-adding your new custom fields. I would do this in many cases if the changes won't take very long. If something will take a long time, there are some bulk update tools available, depending on what you're doing, and there's also the API. And of course you can always run queries on the db directly, however this is the most error prone and requires the most understanding of Civi's data structure.


To address some of the specific concerns:

  • First - check out hook_civicrm_managed and the *.mgd.php file format in the dev docs. A lot of changes made in the db can be made in code instead and easily moved between sites.
  • Added/edited reports can be moved to an extension. A corresponding .mgd.php file will ensure the report is added to the db. See Extended Reports extension for an example.
  • It sounds like you don't have advanced logging turned on; I would do it now. This will give you your timestamped entries.
  • Custom fields, message templates, dashlets - all candidates for managed entities.
  • Unique field names are available at the DAO layer, so are available via API - but not via SQL.

I understand your frustration with Civi - you have some good points. Unfortunately, Civi was designed for deployment on least-common-denominator shared hosting, which makes a lot of better practices impossible. We don't have the benefit of assuming composer, npm, or even non-EOL versions of PHP/MySQL are available. However, I can assure you that some of the very largest NGOs in the world use it successfully.


As Pete & Parvez have already said, there is no general solution for syncing two systems as you describe.

It depends quite what changes you have been making but some of your difficulties can be eased by packaging your enhancements as extensions. There are plenty of examples of extensions creating and using custom fields, tables, reports etc in a portable way.

You will need to go through your dev instance, remember what changes you made and then bundle them into suitable extensions. That is tedious but you will end up with something more maintainable - and none of your options from this point are easy.


I have not tried this but https://lab.civicrm.org/extensions/configitems says

The extension can import the following types of items:

  • ContactTypes
  • MembershipTypes
  • RelationshipTypes
  • OptionGroups with OptionValues
  • Groups
  • Tags
  • FinancialAccounts
  • FinancialTypes
  • EventTypes
  • ActivityTypes
  • LocationTypes
  • CaseTypes
  • CustomGroups with CustomFields
  • CiviCRM Settings (system settings set through the Setting.API - be especially careful with that!)
  • and this link looks like a useful set of answers
    – petednz - fuzion
    Jun 12, 2020 at 3:04
  • Looks interesting, doesn't cover everything I am worrried about, but goes a long way. I shall investigate further and report back. Jun 12, 2020 at 8:23
  • looks like i also forgot to paste this in my above comment civicrm.stackexchange.com/a/30474/181
    – petednz - fuzion
    Jun 12, 2020 at 19:02

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.