Обсуждение: No subject
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. The idea is to periodicaly (e.g. every two minutes) store the newly executedqueries in a sort of wal segment file. I first tried to figure this out by monitoring the data in the wal file thatis currently being updated (in the directory .../pg_xlogs). I hoped that it would be blank (filled with zeroes) fromthe last wal info inserted up to the end of the file. That was not the case. The wal files are recycled and the new walinfo is written over the old one. Another possible solution would be to configure the server to 'cycle' through the wal files every X seconds and not whenthe file is full (default 16MB) but i don't know if such a configuration parameter is available in the server functionality. Finally, I also thought about placing a trigger in every table's update but this is far too complicated and cannot be appliedto already functional database applications. Any help appreciated Tsakiridhs Oresths P.S. It is very important that wal-like log files that are generated, in case of execution, will reproduce the database exactly the way it was.
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