Re: data dump help

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.