gkz cloud sql_Google Cloud SQL提示技巧

gkz cloud sql

创建新的Cloud SQL实例时… (When creating a new Cloud SQL instance…)

Ensure that the instance name is picked wisely. You may find it helpful if it remains short, and contains the Cloud SQL edition (MySQL, PostgreSQL, SQL Server) and version number. For example, pg12.

确保明智地选择实例名称。 如果它仍然很短，并且包含Cloud SQL版本(MySQL，PostgreSQL，SQL Server)和版本号，您可能会发现它很有用。 例如pg12 。

https://console.cloud.google.com/sql/ https://console.cloud.google.com/sql/

Down the road, when you’ll be upgrading it to the newer version, you would create a separate Cloud SQL instance (e.g. pg14) backup data from the previous database instance and restore it in the new one. Note that renaming a Cloud SQL instance (ID) is not possible, as well as changing its region. When you delete a Cloud SQL instance, you will not be able to create a new instance with the same name, at least not within the next couple of months.

在将来，当您将其升级到较新的版本时，您将从前一个数据库实例创建一个单独的Cloud SQL实例(例如pg14 )备份数据，并将其还原到新的数据库实例中。 请注意，无法重命名Cloud SQL实例(ID)以及更改其区域。 删除Cloud SQL实例时，至少在接下来的几个月内，您将无法创建具有相同名称的新实例。

Most likely you will be using a separate GCP project for each deployment environment ( prod, stading, test, dev). In which case, adding the environment (slug) into the instance name is not necessary.

对于每个部署环境( prod ， stading ， test ， dev )，您很可能将使用单独的GCP项目。 在这种情况下，无需将环境(段)添加到实例名称中。

Enable access from public IP addresses solely for development and administration convenience. Whitelist IP addresses (or, IP ranges) from which it would be allowed to access the database. You would also need to restrict access to the database without a valid SSL certificate.

仅出于开发和管理方便性，才允许从公共IP地址进行访问。 将允许其访问数据库的IP地址(或IP范围)列入白名单。 您还需要在没有有效SSL证书的情况下限制对数据库的访问。

If unsure, pick “micro” database instance size (shared vCPU, 0.6 GB RAM, $8/month), it should handle a small web application payload just fine unless something is misconfigured.

如果不确定，选择“微型”数据库实例大小(共享的vCPU，0.6 GB RAM，每月8美元)，它应该可以处理少量的Web应用程序有效负载，除非配置错误。

Once the database instance (VM) is created, go to its settings > Connections, disable unsecured connections, and download client and server SSL certificates to your web application project.

创建数据库实例(VM)后，请转到其设置>连接，禁用不安全的连接，并将客户端和服务器SSL证书下载到您的Web应用程序项目。

创建数据库时… (When creating a database…)

During development, it’s totally fine to use a single instance of Cloud SQL, just create a separate database per deployment environment. Adding environment name (slug) to database names would help to ensure that whenever you administer it, it would be clear to which environment the opened database belongs, right in your face 😄

在开发过程中，使用单个Cloud SQL实例完全可以，只需为每个部署环境创建一个单独的数据库即可。 在数据库名称中添加环境名称(段)将有助于确保无论何时进行管理，都可以清楚地看到打开的数据库属于哪个环境，😄

• example — production

  example —生产

• example_dev — development; shared development environment

  example_dev —开发； 共享开发环境

• example_test — test / QA

  example_test —测试/质量检查

• example_test_123 — review app; automatically created per PR

  example_test_123评论应用； 根据PR自动创建

• example_local — local instance of PostgreSQL database

  example_local — PostgreSQL数据库的本地实例

Right before releasing your app to the public, you would just copy the production database to a separate Cloud SQL instance.

在将您的应用发布给公众之前，您只需将生产数据库复制到单独的Cloud SQL实例。

When creating these databases, ensure that the correct charset and collation are used.

创建这些数据库时，请确保使用正确的charset和collation 。

从Node.js连接到Cloud SQL实例 (Connecting to a Cloud SQL instance from Node.js)

You may find it convenient to place database connection settings into .env files, this way you will be able to re-use them for different purposes — launching the app, migrating database, configuring CI/CD workflows, etc.

您可能会发现将数据库连接设置放入.env文件很方便，这样您就可以将它们重新用于不同的目的-启动应用程序，迁移数据库，配置CI / CD工作流等。

In the case of PostgreSQL, the environment variables would look like this:

对于PostgreSQL，环境变量如下所示：

