On Tue, Jan 19, 2010 at 12:06 AM, Johan Nel <johan.nel@xsinet.co.za> wrote:
> 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.
>
I appreciate everyone's response. My first option was to create a new
column but since doing so would probably break the app or at the very
least null my support with them, I chose against it. I am instead
just doing it in a script and retaining a position file that keeps
track of the most recent record that was dumped from the table. Easy
enough and it works.
