#1
  1. No Profile Picture
    Junior Member
    Devshed Newbie (0 - 499 posts)

    Join Date
    Mar 2003
    Posts
    1
    Rep Power
    0

    Data transfer between databases in PostgreSQL


    Can anyone suggest a good method to perform data transfer frequently (let us say every 10 minutes) between 2 databases in PostgreSQL (on Linux) on the same machine ?

    Thanks in advance for your suggestions.
  2. #2
  3. Second highest poster :p
    Devshed Supreme Being (6500+ posts)

    Join Date
    Jul 2001
    Posts
    7,322
    Rep Power
    33
    the only way i can think of doing this is using a sql dump and then running the sql in the other db. dont know how efficient it would be and i dont think i would like it to be running every 10 minutes
    - Andreas Koepke

    Koepke Photography

  4. #3
  5. No Profile Picture
    Gödelian monster
    Devshed Regular (2000 - 2499 posts)

    Join Date
    Jul 1999
    Location
    Central Florida, USA
    Posts
    2,307
    Rep Power
    62
    The cheap and easy way is to automate pg_dump and restore between the two, which should work fine as long as your integrity rules are properly thought out and enforced on the database. But this is not as efficient or safe as full replication:

    According to the PGReplication project, replication is now available for PostgreSQL 7.2, although it is still in development. It should be available for 7.3 soon.
    The real n-tier system:

    FreeBSD -> PostgreSQL -> [any_language] -> Apache -> Mozilla/XUL

    Amazon wishlist -- rycamor (at) gmail.com
  6. #4
  7. No Profile Picture
    Junior Member
    Devshed Newbie (0 - 499 posts)

    Join Date
    Sep 2003
    Posts
    3
    Rep Power
    0
    Would this work with one table having more columns than the other ?
  8. #5
  9. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jun 2003
    Posts
    373
    Rep Power
    12
    Originally posted by laguy
    Would this work with one table having more columns than the other ?
    not with just a pg_dump of the data and restore; you could write a PL/pgSQL function that would do it when you ran it, or most easily you can just have a trigger that would always insert into the other table when there was an insert on the first one but that would also be a PL/pgSQL function

IMN logo majestic logo threadwatch logo seochat tools logo