Support remote-to-remote aliases on sql-sync/rsync

[mikeryan776]

#3046 (and at least one other issue I've lost track of) references the removed functionality in Drush 9 of being able to sync directly between two remote aliases, and the desire to restore it at some point. I can't find an open issue for implementing this, so (unless someone can point me to one) let this stand as that feature request.

Context: the workflow described by geerlingguy for syncing between a remote production server and local DrupalVM instance.

[weitzman]

Try the workaround mentioned in that issue drush @synctest.remote2 rsync @synctest.remote1 @self. That, run the command at a remote so that your sql:sync is now using 1 remote and a @self.

[joelpittet]

There are are no keys in the car... I mean VM. The work around seems to not work because from within the VM there are no SSH keys to the remote servers. This used to work in 8.x (still does).

I can explicitly work around the rsync because the files are mounted, but not sure how to work around the sql-sync?

rsync workaround:

drush rsync @self.prod:%files ./web/sites/default/files

[iamsophiesk]

Up until very recently we've been able to sync databases between two environments on Acquia using the following command:

$ drush sql:sync @site.prod @site.test --structure-tables-key=common 

This isn't working anymore with the error:

[notice] Starting to dump database on source.
[success] Database dump saved to /home/site/drush-backups/site_backup_20181019_072149.sql.gz
[notice] Starting to discover temporary files directory on target.
[error]  Cannot specify two remote aliases. Instead, use one of the following alternate options:

   `drush  rsync @self `
   `drush  rsync @self [email protected]:/location/site_backup_20181019_072149.sql.gz

Use the second form if the site alias definitions are not available at . 
[notice] Copying dump file from source to target.
[error]  core-rsync failed. 

This has only become an issue within the last month or so and may be related to Acquia updating Drush (??) but it's making it difficult to copy databases around. I'd like to avoid copying certain tables (CRM data) so that we can have different test data per environment.

I'm also not running rsync so I don't think I can use the suggestions provided but I'm a little stuck as to where to go from here.

[greg-1-anderson]

@Tawreh Drush sql:sync uses the rsync command internally. It might be clearer if we did a redundant check for remote-to-remote in sql:sync and gave a more specific error message. Also, it looks like the workaround provided in the help output is not rendered correctly, making the advice even more obscure.

Try drush @site.prod sql:sync @self @site.test

This won't work unless @site.prod has ssh keys and alias definitions for @site.test.

[iamsophiesk]

Thanks @greg-1-anderson, appreciate the speedy reply.

In that case, sounds like there's some work to be done adding a more specific error message. Do you have a contribution/development guide for the project? I'd be happy to try and help out (when I get some time) but I've never really delved into Drush code before :)

I'll give that a go later. It's Drush 9 so the aliases are contained in a drush/sites/site.site.yml file, but it's Acquia, so anything could happen.

[greg-1-anderson]

Thanks for offering to help out. See our contributing guide for pointers. If you need help with anything, we're usually available in the #drush slack channel.

[mirsoftacquia]

I had to dig in deeper into this problem, because I faced the similar issue as well - I even wasn't able to sql:sync between remote and local multisite with the latest drush. I found out there are two different problems I faced:

1. Local drush alias is not always handled properly as local when posting to rsync command. This works only if local alias is @self. But on Drupal multisite projects, you typically have multiple local sites aliased like @site1.local , @site2.local etc. and every alias points to different site. Currently, drush evaluates if the alias is local according its "host" setting - if host is "localhost" or "127.0.0.1", it's evaluated as local and sends information to rsync in form like 127.0.0.1:/path/to/file. However, rsync evaluates this information then as a remote host, because it has a host prefix - and thus it fails to proceed further throwing an error it doesn't support transfer between two remotes. For this, I submitted a drush fix in this PR: Generate full path for rsync only if alias is remote. #3760 . It posts the path to rsync without the host part if the record is local. I tested this on drupal multisite (inside of drupal vm) and also with Acquia Cloud and with Acquia BLT. The BLT commands like blt sync, blt sync --site=<site> and blt sync:all (to sync all multisites at once) that are intended to quickly transfer between the cloud sites and local (and use internally drush sql:sync) worked perfectly and only after that patch as well - assuming the local alias has host properly defined as localhost or 127.0.0.1, of course.

2. Sync itself between two remotes. This will be harder, but after reliably working the sync between remotes and locals, this opens the way to support remote rsync, in my opinion, at least with tunelling between local environment (data transferred @site1.prod -> local -> @site2.prod). Still not 100% ideal as it doubles transferring time, but it would at least bring some possibility to get around this.

[mirsoftacquia]

Update to point 1: Actually, it was my mistake - removal of host and user entries from local alias solved the problem, with remote->local sync.
That means, just point 2 remains.