☰

PostgreSQL 从日志表中删除旧记录

tag-icon • tag-icon database sql postgresql
PostgreSQL 从日志表中删除旧记录

我有一个 PostgreSQL 数据库，用于存储我的半径连接信息。我想要做的只是存储一个月的日志。我该如何编写一个可以从 cron 运行的 SQL 语句来删除一个月之前的行？

表中日期的格式。该日期取自 acctstoptime 列。日期格式 2010-01-27 16:02:17-05

所讨论表格的格式。--表格：radacct

CREATE TABLE radacct
(
  radacctid bigserial NOT NULL,
  acctsessionid character varying(32) NOT NULL,
  acctuniqueid character varying(32) NOT NULL,
  username character varying(253),
  groupname character varying(253),
  realm character varying(64),
  nasipaddress inet NOT NULL,
  nasportid character varying(15),
  nasporttype character varying(32),
  acctstarttime timestamp with time zone,
  acctstoptime timestamp with time zone,
  acctsessiontime bigint,
  acctauthentic character varying(32),
  connectinfo_start character varying(50),
  connectinfo_stop character varying(50),
  acctinputoctets bigint,
  acctoutputoctets bigint,
  calledstationid character varying(50),
  callingstationid character varying(50),
  acctterminatecause character varying(32),
  servicetype character varying(32),
  xascendsessionsvrkey character varying(10),
  framedprotocol character varying(32),
  framedipaddress inet,
  acctstartdelay integer,
  acctstopdelay integer,
  freesidestatus character varying(32),
  CONSTRAINT radacct_pkey PRIMARY KEY (radacctid)
)
WITH (OIDS=FALSE);
ALTER TABLE radacct OWNER TO radius;

-- Index: freesidestatus

CREATE INDEX freesidestatus
  ON radacct
  USING btree
  (freesidestatus);

-- Index: radacct_active_user_idx

CREATE INDEX radacct_active_user_idx
  ON radacct
  USING btree
  (username, nasipaddress, acctsessionid)
  WHERE acctstoptime IS NULL;

-- Index: radacct_start_user_idx

CREATE INDEX radacct_start_user_idx
  ON radacct
  USING btree
  (acctstarttime, username);

答案1

DELETE FROM radacct WHERE acctstoptime < (now() - '30 days'::interval);

删除旧数据。

REINDEX TABLE radacct;

重建索引（否则可能会导致索引膨胀）。

如果您运行的是旧版本的 Postgres，您可能需要VACUUM定期更新表。
如果表很大，请在VACUMM FULL ANALYZE radacct第一次转储旧数据（或转储/重新加载表）时执行此操作以回收空间并更新查询计划器。

答案2

另外，考虑使用约束排除。然后您可以轻松删除旧分区。它还将节省大量的真空负载。

相关内容