AUTOMATING Export and Import Of tables For CLOUD SQL postgresql database

[Harrison Akum]

Hi guys,

I hope everyone staying home and save during these difficult times. I am NEW to clould sql. I recently migrated our ON-PREM postgres databases in to Google CLOUD SQL (postgres)

Every week I have to MANUALLY export 15 tables From one postgres cloud sql instance to another. First within the Same project and then across to 6 other Projects /Environments.

This takes too long and I do not know how I can automate this.

I use pgAdmin or CLOUD Shell command line to perform these exports and imports.

STEPS

           - create the exports (DATA ONLY) of all 15 tables in SOURCE

           - Connect to psql client to truncate these 15 table at the TARGET

           - Import tables (data only) in to TARGET 

I am used to using CRONTAB and bash/SQL scripting to achieve this task on my ON-PREM databases.

HOW DO I script and automate this task in Google Cloud???

Thanks

[Aref Amiri (Cloud Platform Support)]

Hi,

As you are aware, there is no specific feature that can automate the process, however, you may want to consider using a combination of Google Cloud Platform services to automate such tasks. 

As a good example here, a user was able to use the combination of Cloud Scheduler, Pub/Sub, Cloud Functions, and SQL Admin API to automate a periodic on-demand Cloud SQL backup.

I would also suggest to post your question on Stackoverflow, and ask the community to see if they have used any alternatives that has helped them in automating the import/export process.

[Harrison Akum]

Hi Aref,

Thank you so much for this reply. I apologize for the late feedback. Ended up creating a VM in Compute Engine, creating a network route to my On Prem Database and then another to the CloudSQL postgres instance and storage bucket in GCP. Used a paasword file on The VM to store Hostname, Username, and passwords. Used crontab to automate the bash script I created on the VM.

Backups up tables on-prem ------ transfer file from on prem to GCO storage bucket ------ connects to Cloud SQL database, truncates tables ------ import data from stored file in bucket. Works so well.

[Aref Amiri (Cloud Platform Support)]

I'm glad you were able to work it out and thanks for sharing your solution as this can be useful for other users who are looking to accomplish such tasks.