# PostgreSQL
# https://www.postgresql.org/docs/current/static/libpq-envars.html
PGHOST=104.197.10.130
PGPORT=5432
PGUSER=postgres
PGPASSWORD=~E7Rf][k*P/$9.%F
PGDATABASE=example_dev
PGSSLMODE=verify-ca
PGSSLCERT=./ssl/client-cert.dev.pem
PGSSLKEY=./ssl/client-key.dev.pem
PGSSLROOTCERT=./ssl/server-ca.dev.pem
PGSERVERNAME=example-dev:pg12

These settings will be used for local development, and during deployment, you would inject slightly different settings:

这些设置将用于本地开发，并且在部署期间，您将注入略有不同的设置：

PGHOST=/cloudsql/example-dev:us-central1:pg12
PGUSER=postgres
PGPASSWORD=~E7Rf][k*P/$9.%F
PGDATABASE=example_dev

It would ensure that the app efficiently connects to the Cloud SQL instance through a Unix socket available to Cloud Functions, Cloud Run, and App Engine apps.

它将确保该应用程序通过可用于Cloud Functions，Cloud Run和App Engine应用程序的Unix套接字有效地连接到Cloud SQL实例。

Once these environment variables are loaded into the app (see dotenv , envalid), initializing a new database client can be done without explicitly passing any connection settings other than SSL and connection pool size.

一旦将这些环境变量加载到应用程序中(请参阅dotenv ， envalid )，就可以初始化新的数据库客户端，而无需显式传递除SSL和连接池大小以外的任何连接设置。

Note that the servername option needs to match the server name listed on the SSL certificate. Otherwise, the connection would fail with the following error:

请注意， servername选项需要与SSL证书上列出的服务器名称匹配。 否则，连接将失败，并显示以下错误：

Error [ERR_TLS_CERT_ALTNAME_INVALID]: Hostname/IP does not match certificate's altnames: Host: localhost. is not cert's CN: example-dev:pg12

Do not disable certificate verification by setting rejectUnauthorized: false.

不要通过设置rejectUnauthorized: false禁用证书验证。

在环境之间切换… (Switching between environments…)

Ensure that you can easily specify the target database when launching the app locally as well as running any db administration scripts. For example:

确保在本地启动应用程序以及运行任何数据库管理脚本时，可以轻松指定目标数据库。 例如：

$ yarn start                   # Connects to `example_dev` (default)
$ yarn start --env=local       # Connects to the local database
$ yarn start --env=test        # Connects to the test (QA) database
$ yarn db:migrate --env=local  # Migrates local database
$ yarn db:migrate --env=test   # Migrates test (QA) database

正在存储数据库密码… (Storing db passwords…)

While it’s totally fine to store the (shared) dev database password in the code repository, storing production secrets must be avoided. Below is a couple of lightweight approaches that you can use for that purpose.

将(共享的)dev数据库密码存储在代码存储库中完全可以，但是必须避免存储生产秘密。 以下是可以用于此目的的两种轻量级方法。

(1) Add .env.*.override files to .gitignore and move PGPASSWORD env variables to the env files that are not being committed to the code repo.

(1)将.env.*.override文件添加到.gitignore并将PGPASSWORD env变量移至未提交给代码存储库的env文件。

.env.local           # Settings for local database
.env.dev             # Settings for dev (shared) database
.env.test            # Settings for test (QA) database
.env.prod            # Settings for production database
.env.prod.override   # Overrides for prod environment

Which would be loaded by using dotenv like this:

可以通过使用dotenv这样加载：

import dotenv from "dotenv";
import minimist from "minimist";const { env } = minimist(process.argv.slice(2), {
  default: { env: "dev" }
});dotenv.config({ path: `.env.${env}.override` });
dotenv.config({ path: `.env.${env}` });

(2) Alternatively, store db passwords and other secrets encrypted and decrypt them at runtime by either using Google Cloud KMS or a master passphrase from .env.{env}.secret file that would be excluded from the repository via .gitignore. Do not encrypt passwords for the “dev” environment.

(2)另外，也可以存储数据库密码和其他机密，并使用Google Cloud KMS或.env.{env}.secret文件中的主密码在运行时解密，并通过.gitignore从存储库中排除这些密码。 不要为“开发”环境加密密码。

Optionally, add a script for reading/writing secrets from/to .env files:

(可选)添加一个脚本，用于从.env文件读取/写入机密：

yarn env:set {name} {secret}     # Encrypt and save to .env.{env}
yarn env:get {name}              # Read from .env.{env} and decrypt

This can be easily implemented by using the nativecrypto Node.js module.

这可以通过使用本机crypto Node.js模块轻松实现。

使用Cloud SQL代理… (Using Cloud SQL Proxy…)

It’s also possible to use Google Cloud SQL proxy when connecting to a Cloud SQL server from the local machine (or, CI/CD server). This way, you will not need to worry about tweaking SSL and Cloud SQL firewall settings. But, I personally find it less convenient.

从本地计算机(或CI / CD服务器)连接到Cloud SQL服务器时，也可以使用Google Cloud SQL代理。 这样，您无需担心调整SSL和Cloud SQL防火墙设置。 但是，我个人觉得不太方便。

使用PostgreSQL CLI工具… (Using PostgreSQL CLI tools…)

If you’re planning to use a hosted version of SQL database most of the time, it’s possible to install just the CLI tools without installing the whole PostgreSQL server on your local machine. On macOS, psql, pg_dump, pg_restore, and other CLI tools can be installed using Homebrew:

如果您打算大部分时间使用SQL数据库的托管版本，则可以仅安装CLI工具，而无需在本地计算机上安装整个PostgreSQL服务器。 在macOS上，可以使用Homebrew安装psql ， pg_dump ， pg_restore和其他CLI工具：

$ brew install libpq

正在创建备份... (Creating backups…)

By default, Google Cloud SQL will write backups to a Google Storage bucket daily, you would just need to configure the backup window when these operations will be taking place.

默认情况下，Google Cloud SQL每天都会将备份写入Google存储桶，您只需在进行这些操作时配置备份窗口即可。

In addition to that, you need to ensure that a fresh backup of the production database is created as part of the CI/CD deployment workflow.

除此之外，您还需要确保在CI / CD部署工作流中创建了生产数据库的全新备份。

The example source code is available on GitHub, see Node.js API Starter Kit.BTW, if you have a solid startup idea and would like to join our efforts in bringing it to life, please don’t hesitate to get in touch on Discord or Telegram, I’ll be happy to discuss it an see how we could collaborate.

示例源代码可在GitHub上找到，请参阅Node.js API Starter Kit .BTW，如果您有扎实的启动想法，并希望与我们一起努力将其付诸实践，请随时与Discord联系。或Telegram ，我很乐意讨论它，看看我们如何合作。

See you around!

再见！

翻译自: https://medium.com/@koistya/google-cloud-sql-tips-tricks-d0fe7106c68a

gkz cloud sql