Best practice for staging to live database synchronization

[evanmcd]

Hi all,

My team has built a few ProcessWire sites, and we LOVE it - it is now our go to CMS. However, we have come up against the same issue on each site and wonder if/how any of you are managing it.

I don't think the issue is entirely specific to ProcessWire, but due to the structure of the way PW stores data it may require an answer that only applies to PW. It's the issue of keeping a staging site database in synch with a live site.

To illustrate: we start developing a new section, or modifying the functionality of a current section. All the while the client is making content changes on the current site, and in so doing changing the data in many different tables.

Is there an approach, other than manually noting which tables have changed during our update, that people are using to manage this situation?

With WordPress, for example, all of the content ends up in one big table, the structure of which doesn't change, so updates aren't that tricky. With PW data and structure being intermingled, it makes it a bit more tricky to manage.

I'd really appreciate knowing more about how people manage this, even if it's just confirming that there isn't a better way than doing it manually.

Thanks.

[ryan]

Good question. I'm not sure there's a simple answer though. What you mentioned about migrating changes directly from the database of WordPress doesn't sound like a safe thing to do with any database-driven CMS. The nature of a relational database is that there will be records in different tables referencing each other, so when you've got two versions of the same database, they would be challenging to merge. As a result, it's a problem if you have two databases going off on different paths of changes if they need to come back together at some point.

I use my staging servers for staging things like new template files, modules, new PW versions, etc. I don't usually stage actual content from one server to another. Or if I do, I'm copying and pasting it (in PW admin) from one to the other. But the way I usually stage major content changes is to make them on the live server, but in a staging part of the site (unpublished, hidden or otherwise inaccessible pages). When ready to migrate, I publish or drag the relevant pages to their live home. Note that this is for content, and not for anything that could interfere with the operation of the live site. Anything that could interfere with the live site (templates already in use, modules, versions, etc.) ideally should be staged on a different server. I don't know of a better way than that. It's not perfect, but it's always worked well for me.

Longer term, I want ProcessWire to support a workflow that lets a staging server talk to a live server and publish from one to the other (using web services). I already use this workflow on a lot of sites with custom modules, but they are specific enough to the individual sites that they aren't suitable as a broader solution. However, I do think we'll have a broader solution like this in the next 6 months to a year.

[landitus]

I was wondering the same thing! I find it much faster and safer to develop a section with templates and custom fields on my local machine. Once everything works I have to repeat the same steps on the live server... A "sync" solution would be really cool!!

[evanmcd]

Thanks for the feedback, Ryan. Would be so great to have a built-in synch capability. For now, we'll continue as we were in regards to keeping everything in synch.

[neildaemond]

When you guys move a site from staging to live for the first time (lets say the live site is on another web host server), do you need to install pw on that server first, then import the database and change the templates?

or do you copy over the files from the staging root, export the staging database, and import the database into the live database?

(ie: do you skip the installation step?)

I have an issue with my admin section after starting a new live site, where I skipping the pw installation step and am wondering if that could have caused it.

Edit: I usually use the same database name, username and password on staging and live for synchronization convenience.

[Marty Walker]

I either develop a PW site locally or on a subdomain on the destination server. In either case I haven't encountered any problems simply moving the install over (thereby skipping the installation step), uploading the database and even changing db username and passwords to match the destination setup. I usually zip up the local contents and extract it with the cPanel file manager too and haven't had any (touch wood) problems with permissions.

Moving from a subdomain to the site root is the simpler option though: drag and drop

[DaveP]

I'm with stillmovingdesign on this one - that's exactly how I have done it a couple of times with hardly any issues. The only thing last time was images inserted in content through tinymce didn't seem to have the correct path, but as there were only a couple of pages involved it wasn't a big deal.

[diogo]

I never install a new one, Just move and change the config.

[Soma]

I'm with stillmovingdesign on this one - that's exactly how I have done it a couple of times with hardly any issues. The only thing last time was images inserted in content through tinymce didn't seem to have the correct path, but as there were only a couple of pages involved it wasn't a big deal.

And even for this issue there's a module

[DaveP]

Soma, that's cool. I had seen the module but not fully appreciated what it did.

[ryan]

I always just copy the files, dump the DB, and import with phpmyadmin on the new server, then update /site/config.php to point to the server's DB. But if there's some suspicion about an incompatibility with the server, I'll try to install a blank/default-profile copy of ProcessWire just to make sure it works (and I'll delete it afterwards). Sometimes I have to do a recursive update of the permissions of /site/assets to make it writable, but rarely.

And even for this issue there's a module http://processwire.c...ink-abstractor/

I need to get this actually built into the TinyMCE module so that people don't even have to think about it. +1 on the todo list.

[Adam Kiss]

