PG\u51e0\u79cd\u5b9a\u65f6\u4efb\u52a1\u5b9e\u73b0\u65b9\u5f0f\u5bf9\u6bd4\uff1ahttps:\/\/blog.csdn.net\/weixin_73350116\/article\/details\/134284090<\/p>\n\n\n\n
pg_cron\u5b98\u7f51\uff1ahttps:\/\/github.com\/citusdata\/pg_cron<\/p>\n\n\n\n
sudo apt-get -y install postgresql-16-cron
\u4fee\u6539postgresql.conf\uff1a
\/etc\/postgresql\/16\/main$ :chmod 666 postgresql.conf
\/etc\/postgresql\/16\/main$ :vim postgresql.conf
shared_preload_libraries = 'pg_cron'
cron.database_name = 'postgres'
cron.timezone = 'PRC'
cron.use_background_workers = on
sudo systemctl restart postgresql
-- run as superuser:
CREATE EXTENSION pg_cron;
-- optionally, grant usage to regular users:
GRANT USAGE ON SCHEMA cron TO log;
SELECT cron.schedule('part log', '25 14 * * *', 'select auto_log_partition();');
select * from cron.job
update cron.job set database='XX'
DECLARE
table_name text ; -- \u8868
curMM varchar(6); -- 'YYYYMM'\u5b57\u4e32,\u7528\u505a\u5206\u533a\u5b50\u8868\u7684\u540e\u7f00
isExist boolean; -- \u5206\u533a\u5b50\u8868,\u662f\u5426\u5df2\u5b58\u5728
startTime text;
endTime text;
strSQL text;
BEGIN
table_name := 'sys_log';
-- \u5224\u65ad\u5bf9\u5e94\u5206\u533a\u8868 \u662f\u5426\u5df2\u7ecf\u5b58\u5728?
curMM := to_char( now() , 'YYYYMM' );
select count(*) INTO isExist from pg_class where relname = (table_name||'_'||curMM);
--raise warning 'rkind%',rkind;
IF ( isExist = false ) THEN
-- \u521b\u5efa\u5b50\u5206\u533a\u8868
startTime := curMM||'01 00:00:00.000';
endTime := to_char( startTime::timestamp + interval '1 month', 'YYYY-MM-DD HH24:MI:SS.MS');
strSQL := 'CREATE TABLE IF NOT EXISTS '||table_name||'_'||curMM||
' PARTITION OF '||table_name||' FOR VALUES FROM ('''|| startTime ||''') TO ('''|| endTime ||''' ) ;';
EXECUTE strSQL;
END IF;
<\/pre>\n","protected":false},"excerpt":{"rendered":"PG\u51e0\u79cd\u5b9a\u65f6\u4efb\u52a1\u5b9e\u73b0\u65b9\u5f0f\u5bf9\u6bd4\uff1ahttps […]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"site-sidebar-layout":"default","site-content-layout":"","ast-site-content-layout":"","site-content-style":"default","site-sidebar-style":"default","ast-global-header-display":"","ast-banner-title-visibility":"","ast-main-header-display":"","ast-hfb-above-header-display":"","ast-hfb-below-header-display":"","ast-hfb-mobile-header-display":"","site-post-title":"","ast-breadcrumbs-content":"","ast-featured-img":"","footer-sml-layout":"","theme-transparent-header-meta":"","adv-header-id-meta":"","stick-header-meta":"","header-above-stick-meta":"","header-main-stick-meta":"","header-below-stick-meta":"","astra-migrate-meta-layouts":"default","ast-page-background-enabled":"default","ast-page-background-meta":{"desktop":{"background-color":"var(--ast-global-color-4)","background-image":"","background-repeat":"repeat","background-position":"center center","background-size":"auto","background-attachment":"scroll","background-type":"","background-media":"","overlay-type":"","overlay-color":"","overlay-gradient":""},"tablet":{"background-color":"","background-image":"","background-repeat":"repeat","background-position":"center center","background-size":"auto","background-attachment":"scroll","background-type":"","background-media":"","overlay-type":"","overlay-color":"","overlay-gradient":""},"mobile":{"background-color":"","background-image":"","background-repeat":"repeat","background-position":"center center","background-size":"auto","background-attachment":"scroll","background-type":"","background-media":"","overlay-type":"","overlay-color":"","overlay-gradient":""}},"ast-content-background-meta":{"desktop":{"background-color":"var(--ast-global-color-5)","background-image":"","background-repeat":"repeat","background-position":"center center","background-size":"auto","background-attachment":"scroll","background-type":"","background-media":"","overlay-type":"","overlay-color":"","overlay-gradient":""},"tablet":{"background-color":"var(--ast-global-color-5)","background-image":"","background-repeat":"repeat","background-position":"center center","background-size":"auto","background-attachment":"scroll","background-type":"","background-media":"","overlay-type":"","overlay-color":"","overlay-gradient":""},"mobile":{"background-color":"var(--ast-global-color-5)","background-image":"","background-repeat":"repeat","background-position":"center center","background-size":"auto","background-attachment":"scroll","background-type":"","background-media":"","overlay-type":"","overlay-color":"","overlay-gradient":""}},"footnotes":""},"categories":[1],"tags":[],"_links":{"self":[{"href":"http:\/\/www.bigqq.com\/index.php?rest_route=\/wp\/v2\/posts\/165"}],"collection":[{"href":"http:\/\/www.bigqq.com\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"http:\/\/www.bigqq.com\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"http:\/\/www.bigqq.com\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"http:\/\/www.bigqq.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=165"}],"version-history":[{"count":2,"href":"http:\/\/www.bigqq.com\/index.php?rest_route=\/wp\/v2\/posts\/165\/revisions"}],"predecessor-version":[{"id":244,"href":"http:\/\/www.bigqq.com\/index.php?rest_route=\/wp\/v2\/posts\/165\/revisions\/244"}],"wp:attachment":[{"href":"http:\/\/www.bigqq.com\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=165"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"http:\/\/www.bigqq.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=165"},{"taxonomy":"post_tag","embeddable":true,"href":"http:\/\/www.bigqq.com\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=165"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}