Postgresql – How to call pg_dump from Google Cloud SQL

backupexportgoogle-cloud-platformgoogle-cloud-sqlpostgresql

I am looking for a solution to backup my Cloud SQL schemas (and Cloud Storage files).

At first I tried using Cloud Scheduler to call the googleapis (https://cloud.google.com/sql/docs/postgres/import-export/exporting) in order to make an export of the database. The problem is that I need specific backup arguments, and I need to export specific schemas in the PostgreSQL database. Each of my clients has a schema, and i would like to be able to restore a schema without restoring the entire database. Googleapis don't seem to support exporting specific schemas (yet), which is why I would like to call pg_dump directly.

The same problem is with using automatic backups in Cloud SQL. I cannot restore a specific schema in a database, and I need to keep the backups around for atleast a month.

However, I have yet to find a way to call pg_dump using e.g. Cloud Functions. So currently I am thinking about using my own Windows 10 laptop to call pg_dump and to setup a daily task in Windows. I would much rather use Google Cloud, instead of my own laptop.

If anybody has any ideas of how to I should build my backup infrastructure, it would be greatly appreciated.

Best Answer

Connect your cloudsql instance with proxy > https://cloud.google.com/sql/docs/postgres/sql-proxy

then this commands should do the job

pg_dump -U postgres -h localhost -p [PORT] [DBNAME] -n public --format=p  >publicBackup.sql

psql -U postgres -h localhost -p [PORT] [DBNAME]  -c "drop schema public cascade;" 

psql -U postgres -h localhost -p [PORT] [DBNAME] -n public -1 -f  ./publicBackup.sql

You can do this job from any container which have a proxy connection to your database.

Related Topic