I have a config based on some $_SERVER variables (looking for URL to switch to correct database) and just copy stuff; I manage most of my websites myself, so I do changes locally and just push new db dump to live together with changed files. If the rare occasion happens where client does some stuff on live version (or I need to push something live), I take the db in the other direction (live->localhost) and deal with the uploaded files (If only my server supported git; this would be like million times easier).

[neildaemond]

I usually scp it if I have ssh access it as well, but I just wanted to make sure there wasn't any "Magic" that I missed out on by not installing it... Thanks for your responses guys

I'm with stillmovingdesign on this one - that's exactly how I have done it a couple of times with hardly any issues. The only thing last time was images inserted in content through tinymce didn't seem to have the correct path, but as there were only a couple of pages involved it wasn't a big deal.

Happened to me too!

[mindplay.dk]

Migrating changes to a live site is a big pain-point for me in Drupal at the moment - simply replicating changes by hand is not acceptable to me, not on a production-site, due to potential mistakes and "weirdness" during the repeat of changes, with features half-done or content missing in the interim.

The root of the problem, is the idea of storing data (content) and meta-data all together in the database - treating it all as "one thing" makes for very efficient and elegant development, but makes it difficult (impossible really) to migrate changes incrementally.

A couple of ideas to address this issue:

1. Separate data from meta-data. In my own CMF architecture that I've been dreaming up for some years now, meta-data is stored in flat files rather than in the database, which means you can check structural changes into a VCS, and deploy those changes easily. In the case of PW, this would mean you could check-in changes to meta-data along with matching changes to template-files, so that there is no chance of inconsistency between those.

2. Have a "recorder mode" that you can switch on during development. This would simply record all insert/update/create/alter SQL statements to a flat file, so you can repeat all the operations by just running that. Not quite as simple as it sounds though, due to primary keys possibly colliding with the same primary keys on the server you're deploying to - the only real way around that, as far as I can figure, is to not use numeric sequential keys, and use GUIDs instead.

3. Record (certain) form-submissions and controller/action, so you can repeat operations by simply running the controllers/actions again, essentially simulating the same sequence of requests that generated the original changes. Again, not necessarily as simple as it sounds, if for instance auto-insert IDs are posted via hidden-fields on a form, it wouldn't "just work".

Clearly neither of these are a "quick fix", but I think it's something we should discuss - it's not a small problem that you can ignore, in my opinon.

[ryan]

Good points. The #1 idea sounds interesting and I like where you are going with that, though I don't think it's safe to consider the meta data independent from the content to the point where it would be safe to version them separately. The recorder modes (#2 & #3) would be great if it weren't for the issue of primary keys you mentioned, but maybe there are places where it could still work. I think that anything that involves merging two versions of the same site has to treat the other's data as totally foreign. Things like IDs have to be thrown out unless you are giving all ID generation authority to one or the other. Personally, I have never seen this as a particular problem to be solved because I've never seen the ability to do it, never expected to be able to, and not sure I could ever trust it if it existed. Nevertheless, I'm enthusiastic about exploring the possibility. But I'd be scared to death of having to provide support for such a feature.

Something that I've got a higher comfort level with are specific import/export tools. For instance, a tool that enables me to connect my PW install with another, and browse the fields, templates and pages. Then I can click on "import" and have that field, template or page(s) created on my system too. If there's some matter of meta data to be resolved (like a new page reference with non-existing parent), then I know it then and there and can adjust my import sequence accordingly. If I'm modifying data rather than importing, then I can have the opportunity to see and confirm what's going to happen before it takes place. If we're talking about going between two servers that can't talk to each other, then copy/paste of JSON data from one to the other in the developer's browser is another way to accomplish it. This process seems more supportable because the individual site developer still has some ownership over what's happening.

[mindplay.dk]

The #1 idea sounds interesting and I like where you are going with that, though I don't think it's safe to consider the meta data independent from the content to the point where it would be safe to version them separately.

It's a decision you'd have to make about how you're going to use the framework - for instance, if you decide that all development happens on your local machine, and you set up users/rights on the production site so that metadata (fields and templates) can't be modified, that simplifies things.

I don't necessarily need a solution that enables me to create/change the metadata both on the running server and on my development server and then expect to merge them. That would be an extra benefit - but the key requirement is that I be able to develop the site off-line, and continuously integrate changes to the live site.

[ryan]

From both a security and collaboration standpoint, I have a strong preference for pulling changes where they need to be made, rather than having them pushed from a dev server (export/import). I think you'll like where we plan to go with import/export of field and template data. Especially the copy/paste or file JSON option (which was in PW1 for fields at least), because it doesn't require you to be running a web server to import from. You can develop these things offline on your dev server, then export what you want, take it to your other/live server and paste in the JSON (or upload JSON file) and it re-creates them. PW1 didn't support using the option for modification of existing items (just for creating), but it's certainly feasible to provide the option.

