Jump to content
evanmcd

Best practice for staging to live database synchronization

Recommended Posts

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.

  • Like 6

Share this post


Link to post
Share on other sites

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.

  • Like 9

Share this post


Link to post
Share on other sites

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!!

Share this post


Link to post
Share on other sites

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.

Share this post


Link to post
Share on other sites

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.

Share this post


Link to post
Share on other sites

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 :)

Share this post


Link to post
Share on other sites

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.

Share this post


Link to post
Share on other sites

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

  • Like 1

Share this post


Link to post
Share on other sites

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

  • Like 1

Share this post


Link to post
Share on other sites

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

Share this post


Link to post
Share on other sites

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.

  • Like 3

Share this post


Link to post
Share on other sites

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).

  • Like 1

Share this post


Link to post
Share on other sites

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 :D

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!

Share this post


Link to post
Share on other sites

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.

  • Like 2

Share this post


Link to post
Share on other sites

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.

  • Like 3

Share this post


Link to post
Share on other sites

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.

Share this post


Link to post
Share on other sites

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.

Share this post


Link to post
Share on other sites

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.

Share this post


Link to post
Share on other sites

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.

Share this post


Link to post
Share on other sites

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?

Share this post


Link to post
Share on other sites

You can always send him a PM in the forum.

  • Like 1

Share this post


Link to post
Share on other sites

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).

:D

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

:P

// 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!!!

  • Like 4

Share this post


Link to post
Share on other sites

