问题[postgresql-13](dba)

如果 Postgres 连接是从 5432 端口完成的，我可以获得 IP 地址。现在，如果我使用 6432 端口连接 Postgres 意味着此时连接来自 pgbouncer，我没有从pg_stat_activity的客户端 IP 地址选项卡中获取机器的 IP 地址，并且在日志中也获取相同的 localhost。我启用了 log_hostname和log_line_prefix我还添加了%h 和 %r来获取连接发生的机器的 IP。我的目的是审核/获取从哪台机器发生的查询或操作的地址

我有一个 PSQL 13.6 数据库，它最初是由 创建的owner_1，并且随着时间的推移而被填充。由于外部原因，我现在需要定期更改数据库的所有者帐户并删除旧用户帐户。当我尝试实现这一点时，我被阻止了，因为有些对象依赖于它。

我不想冒险失去manager_role可能存在的或任何级联依赖项的特权。
我怎样才能找到并转移仍然拥有的对象owner_1？

MWE：

1. 作为postgres用户：创建owner_1.

CREATE USER owner_1 WITH CREATEDB CREATEROLE ENCRYPTED PASSWORD 'owner_password_1';

2. As owner_1：设置数据库和对象。

CREATE DATABASE test_db;
\c test_db
CREATE ROLE manager_role;
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT, INSERT, UPDATE ON TABLES TO manager_role;
CREATE USER manager_1 WITH ENCRYPTED PASSWORD 'manager_password_1' IN ROLE manager_role;

3. As owner_1：创建一个owner_role，转移所有权，并在该角色中创建新用户。

CREATE ROLE owner_role WITH NOLOGIN NOSUPERUSER INHERIT CREATEDB CREATEROLE NOREPLICATION;
GRANT USAGE, CREATE ON SCHEMA public TO owner_role;
GRANT owner_role TO "owner_1";
REASSIGN OWNED BY owner_1 TO owner_role;

CREATE USER owner_2 WITH CREATEDB CREATEROLE ENCRYPTED PASSWORD 'owner_password_2' IN ROLE owner_role;

您现在应该拥有这些用户定义

\du
                                       List of roles
  Role name   |                         Attributes                         |   Member of    
--------------+------------------------------------------------------------+----------------
 manager_1    |                                                            | {manager_role}
 manager_role | Cannot login                                               | {}
 owner_1      | Create role, Create DB                                     | {owner_role}
 owner_2      | Create role, Create DB                                     | {owner_role}
 owner_role   | Create role, Create DB, Cannot login                       | {}
 postgres     | Superuser, Create role, Create DB, Replication, Bypass RLS | {}

4. As owner_2：删除旧用户。

DROP USER owner_1;

最后一个命令将失败并出现错误：

ERROR:  role "owner_1" cannot be dropped because some objects depend on it
DETAIL:  owner of default privileges on new relations belonging to role owner_1 in schema public

编辑DROP OWNED BY owner_1;从用户 执行owner_2失败并出现错误：

test_db=> DROP OWNED BY owner_1;
ERROR:  permission denied to drop objects 

我正在处理一些SEC 数据，这些数据在非常规则格式的制表符分隔文件中可用。

在 MySQL 上，我可以这样导入这些文件：

CREATE TABLE import ( adsh text, tag text, version text, ddate text, qtrs text, uom text, dimh text, iprx text, value text, footnote text, footlen text, dimn text, coreg text, durp text, datp text, dcml text);

LOAD DATA INFILE '/pg/import/2011/Q2/num.tsv' INTO TABLE import FIELDS ESCAPED BY '' IGNORE 1 ROWS;

这适用于每个文件。但是，在 Pg 服务器上运行的类似命令不起作用。在这种情况下，我使用该页面上 .zip 中的 2011 年第二季度“num”数据。此文件是一个 40 列的制表符分隔文件。我在 Pg 服务器上运行这个命令。

