in reply to conditional backup in postgres

I'm not familar with postgreSQL database, but I am currently working with MySQL and have found the book: "Programming the Perl DBI" by Alligator Descartes and Tim Bunce to be quite helpful. The Perl DBI is fantastic and is easier to use than similar things in other languages.

What you are describing is not what I would call "backup". It sounds like you want to export and then remove that data from the main data base, with the option to re-import that data later. Basically, "prune" some old stuff out that isn't relevant anymore, but keep an archive copy so that it could be accessed later if the need arises.

Knowing Perl and how to use the Perl DBI isn't going to help you if you don't have a firm grasp of the postgreSQL tools available for this task. A serious point to consider is the format of the data for this "archived" copy. I mean if you need it 5 years from now, is it "going to work"?.

I did look on the web re: postgreSWL and they appear to have a lot of utilities for this sort of thing. "Pruning" out some data is a common task. It could very well be that Perl is not the main thrust of what you need to do/learn about. I would look seriously at the postgreSQL tools first.

So: Can you do this "manually" without Perl? What part of the problem do you think that Perl will help solve?

Replies are listed 'Best First'.
Re^2: conditional backup in postgres
by suhailck (Friar) on Jul 16, 2010 at 10:28 UTC
    Yes agree.
    I think i cannot achieve this task using pg_dump.
    Another way i thought of is,

    Dump

    create table c_order_A as (select * from c_order where period=2009 and org <> 'A')
    pg_dump -T c_order MyDB > dumpfile
    # -T exclude table c_order
    delete from c_order where period=2009 and org <> 'A';
    delete from c_order_A;
    drop table c_order_A;

    Restore

    psql MyDB < dumpfile
    insert into c_order (select * from c_order_A);
    delete from c_order_A;
    drop table c_order_A;

[reply]

In Section Seekers of Perl Wisdom