CODEWITHSUNDEEP
google-cloud-platformgoogle-cloud-sql
d4nielfr4nco
d4nielfr4nco

Reputation: 655

How can I synchronize a Cloud SQL prod database with another Cloud SQL staging database?

I currently have one Cloud SQL instance (PostgreSQL) in my Google Account, but I want to create a staging database so I can perform some tests before making schema changes to the prod Cloud SQL database.

The prod database automatically receives data from different data sources by some simple ETL processes and I don't want to replicate these data loads on the staging database. These automated processes should not run in staging because they are unecessary for testing purposes most of the time. I also don't want to create a staging database as big as the prod.

Considering what I've talked above and that I don't use different projects for setting up staging/prod environments, I have a few questions to make:

  1. I've realized there is a feature of cloning databases in GCP. I've never used it, but I wonder if it is a good practice to clone my prod database from time to time to create my staging database, since doing backups and restores can be a pain.

  2. Can I use the cloning feature in a logical fashion (select only some tables to clone, etc.)? Or does it rely entirely on physical backups?

  3. I imagine cloning a huge database might take a few hours, so I'm wondering if there is a way to apply only the diff between the two databases, so I don't have to clone the whole database every time, similar to a replication approach?

  4. If I use separate projects for dev/test/prod. What are the available approaches for synchronizing the different database environments?

Upvotes: 5

Views: 4840

Answers (1)

Nahuel Varela
Nahuel Varela

Reputation: 1040

1- The cloning feature is for cloning instances. So you clone the entire database.

2- You can't clone just some tables, but you could export this desired tables using:

gcloud sql export sql [INSTANCE_NAME] gs://[BUCKET_NAME]/sqldumpfile.gz --database=[DATABASE_NAME] --table=[TABLE_NAME]

3- Right now, you can do this using SQL, then save the query as a table and export this table.

4- There various ways to connect to a SQL instance it like a psql client, third-party tools that use the standard PostgreSQL client-server protocol, Cloud App Engine, etc.

Upvotes: 4

Related Questions