CockroachDB TIL:第9卷

这是我的一系列文章，涵盖了我在处理CockroachDB时的简短“今天我学到了”主题。阅读前几期。

主题

• 主题1：捕获模式更改历史

• 主题2：为CockroachDB批量操作使用Linode对象存储

• 主题3：为CockroachDB无服务器配置DBeaver

• 主题4：为多个数据库配置DBeaver

• 主题5：使用pgpass配置DBeaver CockroachDB连接

主题1：捕获模式更改历史

在我们的社区中，在从集群捕获模式更改方面存在一个问题。CockroachDB有一个系统。eventlog表，可检查是否存在审核事件，如架构更改、群集配置更改、用户和角色更改等。该表是这些事件的运行记录，默认情况下，它保留由“server.eventlog.ttl”属性控制的90天事件。这是一个值得注意的事件表，而不是模式更改工具的替代品，模式更改工具通常永久保存在模式更改历史记录中。下面是表中的示例输出：

{
      "ApplicationName": "$ cockroach demo",
      "DescriptorID": 106,
      "EventType": "create_table",
      "Statement": "CREATE TABLE IF NOT EXISTS movr.public.users (id UUID NOT NULL, city VARCHAR NOT NULL, name VARCHAR NULL, address VARCHAR NULL, credit_card VARCHAR NULL, PRIMARY KEY (city, id ASC))",
      "TableName": "movr.public.users",
      "Tag": "CREATE TABLE",
      "Timestamp": 1660142974199075000,
      "User": "demo"
  }
  {
      "ApplicationName": "$ cockroach demo",
      "DescriptorID": 107,
      "EventType": "create_table",
      "Statement": "CREATE TABLE IF NOT EXISTS movr.public.vehicles (id UUID NOT NULL, city VARCHAR NOT NULL, type VARCHAR NULL, owner_id UUID NULL, creation_time TIMESTAMP NULL, status VARCHAR NULL, current_location VARCHAR NULL, ext JSONB NULL, PRIMARY KEY (city, id ASC), INDEX vehicles_auto_index_fk_city_ref_users (city, owner_id ASC))",
      "TableName": "movr.public.vehicles",
      "Tag": "CREATE TABLE",
      "Timestamp": 1660142974206742000,
      "User": "demo"
  }

表中的事件是字符串，但它们可以快速外推为JSONB对象。我提出了以下查询以关注语句对象：

WITH X AS (
    SELECT jsonb_pretty(info::jsonb) AS payload 
        FROM system.eventlog
    ) 
    SELECT payload::jsonb->'Statement' FROM x;

