Feature request - Restore or clone across projects [74599278]

Assigned

Feature Request

Status Update

No update yet.

Description

sw...@bainbridgehealth.com

created issue #1

Mar 13, 2018 07:27PM

There are times when it is necessary to move a database instance from one project to another. For example, if you are trying to isolate resource from an IAM perspective, or if you are trying to consolidate them from a billing perspective, or if you want to co-locate them with other resources for performance or security resources.

Unfortunately, in order to move a database from one project to another, you only have the following, existing options:

- setup an external read replica, wait for it to catch up, and then promote it to master.
- export data, copy to new project, then import data.

The first option is rather cumbersome, but has the benefit of allowing you to make a fairly clean cutover with limited downtime. The second option will require more downtime, but requires a bunch of extra steps administratively.

Having the option to easily clone your database to a new one in a different project, or the ability to do a recovery from a backup in a different project, would greatly simplify this administrative process and require much less planning.

Comments

kn...@google.com <kn...@google.com> Mar 13, 2018 10:51PM

Assigned to kn...@google.com.

kn...@google.com <kn...@google.com> #2Mar 14, 2018 12:43AM

Reassigned to gc...@google.com.

A feature request is created with the engineering team based on your inquiry, so that they may evaluate it, however there is no ETA or guarantee of implementation. All further updates should occur here.

jh...@6river.com <jh...@6river.com> #3Sep 13, 2018 03:28AM

This would be a very useful feature for almost every enterprise customer.

na...@gmail.com <na...@gmail.com> #4Sep 26, 2018 04:04PM

We also would like this feature. Our non production environment is in a separate project and we have a need for regular "refreshes" of the non-production environment with production data. This would make it much easier

ad...@paramount.com <ad...@paramount.com> #5Oct 3, 2018 01:34AM

Yes this would be a useful feature for replicating data from prod to dev projects.

dm...@gtempaccount.com <dm...@gtempaccount.com> #6Nov 7, 2018 08:21PM

i second.

je...@panerabread.com <je...@panerabread.com> #7Nov 12, 2018 12:42AM

This feature would be very helpful.

As mentioned, the ability to clone a production DB to another non-prod project is something that would be used often.

de...@uk.qbe.com <de...@uk.qbe.com> #8Nov 12, 2018 09:00AM

ra...@farmote.com <ra...@farmote.com> #9Nov 20, 2018 04:24AM

This would be super helpful. Otherwise the work around is expensive and time consuming. Please make this a priority.

ji...@platforma.one <ji...@platforma.one> #10Dec 6, 2018 03:05PM

Found a way, you can do:
gcloud sql export sql db-production gs://someproject-development-bucket/db-production_dump.sql --project someproject-production && \
gcloud sql import sql db-development gs://someproject-development-bucket/db-production_dump.sql --project someproject-development

As long as permissions are set, you can import/export to/from any project to/from any project.

---

Our setup:

project-development:
- Cloud SQL: db-development
- Cloud Storage: bucket-development
- Cloud Build: clone-prod-to-dev

project-production:
- Cloud SQL: db-production

// YAML from Cloud Build trigger:
steps:

# export SQL databases
- name: '

gcr.io/cloud-builders/gcloud'
args: [
'sql', 'export', 'sql',
'db-production',
'gs://someproject-development-bucket/db-production_dump-$BUILD_ID.sql',
'--project', 'someproject-production'
]
waitFor: ['-']
id: 'sql-export'

# import SQL databases
- name: '

gcr.io/cloud-builders/gcloud'
args: [
'sql', 'import', 'sql',
'db-development',
'gs://someproject-development-bucket/db-production_dump-$BUILD_ID.sql',
'--project', 'someproject-development'
]
waitFor: ['sql-export']

ab...@gmail.com <ab...@gmail.com> #11Apr 2, 2019 08:40PM

Any plan to implement it? export/Import is cumbersome esp if the size of cloudsql is huge, in my case its 1.5 TB and i'll be getting the refresh request ( from Prod to DEV/Test env) frequently.

pr...@google.com <pr...@google.com> #12Jul 29, 2019 09:52PM

Also, is there a way to clone replicas without promoting them? This will solve the problem for most case.

c....@gmail.com <c....@gmail.com> #13Sep 7, 2019 01:45PM

looks like cross project restores are possible now:

https://cloud.google.com/sql/docs/postgres/backup-recovery/restoring . Direct Cloning still not possible yet unfortunately.

ad...@paramount.com <ad...@paramount.com> #14Sep 9, 2019 12:26AM

Thanks for the link on the cross-project restores. We have a nightly dump that we load into development runs as a Cronjob and works very similar to what is described above with Cloud Builder. It's still not ideal and would prefer the cross-project cloning.

oc...@gmail.com <oc...@gmail.com> #15Jul 1, 2020 06:31PM

Any plan to implement it? export/Import is cumbersome esp if the size of cloud SQL is huge, in my case its 1.5 TB

[Deleted User] <[Deleted User]> #16Oct 20, 2020 12:22PM

+1, we have the same usecase

aj...@google.com <aj...@google.com> #17Dec 1, 2020 02:47AM

Hi folks - thank you for your feedback on cross-project cloning. This request is under review. As a previous commenter mentioned, the best workaround today to set up a clone of an instance into another project is through using our backup restore feature. Once you've backed up your source instance, you can use our REST API to call restoreBackup and set the project-id and instance-id variables of your target instance in your request. You can read more about this procedure here:

https://cloud.google.com/sql/docs/mysql/backup-recovery/restoring#projectid

Best,
Akhil
Product Manager, Cloud SQL

Message last modified on Dec 1, 2020 02:48AM

ja...@homeprotech.com <ja...@homeprotech.com> #18Jul 15, 2021 01:43AM

Handling this at the platform level rather than manual export and import would be highly beneficial in environments with multiple projects which most implementation that involve production use are likely to have.

ca...@google.com <ca...@google.com> #19Jul 28, 2021 07:35PM

For now a good option is to use the Data Migration Service which can automate most of the manual tasks for the migration.

For PostgreSQL For MySQL

s....@ndr.de <s....@ndr.de> #20Mar 18, 2022 09:32AM

This is in fact possible now! https://cloud.google.com/sql/docs/mysql/backup-recovery/restoring#projectid The documentation is a bit hard to understand. Also, you can't just do a gcloud call. This is how I managed to do it with curl:

payload.json:

{
  "restoreBackupContext":
  {
    "backupRunId": 1647594254833,
    "project": "my-source-project",
    "instanceId": "my-source-database"
  }
}

curl -X POST \
-H "Authorization: Bearer "$(gcloud auth print-access-token) \
-H "Content-Type: application/json; charset=utf-8" \
-d @payload.json \
"https://sqladmin.googleapis.com/v1/projects/my-destination-project/instances/my-destination-database/restoreBackup"

Just adjust the values for backupRunId, project and instanceId in payload.json. As well as the url in the curl call.

b....@gmail.com <b....@gmail.com> #21Aug 2, 2023 02:27PM

Hey folks,
Could you please share any hints if it is possible to set up cross projects replicas for Cloud SQL in GCP?

Thank you!

br...@gmail.com <br...@gmail.com> #22Aug 30, 2023 07:49PM

The only way I have seen is to use Database Migration Service to replicate a DB instance in your source project, to a new db instance in your destination project, and clone that to another db in your destination project, but now you are paying for an extra DB server.

I also need the ability to clone across projects, its preventing me from migrating about 120 databases (all > 200GB) over to cloud SQL instead of postgres running on GCE instances. (we can clone database disks from one project to another)