On Tue, Jan 19, 2010 at 12:06 AM, Johan Nel <johan.nel@xxxxxxxxxxxx> 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. -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
