postgres---postgres操作，配置语句

时区

查看时区

show time zone;

查看当前时间

select now();

查看支持的时区列表

select * from pg_timezone_names;

设置时区

set time zone 'PRC'; #设置为东八区 北京时间 utc+8

SSL

sslmode选项列表

数据库连接SSL选项sslmode，安全等级由低到高：
disable: 只尝试非SSL连接
allow：首先尝试非SSL连接，若失败再尝试SSL连接
prefer (default)：首先尝试SSL连接，若失败再尝试非SSL连接
require：只尝试SSL连接，若有根证书存在，等同于verify-ca
verify-ca：只尝试SSL连接，并用根证书验证服务器证书是不是根CA签发的
verify-full：只尝试SSL连接，并用根证书验证服务器证书是不是根CA签发的，且主题必须匹配连接域名或IP地址

时间类型问题

定义存字符串数组类型的字段

import (
	"github.com/lib/pq"
	"gorm.io/gorm"
)
type GeneralOrder struct {
	gorm.Model
	Param  pq.StringArray `gorm:"type:text[]"`
}

根据时间区间查询

1. 查询2021-11-10当天的记录

select * from events where created_at >= '2021-11-10'::timestamp and created_at < '2021-11-11'::timestamp

或者

select * from events where created_at between to_date('2021-11-10','YYYY-MM-DD') and to_date('2021-11-11','YYYY-MM-DD')

慢SQL

要将PostgreSQL的慢查询阈值配置为400ms，可以按照以下步骤进行操作：

1. 打开PostgreSQL的配置文件，通常是postgresql.conf。你可以使用以下命令来查找配置文件的位置：

   sudo -u postgres psql -c "SHOW config_file;"
   

2. 在配置文件中找到以下参数，并进行相应的修改：

   • log_statement：这个参数用于配置要记录的SQL语句类型。将其设置为all，将会记录所有语句，包括慢查询和其他语句。默认值为none。
   • log_duration：该参数用于启用或禁用记录查询持续时间的功能。将其设置为on，开启记录持续时间。默认值为off。
   • log_min_duration_statement：这个参数用于设置慢查询的阈值。将其设置为400（以毫秒为单位），将会记录所有执行时间超过400毫秒的查询语句。默认值为-1，表示禁用慢查询日志。

   例如：

   log_statement = 'all'
   log_duration = on
   log_min_duration_statement = 400
   

3. 修改完成后，保存文件，并重新启动PostgreSQL服务，以使配置生效。你可以使用以下命令重启服务：

   sudo service postgresql restart
   

4. 现在，所有执行时间超过400毫秒的查询语句都将被记录在PostgreSQL的日志文件中。

注意：设置慢查询阈值时需要谨慎操作，不要将阈值设置得太低，否则可能会导致过多的日志记录和性能影响。建议根据实际情况和系统负载进行调整。

timescaledb

create_hypertable('表名','列名')

分区大小查看

为了查看默认的时间分区间隔，你可以尝试以下查询：

SELECT table_schema, table_name, column_name
FROM information_schema.columns
WHERE table_name = '表名' AND column_name = '列名';

请将 data_h 替换为你的Hypertable名称，并将 time 替换为你的时间列名称。这将返回指定Hypertable的时间列名称。

一旦你找到了时间列名称，请使用以下查询来查找相应的默认时间分区间隔：

SELECT table_name, partition_interval
FROM _timescaledb_catalog.hypertable
WHERE table_name = '表名';

请将 表名 替换为你的Hypertable名称（我的就是data_h）。这将返回指定Hypertable的默认时间分区间隔。
'time’列就是分区用的列

根据提供的结果，我们可以看到 chunk_target_size 的值为 0。这意味着没有设置默认的分区大小或目标分区大小。在这种情况下，TimescaleDB将使用默认的分区大小和目标分区大小。

默认情况下，TimescaleDB将根据表的插入模式和查询需求自动选择合适的分区大小和目标分区大小。这样可以确保在存储和查询性能之间获得平衡。

如果你想自定义分区大小，你可以使用以下命令设置目标分区大小：

SELECT set_chunk_target_size('public', 'data_h', your_size);

请将 public 替换为表所在的架构名称，data_h 替换为表的名称，your_size 替换为你希望设置的目标分区大小。目标分区大小可以使用字节（以 B 结尾），千字节（以 KB 结尾），兆字节（以 MB 结尾）或其他合适的单位。

需要注意的是，分区大小的设置应该根据你的数据量、插入频率和查询需求进行调整，以获得最佳的性能和存储效率。