postgresql deleteing old tables
- by BB
I have a postgresql database which stores my radius connection information. What I want to do is only store a months worth of logs. How would I craft a sql statement that I can run from cron that would go and delete and rows that where older then a month?
Format of the date in the table. that date is taken from acctstoptime collum
Date format 2010-01-27 16:02:17-05
Format of the table in question.
-- Table: radacct
-- DROP 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
-- DROP INDEX freesidestatus;
CREATE INDEX freesidestatus
ON radacct
USING btree
(freesidestatus);
-- Index: radacct_active_user_idx
-- DROP 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
-- DROP INDEX radacct_start_user_idx;
CREATE INDEX radacct_start_user_idx
ON radacct
USING btree
(acctstarttime, username);
