Re: data dump help

On Tue, Jan 19, 2010 at 12:06 AM, Johan Nel <johan(dot)nel(at)xsinet(dot)co(dot)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.