我正在使用 PostgreSQL-13.0。
有没有办法获取数据库的创建时间或上次修改时间?
谢谢!
我正在使用 PostgreSQL-13.0。
有没有办法获取数据库的创建时间或上次修改时间?
谢谢!
我正在Postgresql-11中创建一个表,我需要同时为其创建一个主键,以防止在创建PK之前插入重复的数据,所以我希望这两个操作在同一个事务中完成。
我知道这可以得到它:
CREATE TABLE IF NOT EXISTS my_tab (
my_key INT8 NOT NULL,
PRIMARY KEY ( my_key ) );
但我还想为此 PK 设置一个名称,而不是默认名称,以便使维护工作变得容易。
如果我这样做:
CREATE TABLE IF NOT EXISTS my_tab ( my_key INT8 NOT NULL );
ALTER TABLE my_tab ADD CONSTRAINT custom_pk_name_my_tab PRIMARY KEY ( my_key );
我担心这两个语句将在两个不同的事务中完成。即在创建 PK 之前是否有可能插入一些重复的数据?
更进一步,如果该表已经存在怎么办?如果 tabe 已经存在,第二条语句会失败吗?
上面的SQL是在我的C++程序中执行的,这是一个长时间的守护服务。我不希望它因 SQL 故障而在无人参与的情况下崩溃。
如何获得?谢谢!
我搜索了 PostgreSQL 和 Google 的文档,但没有找到该函数pg_get_tabledef
, whilepg_get_indexdef
存在。
没有吗pg_get_tabledef
?也许实施起来太困难了?但是 pgAdmin4 可以为数据库中的每个对象提供非常漂亮/清晰/干净的 DDL 脚本。效果如何?
事实上,我需要比较两个数据库的许多表的定义(How to rapid Compare of multiple tables of两个 PostgreSQL 数据库的定义?)。
尽管有pg_dump --section=pre-data
并且pg_dump --section=post-data
可以转储所有定义,但比较结果仍然是一项艰巨的工作。我需要准确找出哪些表结构已更改。
所以我期待pg_get_tabledef
,比如pg_get_indexdef
,但是对于桌子。奇怪的是,虽然存在,但没有这样的功能pg_get_indexdef
。
请给我一些提示吗?谢谢!
我在 Postgresql-11 数据库中有数百个具有相同结构的表,我需要对每个表执行相同的统计。
由于真正的统计逻辑非常复杂,为了简化我在这里的问题,假设我需要计算每个的 MAX/MIN/MEAN/STDDEV。
我不想为每个单独的表一个一个地编辑单独的 SQL,因为如果是这样,我将编写数百行非常相似的 SQL,唯一不同的是表名。
所以我在一个函数中使用动态 SQL 一次对一个表进行计算,如下所示:
DROP FUNCTION IF EXISTS get_features_for;
CREATE OR REPLACE FUNCTION get_features_for(
IN table_name VARCHAR,
OUT result_ RECORD ) LANGUAGE 'plpgsql'
AS $func$
DECLARE sql_string VARCHAR :=
format('
SELECT ''%s'' table_name, -- just for validating
MAX(feature) max_feature,
MIN(feature) min_feature,
AVG(feature) avg_feature,
STDDEV(feature) std_feature
FROM "%s";',
table_name, table_name );
BEGIN
EXECUTE sql_string INTO result_;
END
$func$;
然后在查询中用数据库中的每个表名调用它,如下所示:
SELECT tbs."table_name", get_features_for( tbs."table_name" )
FROM information_schema."tables" tbs
WHERE tbs.table_schema = 'public' AND tbs.table_type = 'BASE TABLE'
AND tbs."table_name" LIKE 'analy%'
ORDER BY tbs."table_name";
它有效,但结果类似于以下内容:
"table_name", "get_features_for"
“analy_001”,“(analy_001,-3,2,0,2.5)”
“analy_002”,“(analy_002,-3,2,-1,2)”
“analy_003”,“(analy_003,-3,2,0,2)”
你可以看到所有的结果列都被包装成一个字符串列,让我不能方便地使用它们。
我希望它们类似于:
表名、最大特征、最小特征、平均特征、标准特征
"analy_001", "analy_001", -3, 2, 0, 2.5
"analy_002", "analy_002", -3, 2, -1, 2
"analy_003", "analy_003", -3, 2, 0, 2
我应该怎么得到它?
顺便说一句,我正在使用 pgAdmin。
谢谢!!!
我有一张像下面这样的大桌子:
CREATE TABLE public.huge_table (
sampl_day date NOT NULL,
tick_time timestamp(6) with time zone NOT NULL,
crit_feat integer NOT NULL,
---
--- other fields
---
CONSTRAINT pkey_huge_table PRIMARY KEY (tick_time)
);
CREATE INDEX idx_huge_table_day_time
ON public.huge_table USING brin (sampl_day, tick_time);
该表存储了一个时间序列的许多样本(行),一个样本一行。大多数时候,“crit_feat”字段的值很低,例如 1 或 2。在某些异常时间点,它会变得很高,例如 100。
在单个查询中找出这些特殊行非常容易。我们称它们为“特殊时间点”。
现在我需要查看查询结果集中上述每个特殊时间点之前/之后5s内发生的行,以找出特殊事件之前/之后发生的事情。
如果我只需要找出一个这样的特殊时间点和它前后5s的行,相对容易一些。
我可以这样做:
WITH spec_time as (
SELECT tick_time tp
FROM huge_table
ORDER BY crit_feat DESC LIMIT 1)
SELECT *
FROM huge_table
WHERE tick_time BETWEEN (SELECT tp FROM spec_time) - INTERVAL '5s'
AND (SELECT tp FROM spec_time) + INTERVAL '5s'
ORDER BY tick_time;
但是我需要对每个特殊点和UNION
所有特殊点都这样做!
所有特殊点的数量至少在20k左右,应该选出的事件不仅是“ crit_feat”字段具有最大值的事件,而且还包括值大于指定阈值的事件,例如:整个数据集中“crit_feat”字段的中值。
我想我可以在存储过程/函数中使用临时表和游标来获取它,但我期待一种更简单的方法!
抱歉我的英语不好,我希望我已经正确表达了我想说的话。
谢谢!
我在 Postgresql-11 中有一个巨大的表,如下所示:
CREATE TABLE my_huge_table(
tick_time timestamp(6) with time zone NOT NULL,
brok_time timestamp(6) with time zone,
trade_day date NOT NULL,
--other fields ...
...
CONSTRAINT my_huge_table_pkey PRIMARY KEY (tick_time)
);
CREATE INDEX idx_my_huge_table_td_time ON my_huge_table USING brin
( trade_day, abs(tick_time - brok_time) );
然后我进行查询并希望它利用索引idx_my_huge_table_td_time
,如下所示:
SELECT * FROM my_huge_table
WHERE trade_day BETWEEN TO_DATE('20220104', 'YYYYMMDD') AND TO_DATE('20220104', 'YYYYMMDD')
AND ABS(tick_time - brok_time) < INTERVAL '10 s';
但是 PostgreSQL 拒绝执行它,并说:
错误:函数 abs(interval) 不存在
第 3 行:AND ABS(tick_time - brok_time) < INTERVAL '10 s'
^
提示:没有函数匹配给定的名称和参数类型。您可能需要添加显式类型转换。
SQL 状态:42883 字符:525
看起来 funcabs()
不能接受区间值作为参数。
然后,我改变了我的查询:
SELECT * FROM my_huge_table
WHERE trade_day BETWEEN TO_DATE('20220104', 'YYYYMMDD') AND TO_DATE('20220104', 'YYYYMMDD')
AND GREATEST(tick_time - brok_time, brok_time - tick_time) < INTERVAL '10 s';
这次可以执行了,但是没有利用到索引。
我的问题:
1.索引表达式应该怎么写?事实上,我希望它记录两个时间戳字段之间的距离(绝对间隔值);
2.我应该如何编写可以使用上面索引的查询?
3.实际上GREATEST(tick_time - brok_time, brok_time - tick_time)
不是一个好主意,因为它调用了两次计算。不是吗?
4.创建索引后,我注意到PostgreSQL上报的索引真正的DDL SQL是:
CREATE INDEX idx_my_huge_table_td_time ON public.my_huge_table USING brin
(trade_day, abs(date_part('epoch'::text, tick_time - brok_time)));
表达式的值是否已转换为类型text
?这显然不是我的期望!
最后几天,我针对大量大量写入事务调整了 PG11,并获得了惊人的结果。
我唯一担心的是,我关闭时是否有更大的丢失数据的风险synchronous_commit
,并使用了一个大的wal_buffers
值。
我正在使用 Postgresql-11,使用传统的 HDD 作为物理存储设备。
我需要优化写作性能。
我已将 -1 更改wal_buffers
为 512MB,这意味着wal_buffers
不再使用shared_buffers
. 我对吗?
我想知道它是否可以提高写入性能以扩大shared_buffers
。在我看来,它仅用于查询?
当前值为shared_buffers
2GB,我的RAM是8GB。我想将它更改为 4GB。谢谢!
在调试程序时,我需要经常删除一个Postgresql的数据库,然后重新创建它。
为方便起见,我想在单个命令行中执行此操作,如下所示:
psql -c "DROP DATABASE my_db; CREATE DATABASE my_db;"
但我得到一个错误:
错误:DROP DATABASE 不能在事务块内运行
有什么办法可以做到吗?谢谢!
Postgresql-11
select tick_time, nano_secs,
concat( to_char(tick_time, 'MMDD HH24:MI:SS.US'),
to_char(nano_secs, '000') )
from ticks
order by tick_time, nano_secs limit 100;
我想像上面那样连接 2 个字符串,但它总是在两个字符串之间添加一个空格字符。
如何防止它这样做?
谢谢!
我将使用 BRIN 索引来替换 Postgresql-11 中日期/时间戳列上的 B 树索引。我以前从未使用过它。
因为如果数据以列被索引的顺序物理存储,那么 BRIN 索引会更有效。
我怀疑我是否应该删除所有数据,然后按顺序重新插入它们。我的数据被用于静态分析,不会改变。
pg_dump
我有一个由PostgreSQL v12 客户端附带的大转储文件,它是自定义格式而不是纯 sql 文本。服务器的版本也是 PostgreSQL v12。
现在我需要将文件重新加载到 PostgreSQL v11 服务器中,我收到一条错误消息:
unrecognized configuration parameter "default_table_access_method"
我猜这个参数是PG12引入的,所以PG11识别不出来,虽然我用的是12版本的pg_restore
.
因为文件很大并且是二进制格式,我不能编辑它来手动删除该行。
我pg_restore test_file -f plain.sql
用一个测试转储文件对其进行了测试,它看起来像工作,我可以从纯 SQL 文件中删除该行。但真正的转储文件大约是 30GB,从二进制扩展的纯 SQL 文件可能更大(工作数据库占用 300GB 磁盘空间)。
有没有办法,我可以干净地将它重新加载到 PG11 中,只有没有“SET default_table_access_method = heap;”这一行?
其实下面这些都可以忽略,因为都是默认值:
SET statement_timeout = 0;
SET lock_timeout = 0;
SET idle_in_transaction_session_timeout = 0;
SET client_encoding = 'UTF8';
SET standard_conforming_strings = on;
SELECT pg_catalog.set_config('search_path', '', false);
SET check_function_bodies = false;
SET xmloption = content;
SET client_min_messages = warning;
SET row_security = off;
SET default_tablespace = '';
我有一个使用 PostgreSQL v11 运行的大型数据库,并且磁盘空间非常有限,所以我不得不VACUUM (FULL)
频繁地访问数据库(例如每天)。
但是吸尘是一项非常耗时的操作。此外,我经常遇到真空错误消息,例如
ERROR: canceling autovacuum task
CONTEXT: automatic vacuum of table ***
幸运的是,服务可以在一天中的特定时间点关闭,所以我可以在关闭数据库后转储数据库,立即恢复并重新启动服务。
此外,我发现“转储/恢复”比“清理”更快。
我的问题:“倾倒/恢复”所做的事情是否包括“吸满”所做的事情?
如果是这样,我可以这样做而不是吸尘。