PostgreSQL deleteing old records from log tables

databasepostgresqlsql

I have a PostgreSQL database which stores my RADIUS connection information. What I want to do is only store a month worth of logs. How would I craft an SQL statement that I can run from cron that would go and delete and rows that where older than a month?

Format of the date in the table. That date is taken from the acctstoptime colunm.
Date format 2010-01-27 16:02:17-05

Format of the table in question.
— Table: 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);

Best Answer

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

to get rid of the old data.

REINDEX TABLE radacct;

to rebuild the indexes (otherwise you may wind up with index bloat).

If you're running an old version of Postgres you may want to VACUUM the table periodically.
If the table is huge do a VACUMM FULL ANALYZE radacct the first time you dump old data (or dump/reload the table) to reclaim space & update the query planner.