Anyone using pgreplicator?

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

    Anyone using pgreplicator?

    Apologies if this is off topic, but I've tried the pgreplicator forums,
    and they appear to be dormant.

    I'm trying to get a multi master replication app running, I thought I
    was doing well, I've got past all of the problems mentioned on the
    pgreplicator forums and (checking the archives) on this list, ie, tcl
    installed ok, script changes for postgresql 7.3.4 in, communications
    using scp as user replicator working... Everything in fact works.
    Except that it doesn't actually replicate. When I synchronise. I can
    see the traffic going between the sites. I can see the record being
    replicated appearing on the flow table, I can see it on the history
    file on both the source and the destination, so it's reaching the
    destination. There is no sign of any error in any of the pgreplicator
    logs or the system logs. I've even started postgresql in debug log mode
    and tailed the log while started the replicator on the remote site. I
    see the communications come in, and see the commands being entered. But
    the data isn't being changed on the destination.

    Any pgreplicator users out there seen this sort of thing before?

    I thought it might be a permissions issue, but all users have full
    access. The replicator user can make the changes by submitting an SQL
    update. pgreplicator has a 'rejected sql' log file, but that's empty.
    I'm at my wits end...

    Regards

    --
    Alan Graham <alan.graham@in fonetsystems.co m.au>


    ---------------------------(end of broadcast)---------------------------
    TIP 2: you can get off all lists at once with the unregister command
    (send "unregister YourEmailAddres sHere" to majordomo@postg resql.org)

    Tags: None
    • Alan Graham
      #2
      Re: Anyone using pgreplicator?

      Replying to my own post, tch, tch... :-)

      Thanks to those of you who have contacted me off list. It seems that
      there's a fair bit of interest in replication in general at the moment.

      I fixed my problem with pgReplicator, so the test system is merrily
      replicating between Perth and Brisbane as I type. Bandwidth usage is
      low at the moment. The problem was embarrassingly simple once I checked
      the actual SQL being received on the remote server. (Thanks Stef of .za
      for the suggestion).

      When pgReplicator creates its replication changes to a data base it adds
      its own unique ID. I did this independently when I initially set this
      up. This meant that the id for records that I considered to be
      identical were actually different according to pgreplicator and
      therefore updates couldn't find the corresponding record to update in
      the remote system. As I said, embarrassingly simple!

      The trick when setting up a replicated multi master db is to first of
      all create the combined db, create the pgr replication schema on this
      combined db, and then distribute this db around to all sites. Only then
      do you make the site specific changes (eg, to pgr_lsite, and any locally
      independent tables). Note that this is the opposite approach to that on
      the website. Alternatively, (I haven't tried this) one could distribute
      a schema only db, put the site specific data into it and let replication
      handle the filling of the common data, by dumping the data as user
      replicator, then loading it again as a real user.

      Hope this helps someone else avoid my mistake.

      Alan Graham

      On Thu, 2004-04-15 at 22:11, Alan Graham wrote:[color=blue]
      > Apologies if this is off topic, but I've tried the pgreplicator forums,
      > and they appear to be dormant.
      >
      > I'm trying to get a multi master replication app running, I thought I
      > was doing well, I've got past all of the problems mentioned on the
      > pgreplicator forums and (checking the archives) on this list, ie, tcl
      > installed ok, script changes for postgresql 7.3.4 in, communications
      > using scp as user replicator working... Everything in fact works.
      > Except that it doesn't actually replicate. When I synchronise. I can
      > see the traffic going between the sites. I can see the record being
      > replicated appearing on the flow table, I can see it on the history
      > file on both the source and the destination, so it's reaching the
      > destination. There is no sign of any error in any of the pgreplicator
      > logs or the system logs. I've even started postgresql in debug log mode
      > and tailed the log while started the replicator on the remote site. I
      > see the communications come in, and see the commands being entered. But
      > the data isn't being changed on the destination.
      >
      > Any pgreplicator users out there seen this sort of thing before?
      >
      > I thought it might be a permissions issue, but all users have full
      > access. The replicator user can make the changes by submitting an SQL
      > update. pgreplicator has a 'rejected sql' log file, but that's empty.
      > I'm at my wits end...
      >
      > Regards[/color]
      --
      Alan Graham <alan.graham@in fonetsystems.co m.au>

      -----BEGIN PGP SIGNATURE-----
      Version: GnuPG v1.2.3 (GNU/Linux)

      iD8DBQBAhUYNoD2 Tjq9atDkRAsljAJ 0RnmYgCNFy/3LbkmRSmxKPi5Ul tACeKvD6
      bSXOqrCSRkfo1o3 iV1WQqvQ=
      =op66
      -----END PGP SIGNATURE-----

        Comment

        • Alan Graham
          #3
          Re: Anyone using pgreplicator?

          Replying to my own post, tch, tch... :-)

          Thanks to those of you who have contacted me off list. It seems that
          there's a fair bit of interest in replication in general at the moment.

          I fixed my problem with pgReplicator, so the test system is merrily
          replicating between Perth and Brisbane as I type. Bandwidth usage is
          low at the moment. The problem was embarrassingly simple once I checked
          the actual SQL being received on the remote server. (Thanks Stef of .za
          for the suggestion).

          When pgReplicator creates its replication changes to a data base it adds
          its own unique ID. I did this independently when I initially set this
          up. This meant that the id for records that I considered to be
          identical were actually different according to pgreplicator and
          therefore updates couldn't find the corresponding record to update in
          the remote system. As I said, embarrassingly simple!

          The trick when setting up a replicated multi master db is to first of
          all create the combined db, create the pgr replication schema on this
          combined db, and then distribute this db around to all sites. Only then
          do you make the site specific changes (eg, to pgr_lsite, and any locally
          independent tables). Note that this is the opposite approach to that on
          the website. Alternatively, (I haven't tried this) one could distribute
          a schema only db, put the site specific data into it and let replication
          handle the filling of the common data, by dumping the data as user
          replicator, then loading it again as a real user.

          Hope this helps someone else avoid my mistake.

          Alan Graham

          On Thu, 2004-04-15 at 22:11, Alan Graham wrote:[color=blue]
          > Apologies if this is off topic, but I've tried the pgreplicator forums,
          > and they appear to be dormant.
          >
          > I'm trying to get a multi master replication app running, I thought I
          > was doing well, I've got past all of the problems mentioned on the
          > pgreplicator forums and (checking the archives) on this list, ie, tcl
          > installed ok, script changes for postgresql 7.3.4 in, communications
          > using scp as user replicator working... Everything in fact works.
          > Except that it doesn't actually replicate. When I synchronise. I can
          > see the traffic going between the sites. I can see the record being
          > replicated appearing on the flow table, I can see it on the history
          > file on both the source and the destination, so it's reaching the
          > destination. There is no sign of any error in any of the pgreplicator
          > logs or the system logs. I've even started postgresql in debug log mode
          > and tailed the log while started the replicator on the remote site. I
          > see the communications come in, and see the commands being entered. But
          > the data isn't being changed on the destination.
          >
          > Any pgreplicator users out there seen this sort of thing before?
          >
          > I thought it might be a permissions issue, but all users have full
          > access. The replicator user can make the changes by submitting an SQL
          > update. pgreplicator has a 'rejected sql' log file, but that's empty.
          > I'm at my wits end...
          >
          > Regards[/color]
          --
          Alan Graham <alan.graham@in fonetsystems.co m.au>

          -----BEGIN PGP SIGNATURE-----
          Version: GnuPG v1.2.3 (GNU/Linux)

          iD8DBQBAhUYNoD2 Tjq9atDkRAsljAJ 0RnmYgCNFy/3LbkmRSmxKPi5Ul tACeKvD6
          bSXOqrCSRkfo1o3 iV1WQqvQ=
          =op66
          -----END PGP SIGNATURE-----

            Comment

            • Richard Welty
              #4
              Re: Anyone using pgreplicator?

              On Tue, 20 Apr 2004 23:47:26 +0800 Alan Graham <alan.graham@in fonetsystems.co m.au> wrote:[color=blue]
              > I fixed my problem with pgReplicator, so the test system is merrily
              > replicating between Perth and Brisbane as I type. Bandwidth usage is
              > low at the moment. The problem was embarrassingly simple once I checked
              > the actual SQL being received on the remote server. (Thanks Stef of .za
              > for the suggestion).[/color]

              any chance of your publishing notes on how you got pgreplicator going?
              i've taken two shots at it, and gotten stuck on getting tcl-dp working properly
              both times. from a functionality point of view, it's closer to what i want than
              any of the others, but it's been frustrating as hell to try and get going.

              richard
              --
              Richard Welty rwelty@averillp ark.net
              Averill Park Networking 518-573-7592
              Java, PHP, PostgreSQL, Unix, Linux, IP Network Engineering, Security


              ---------------------------(end of broadcast)---------------------------
              TIP 2: you can get off all lists at once with the unregister command
              (send "unregister YourEmailAddres sHere" to majordomo@postg resql.org)

                Comment

                • Richard Welty
                  #5
                  Re: Anyone using pgreplicator?

                  On Tue, 20 Apr 2004 23:47:26 +0800 Alan Graham <alan.graham@in fonetsystems.co m.au> wrote:[color=blue]
                  > I fixed my problem with pgReplicator, so the test system is merrily
                  > replicating between Perth and Brisbane as I type. Bandwidth usage is
                  > low at the moment. The problem was embarrassingly simple once I checked
                  > the actual SQL being received on the remote server. (Thanks Stef of .za
                  > for the suggestion).[/color]

                  any chance of your publishing notes on how you got pgreplicator going?
                  i've taken two shots at it, and gotten stuck on getting tcl-dp working properly
                  both times. from a functionality point of view, it's closer to what i want than
                  any of the others, but it's been frustrating as hell to try and get going.

                  richard
                  --
                  Richard Welty rwelty@averillp ark.net
                  Averill Park Networking 518-573-7592
                  Java, PHP, PostgreSQL, Unix, Linux, IP Network Engineering, Security


                  ---------------------------(end of broadcast)---------------------------
                  TIP 2: you can get off all lists at once with the unregister command
                  (send "unregister YourEmailAddres sHere" to majordomo@postg resql.org)

                    Comment

                    Previous template Next
                    Working...