Synchronize unicode data using copy or pg_dump

Collapse
This topic is closed.
X
X
Collapse
 
  • Page of 1
  • Time
  • Show
Clear All
new posts
Previous template Next
  • Pascal Polleunus
    #1

    Synchronize unicode data using copy or pg_dump

    Hi,

    I need to synchronize some tables from a database (master) to another
    one (slave).
    Both servers are running Debian Woody with PostgreSQL 7.2.1 (postgresql
    7.2.1-2woody4).
    The databases are in unicode and doesn't contain any binary data.
    The tables have primary/foreign key constraints, sequences and indexes,
    but no triggers/rules. There are OIDs but these are different on the 2
    databases. In fact they are not used by the application.

    I need to do that ASAP, and hopefully only once (there are already some
    kind of home-made replication scripts, that are not the origin of my
    problem).
    Later I would like to set up pgreplication or something... any advice on
    that is welcomed too ;-)


    What would be the fastest way to achieve that?

    On the master:
    For each table, use COPY TO or pg_dump?

    On the slave:
    - delete everything from these tables
    - drop the FK constraints
    - (drop indexes?)
    - vacuum (or later?)
    - import from files (using COPY FROM or pg_restore)
    - recreate the FK constraints
    - update the sequences
    - reindex (or recreate them)

    Does that sound good?


    Thanks for you help,
    Pascal Polleunus



    ---------------------------(end of broadcast)---------------------------
    TIP 9: the planner will ignore your desire to choose an index scan if your
    joining column's datatypes do not match

    Tags: None
    • Pascal Polleunus
      #2
      Re: Synchronize unicode data using copy or pg_dump

      Pascal Polleunus wrote:
      [color=blue]
      > Hi,
      >
      > I need to synchronize some tables from a database (master) to another
      > one (slave).
      > Both servers are running Debian Woody with PostgreSQL 7.2.1 (postgresql
      > 7.2.1-2woody4).
      > The databases are in unicode and doesn't contain any binary data.
      > The tables have primary/foreign key constraints, sequences and indexes,
      > but no triggers/rules. There are OIDs but these are different on the 2
      > databases. In fact they are not used by the application.
      >
      > I need to do that ASAP, and hopefully only once (there are already some
      > kind of home-made replication scripts, that are not the origin of my
      > problem).
      > Later I would like to set up pgreplication or something... any advice on
      > that is welcomed too ;-)
      >
      >
      > What would be the fastest way to achieve that?[/color]

      Wouldn't it be easier to copy directly the files from the filesystem?
      Are there particular precautions to take before doing that (except
      stopping pg)?


      [color=blue]
      > On the master:
      > For each table, use COPY TO or pg_dump?
      >
      > On the slave:
      > - delete everything from these tables
      > - drop the FK constraints
      > - (drop indexes?)
      > - vacuum (or later?)
      > - import from files (using COPY FROM or pg_restore)
      > - recreate the FK constraints
      > - update the sequences
      > - reindex (or recreate them)
      >
      > Does that sound good?
      >
      >
      > Thanks for you help,
      > Pascal Polleunus
      >
      >[/color]



      ---------------------------(end of broadcast)---------------------------
      TIP 8: explain analyze is your friend

        Comment

        • Pascal Polleunus
          #3
          Re: Synchronize unicode data using copy or pg_dump

          Pascal Polleunus wrote:
          [color=blue]
          > Hi,
          >
          > I need to synchronize some tables from a database (master) to another
          > one (slave).
          > Both servers are running Debian Woody with PostgreSQL 7.2.1 (postgresql
          > 7.2.1-2woody4).
          > The databases are in unicode and doesn't contain any binary data.
          > The tables have primary/foreign key constraints, sequences and indexes,
          > but no triggers/rules. There are OIDs but these are different on the 2
          > databases. In fact they are not used by the application.
          >
          > I need to do that ASAP, and hopefully only once (there are already some
          > kind of home-made replication scripts, that are not the origin of my
          > problem).
          > Later I would like to set up pgreplication or something... any advice on
          > that is welcomed too ;-)
          >
          >
          > What would be the fastest way to achieve that?[/color]

          Wouldn't it be easier to copy directly the files from the filesystem?
          Are there particular precautions to take before doing that (except
          stopping pg)?


          [color=blue]
          > On the master:
          > For each table, use COPY TO or pg_dump?
          >
          > On the slave:
          > - delete everything from these tables
          > - drop the FK constraints
          > - (drop indexes?)
          > - vacuum (or later?)
          > - import from files (using COPY FROM or pg_restore)
          > - recreate the FK constraints
          > - update the sequences
          > - reindex (or recreate them)
          >
          > Does that sound good?
          >
          >
          > Thanks for you help,
          > Pascal Polleunus
          >
          >[/color]



          ---------------------------(end of broadcast)---------------------------
          TIP 8: explain analyze is your friend

            Comment

            Previous template Next
            Working...