Backup for Cloud SQL for PostgreSQL
→ 日本語版を読むOverview
There are several ways to back up Cloud SQL for PostgreSQL. I wasn't sure what was actually covered by each backup method, so I compared them.
Terminology
- Database flags
In Cloud SQL for PostgreSQL, configuration files like postgresql.conf are not directly accessible. Instead, there are "database flags" that allow adjusting PostgreSQL parameters.
Comparison Results
There are four backup patterns:
- Create an instance clone (backs up the entire instance)
- Create an on-demand backup (backs up all databases)
- Export using SQL dump files (backs up per database)
- Export using CSV files (backs up per table)
Pattern 1 has the widest backup scope; pattern 4 has the narrowest. Cloning an instance backs up even database flags (equivalent to configuration files). In contrast, exporting with CSV files backs up only table data.
1. Create an Instance Clone
Clone instances | Cloud SQL for PostgreSQL | Google Cloud
Create a clone of the instance to back up the entire instance.
The new instance is assigned a new IP address, but its configuration (database flags, connection options, machine type, storage and memory settings) is the same as the original instance.
| Target | Backed Up |
|---|---|
| Database flags | ✓ |
| Users/Roles | ✓ |
| Databases | ✓ |
| Object permissions | ✓ |
| Table definitions | ✓ |
| Tables | ✓ |
2. Create an On-Demand Backup
Create and manage on-demand and automatic backups | Cloud SQL for PostgreSQL | Google Cloud
Use the "on-demand backup" feature to back up all databases. Database flags are not backed up.
| Target | Backed Up |
|---|---|
| Database flags | ✗ |
| Users/Roles | ✓ |
| Databases | ✓ |
| Object permissions | ✓ |
| Table definitions | ✓ |
| Tables | ✓ |
3. Export Using SQL Dump Files
Export and import using SQL dump files | Cloud SQL for PostgreSQL | Google Cloud
Use the "export using SQL dump files" feature to back up per database. Output is in the form of SQL queries.
| Target | Backed Up |
|---|---|
| Database flags | ✗ |
| Users/Roles | ✗ |
| Databases | ✗ |
| Object permissions | ✓ |
| Table definitions | ✓ |
| Tables | ✓ |
4. Export Using CSV Files
Export and import using CSV files | Cloud SQL for PostgreSQL | Google Cloud
Use the "export using CSV files" feature to back up table data. Records to export can be specified with SQL. Output is in CSV format.
| Target | Backed Up |
|---|---|
| Database flags | ✗ |
| Users/Roles | ✗ |
| Permissions | ✗ |
| Databases | ✗ |
| Object permissions | ✗ |
| Table definitions | ✗ |
| Table data | ✓ |