1

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.

Jasper
  • 111
  • 1
  • 2

2 Answers2

4

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.

Murat Karyağdı
  • 41
  • 2
  • Can I call these commands from an app engine instance? I can locate pg_dump on my laptop and call it from within .NET. But how do I locate pg_dump on an app engine instance? Is that even possible? – Jasper Jun 14 '20 at 12:45
  • This answer solves the export / import problems with google cloud postgresql – london_utku Jul 07 '20 at 08:20
0

As you mentioned, the docs say that the backups only work on instances and databases, not on specific schemas. Nothing new here.

I know this might not the best thing to do but due to this restrictions, have you considered an update/change on your design? One database per client so the backups won't become a pain in the back. At least until they release something to backup or export single schemas.

Your bills wouldn't be affected because they charge for storage, not for database or schema. This, however, might take even more time than your expected.

Kevin Quinzel
  • 136
  • 7