PostgreSQL插件(1): pg_timeout及pg_timetable 及 若干FAQ(1)
PostgreSQL 2023-05-12 09:20 发表于河北编者荐语：
报考PG数据库专家 上盘古云课堂
以下文章来源于数据库杂记 ，作者SeanHe
1、前言
这次将简单介绍PG的两个插件，以及若干常见问题汇集，作为“福利”附送。这两个插件分别是：pg_timeout以及pg_timetable。
pg_timeout主要是解决PG14的配置参数：idle_session_timeout在早期版本不支持的问题，于是引入了这个插件。
pg_timetable提供的是类似于crontab或schedule job的功能。
2. pg_timetable
网站介绍：
https://pg-timetable.readthedocs.io/en/master/README.html
下载安装：
https://github.com/cybertec-postgresql/pg_timetable/releases
取：
https://github.com/cybertec-postgresql/pg_timetable/releases/download/v5.3.0/pg_timetable_5.3.0_Linux_i386.rpm
使用rpm直接安装。它实质上是一个独立的客户端程序。安装完以后即可启动这个进程：
[20:09:45-postgres@centos1:/pgccc/soft]$ pg_timetable -c timetable -u demo --password=test123 -d demo -p 5555
2023-03-16 20:09:50.407 [INFO] [sid:1651496265] Starting new session...
2023-03-16 20:09:50.423 [INFO] Database connection established
2023-03-16 20:09:50.424 [INFO] Executing script: Schema Init
2023-03-16 20:09:50.434 [INFO] Schema file executed: Schema Init
2023-03-16 20:09:50.434 [INFO] Executing script: Cron Functions
2023-03-16 20:09:50.453 [INFO] Schema file executed: Cron Functions
2023-03-16 20:09:50.453 [INFO] Executing script: Tables and Views
2023-03-16 20:09:50.502 [INFO] Schema file executed: Tables and Views
2023-03-16 20:09:50.502 [INFO] Executing script: JSON Schema
2023-03-16 20:09:50.508 [INFO] Schema file executed: JSON Schema
2023-03-16 20:09:50.509 [INFO] Executing script: Job Functions
2023-03-16 20:09:50.513 [INFO] Schema file executed: Job Functions
2023-03-16 20:09:50.513 [INFO] Configuration schema created...
2023-03-16 20:09:50.515 [INFO] Accepting asynchronous chains execution requests...
2023-03-16 20:09:50.517 [INFO] [count:0] Retrieve scheduled chains to run @reboot
2023-03-16 20:09:50.520 [INFO] [count:0] Retrieve interval chains to run
2023-03-16 20:09:50.557 [INFO] [count:0] Retrieve scheduled chains to run
我们来看看这个命令行：
pg_timetable -c timetable -u demo --password=test123 -d demo -p 5555
-
-c 指定客户端程序名 -
-u 用户名 -
--password 密码 -
-d 数据库名 -
-p 连接的端口号
启动这个进程以后，数据库demo里头就有timetable这个schema。我们就用一个简单的例子来验证它的功能。例子来源于源网站。
SELECT timetable.add_job(
job_name => 'notify every minute',
job_schedule => '* * * * *',
job_command => 'SELECT pg_notify($1, $2)',
job_parameters => '[ "TT_CHANNEL", "Ahoj from SQL base task" ]' :: jsonb,
job_kind => 'SQL'::timetable.command_kind,
job_client_name => NULL,
job_max_instances => 1,
job_live => TRUE,
job_self_destruct => FALSE,
job_ignore_errors => TRUE
) as chain_id;
执行完这个之后，就有一个job: notify every minute, 会每隔一分钟执行一次。主要就是往"tt_channel"上发送一则通知。
删除这个job:
demo=# select timetable.delete_job('notify every minute');
delete_job
------------
t
(1 row)
弄一个简单点儿的：
准备一张表：
demo=# create table t(id int);
CREATE TABLE
创建job:demo=# SELECT timetable.add_job('insert_every_minute', '* * * * *', ' INSERT INTO t values(100*random()::int)');
add_job
---------
3
(1 row)
监测该表的值：
demo=# select count(*) from t;
count
-------
1
(1 row)
demo=# \watch 30
Thu 16 Mar 2023 01:43:41 PM UTC (every 30s)
count
-------
1
(1 row)
Thu 16 Mar 2023 01:44:41 PM UTC (every 30s)
count
-------
2
(1 row)
我们通过30秒一次的监测，可以发现表的记录数在增长。从而证明那个job一
直在运行。
3. pg_timeout
下载地址：https://github.com/pierreforstmann/pg_timeout
安装：
git clone https://github.com/pierreforstmann/pg_timeout
cd pg_timeout
make
sudo make install
这个插件支持的版本：This extension has been validated with PostgresSQL 9.5, 9.6, 10, 11, 12, 13, 14 and 15.
pg_timeout的使用：
1) 配置文件，postgresql.conf，添加： shared_preload_libraries = 'pg_timeout'
2) 两个参数:
pg_timeout.naptime: number of seconds for the dedicated backgroud worker to sleep between idle session checks (default value is 10 seconds)
pg_timeout.idle_session_timeout: database session idle timeout in seconds (default value is 60 seconds)
重点看第2个参数：session的idle时间，默认是60秒。
我们来模拟验证一下。先配好上述参数。vi postgresql.conf, 将超时设为10秒。
shared_preload_libraries = 'pg_timeout'
pg_timeout.idle_session_timeout = 10
pg_timeout.so安装的位置可能不对，将其复制到正确的位置：
sudo cp /usr/lib64/pgsql/pg_timeout.so /usr/pgsql-14/lib/
重启PG。
我们如果查看一下日志文件：
2023-03-16 14:17:36.546 UTC [19643] LOG: pg_timeout_worker initialized
2023-03-16 14:18:06.577 UTC [19643] LOG: pg_timeout_worker: idle session PID=19649 user=postgres database=postgres application=psql hostname=NULL
2023-03-16 14:18:06.578 UTC [19643] LOG: pg_timeout_worker: idle session(s) since 10 seconds terminated
2023-03-16 14:18:06.578 UTC [19649] FATAL: terminating connection due to administrator command
能看到有connection因为idle超时而关闭。
[14:17:[email protected]:/var/lib/pgsql/14/data]$ psql
psql (14.5)
Type "help" for help.
postgres=# select 1;
?column?
----------
1
(1 row)
postgres=# select 1;
FATAL: terminating connection due to administrator command
server closed the connection unexpectedly
This probably means the server terminated abnormally
before or while processing the request.
The connection to the server was lost. Attempting reset: Succeeded.
这个插件对于session 的 idle timeout管理很实用。
[ 3、FAQ ]
1) 我用的是老版本PG11, 想实现session idle timeout， 怎么破？
>>: 就用上边的pg_timeout插件好了. 另一种方案是xiongcc老师提出的，自己手写一个脚本。发现连接数满了，定期查杀。我们可以在后续的文章里找机会介绍。
2) 关于系统日志文件，总共有50来个G。太多了，删之。我不小心全给清掉了。然后，它半天不再出新的log。我又不能重启系统。怎么让它重新冒出来。
>>: 直接用函数pg_rotate_logfile()，让它生成
postgres=# select pg_rotate_logfile();
pg_rotate_logfile
-------------------
t
(1 row)
[14:22:52-postgres@sean-rh1.:/var/lib/pgsql/14/data/log]$ ls
[14:26:08-postgres@sean-rh1.:/var/lib/pgsql/14/data/log]$ ls
[14:26:10-postgres@sean-rh1.:/var/lib/pgsql/14/data/log]$ ls
postgresql-Thu.log
一个purge的shell, 只清理几天以前或几个小时以前的log, 那样就不会误删了。如, 清除1天前的：
find . -mtime +1 -type f -exec rm {} \;
3) 请教一下，.pgpass都是明文密码，好像没有密码文件加密特性？
>>: 没有。600的权限很重要。
密码文件，用于存放用户登陆信息，格式为hostnam在Unixe:port:database:username:password系统，该文件权限需为 0600，否则会被忽略.
如果文件权限大于 0600，当连接数据库时，会触发WARN 警告。且该文件存储的是明文密码，从安全角度来讲，并不建议使用.
4) 我怎么构造PG中的一张表，每个页面只保存一条记录？
这个问题，确实需要点儿逆向思维。我在想，莫不是问问题的人，确实在实际案例中碰到了这种情形。如果是Sybase ASE数据库，那很简单，默认的LOB数据存到index页上，你就是存一个字节，它都能额外给你搞出一个页来。
再看看PG，以默认的8K大小为例。要想让一行记录占据一页，一个直觉的思路是，尽量的占满，还不能让它被TOAST分去。
再回想一下TOAST有几种存储策略，用于变长数据的处理。
plain 指TOAST不被使用（该策略适用于短的数据类型，如integer类型）
extended 允许压缩属性， 同时将它们存到单独的TOAST表中
external 暗示长属性存到TOAST表中，以非压缩的方式存储
main 要求长属性先要压缩；只有压缩也不起作用的情况下，才会移到TOAST表。
上边是一个找出某表当中所有字段的对应的TOAST策略：
mydb=# create table t(id int, col2 char(5120) not null);CREATE TABLE
mydb=# SELECT attname, atttypid::regtype,
CASE attstorage
WHEN 'p' THEN 'plain'
WHEN 'e' THEN 'external'
WHEN 'm' THEN 'main'
WHEN 'x' THEN 'extended'
END AS storage
FROM pg_attribute
WHERE attrelid = 't'::regclass AND attnum > 0;
attname | atttypid | storage
---------+-----------+----------
id | integer | plain
col2 | character | extended
(2 rows)
那么我们得相办法不让它被TOAST，同时至少要占据半页以上的空间，那样的话，再插入一条新记录，它必须开辟一个新页来容纳新记录。
先改一下存储策略，针对字段col2:
mydb=# ALTER TABLE t ALTER COLUMN col2 SET STORAGE plain;
ALTER TABLE
造几行值(并且让它无法压缩)，试着看一下：
我们用前边介绍的随机生成字符串函数：
CREATE OR REPLACE FUNCTION random_string( int ) RETURNS TEXT as $$
SELECT string_agg(substring('abcdefghijklmnopqrstuvwxyz', round(random() * 25 + 0.5)::integer, 1), '')
FROM generate_series(1, $1);
$$ language sql;
我们看看插入10行以及10行以后的元组分布：(ctid值），果然是每行占据一个页面。(0,1)一直到(9,1)。每页只有一个元组。
mydb=# insert into t values(1, random_string(5120));
INSERT 0 1
mydb=# insert into t select generate_series(2,10), random_string(5120);
INSERT 0 9
mydb=# select tableoid, ctid, xmin,xmax, cmin, cmax, id from t;
tableoid | ctid | xmin | xmax | cmin | cmax | id
----------+-------+--------+------+------+------+----
25091 | (0,1) | 401104 | 0 | 0 | 0 | 1
25091 | (1,1) | 401105 | 0 | 0 | 0 | 2
25091 | (2,1) | 401105 | 0 | 0 | 0 | 3
25091 | (3,1) | 401105 | 0 | 0 | 0 | 4
25091 | (4,1) | 401105 | 0 | 0 | 0 | 5
25091 | (5,1) | 401105 | 0 | 0 | 0 | 6
25091 | (6,1) | 401105 | 0 | 0 | 0 | 7
25091 | (7,1) | 401105 | 0 | 0 | 0 | 8
25091 | (8,1) | 401105 | 0 | 0 | 0 | 9
25091 | (9,1) | 401105 | 0 | 0 | 0 | 10
(10 rows)
再用常见的两个plugin/extension pgstattutple和pg_freespacemap来印证一下：
mydb=# create extension pgstattuple;
CREATE EXTENSION
mydb=# create extension pg_freespacemap;
CREATE EXTENSION
mydb=# select * from pg_freespace('t'::regclass);
blkno | avail
-------+-------
0 | 3008
1 | 3008
2 | 3008
3 | 3008
4 | 3008
5 | 3008
6 | 3008
7 | 3008
8 | 3008
9 | 0
(10 rows)-- 用freespacemap，找到所有的10个块（页的信息)
mydb=# select * from pg_relpages('t');
pg_relpages
-------------
10
(1 row)-- 用pgstattuple得到relpage的总数
当然，我们也可以用pg_class得到一些统计信息，前提是要让统计信息是及时的。
mydb=# select * from pg_class where relname = 't'\gx
-[ RECORD 1 ]-------+-------
oid | 25091
relname | t
relnamespace | 2200
reltype | 25093
reloftype | 0
relowner | 10
relam | 2
relfilenode | 25091
reltablespace | 0
relpages | 0
reltuples | -1
-- 第一次，relpages值是0
-- 更新一下统计信息
mydb=# vacuum analyze t;
VACUUM
-- 再查一次
mydb=# select * from pg_class where relname = 't'\gx
-[ RECORD 1 ]-------+-------
oid | 25091
relname | t
relnamespace | 2200
reltype | 25093
reloftype | 0
relowner | 10
relam | 2
relfilenode | 25091
reltablespace | 0
relpages | 10
reltuples | 10
relallvisible | 10
reltoastrelid | 25094
relhasindex | f
relisshared | f
-- 这下结果是对的
试验到这里，补一个问题，mydb=# create index idx_t on t(col2); 这个语句会执行成功吗？有兴趣的朋友可以自己试一下。
5) 请教下 为什么在函数中set statement_timeount 不生效呢？服务器级别设置为3h， 函数中设置为10ms，查询显示set成功， 但是函数内sql不会超时?
可以换一种思路：
show statement_timeout;
create or replace function mock_query()
returns integer as
$$
declare res int;
begin
perform pg_sleep(1);
select 1 into res;
return res;
end;
$$
language plpgsql;
begin;
set local statement_timeout to '10ms';
select mock_query();
end;
同时也要提示一下刚刚开始在PG中使用function/proc的朋友们，一定要注意不要在function中滥用事务，那样很容易出问题。从11开始，可以在proc中调用事务。但是function中还是不行。
标签：03,插件,PostgreSQL,16,job,pg,timeout,timetable From： https://www.cnblogs.com/chuangsi/p/17396847.html