Cloud SQL への接続とポイントインタイムリカバリの検証

Cloud SQLは，豊富な拡張機能コレクション、構成フラグ、デベロッパーエコシステムを備えた、MySQL、PostgreSQL、SQL Server 向けのフルマネージドリレーショナルデータベースサービスです。(https://cloud.google.com/sql?hl=ja)

Cloud SQL で PostgreSQL を用いて機能を試してみました．

Cloud SQL インスタンスを作成する
ローカル環境からCloud SQL インスタンスに接続する
ポイントインタイムリカバリを試してみる
- requirements.txt
- main.py
注釈

Cloud SQL インスタンスを作成する

まずは Terraform を使用して，GCP に Cloud SQL インスタンスを作成します．

main.tf

terraform {
  backend "local" {}
}

provider "google" {
  project = var.project_id
  region  = "asia-northeast1"
  zone    = "asia-northeast1-a"
}

variable "project_id" {
  type    = string
}

api.tf

必要な API を有効にします．

resource "google_project_service" "cloudresourcemanager" {
  project                    = var.project_id
  service                    = "cloudresourcemanager.googleapis.com"
  disable_dependent_services = true
}
resource "google_project_service" "compute" {
  project                    = var.project_id
  service                    = "compute.googleapis.com"
  disable_dependent_services = true
}
resource "google_project_service" "iam" {
  project                    = var.project_id
  service                    = "iam.googleapis.com"
  disable_dependent_services = true
}
resource "google_project_service" "servicenetworking" {
  project                    = var.project_id
  service                    = "servicenetworking.googleapis.com"
  disable_dependent_services = true
}
resource "google_project_service" "sqladmin" {
  project                    = var.project_id
  service                    = "sqladmin.googleapis.com"
  disable_dependent_services = true
}

https://registry.terraform.io/providers/hashicorp/google/latest/docs/resources/google_project_service

database.tf

簡単に Public IP でアクセスできるように，ipv4_enabled を true に設定します．インターネットを経由するアクセスなので，require_ssl も true に設定します．

resource "google_sql_database_instance" "main" {
  name                = "main"
  region              = "asia-northeast1"
  database_version    = "POSTGRES_14"
  deletion_protection = true
  settings {
    tier              = "db-f1-micro"
    availability_type = "ZONAL"
    disk_autoresize   = true
    disk_size         = 10
    disk_type         = "PD_SSD"
    ip_configuration {
      ipv4_enabled = true
      require_ssl  = true
    }
    backup_configuration {
      enabled                        = true
      point_in_time_recovery_enabled = true
    }
  }
}

resource "random_password" "main" {
  length  = 16
  special = true
}

resource "google_sql_user" "main" {
  name     = "main"
  instance = google_sql_database_instance.main.name
  password = random_password.main.result
}

resource "google_sql_database" "main" {
  name     = "main"
  instance = google_sql_database_instance.main.name
}

serviceaccount.tf

データベースを操作するために，roles/cloudsql.client を有するサービスアカウントを作成します．

resource "google_service_account" "dbmain" {
  project      = var.project_id
  account_id   = "dbmain"
  display_name = "Main Service Account"
}

resource "google_project_iam_member" "main" {
  project = var.project_id
  role    = "roles/cloudsql.client"
  member  = "serviceAccount:${google_service_account.dbmain.email}"
}

output.tf

後で必要となる情報を出力します．

output "db_connection_name" {
  value = google_sql_database_instance.main.connection_name
}

output "db_password" {
  value     = random_password.main.result
  sensitive = true
}

GCP に新規のプロジェクトを作成して，terraform apply を実行します．作成した Cloud SQL インスタンスは以下のようになりました．

ローカル環境からCloud SQL インスタンスに接続する

Cloud SQL インスタンスに割り当てた Public IP を利用して，Cloud SQL Auth proxy 経由で接続します．おそらくこの方法が最も簡単です．

$ wget https://dl.google.com/cloudsql/cloud_sql_proxy.linux.amd64 -O cloud_sql_proxy
$ chmod +x cloud_sql_proxy
$ ./cloud_sql_proxy \
  -instances=[db_connection_name に置き換える]=tcp:0.0.0.0:5432 \
  -credential_file=[google_service_account.dbmain の認証情報に置き換える]

認証など問題なく設定ができていれば以下のように成功します．

2022/12/13 07:23:56 Listening on 0.0.0.0:5432 for xxxxxxx:asia-northeast1:main
2022/12/13 07:23:56 Ready for new connections
2022/12/13 07:23:56 Generated RSA key in 163.476961ms

試しに，DBeaver を使ってデータベースにアクセスしてみます．

接続できました．

ポイントインタイムリカバリを試してみる

ポイントインタイムリカバリにより、インスタンスを特定のポイントインタイムに復旧できます。たとえば、エラーによってデータが失われた場合、エラーが発生する前の状態にデータベースを復旧できます。ポイントインタイムリカバリは、常に新しいインスタンスを作成します。既存のインスタンスには、ポイントインタイムを実行できません。新しいインスタンスは、クローン作成と同様に、ソースインスタンスの設定を継承します。(https://cloud.google.com/sql/docs/postgres/backup-recovery/restore?hl=ja#tips-pitr)

つまり，データベースのログを利用して任意の時刻の状態にデータベースを復元する機能です．

この機能を検証するために，maintable table に，1秒ごとに現在時刻を timestamp 型で書き込む python スクリプト作成し，これを実行中にポイントインタイムリカバリを開始し，複製されたデータベースの中身を確認してみました．

requirements.txt

psycopg2
python-dotenv

main.py

dev.env ファイルにデータベースの接続設定を別途入力します．

import os
import time

import psycopg2
from dotenv import load_dotenv

load_dotenv("dev.env")


def get_connection():
    dsn = "postgresql://{}:{}@{}:{}/{}".format(
        os.getenv("DB_USER"),
        os.getenv("DB_PASSWORD"),
        os.getenv("DB_HOST"),
        os.getenv("DB_PORT"),
        os.getenv("DB_NAME"),
    )
    return psycopg2.connect(dsn)


def run(conn: psycopg2.extensions.connection):
    with conn.cursor() as cur:
        cur.execute("drop table if exists maintable;")
        cur.execute("create table maintable (dt timestamp);")
        conn.commit()
        while True:
            cur.execute("insert into maintable (dt) values (current_timestamp)")
            conn.commit()
            time.sleep(1)


def main():
    with get_connection() as conn:
        run(conn)


if __name__ == "__main__":
    main()

これを実行すると，1秒ごとにデータが書き込まれ，以下のような結果が得られました．

select (dt) from maintable;

dt                     |
-----------------------+
2022-12-13 08:38:26.293|
...
2022-12-13 08:39:56.891|
2022-12-13 08:39:57.921|
2022-12-13 08:39:58.950|
2022-12-13 08:39:59.980|
2022-12-13 08:40:01.010|
2022-12-13 08:40:02.039|
2022-12-13 08:40:03.070|
2022-12-13 08:40:04.100|
...
2022-12-13 08:45:13.219|
2022-12-13 08:45:14.250|
2022-12-13 08:45:15.280|
2022-12-13 08:45:16.309|
2022-12-13 08:45:17.339|

なお，Cloud SQL のタイムゾーンはデフォルトで UTC に設定されているようです．JST と UTC を区別するため，以下で UTC の場合は別途記載します．

ポイントインタイムリカバリを開始するには，Cloud SQL のインスタンスの "Clone" ボタンから開始し，復元する時刻を指定します．

データ書き込みは 2022-12-13 08:38 (UTC) から 08:45 (UTC) まで実施し，08:40 (UTC) の状態に復元する操作を 08:43 (UTC) に開始しました．

"main-clone" の作成が開始されました．

別のインスタンスとして復元されました．"connection name" と Global IP Address が異なりますので，Cloud SQL Auth proxy の接続情報を変更します．

データベースの中身を確認したところ，以下のような状態でした．

select (dt) from maintable;

dt                     |
-----------------------+
2022-12-13 08:38:26.293|
...
2022-12-13 08:39:53.802|
2022-12-13 08:39:54.832|
2022-12-13 08:39:55.861|
2022-12-13 08:39:56.891|
2022-12-13 08:39:57.921|
2022-12-13 08:39:58.950|
2022-12-13 08:39:59.980|

08:40 (UTC) の状態に復元できたことが分かりました．

注釈

この記事の検証は，Linux で実行する Docker Engine 内の devcontainer を使用し，MacOS から操作していますので，一見すると，CPUのアーキテクチャなどの環境に一貫性がないように見えます．

みーのぺーじ

みーが趣味でやっているPCやソフトウェアについて．Python, Javascript, Processing, Unityなど．

Cloud SQL への接続とポイントインタイムリカバリの検証

Cloud SQL インスタンスを作成する

main.tf

api.tf

database.tf

serviceaccount.tf

output.tf

ローカル環境からCloud SQL インスタンスに接続する

ポイントインタイムリカバリを試してみる

requirements.txt

main.py

注釈

Cloud SQL インスタンスを作成する

main.tf

api.tf

database.tf

serviceaccount.tf

output.tf

ローカル環境からCloud SQL インスタンスに接続する

ポイントインタイム リカバリ を試してみる

requirements.txt

main.py

注釈

ポイントインタイムリカバリを試してみる