Re: RServ patch to support multiple slaves (sorta) - Mailing list pgsql-hackers

OK, I have backed out this patch.  Hopefully we can get a more complete
patch sometime.

---------------------------------------------------------------------------

Michael A Nachbaur wrote:
> On Wednesday 25 June 2003 08:42 am, Alvaro Herrera wrote:
> > On Wed, Jun 25, 2003 at 11:11:35AM -0400, Bruce Momjian wrote:
> > > Tom Lane wrote:
> > > > Bruce Momjian <pgman@candle.pha.pa.us> writes:
> > > > > Patch applied.  Thanks.
> > > > >
> > > > > Michael A Nachbaur wrote:
> > > > >> Attached is a patch that provides *VERY* limited support for
> > > > >> multiple slave servers.  I haven't tested it very well, so use at
> > > > >> your own risk (and I recommend against using it in production).
> > > >
> > > >    ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
> > > >
> > > > It sounded to me like that patch was intended for comment, not for
> > > > application.
>
> Yes, this was my original intent.  If anyone else thought it was worthy enough
> to go into CVS, then great, but mainly I wanted a few more pairs of eyes to
> look it over.
>
> > > He said it wasn't all he wanted to do with the code, but that it did
> > > work.  With so few rserv patches, it seems like something we should get
> > > in, but maybe not?  Other comments?  I am not sure myself.
> >
> > I don't remember the patch right now, but it seemed to me the patch
> > didn't have anything to do with multiple slaves anyway...  When was it
> > posted?  I can't find it in the archives...  (it'd be nice to have the
> > date of the original message in the attribution when you quote other
> > people, that way it's much easier to find it in the archives)
>
> All my patch does is allow you to limit what tables you replicate from a
> slave.  In this way, SlaveA can replicate tables X, Y and Z, while SlaveB can
> replicate tables M, N and O.
>
> I have a single master database, and different authentication databases at key
> areas of my infrastructure (mail authentication, web server configuration,
> etc).  I was getting errors when trying to replicate SlaveA just after adding
> SlaveB, because the necessary tables didn't exist on SlaveA.
>
> > Some 2 years ago I wrote a patch for multiple slaves and it worked
> > reasonably well... I wasn't too much in the Postgres world those days so
> > I didn't submit it.  If I can get to my CVS archive I'll extract it and
> > post for review.
>
> That'd be great.  My patch, like I said in my original post (06/19/2003
> 07:36pm PST), is just a beginning, and I'm not even 100% sure it'll work
> reliably.  Although I'm an experienced Perl programmer, I'm not as familar
> with PostgreSQL's internals as I'd like to be (e.g. I tread lightly when it
> comes to the pg_* tables).
>
> If someone else has better support, I'd much rather a) take the code and run,
> and b) not have to do the same myself since I have too many items on my task
> list as it is.
>
> --
> Michael A Nachbaur <mike@nachbaur.com>
>
>

--
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 359-1001
  +  If your life is a hard drive,     |  13 Roberts Road
  +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073