Greets,

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

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/

  • Like 2

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now

  • Recently Browsing   0 members

    No registered users viewing this page.

  • Similar Content

    • By jom
      Chäs & Co is a b2b cheese trader. They love good cheese and are proud of their excellent knowledge und connections to small cheese producers. They maintain a custom made database and used to copy/paste (!) the cheese infos to the former CMS (Contao). Since substituting the database with ProcessWire was no option, they export now the database to a csv file and import it to the new website (expanded version of module ImportPagesCSV).
      From there, the pw magic begins. The staff works with the admin interface: They search and find products in notime using the build-in search field (using cheese names or id. Using ids only possible by adding hook, see this forum thread). They generate price lists easily based on a template, providing checkboxes and option buttons for some restrictions, choose i.e. the cheese origin and price audience (all implemented as page selectors). By saving the page a PDF list gets generated (using mpdf, a php pdf library).
      The visitors can sort and filter the products using a form by shuffle.js. Product details are shown in a popup (fancybox). There's also the option for generating a nice pdf data sheet. No CSS framework being used.
      Other modules used: ProcessJumpLinks, ProcessProMailer, ProcessWireUpgrade.
      Visual design: Nicole Haller


    • By Juergen
      Hello @ all,
      I am creating a new inputfield/fieldtype to store opening hours, but I am struggeling to save values from multiple dynamic created inputfields in 1 column of the database.
      Scenario:
      The user can enter one or more opening times per day in a UI.
      Fe:
      Monday open from 08:00 to 12:00 and from 14:00 to 17:00 Tuesday open from 08:00 to 12:00 and from 14:00 to 19:00 and so on
      Via a little JavaScript you can add as much opening times as you need per day - the additional inputfield will be created dynamically.
      After form submission all the values are in the POST array -> this works (see example below):
      ProcessWire\WireInputData Object ( [openinghours_mo-0-start] => 09:00 [openinghours_mo-0-finish] => 13:00 [openinghours_mo-1-start] => 14:00 [openinghours_mo-1-finish] => 18:00 [openinghours_mo-2-start] => 21:00 [openinghours_mo-2-finish] => 23:00 [openinghours_tu-0-start] => 09:00 [openinghours_tu-0-finish] => 13:00 [openinghours_tu-1-start] => 14:00 [openinghours_tu-1-finish] => 18:00 [openinghours_we-0-start] => 09:00 [openinghours_we-0-finish] => 13:00 [openinghours_we-1-start] => 14:00 [openinghours_we-1-finish] => 18:00 [openinghours_th-0-start] => 09:00 [openinghours_th-0-finish] => 13:00 [openinghours_th-1-start] => 14:00 [openinghours_th-1-finish] => 18:00 [openinghours_fr-0-start] => 09:00 [openinghours_fr-0-finish] => 13:00 [openinghours_fr-1-start] => 14:00 [openinghours_fr-1-finish] => 18:00 [openinghours_sa-0-start] => [openinghours_sa-0-finish] => [openinghours_so-0-start] => [openinghours_so-0-finish] => ) The property name is always the name attribute of the field 😉 . If the property is empty means closed on that day.
      Now I need to combine all those values into 1 array (or json array) and store it in the database in 1 column called 'hours' in my case (see screenshot below):

      In my ___processInput(WireInputData $input) method I have tried to make it work like this:
      public function ___processInput(WireInputData $input): self { $name = $this->attr('name'); $value = $this->attr('value'); //input object includes always every input on the page, so lets filter out only inputs from this field //we need to do this, because the number of values is variable - so extract only values that starts with $name.'_' $nameAttributes = []; foreach($input as $key=>$value){ if(substr($key, 0, strlen($name.'_')) === $name.'_'){ $nameAttributes[$key] = $value; } } // loop through all inputfields of this fieldtype $time_values = []; foreach($nameAttributes as $nameAttr => $value) { $time_values[$nameAttr] = $value; } } //save it in the database $input->set('hours', serialize($time_values)); return $this; } The only important part of this code is the last part with the serialize function.
      After saving it will create a record in the database, but the value is always NULL (default value) (see below).

      Checking $time_values returns all the values, but printing out "$this" shows me that the property "hours" inside the Openinghours object is empty (see below) - so the mistake must be there, but I dont know where?!?!?!?
      [title] => Home [openinghours] => ProcessWire\OpeningHours Object ( [data] => Array ( [hours] => ) ) If I check the sleepValue() method or the sanitizeValue() - they are also empty. So it seems that the values will not reach these methods. I havent found a clear documentation of whats going on behind the saving process of an inputfield.
      As far as I know the saving process starts with the form submission. The values are in the POST array and will be processed by the processInput() method. Before they will be saved in the database they will be sanitized by the sanitizeValue() mehtod and afterwards they will be prepared for storage in the sleepValue() method.  The last step is the storage itself.
      Has someone an idea what is missing by storing values from multiple fields into 1 database column or has someone a working example of such a scenario on github to help me out.
      A clear explanation of the storage process will be also helpful.
      Thanks and best regards
    • By Pip
      Hi Everyone 
      I've been working on Processwire for two months now. Structuring the website as needed. Unsure why but I'm getting this one now. Seems my fields has crashed. 
      I've tried googling some answers but can't seem to find a step by step guide on how to rectify this. Any advise? Practically new on this. 
      TIA. 

    • By Rodd
      Hi everyone!
      I have a website in a production environment and I want to duplicate it in a local environment. I exported the content of the website (with the 'Site Profile Exporter' module) but I cannot use it actually. I've got an issue with the database. I imported this one in MAMP then.

      I also exported the pages (with the 'ProcessPagesExportImport' module), but I cannot import it to my local website because the fields don't exist. So I created this fields, but I have this error :
      How can I use the elements that already exist and are presents in my database? How can I duplicate correctly the templates, fields and pages?
      Thanks by advance
      PS: Sorry if my english is bad
       
    • By DooM
      Hello guys,
      I'm trying to figure out how to sync fields and templates between staging and production environments.
      I've found Migrations module by Lostkobrakai, but with use of it all the fields and templates must be created by API, which is kind of uncomfortable.
      I also tried ProcessDatabaseBackups module which can export only certain tables, but I don't think it's the best practice to do that.
      How do you guys solve this problem? It's very annoying to setup everything three times (dev, staging, production).
      Thanks a lot :)
×
×
  • Create New...