When it comes to pages, the same concept applies but is a little more complex given the diverse nature of page data that might include page references, file-based assets and so on. For example, with files/images, we can't reasonably encode those in JSON files and import elsewhere, so the dev server would need to be able to talk with the live server via a web service and pull the assets individually.

[mindplay.dk]

Sounds interesting - yes, import/export features would do it as well.

You could encode entire change-sets, including binaries, as JSON if you wanted to - you could base64 encode them. Granted, there's a substantial performance penalty and bandwidth overhead to consider there, but it might be useful to have it as an option for those who do want to archive an entire change-set in a single file.

Phar-archives would be another option - they are self-contained, bandwidth-efficient, and supported on every PHP installation. Worth considering anyhow.

[ryan]

Those sound like good options to consider. I wasn't aware of the Phar-archives though am interested and need to take a closer look. But my worry is that anything that tries to bundle everything (including potential large images/files) in one export file to be imported elsewhere is going to run up against scale. I've got a couple sites where the client might have dozens of 12 megapixel photos on a single page. They are pretty unwieldy in size to deal with individually as it is. File-based assets are so unpredictable in size I think I'd be afraid to package them up together in any manner. But packaging up the references, and letting the system transfer them individually will scale well. But it does require that production server can talk to the dev server.

[OrganizedFellow]

I have a config based on some $_SERVER variables (looking for URL to switch to correct database) and just copy stuff; I manage most of my websites myself, so I do changes locally and just push new db dump to live together with changed files. If the rare occasion happens where client does some stuff on live version (or I need to push something live), I take the db in the other direction (live->localhost) and deal with the uploaded files (If only my server supported git; this would be like million times easier).

This post is almost two years old  

Would you share that code snippet?

[diogo]

Would you share that code snippet?

Good luck with that! Adam comes to the forum only on Christmas these days

Try his twitter https://twitter.com/adamkiss

[OrganizedFellow]

Good luck with that! Adam comes to the forum only on Christmas these days

Try his twitter https://twitter.com/adamkiss

Sadly I'm not a twittererer.

[diogo]

You can always send him a PM in the forum.

[OrganizedFellow]

I have a config based on some $_SERVER variables (looking for URL to switch to correct database) and just copy stuff; I manage most of my websites myself, so I do changes locally and just push new db dump to live together with changed files. If the rare occasion happens where client does some stuff on live version (or I need to push something live), I take the db in the other direction (live->localhost) and deal with the uploaded files (If only my server supported git; this would be like million times easier).

I'm glad to announce that (with my limited php knowledge) I figured it out.

// site/config.php

$base_url  = $_SERVER['SERVER_NAME'];
switch ($base_url) {
	case "samplesite.dev":
		// LOCAL CONFIG
		$config->dbHost = 'localhost';
		$config->dbName = 'processwiredb';
		$config->dbUser = 'devuser';
		$config->dbPass = 'DeVpAsS';
		$config->dbPort = '3306';
		$config->httpHosts = array('samplesite.dev', 'www.samplesite.dev');
		break;
	case "samplesite.com":
		// LIVE CONFIG
		$config->dbHost = 'localhost';
		$config->dbName = 'processwiredb';
		$config->dbUser = 'comuser';
		$config->dbPass = 'dEvPaSs';
		$config->dbPort = '3306';
		$config->httpHosts = array('samplesite.com', 'www.samplesite.com');
		break;
}

I love this community as it encourages me to learn things I would otherwise just Google the answer to.

And then, using Adminer, I dumped and copied the database from the development site to the live site. This only took a minute.

I had previously fiddled with HeidiSQL but I'm still learning it  

THanks everyonE!!!

[Can]

Greets,

I know, this thread is quite old but I wanted to throw in some thought, even if they're nonsense

My "problem" is user generated content. I have comments, even only a few for now and statistics. I have them in seperate pages, and if a page doesn't have one yet it get's created.

So while I'm working offline there could be new pages online. And sometimes while I'm testing something offline, I'm creating and deleting pages, id's are incrementing.

This means I'm not able to use $pages->get(1824) any more or just import/export my db.

What if the id's would have a prefix? So for example you could define an id prefix for some automated page creation or user content like comments for example

$new = new Page();
$new->template = "comment";
$new->idPrefix = 2;

So the 2 wouldn't increment.

I'm not good with mySQL, but I guess it sounds easier than it is right? Then the id is not a normal integer incrementing as usual and having a different field for the prefix is probably nonsense, too?!

But maybe it inspires someone somehow

By the way, I don't know how long this exists, but I'm using a config.php and a config-dev.php and excluded the config-dev.php in my FTP app as mentioned last in the API https://processwire.com/api/variables/config/