hello foo bar Saba note | GCP Cloud SQLの使い方 (PostgreSQL)

GCP Cloud SQLの使い方 (PostgreSQL)

2019.11.6 (水)

GCPのCloud SQLの使い方です。初心者から上級者までいろいろ使えるCloud SQLですが、なかなかいろいろ覚えることが多くて難しいです。ここではある程度理解している中級者以上を対象に解説しています。ここではPostgreSQLの使い方を説明しています。
公式ドキュメント:
Cloud SQL
Cloud SQL for PostgreSQL のドキュメント

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

Cloud SQLのインスタンスの作成をします。

  • インスタンス ID: (小文字、数字、ハイフンのみ使用できます。先頭には小文字を使用してください。インスタンスを削除した際も一週間ぐらい予約されたままになるっぽいので注意。)
  • デフォルトのユーザー パスワード: xxxxxxx (適当) ‘postgres’ ユーザーのパスワードを設定します。このユーザーのログインにはパスワードが必要です。
  • ロケーション
    • リージョン: (利用するVPCのリージョン) できるだけAPPインスタンスの場所に近いところ。
    • ゾーン: (利用するVPCのゾーン) できるだけAPPインスタンスの場所に近いところ。
  • データベースのバージョン: PostgreSQL 11

データベースを作成する

インスタンスを立ち上げるとデフォルトでpostgresというデータベースが自動的に作成されます。これを使うのでも全然OKですが、新たに作成したいときは、別途、「データベース」から作成します。日本語アカウントの場合は自動的に

文字セット: UTF8
照合: en_US.UTF8  

がセットされます。

ユーザーアカウントの設定

ユーザーもデフォルトでpostgresがすでに作成されていますが、ユーザーも任意で追加できます。パスワードも同時に設定してください。

データベースの作成

データベースを作成します。デフォルトではpostgresというデータベースがすでに存在します。任意で追加ができます。

Cloud storageからインポートする

Cloud storageからダンプデータとリストアできるんだけど、選択したCloud storageがグレーアウトしていて選択できないときがあります。「読み取りアクセス権」がないと選択できない状態になっています。

データのインポート元となるファイルを選択してください
ファイルを参照するか、ファイルのパス(バケット/フォルダ/ファイル)を入力します。まず、読み取りアクセス権があることを確認してください。 詳細

Cloud SQL にデータをインポートする

インポートを行うと以下のようなエラーになることがあります。

2019-11-06 19:37:13.706 JST
[81011]: [1-1] db=postgres,user=cloudsqlsuperuser ERROR: must be member of role "hoge"

インポートするデータ内に存在するhogeユーザーをスキーマ作成できるroleのメンバーにしないとダメだと言っています。これはロールpostgresqlがスーパーユーザーでないために発生するエラー。
Postgresql独自の制約であり、PostgreSQLドキュメントにも記載されているらしい。Cloud SQLではどこでこれを変更する作業をすればいいのかというと、いろいろ面倒です。

PostgreSQL ユーザーというCloud SQLに特有の特殊な者がいて、彼がすべてのスーパーユーザー権限をもっているようです。

このページでは、Cloud SQL による PostgreSQL ユーザーと役割の処理方法について説明します。PostgreSQL 役割を使用すると、どのような種類のアクセス権と機能をユーザーが持ち、PostgreSQL インスタンスにアクセスするのかを制御できます。

細かい操作をする

通常コマンドラインで設定するような詳細な設定をCloud SQLではどこでやるのかというと、コンソール上のCloud Shellを使います。Google Cloud Platform Consoleで、右上にある Cloud Shell アイコン([>_])をクリックします。または、SQLのインスタンスの詳細画面から「Cloud Shellを使用して接続」をクリックします。すると画面下部にコマンド入力のコンソール画面が現れます。


以下のgcloudコマンドで接続します。

$ gcloud sql connect [DB_INSTANCE_NAME] --user=postgres --quiet
(接続にクソみたいに時間がかかる。5分はかからない。)
Whitelisting your IP for incoming connection for 5 minutes...done.
Connecting to database with SQL user [postgres].Password for user postgres: (パスワード入力)
postgres=>

パスワードを入力すると、postgresのプロンプトが出てきます。これでいつものように操作が可能になります。
(改行が取れてしまったり、タイムアウトになったり、なんか不安定な感じ。あとMacからバックスラッシュが打てなくて困った。コピペするしかない。)

cloudsqladminとcloudsqlsuperuser

Error関連

PostgreSQL: 非特権ユーザーによる pg_dump と pg_restore

set_config ------------ (1 row) SET SET SET CREATE SCHEMA ALTER SCHEMA CREATE EXTENSION ERROR: must be owner of extension plpgsql Import error: exit status 3
db=voyager,user=voyager ERROR: must be owner of extension plpgsql

パブリックスキーマとplpgsqlを変更する権限を持っていないことにあるのかな。通常のコマンドラインだと--schema=publicを付与すればリストアできるっぽいのだけど、Cloud SQLだとどういう段取りになるのかわかりづらい。

ERROR:  must be member of role "voyager"