输出如下：

 "CREATE DATABASE movr"
  "CREATE TABLE IF NOT EXISTS movr.public.users (id UUID NOT NULL, city VARCHAR NOT NULL, name VARCHAR NULL, address VARCHAR NULL, credit_card VARCHAR NULL, PRIMARY KEY (city, id ASC))"
  "CREATE TABLE IF NOT EXISTS movr.public.vehicles (id UUID NOT NULL, city VARCHAR NOT NULL, type VARCHAR NULL, owner_id UUID NULL, creation_time TIMESTAMP NULL, status VARCHAR NULL, current_location VARCHAR NULL, ext JSONB NULL, PRIMARY KEY (city, id ASC), INDEX vehicles_auto_index_fk_city_ref_users (city, owner_id ASC))"
  "CREATE TABLE IF NOT EXISTS movr.public.rides (id UUID NOT NULL, city VARCHAR NOT NULL, vehicle_city VARCHAR NULL, rider_id UUID NULL, vehicle_id UUID NULL, start_address VARCHAR NULL, end_address VARCHAR NULL, start_time TIMESTAMP NULL, end_time TIMESTAMP NULL, revenue DECIMAL(10,2) NULL, PRIMARY KEY (city, id ASC), INDEX rides_auto_index_fk_city_ref_users (city, rider_id ASC), INDEX rides_auto_index_fk_vehicle_city_ref_vehicles (vehicle_city, vehicle_id ASC), CONSTRAINT check_vehicle_city_city CHECK (vehicle_city = city))"
  "CREATE TABLE IF NOT EXISTS movr.public.vehicle_location_histories (city VARCHAR NOT NULL, ride_id UUID NOT NULL, \\"timestamp\\" TIMESTAMP NOT NULL, lat FLOAT8 NULL, long FLOAT8 NULL, PRIMARY KEY (city, ride_id ASC, \\"timestamp\\" ASC))"
  "CREATE TABLE IF NOT EXISTS movr.public.promo_codes (code VARCHAR NOT NULL, description VARCHAR NULL, creation_time TIMESTAMP NULL, expiration_time TIMESTAMP NULL, rules JSONB NULL, PRIMARY KEY (code ASC))"
  "CREATE TABLE IF NOT EXISTS movr.public.user_promo_codes (city VARCHAR NOT NULL, user_id UUID NOT NULL, code VARCHAR NOT NULL, \\"timestamp\\" TIMESTAMP NULL, usage_count INT8 NULL, PRIMARY KEY (city, user_id ASC, code ASC))"
  "ALTER TABLE movr.public.vehicles ADD FOREIGN KEY (city, owner_id) REFERENCES movr.public.users (city, id)"
  NULL
  "ALTER TABLE movr.public.rides ADD FOREIGN KEY (city, rider_id) REFERENCES movr.public.users (city, id)"
  NULL
  "ALTER TABLE movr.public.rides ADD FOREIGN KEY (vehicle_city, vehicle_id) REFERENCES movr.public.vehicles (city, id)"
  NULL
  "ALTER TABLE movr.public.vehicle_location_histories ADD FOREIGN KEY (city, ride_id) REFERENCES movr.public.rides (city, id)"
  NULL
  "ALTER TABLE movr.public.user_promo_codes ADD FOREIGN KEY (city, user_id) REFERENCES movr.public.users (city, id)"
  NULL

主题2：为CockroachDB批量操作使用Linode对象存储

在我们的社区Slack中，我遇到了一个问题：如何使用Linode对象存储进行CockroachDB的批量操作。由于Linode对象存储使用与S3兼容的API，它应该可以正常工作。

顺便说一句，这个技巧不是我刚刚学到的。我已经在我的Minio文章中讨论了这一点（使用Docker Compose运行CockroachDB-第1部分和第2部分）；然而，专门与Linode合作对我来说是新的。

*注意：在我的测试中，对于任何与S3兼容的服务，您必须传递可选的AWS_REGION和AWS_ ENDPOINT参数，以使对象存储与CockroachDB一起工作。让我们以备份作业为例，看看如果省略AWS_REGION参数会发生什么。

BACKUP INTO 's3://bucketname?AWS_ACCESS_KEY_ID=accesskeyid&AWS_SECRET_ACCESS_KEY=secret' AS OF SYSTEM TIME '-10s';

我们得到以下错误：

ERROR: could not find s3 bucket's region: NotFound: Not Found
        status code: 404, request id: , host id: 

让我们再次尝试AWS_REGION：

BACKUP INTO 's3://bucketname?AWS_ACCESS_KEY_ID=accesskeyid&AWS_SECRET_ACCESS_KEY=secret&AWS_REGION=us-east-1' AS OF SYSTEM TIME '-10s';

ERROR: failed to get s3 object: InvalidAccessKeyId: The AWS Access Key Id you provided does not exist in our records.
        status code: 403, request id: , host id: 

错误可以更清楚。在这种情况下，我建议使用awscli实用程序来诊断对bucket的访问。

让我们尝试在bucket上运行ls命令：

aws s3 ls s3://bucketname 

An error occurred (InvalidAccessKeyId) when calling the ListObjectsV2 operation: The AWS Access Key Id you provided does not exist in our records.