COPY import._2011_q2_num from '/pg/import/2011/Q2/num.tsv' DELIMITER E'\t' CSV HEADER;
ERROR:  unterminated CSV quoted field
CONTEXT:  COPY _2011_q2_num, line 830954: "0001193125-11-104388 DerivativeInstrumentsGainLossReclassifiedFromAccumulatedOCIIntoIncomeEffectiveP..."

根据 wc -l，该文件中有 830953 行。如果我删除最后一行，错误仍然存​​在 - 它只是说它像 830953 一样打开，等等。

我认为问题在于文件中出现了多个空字段：

\t\t\t\t

（来自 od -c）。我认为 Pg 正在将那些连续的标签视为转义。文档说：

ESCAPE...默认值与 QUOTE 值相同（这样如果引用字符出现在数据中，则加倍）。这必须是一个单字节字符。

好的，所以我必须为 ESCAPE 赋予一些价值，即使在这些文件中什么都不会被转义。文件中没有 \b ，所以我尝试了：

COPY import._2011_q2_num from '/pg/import/2011/Q2/num.tsv' DELIMITER E'\t' ESCAPE E'\b' CSV HEADER;

但是，我得到了同样的错误。

\b 也许有些时髦？我还尝试了波浪号（它出现在某些文件中，但我尝试了一个没有它的文件）和同样的错误。

-- with E for ESCAPE byte
copy import._2011_q2_num from '/pg/import/2011/Q2/num.tsv' ESCAPE E'~' DELIMITER E'\t' CSV HEADER
-- without E
copy import._2011_q2_num from '/pg/import/2011/Q2/num.tsv' ESCAPE '~' DELIMITER E'\t' CSV HEADER

根据我的测试，大约四分之一的文件失败，其余的成功。每个文件中有数百万行，因此手动检查它们是不切实际的，而且我不认为这些文件的构造很糟糕，因为 MySQL 没有问题。我想我只是没有给出正确的 Pg 语法 - ？

这是 Debian 11.3 上的 PostgreSQL 13.5

所以我必须根据日期和产品到达的时间对表格进行分组，时间将是：

morning = [5, 6, 7 , 8, 9]
mid_morning = [10, 11]
midday = [12, 13, 14]
evening = [15, 16, 17 ,18 ,19, 20]
night = [21, 22, 23, 0, 1, 2, 3, 4]

这是表格：

 CREATE TABLE inventory (
      inventory_id serial PRIMARY KEY,
      arrive_date date NOT NULL,
      arrive_location character varying NOT NULL,
      thing_type integer NOT NULL,
      quantity integer NOT NULL
    );

INSERT INTO inventory (arrive_date, arrive_location, thing_type, quantity) VALUES
  ('2018-05-30 05:00:00-00', 'location_00', 3, 2)
, ('2018-05-30 06:00:00-00', 'location_00', 3, 8)
, ('2018-05-30 12:50:00-00', 'location_00', 5, 2)
, ('2018-05-30 13:40:00-00', 'location_00', 1, 3)
, ('2018-05-31 13:00:00-00', 'location_00', 4, 7)
, ('2018-05-31 18:00:00-00', 'location_00', 2, 3)
;

期望的结果是得到这个表结果：

我只有按天分组的当前查询小提琴，是否可以有日期然后是白天？

如果我正在运行查询，我有这张表：

CREATE TABLE IF NOT EXISTS public.preprocess_things
(
    preprocess_id integer NOT NULL DEFAULT nextval('preprocess_things_preprocess_id_seq'::regclass),
    arrive_date date NOT NULL,
    arrive_location character varying COLLATE pg_catalog."default" NOT NULL,
    data jsonb NOT NULL,
    CONSTRAINT preprocess_things_pkey PRIMARY KEY (preprocess_id),
    CONSTRAINT preprocess_things_arrive_date_arrive_location_bo_key UNIQUE (arrive_date, arrive_location)
)

我正在运行的查询是这样的：

