Terry wrote:
Hello,
Sorry for the poor subject. Not sure how to describe what I need
here. I have an application that logs to a single table in pgsql.
In order for me to get into our log management, I need to dump it out
to a file on a periodic basis to get new logs. I am not sure how to
tackle this. I thought about doing a date calculation and just
grabbing the previous 6 hours of logs and writing that to a new log
file and setting up a rotation like that. Unfortunately, the log
management solution can't go into pgsql directly. Thoughts?
You do not indicate in your post, exactly how the data is stored, but I
would assume there is a timestamp inside this single table.
From my perspective there are 3 options available:
Firstly, create a table that you can monitor when you have made dumps,
typically with a column that will store a datetimestamp with now() in it.
1. You have access to the DB and you can schedule a pgAgent job to run
every 6 hours that dumps the table into some usable format e.g. csv:
SELECT Max(dumptimestamp) FROM dump_log INTO lastdump;
currtime := now();
COPY (SELECT * FROM singletable WHERE timestamp > lastdump) TO
'someexternaltable' DELIMETER ',' CSV HEADER ...;
INSERT INTO dumplog (dumptimestamp) VALUES (currtime);
2. Same as above but run this as a trigger on your dumplog table when you
need a dump by inserting the current_datetime into the dumplog table
that will trigger a process to export the data.
3. You have an application that have an option to insert the current
datetimestamp into your dumplog table and then read the exported table
after completion.
HTH,
Johan Nel
Pretoria, South Africa.
--
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