? config.log
? GNUmakefile
? config.status
? src/log
? src/Makefile.global
? src/Makefile.custom
? src/backend/utils/mb/conversion_procs/conversion_create.sql
? src/backend/utils/mb/conversion_procs/ascii_and_mic/libascii_and_mic.so.0.0
? src/backend/utils/mb/conversion_procs/cyrillic_and_mic/libcyrillic_and_mic.so.0.0
? src/backend/utils/mb/conversion_procs/euc_cn_and_mic/libeuc_cn_and_mic.so.0.0
? src/backend/utils/mb/conversion_procs/euc_jp_and_sjis/libeuc_jp_and_sjis.so.0.0
? src/backend/utils/mb/conversion_procs/euc_kr_and_mic/libeuc_kr_and_mic.so.0.0
? src/backend/utils/mb/conversion_procs/euc_tw_and_big5/libeuc_tw_and_big5.so.0.0
? src/backend/utils/mb/conversion_procs/latin2_and_win1250/liblatin2_and_win1250.so.0.0
? src/backend/utils/mb/conversion_procs/latin_and_mic/liblatin_and_mic.so.0.0
? src/backend/utils/mb/conversion_procs/utf8_and_ascii/libutf8_and_ascii.so.0.0
? src/backend/utils/mb/conversion_procs/utf8_and_big5/libutf8_and_big5.so.0.0
? src/backend/utils/mb/conversion_procs/utf8_and_cyrillic/libutf8_and_cyrillic.so.0.0
? src/backend/utils/mb/conversion_procs/utf8_and_euc_cn/libutf8_and_euc_cn.so.0.0
? src/backend/utils/mb/conversion_procs/utf8_and_euc_jp/libutf8_and_euc_jp.so.0.0
? src/backend/utils/mb/conversion_procs/utf8_and_euc_kr/libutf8_and_euc_kr.so.0.0
? src/backend/utils/mb/conversion_procs/utf8_and_euc_tw/libutf8_and_euc_tw.so.0.0
? src/backend/utils/mb/conversion_procs/utf8_and_gb18030/libutf8_and_gb18030.so.0.0
? src/backend/utils/mb/conversion_procs/utf8_and_gbk/libutf8_and_gbk.so.0.0
? src/backend/utils/mb/conversion_procs/utf8_and_iso8859/libutf8_and_iso8859.so.0.0
? src/backend/utils/mb/conversion_procs/utf8_and_iso8859_1/libutf8_and_iso8859_1.so.0.0
? src/backend/utils/mb/conversion_procs/utf8_and_johab/libutf8_and_johab.so.0.0
? src/backend/utils/mb/conversion_procs/utf8_and_sjis/libutf8_and_sjis.so.0.0
? src/backend/utils/mb/conversion_procs/utf8_and_tcvn/libutf8_and_tcvn.so.0.0
? src/backend/utils/mb/conversion_procs/utf8_and_uhc/libutf8_and_uhc.so.0.0
? src/backend/utils/mb/conversion_procs/utf8_and_win1250/libutf8_and_win1250.so.0.0
? src/backend/utils/mb/conversion_procs/utf8_and_win1256/libutf8_and_win1256.so.0.0
? src/backend/utils/mb/conversion_procs/utf8_and_win874/libutf8_and_win874.so.0.0
? src/bin/initdb/initdb
? src/bin/initlocation/initlocation
? src/bin/ipcclean/ipcclean
? src/bin/pg_config/pg_config
? src/bin/pg_controldata/pg_controldata
? src/bin/pg_ctl/pg_ctl
? src/bin/pg_dump/pg_dump
? src/bin/pg_dump/pg_restore
? src/bin/pg_dump/pg_dumpall
? src/bin/pg_encoding/pg_encoding
? src/bin/pg_id/pg_id
? src/bin/pg_resetxlog/pg_resetxlog
? src/bin/pgtclsh/pgtclsh
? src/bin/pgtclsh/pgtksh
? src/bin/psql/psql
? src/bin/scripts/createdb
? src/bin/scripts/createlang
? src/bin/scripts/createuser
? src/bin/scripts/dropdb
? src/bin/scripts/droplang
? src/bin/scripts/dropuser
? src/bin/scripts/clusterdb
? src/bin/scripts/vacuumdb
? src/include/pg_config.h
? src/include/stamp-h
? src/interfaces/ecpg/compatlib/libecpg_compat.so.1.0.0
? src/interfaces/ecpg/ecpglib/libecpg.so.4.1.1
? src/interfaces/ecpg/ecpglib/libecpg.so.4.0.0
? src/interfaces/ecpg/pgtypeslib/libpgtypes.so.1.0.0
? src/interfaces/ecpg/preproc/ecpg
? src/interfaces/libpgtcl/libpgtcl.so.2.4
? src/interfaces/libpq/libpq.so.3.1
? src/interfaces/python/lib_pgmodule.so.2.4
? src/pl/plperl/SPI.c
? src/pl/plperl/libplperl.so.0.0
? src/pl/plpgsql/src/libplpgsql.so.1.0
? src/pl/tcl/libpltcl.so.2.0
? src/pl/tcl/modules/pltcl_loadmod
? src/pl/tcl/modules/pltcl_delmod
? src/pl/tcl/modules/pltcl_listmod
? src/test/regress/pg_regress
? src/test/regress/results
? src/test/regress/expected/copy.out
? src/test/regress/expected/create_function_1.out
? src/test/regress/expected/create_function_2.out
? src/test/regress/expected/misc.out
? src/test/regress/expected/constraints.out
? src/test/regress/sql/copy.sql
? src/test/regress/sql/create_function_1.sql
? src/test/regress/sql/create_function_2.sql
? src/test/regress/sql/misc.sql
? src/test/regress/sql/constraints.sql
Index: contrib/rserv/RServ.pm
===================================================================
RCS file: /cvsroot/pgsql-server/contrib/rserv/RServ.pm,v
retrieving revision 1.1
retrieving revision 1.2
diff -c -c -r1.1 -r1.2
*** contrib/rserv/RServ.pm    20 Dec 2000 17:22:35 -0000    1.1
--- contrib/rserv/RServ.pm    25 Jun 2003 01:17:44 -0000    1.2
***************
*** 19,25 ****

  sub PrepareSnapshot
  {
!     my ($conn, $outf, $server) = @_; # (@_[0], @_[1], @_[2]);

      my $result = $conn->exec("BEGIN");
      if ($result->resultStatus ne PGRES_COMMAND_OK)
--- 19,25 ----

  sub PrepareSnapshot
  {
!     my ($conn, $outf, $server, $onlytables) = @_; # (@_[0], @_[1], @_[2]);

      my $result = $conn->exec("BEGIN");
      if ($result->resultStatus ne PGRES_COMMAND_OK)
***************
*** 52,57 ****
--- 52,61 ----
      while (@row = $result->fetchrow)
      {
      #    printf "$row[0], $row[1], $row[2]\n";
+         if (ref($onlytables) eq 'HASH') {
+             next unless (exists $onlytables->{$row[1]});
+             $onlytables->{$row[1]} = $row[0] unless ($onlytables->{$row[1]});
+         }
          push @{$Mtables{$row[0]}}, $row[1], $row[2];
      }

***************
*** 232,238 ****

  sub CleanLog
  {
!     my ($conn, $howold) = @_; # (@_[0], @_[1]);

      my $result = $conn->exec("BEGIN");
      if ($result->resultStatus ne PGRES_COMMAND_OK)
--- 236,242 ----

  sub CleanLog
  {
!     my ($conn, $howold, $onlytables) = @_; # (@_[0], @_[1]);

      my $result = $conn->exec("BEGIN");
      if ($result->resultStatus ne PGRES_COMMAND_OK)
***************
*** 274,279 ****
--- 278,288 ----
      my $alist = join(',', keys %active);
      my $sinfo = "logid < $maxid";
      $sinfo .= " and logid not in ($alist)" if $alist ne '';
+     #if (ref($onlytables) eq 'HASH') {
+     #    foreach my $onlytable (keys %{$onlytables}) {
+     #        $sinfo
+     #    }
+     #}

      $sql = "delete from _RSERV_LOG_ where " .
          "logtime < now() - '$howold second'::interval and $sinfo";
***************
*** 302,308 ****

  sub ApplySnapshot
  {
!     my ($conn, $inpf) = @_; # (@_[0], @_[1]);

      my $result = $conn->exec("BEGIN");
      if ($result->resultStatus ne PGRES_COMMAND_OK)
--- 311,317 ----

  sub ApplySnapshot
  {
!     my ($conn, $inpf, $onlytables) = @_; # (@_[0], @_[1]);

      my $result = $conn->exec("BEGIN");
      if ($result->resultStatus ne PGRES_COMMAND_OK)
***************
*** 336,341 ****
--- 345,354 ----
      while (@row = $result->fetchrow)
      {
      #    printf "    %s    %s\n", $row[1], $row[0];
+         if (ref($onlytables) eq 'HASH') {
+             next unless (exists $onlytables->{$row[1]});
+             $onlytables->{$row[1]} = $row[0] unless ($onlytables->{$row[1]});
+         }
          push @{$Stables{$row[1]}}, $row[0], $row[2], $row[3];
      }

Index: contrib/rserv/Replicate.in
===================================================================
RCS file: /cvsroot/pgsql-server/contrib/rserv/Replicate.in,v
retrieving revision 1.2
retrieving revision 1.3
diff -c -c -r1.2 -r1.3
*** contrib/rserv/Replicate.in    6 Mar 2002 20:41:36 -0000    1.2
--- contrib/rserv/Replicate.in    25 Jun 2003 01:17:44 -0000    1.3
***************
*** 32,37 ****
--- 32,38 ----

  my $master = $ARGV[0] || "master";
  my $slave = $ARGV[1] || "slave";
+ my $tables = $#ARGV < 2 ? undef : { map {($_, undef)} @ARGV[2..$#ARGV] };
  my $server = 0;

  my $minfo = "dbname=$master";
***************
*** 56,62 ****
  my $outf = new IO::File;
  open $outf, ">$snapshot";
  print "\n>>>>>>>>>>>>> Prepare Snapshot\n\n" if ($verbose);
! $res = PrepareSnapshot($mconn, $outf, $server);
  close $outf;
  die "\n>>>>>>>>>>>>> ERROR\n" if $res < 0;
  if ($res == 0)
--- 57,63 ----
  my $outf = new IO::File;
  open $outf, ">$snapshot";
  print "\n>>>>>>>>>>>>> Prepare Snapshot\n\n" if ($verbose);
! $res = PrepareSnapshot($mconn, $outf, $server, $tables);
  close $outf;
  die "\n>>>>>>>>>>>>> ERROR\n" if $res < 0;
  if ($res == 0)
***************
*** 68,74 ****
  my $inpf = new IO::File;
  open $inpf, "<$snapshot";
  print "\n>>>>>>>>>>>>> Apply Snapshot\n\n" if ($verbose);
! $res = ApplySnapshot($sconn, $inpf);
  close $inpf;
  die "\n>>>>>>>>>>>>> ERROR\n" if $res < 0;

--- 69,75 ----
  my $inpf = new IO::File;
  open $inpf, "<$snapshot";
  print "\n>>>>>>>>>>>>> Apply Snapshot\n\n" if ($verbose);
! $res = ApplySnapshot($sconn, $inpf, $tables);
  close $inpf;
  die "\n>>>>>>>>>>>>> ERROR\n" if $res < 0;