该错误是AWS CLI的传递。让我们添加端点。顺便说一下，这里有一篇文章指向端点提示。

aws s3 ls s3://bucketname --endpoint=https://us-east-1.linodeobjects.com

     PRE 2022/
     PRE metadata/

这起作用了，它显示了我在bucket中的一些现有文件。

现在，让我们尝试运行备份作业并传递AWS_ENDPOINT参数。

BACKUP INTO 's3://bucketname?AWS_ACCESS_KEY_ID=accesskeyid&AWS_SECRET_ACCESS_KEY=secret&AWS_REGION=us-east-1&AWS_ENDPOINT=https://us-east-1.linodeobjects.com' AS OF SYSTEM TIME '-10s';

终于成功了！今天的教训：当有疑问时，确保基础工作。

主题3：为CockroachDB无服务器配置DBeaver

前几天，我偶然发现了一个我从未见过的问题。我正在用CockroachDB Serverless配置DBeaver，但它不起作用。今天，我将记录连接到无服务器的新方法，以及DBeaver出现错误时的解决方法：

首先，让我们创建一个集群。提升性能的最佳方式是通过ccloud CLI。

ccloud cluster create serverless artem-serverless

Retrieving the nearest compatible region: succeeded
Creating cluster: succeeded
Success! Created cluster
 name: artem-serverless 
 id: 2490c3b4-5739-475f-ac8d-bda589d068db 
 cockroach version: v22.1.6 
 cloud: GCP 
 plan type: SERVERLESS 
 state: CREATED 
 spend limit: $0.00 
 regions: us-east1 

ccloud cluster sql artem-serverless

Retrieving cluster info: succeeded
 Downloading cluster cert to /Users/artem/.postgresql/root.crt: succeeded
Retrieving SQL user list: succeeded
No SQL users found. Create one now?: Y
Create a new SQL user:
New username: artem
New password: ***************
Confirm password: ***************
Looking up cluster ID: succeeded
Creating SQL user: succeeded
Success! Created SQL user 
 name: artem 
 cluster: artem-serverless 
Starting CockroachDB SQL shell...
#
# Welcome to the CockroachDB SQL shell.
# All statements must be terminated by a semicolon.
# To exit, type: \q.
#
# Server version: CockroachDB CCL v22.1.6 (x86_64-pc-linux-gnu, built 2022/08/23 17:05:04, go1.17.11) (same version as client)
# Cluster ID: 20735006-5a39-49c3-29d6-6f4c8c091e7d
#
# Enter \? for a brief introduction.
#
artem@free-tier11.gcp-us-east1.cockroachlabs.cloud:26257/defaultdb>

现在我们需要配置DBeaver。让我们捕获连接参数，因为我们接下来需要它。

ccloud cluster sql artem-serverless --connection-params

Retrieving cluster info: succeeded
Connection parameters
 Database:  defaultdb
 Host:      free-tier11.gcp-us-east1.cockroachlabs.cloud
 Options:   --cluster=artem-serverless-1812
 Port:      26257

顺便说一下，ccloud CLI支持自动完成。您可以使用“ccloud completion＜您的shell，即zsh、bsh、fish等”生成自动补全。

单击下一步。

如果这是您第一次使用DBeaver配置与PostgreSQL兼容的数据库，您可能需要下载PostgreSQL.jar。

填写主机、数据库、用户和密码，然后单击测试连接。

该错误意味着我们需要输入租户集群标识符。历史上，我们在数据库文本字段中添加了租户路由号，即artem-serverless-1812.defaultdb。

再次点击测试连接。

这与预期的一样，但有另一种方法可以实现相同的结果，而且它更符合其他工具和IDE。让我们将数据库更改回defaultdb并导航到驱动程序属性选项卡。在那里，查找名为“选项”的字段，并输入从ccloud输出中获得的参数输出，即选项：–cluster=artem-serverless-1812，保留选项：part out。

