I have running and quite loaded PostgreSQL server. I need to take snapshots of the certain tables at the certain time (midnight). Those tables are pretty loaded (a lot of updates and inserts).
What is the best way to do it?
Edited: Table that I have to snapshot has about 50.000.000 records. Only 5-20% of them are changing over the day.
Added: Actually I have one idea to add insert/update trigger for each table that should be shapshoted and write all changes to another table that is partitioned on daily basis.
Please tell me if it is a god idea or not?