suhailck has asked for the wisdom of the Perl Monks concerning the following question:

Hi,

I'm not sure it is right to ask this question here.
Some one here might have faced similar situation.
I need to take backup of my postgresql DB, but not all records from a few tables.
My DB contains more than 700 tables, out of which 15 tables i need to take this conditional backup

Example:
Table: C_order
I need to take backup from table C_order where "period=2009" and "org <> A"
This is done to improve performance there by deleting all non-org records for old period from org A's server
If this possible, i want to write the utilty to backup and restore in Perl modules.
Expecting ur feedbacks

Thanks in advance!!!

Replies are listed 'Best First'.
Re: conditional backup in postgres
by Marshall (Canon) on Jul 16, 2010 at 06:47 UTC
    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?

[reply]
      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]
Re: conditional backup in postgres
by sflitman (Hermit) on Jul 16, 2010 at 08:53 UTC
    I work a lot in postgres and I find pg_dump to be a useful command, since I can call it in a cronjob.

    http://www.postgresql.org/docs/current/static/app-pgdump.html

    I don't think it accepts the Where clause.

    An alternative would be to run a command like this: 

    psql mydb -c "select * from C_order where period=2009 and org<>'A'"

    [download]
    And then you'll have output you could save. Look up the psql command's options, it will probably let you make the output more useful for your backup policy.

    HTH,
    SSF

[reply]
[d/l]
Re: conditional backup in postgres
by suhailck (Friar) on Jul 16, 2010 at 14:39 UTC
    We can also use "COPY" to export and import tables from and to database.

    Export

    copy (select * from c_order where period=2009 and org <> 'A') to '/full/path/filename';

    Import

    copy c_order from 'full/path/filename';

[reply]

Back to Seekers of Perl Wisdom