再次单击测试连接。

我们现在准备好了！我展示了使用DBeaver配置无服务器的两种方法。本主题的其余部分将讨论我在使用DBeaver时遇到的问题，特别是驱动程序属性选项卡中缺少options属性，我花了很多时间才弄清楚。事实证明，如果您长期使用DBeaver，并多次将其从旧版本升级到新版本，UI可能会变得混乱，您需要重置元数据。顺便说一句，这个问题并不明显，因为我和我的一个同事一起工作过，让他使用相同版本的DBeaver来配置数据库，他碰巧有选项字段。重置工作区文档帮助我了解了它的底部。解决方案的要点是删除元数据目录并重置工作区。因此，一旦我删除了/Library/DBeaverData/workspace6/元数据和重新启动的DBeaver，我看到了选项字段。

现在，您已经设置并知道了两种连接到无服务器集群的方法。

主题4：为多个数据库配置DBeaver

这是我刚学会的一个新技巧，因为我正在解决上面主题3的问题。DBeaver中的每个数据库连接都是特定于数据库的。注意，在上一主题中，我们将连接固定到defaultdb。如果我们在DBeaver中使用项目导航器，我们将看到整个上下文基于defaultdb和所有底层属性。

假设您希望看到的不仅仅是defaultdb：您应该能够跨多个数据库导航。您可以通过编辑连接来做到这一点。

您将看到与第三个主题中的连接配置类似的上下文。单击CockroachDB选项卡。

选中“显示所有数据库”旁边的框，然后单击“确定”

系统将提示您重新连接。

单击是。现在您可以展开项目导航器以查看其余数据库。

这是我最大的不满，我很高兴我找到了解决办法。现在我可以在数据库之间切换，而无需断开和重新配置连接。我们可以通过创建另一个数据库并刷新导航器选项卡来轻松测试它。

主题5：使用pgpass配置DBeaver CockroachDB连接

对于最后一个主题，我想讨论另一种更安全的方法，即通过DBeaver连接到CockroachDB Serverless。回想一下，在第三个主题中，我们明确使用用户名和密码访问CockroachDB。

从PostgreSQL驱动程序42.3开始，现在支持pgpass，我们已经多次介绍了它。pgpass的好处现在可以传递给像DBeaver这样的IDE工具。您可以通过单击关联按钮并导航到“库”选项卡来编辑当前驱动程序设置。

确保至少安装了42.3 jar。如果没有，DBeaver允许您从上下文菜单下载。完成后，关闭上下文菜单并切换回主连接菜单。

我们可以重新配置连接以利用pgpass而不是“数据库本机”身份验证方法。单击该字段并切换到身份验证旁边的“PostgreSQL PgPass”：

最后，确保正确配置了pgpass。我们已经讨论过多次了，现在应该是第二天性了。我将为后代提供示例文件格式：

free-tier11.gcp-us-east1.cockroachlabs.cloud:26257:defaultdb:user:password

最后，在DBeaver中，单击“确定”，系统将提示您重新连接。单击是。

重要提示：如果您在pgpass中遇到不匹配的错误：

确保pgpass文件中的数据库与DBeaver中的数据库匹配。

它应该匹配pgpass中的defaultdb：

这应与pgpass中的artem-serverless-1812.defaultdb相匹配，即：

free-tier11.gcp-us-east1.cockroachlabs.cloud:26257:artem-serverless-1812.defaultdb:user:password

现在有一个非常重要的注意事项：无论您如何配置数据库名称，pgpass都希望填充驱动程序属性中的选项字段。我还没有找到一种方法来省略选项字段并使路由工作。

最后，您可以测试连接或单击“确定”关闭上下文菜单。

现在，您已经设置好将pgpass与DBeaver一起使用。

原文标题：CockroachDB TIL: Volume 9
原文作者：Artem Ervits
原文链接：https://dzone.com/articles/cockroachdb-til-volume-9-1