Tsakiridis Orestis wrote:
> Hi,
>
> I'm looking for a way to monitor all the queries that are executed by
> the server and to be able, by using these queries, to reproduce the
> data in all databases.
Beware - query shipping runs into problems with
DELETE FROM my_table WHERE ts < now()
> The idea is to periodicaly (e.g. every two
> minutes) store the newly executed queries in a sort of wal segment
> file. I first tried to figure this out by monitoring the data in the
> wal file that is currently being updated (in the directory
> .../pg_xlogs). I hoped that it would be blank (filled with zeroes)
> from the last wal info inserted up to the end of the file. That was
> not the case. The wal files are recycled and the new wal info is
> written over the old one.
If the current PITR isn't good enough for you:
http://www.postgresql.org/docs/8.1/static/backup.html
Check that the changes in 8.2 beta aren't helpful
http://www.postgresql.org/developer/beta
> Finally, I also thought about placing a trigger in every table's
> update but this is far too complicated and cannot be applied to
> already functional database applications.
Have you looked at slony?
http://gborg.postgresql.org/project/slony1/projdisplay.php
The word you want to search on for all this is "replication".
--
Richard Huxton
Archonet Ltd