Tell me more ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.
up vote 2 down vote favorite

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?

share|improve this question
I believe what the OP is looking for is PITR (Point-in time recovery). It's not super hard to set up, and once you get it rolling it's a breeze to use the backups to recover up to any point in time. – user9957 Jul 5 '12 at 20:51

2 Answers

up vote 3 down vote

If you are running a mixed workload (OLTP on the table and analysis on the snapshot), you could consider replication and a hot-standby

This reduces the impact on the heavily loaded server to 0 - you need to be on 9.0 though...

share|improve this answer
I use 9.0.4. But as far as I understood hot-standby is more for recovery cases and not for snapshotting. I need robust snapshoting solution. – MinimeDJ Jun 8 '11 at 10:29
It depends exactly what you need. If you are taking snapshots each night and you need to keep them all then hot-standby is not for you. If you just need the latest snapshot you could halt the streaming at midnight and then start it up again after everyone has gone home - but I was taking a guess that the real purpose was to reduce the load: what are you using the snapshots for? – Jack Douglas♦ Jun 8 '11 at 10:43
Thank for suggestion. Won't work for me - my DB serves people all over the World. They work with my app from home too. So I cannot rely on "everyones drinks beer". I think idea with triggers I wrote about will work for me. – MinimeDJ Jun 8 '11 at 10:50
Are you indexing on insert date? A nightly create table foo_20110607 as select * from foo where insert_date='2011-06-07 would not put the same load on your table than triggers would... – Jack Douglas♦ Jun 8 '11 at 10:57
No, I am not indexing data. It is just transactions, so - no need for index in there. – MinimeDJ Jun 8 '11 at 16:27
show 1 more comment
up vote 3 down vote

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.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.