SELECT DATE_TRUNC('month', arrive_date) AS grouped_date,
  LOWER(arrive_location) AS location,
  json_build_object(
    '1', SUM((data->'1')::int),
    '2', SUM((data->'2')::int),
    '3', SUM((data->'3')::int),
    '4', SUM((data->'4')::int),
    '5', SUM((data->'5')::int)
    ) AS data
FROM preprocess_things
GROUP BY grouped_date,
  location

目前的结果是：

我想应用另一个 SELECT，为每个没有空值的值对添加一行，其中键进入 thing_type 列，值进入总列；像这样：

小提琴数据库可以在这里找到。

更新：

感谢@Gerard H. Pille，我稍微调整了一下他对此的回应：

SELECT
  DATE_TRUNC('month', arrive_date) AS grouped_date,
  LOWER(arrive_location) AS location,
  x.key::int thing_type, sum(x.value::int) total
FROM preprocess_things
JOIN  jsonb_each(data) x ON (x.key::int = ANY('{1,2,3}'::int[]))
GROUP BY grouped_date,  location, x.key::int
ORDER BY grouped_date,  location, x.key::int;

由于在某些情况下，我只需要选择 thing_type 而不是全部计算，唯一的问题是通过添加 JOIN 并比较查询速度不是那么好。

我正在尝试使用 pg_stat_statements 来优化我的查询，但遇到了意外的障碍。

SELECT total_plan_time + total_exec_time as total_time, query
FROM pg_stat_statements
ORDER BY total_time
DESC LIMIT 10;

结果是 ERROR: invalid byte sequence for encoding "UTF8": 0x00

我对此进行了调查，似乎 Postgres 不喜欢文本字段中的 NULL 字符 \00。

大多数关于此错误的现有在线建议都是针对看到将数据插入 Postgres 错误的人们。在这种情况下，修复似乎是在插入之前过滤空字符。

在这种情况下，数据似乎已经在 postgres 中，但它使视图无法查询。

a\d+ pg_stat_statements告诉我 pg_stat_statements 视图是通过运行一个函数构建的，我试图使用 translate 和 replace 摆脱字符，但没有运气。

知道如何使用 NULL 字符找到有问题的查询吗？我假设长期修复是使用 NULL 字符跟踪查询，弄清楚它是如何进入的。

到目前为止我已经尝试过：

• 我通过执行SELECT pg_stat_statements_reset();. 上面的查询会在短时间内立即生效。

• 我做了一个\ef pg_stat_statements ，似乎第一个参数是一个名为 show text 的布尔值。传递 false 让我可以查询表，这让我可以这样做：

SELECT total_plan_time + total_exec_time as total_time, queryid
FROM pg_stat_statements(false)
ORDER BY total_time
DESC LIMIT 10;

不幸的是，queryid 不是很有用，除非我有办法在没有此错误的情况下检索映射的查询文本。

对如何进行的建议表示赞赏！我在 PostgreSQL 13.4

我正在寻找可以使用 psql 查询语句发送一些数据的方法。例如：

insert into mytable (id,name,site_id) values (default,'test',1);

mytable的真实结构：

id
name

在变量的帮助下，site_id我想传递它的值以便在触发器中使用它。在触发器中：

if new.site_id=1 then 
...
else
...

如果上面还有其他方法可以请您指教。

我正在编写一个函数，在某个步骤我有如下查询结果：

我必须把它翻译成这样：

少一行，并将累积值转换为某种“速率”，每行减去前一行并用前一个时间戳标记它。我怎么能做到这一点？

谢谢你的时间。

如何一次更新年龄和状态，

我有一个 jsonb 字段类型，我需要更新 2 个属性（年龄和状态）。我只能更新年龄和状态，使用下面的命令，如何一次更新“年龄”和“状态”？

**Update test Set attributes = jsonb_set(attributes, array['age'],to_jsonb(32))**

我有一个名为 attr 字段的 jsonb 类型，其中包含以下内容：

{ 
  "pid": 1,
  "name": "john",
  "is_default": true
 }

如何将 is_default 更改为 false？

我尝试在下面运行，但没有运气。

update attr set attr ->> 'is_default' = false where sales_type = 2