通过Terraform工具,可以用代码的方式来管理和维护RDS PostgreSQL实例。本文介绍如何使用Terraform开启和关闭SQL审计,以获取数据库实例SQL语句的具体信息,排查各种性能问题。
前提条件
已创建RDS PostgreSQL实例,详情请参见创建RDS PostgreSQL实例。
实例状态为运行中,您可以通过如下两种方式查看:
参见查询实例详情查看参数status,如果取值为Runing则表示实例状态为运行中。
前往RDS管理控制台,切换到目标地域,找到指定实例后,查看实例状态。
开启SQL审计
在terraform.tf文件的
resource "alicloud_db_instance" "instance" {}
中增加sql_collector_status
配置项,具体配置如下:... resource "alicloud_db_instance" "instance" { ... sql_collector_status = "Enabled" }
运行
terraform apply
。出现如下配置信息后,确认配置信息并输入yes,开启SQL审计。
alicloud_db_instance.instance: Refreshing state... [id=pgm-****] Terraform used the selected providers to generate the following execution plan. Resource actions are indicated with the following symbols: ~ update in-place Terraform will perform the following actions: # alicloud_db_instance.instance will be updated in-place ~ resource "alicloud_db_instance" "instance" { ~ ha_config = "Auto" -> "Manual" id = "pgm-****" + manual_ha_time = "2022-09-30T09:00:00Z" ~ sql_collector_status = "Disabled" -> "Enabled" # (40 unchanged attributes hidden) # (1 unchanged block hidden) } Plan: 0 to add, 1 to change, 0 to destroy. Do you want to perform these actions? Terraform will perform the actions described above. Only 'yes' will be accepted to approve. Enter a value:
出现类似如下日志时,表示配置成功。
alicloud_db_instance.instance: Modifying... [id=pgm-****] alicloud_db_instance.instance: Still modifying... [id=pgm-****, 10s elapsed] alicloud_db_instance.instance: Still modifying... [id=pgm-****, 20s elapsed] ... alicloud_db_instance.instance: Still modifying... [id=pgm-****, 3m0s elapsed] alicloud_db_instance.instance: Modifications complete after 3m4s [id=pgm-****] Apply complete! Resources: 0 added, 1 changed, 0 destroyed.
查看结果。
运行
terraform show
查看SQL审计状态。# alicloud_db_instance.instance: resource "alicloud_db_instance" "instance" { acl = "prefer" ca_type = "aliyun" client_ca_enabled = 0 client_crl_enabled = 0 connection_string = "pgm-****.pg.rds.aliyuncs.com" connection_string_prefix = "pgm-****" db_instance_storage_type = "cloud_essd" db_time_zone = "Asia/Shanghai" deletion_protection = false engine = "PostgreSQL" engine_version = "13.0" force_restart = false ha_config = "Manual" id = "pgm-****" instance_charge_type = "Postpaid" instance_name = "terraformtest" instance_storage = 50 instance_type = "pg.n2.2c.2m" maintain_time = "05:00Z-06:00Z" manual_ha_time = "2022-09-30T09:00:00Z" monitoring_period = 300 period = 0 port = "5432" private_ip_address = "192.168.XX.XX" replication_acl = "prefer" resource_group_id = "rg-****" security_group_id = "sg-****" security_group_ids = [ "sg-bp1h9iqx6mxan5tcouwc", ] security_ip_mode = "normal" security_ips = [ "0.0.0.0/0", ] server_cert = <<-EOT -----BEGIN CERTIFICATE----- MIIE7jCCA9****fM4ALgBJ2 N9xwKlPQ65q/kux0yErtwhAD -----END CERTIFICATE----- EOT server_key = <<-EOT -----BEGIN RSA PRIVATE KEY----- MIIJKQIBAA****T4Y1K34yE+e+VAdGp -----END RSA PRIVATE KEY----- EOT sql_collector_config_value = 30 sql_collector_status = "Enabled" ssl_action = "Open" ssl_status = "0" storage_auto_scale = "Enable" storage_threshold = 30 storage_upper_bound = 100 target_minor_version = "rds_postgres_1300_20220830" tcp_connection_type = "SHORT" vpc_id = "vpc-****" vswitch_id = "vsw-****" zone_id = "cn-hangzhou-h" pg_hba_conf { address = "127.0.0.1" database = "all" method = "md5" priority_id = 1 type = "host" user = "all" } }
登录RDS控制台查看SQL审计状态。
关闭SQL审计
在terraform.tf文件的
resource "alicloud_db_instance" "instance" {}
中修改sql_collector_status
配置项,具体配置如下:... resource "alicloud_db_instance" "instance" { ... sql_collector_status = "Disabled" }
运行
terraform apply
。出现如下配置信息后,确认配置信息并输入yes,关闭SQL审计。
alicloud_db_instance.instance: Refreshing state... [id=pgm-****] Terraform used the selected providers to generate the following execution plan. Resource actions are indicated with the following symbols: ~ update in-place Terraform will perform the following actions: # alicloud_db_instance.instance will be updated in-place ~ resource "alicloud_db_instance" "instance" { id = "pgm-****" ~ sql_collector_status = "Enabled" -> "Disabled" # (42 unchanged attributes hidden) # (1 unchanged block hidden) } Plan: 0 to add, 1 to change, 0 to destroy. Do you want to perform these actions? Terraform will perform the actions described above. Only 'yes' will be accepted to approve. Enter a value:
出现类似如下日志时,表示配置成功。
alicloud_db_instance.instance: Modifying... [id=pgm-****] alicloud_db_instance.instance: Modifications complete after 9s [id=pgm-****] Apply complete! Resources: 0 added, 1 changed, 0 destroyed.
查看结果。
运行
terraform show
查看SQL审计状态。# alicloud_db_instance.instance: resource "alicloud_db_instance" "instance" { acl = "prefer" ca_type = "aliyun" client_ca_enabled = 0 client_crl_enabled = 0 connection_string = "pgm-****.pg.rds.aliyuncs.com" connection_string_prefix = "pgm-****" db_instance_storage_type = "cloud_essd" db_time_zone = "Asia/Shanghai" deletion_protection = false engine = "PostgreSQL" engine_version = "13.0" force_restart = false ha_config = "Manual" id = "pgm-****" instance_charge_type = "Postpaid" instance_name = "terraformtest" instance_storage = 50 instance_type = "pg.n2.2c.2m" maintain_time = "05:00Z-06:00Z" manual_ha_time = "2022-09-30T09:00:00Z" monitoring_period = 300 period = 0 port = "5432" private_ip_address = "192.168.XX.XX" replication_acl = "prefer" resource_group_id = "rg-****" security_group_id = "sg-****" security_group_ids = [ "sg-****", ] security_ip_mode = "normal" security_ips = [ "0.0.0.0/0", ] server_cert = <<-EOT -----BEGIN CERTIFICATE----- MIIE7jCCA9a****fM4ALgBJ2 N9xwKlPQ65q/kux0yErtwhAD -----END CERTIFICATE----- EOT server_key = <<-EOT -----BEGIN RSA PRIVATE KEY----- MIIJKQI****+7qT4Y1K34yE+e+VAdGp -----END RSA PRIVATE KEY----- EOT sql_collector_config_value = 30 sql_collector_status = "Disabled" ssl_action = "Open" ssl_status = "0" storage_auto_scale = "Enable" storage_threshold = 30 storage_upper_bound = 100 target_minor_version = "rds_postgres_1300_20220830" tcp_connection_type = "SHORT" vpc_id = "vpc-****" vswitch_id = "vsw-****" zone_id = "cn-hangzhou-h" pg_hba_conf { address = "127.0.0.1" database = "all" method = "md5" priority_id = 1 type = "host" user = "all" } }
登录RDS控制台查看SQL审计状态。
相关文档
通过RDS控制台,您也可以开启和关闭SQL审计,详情请参见SQL审计(数据库审计)。
您也可以使用DAS的SQL洞察和审计功能,可以更好地获取RDS数据库实例SQL语句的具体信息,排查各种性能问题。详情请参见SQL洞察和审计。
- 本页导读 (1)