5

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?

d4nielfr4nco
  • 635
  • 1
  • 6
  • 17

1 Answers1

4

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.

Nahuel Varela
  • 1,022
  • 7
  • 17
  • I realize running SQL to export data may be detrimental to the performance of the prod database. I think what I would really need is to leverage the backup of the prod database and then restore it to the staging database. Cloning the whole instance would indeed be an overkill. The problem is when we use different projects for staging and prod, because it seems to be not possible to restore a backup from a cloud sql instance from another project. – d4nielfr4nco Jan 29 '19 at 16:22
  • As the documentation for restoring from a backup says [https://cloud.google.com/sql/docs/postgres/backup-recovery/restore], you can not restore a backup from a different project. And as the documentation for an overview of the backup says [https://cloud.google.com/sql/docs/postgres/backup-recovery/backups], you cant export a backup, you can only export data. – Nahuel Varela Jan 30 '19 at 12:31
  • Yup. That's unfortunate, but thanks for your answer. – d4nielfr4nco Jan 30 '19 at 19:03