What is the best way to automatically create a snapshot of the table at the certain time?

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?

I'd use either of pg_dump or create table foo_log as select * from foo

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?

Sounds good too. That works with rules too, e.g.:

create rule as on insert to yourtable_log__ins
do also
insert to yourlogs select new.*;

And then, daily:

begin;
create table yourlogs_[date] () inherits (yourlogs);
create or replace rule yourlogs__ins as on insert to yourlogs
do instead
insert into yourlogs_[date] select new.*;
commit;

The main difference with creating the table on the fly on a daily basis will be the overhead that you'll be introducing on every insert/update, as opposed to grabbing the updated rows all in one go.

StackExchange Database Administrators Q#3225, answer score: 5