Database Backup Restoration

This guide explains how to restore a database backup using CloudSQL's point-in-time recovery feature.

Overview

CloudSQL provides a "Clone" feature that allows you to create a new database instance from a point-in-time backup. This is useful for:

• Restoring data to a previous state
• Creating a test environment from production data
• Investigating issues without affecting the production database

Steps to Restore a Database Backup

1. Navigate to CloudSQL Database

Go to your database instance in the Google Cloud Console - CloudSQL.

2. Choose Clone Option

Click on the "Clone" option in the upper menu of the database instance page.

3. Configure Point-in-Time Recovery

1. Select "Clone from an earlier point in time"
2. Specify the exact date and time you want to restore to
3. Configure the new instance name and settings as needed

4. Monitor Cloning Progress

Wait for the cloning process to complete. You can track the progress by:

• Checking the instance status in the CloudSQL console
• Viewing the "View PostgreSQL error logs" in the cloned PostgreSQL instance

5. Configure Foreign Schema Connection

After the cloning is complete, you need to set up a foreign data wrapper (FDW) to access the cloned database from your target application database (e.g., commercial-api-prod).

5.1. Get the Cloned Database Information

1. Note the Internal IP Address of the cloned database instance
2. Note the database name (e.g., commercial_dev)
3. Get the database password for the postgres user

5.2. Execute SQL Commands

Connect to your target database (e.g., commercial-api-prod) and execute the following SQL commands:

-- Drop existing foreign server if it exists
DROP SERVER commercial_backup CASCADE;

-- Create the postgres_fdw extension if it doesn't exist
CREATE EXTENSION IF NOT EXISTS postgres_fdw;

-- Create the foreign server connection
-- Replace '10.44.1.3' with the Internal IP Address of your cloned database
-- Replace 'commercial_dev' with the actual database name of the cloned instance
CREATE SERVER commercial_backup
FOREIGN DATA WRAPPER postgres_fdw
OPTIONS (dbname 'commercial_dev', host '10.44.1.3', port '5432');

-- Create user mapping for authentication
-- Replace '...' with the actual postgres user password
CREATE USER MAPPING for postgres
SERVER commercial_backup
OPTIONS (user 'postgres', password '...');

-- Create the schema to hold the foreign tables
CREATE SCHEMA IF NOT EXISTS commercial_backup;

-- Import all tables from the cloned database's public schema
IMPORT FOREIGN SCHEMA public
  FROM SERVER commercial_backup
  INTO commercial_backup;

5.3. Verify the Connection

After executing the commands, you should be able to query the cloned database tables through the foreign schema:

-- Example: Query a table from the cloned database
SELECT * FROM commercial_backup.your_table_name LIMIT 10;

Important: Replace the placeholder values in the SQL commands:

• 10.44.1.3 → The Internal IP Address of your cloned database instance
• commercial_dev → The actual database name of the cloned instance
• ... → The actual password for the postgres user

6. Restore Specific Tables

Once the foreign schema is set up, you can restore specific tables from the cloned database to your target database. This is useful when you only need to restore certain tables rather than the entire database.

Execute the following SQL commands in your target database, replacing your_table with the actual table name:

BEGIN;

-- Drop the existing table if it exists (use CASCADE to handle dependencies)
DROP TABLE IF EXISTS public.your_table CASCADE;

-- Create the table by copying data from the cloned database
CREATE TABLE public.your_table AS
SELECT *
FROM commercial_backup.your_table;

COMMIT;

Notes:

• Replace your_table with the actual name of the table you want to restore
• The BEGIN and COMMIT statements ensure the operation is atomic - if anything fails, the transaction will be rolled back
• CASCADE in the DROP TABLE statement will also drop dependent objects (like views, foreign keys, etc.) - use with caution
• You can add a WHERE clause to the SELECT statement if you only want to restore specific rows
• For large tables, consider adding indexes after the restore operation

Example with filtering:

BEGIN;

DROP TABLE IF EXISTS public.users CASCADE;

-- Restore only active users from the backup
CREATE TABLE public.users AS
SELECT *
FROM commercial_backup.users
WHERE status = 'active';

COMMIT;

Important Notes

• The cloned instance is a completely separate database instance
• You will be charged for the cloned instance while it exists
• Remember to delete the cloned instance when you no longer need it to avoid unnecessary costs
• The Internal IP Address is required for connecting from other GCP services (like Cloud Run)
• Point-in-time recovery is only available for databases with backups enabled

Related Documentation

• Deployment Guide - For information about application deployment and database connections
• Data Architecture - For information about database structure and data management