X-Original-To: pgsql-admin-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id 2D24E3A3CA7 for ; Tue, 9 Nov 2004 14:37:12 +0000 (GMT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 37107-08 for ; Tue, 9 Nov 2004 14:37:07 +0000 (GMT) Received: from mail.deg.cc (unknown [64.139.134.201]) by svr1.postgresql.org (Postfix) with ESMTP id 6F01C3A3CA3 for ; Tue, 9 Nov 2004 14:37:09 +0000 (GMT) Received: from deg.cc (kyle.deg.cc [198.70.16.205]) by mail.deg.cc (Postfix) with ESMTP id 7987B56183; Tue, 9 Nov 2004 09:37:06 -0500 (EST) Message-ID: <4190D613.7000509@deg.cc> Date: Tue, 09 Nov 2004 09:37:07 -0500 From: Pallav Kalva User-Agent: Mozilla/5.0 (X11; U; Linux i686; en-US; rv:1.4.2) Gecko/20040308 X-Accept-Language: en-us, en MIME-Version: 1.0 To: Chris Browne Cc: pgsql-admin@postgresql.org Subject: Re: Replication Options. References: <418261A5.5060006@deg.cc> <60y8hpntab.fsf@dba2.int.libertyrms.com> In-Reply-To: <60y8hpntab.fsf@dba2.int.libertyrms.com> Content-Type: multipart/mixed; boundary="------------070904090408070308070005" X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.0 tagged_above=0.0 required=5.0 tests= X-Spam-Level: X-Archive-Number: 200411/75 X-Sequence-Number: 15460 This is a multi-part message in MIME format. --------------070904090408070308070005 Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit Chris Browne wrote: >pkalva@deg.cc (Pallav Kalva) writes: > > >> I need to implement the following the replication scenario in >>postgres database. Here are the details of what we are looking to >>implement. >> >>Lets say I have 2 nodes Node1(US) and Node2(Canada), >> >>Node1: tableA , tableB >>Node2: tableB , tableA >> >>tableA in Node1 is the master and it should replicate only to slave >>tableA in Node2 >> >>tableB in Node2 is the master and it should replicate only to slave >>tableB in Node1 >> >>Can this be possible with any of the replication models available for >>Postgres 7.4/8.0 ? >> >> > >This scenario would be supported by Slony-I; there are several ways to >accomplish it, probably the easiest being thus... > >- Create two nodes, 1 and 2 > >- Create two replication sets - #1 and #2. > > - Set 1 has origin of node 1 > - Set 2 has origin of node 2 > > - Add tablea to set 1 > - Add tableb to set 2 > > - Subscribe node 2 to set 1 > - Subscribe node 1 to set 2 > >Either node can run PG 7.4 or 8.0... > > Hi Chris, Based on your suggestion I tried to setup replication for my envrionment and I ran into problems. To start of with initially, I tried to replicate on the same host with one master database and one slave database, I used the same expample as in docs for Slony replication and it went fine and I could replicate the changes and I could see them at the slave database immediately. Then I tried to do the above setup where tableA is the master in one node and tableB is the master in another node (in this case I used the same host) and they have to replicate each other. I created 2 sets in the initial configuration script and added tables to each sets , when i run the script i get the following error : :44: PGRES_FATAL_ERROR select "_slony_example".storeSet(2, 'All testslave tables'); - ERROR: schema "_slony_example" does not exist I am attaching the script and also the environment variables i used for the script. am I missing something in setup configuration or this type of configuration is not possible with Slony. Thanks! again. Pallav --------------070904090408070308070005 Content-Type: application/x-sh; name="slony_setup_scenario2.sh" Content-Transfer-Encoding: 7bit Content-Disposition: inline; filename="slony_setup_scenario2.sh" #!/bin/sh slonik <<_EOF_ #-- # define the namespace the replication system uses in our example it is # slony_example #-- cluster name = $CLUSTERNAME; #-- # admin conninfo's are used by slonik to connect to the nodes one for each # node on each side of the cluster, the syntax is that of PQconnectdb in # the C-API # -- node 1 admin conninfo = 'dbname=$MASTERDBNAME host=$MASTERHOST user=$REPLICATIONUSER'; node 2 admin conninfo = 'dbname=$SLAVEDBNAME host=$SLAVEHOST user=$REPLICATIONUSER'; #-- # init the first node. Its id MUST be 1. This creates the schema # _$CLUSTERNAME containing all replication system specific database # objects. #-- init cluster ( id=1, comment = 'Master Node'); #-- # Because the history table does not have a primary key or other unique # constraint that could be used to identify a row, we need to add one. # The following command adds a bigint column named # _Slony-I_$CLUSTERNAME_rowID to the table. It will have a default value # of nextval('_$CLUSTERNAME.s1_rowid_seq'), and have UNIQUE and NOT NULL # constraints applied. All existing rows will be initialized with a # number #-- #table add key (node id = 1, fully qualified name = 'public.history'); #-- # Slony-I organizes tables into sets. The smallest unit a node can # subscribe is a set. The following commands create one set containing # all 4 pgbench tables. The master or origin of the set is node 1. #-- create set (id=1, origin=1, comment='All test tables'); set add table (set id=1, origin=1, id=1, fully qualified name = 'test.tablea', comment='tableA table'); #-- create set (id=2, origin=2, comment='All testslave tables'); set add table (set id=2, origin=2, id=1, fully qualified name = 'test.tableb', comment='tableB table'); # Create the second node (the slave) tell the 2 nodes how to connect to # each other and how they should listen for events. #-- store node (id=2, comment = 'Slave node'); store path (server = 1, client = 2, conninfo='dbname=$MASTERDBNAME host=$MASTERHOST user=$REPLICATIONUSER'); store path (server = 2, client = 1, conninfo='dbname=$SLAVEDBNAME host=$SLAVEHOST user=$REPLICATIONUSER'); store listen (origin=1, provider = 1, receiver =2); store listen (origin=2, provider = 2, receiver =1); _EOF_ --------------070904090408070308070005 Content-Type: text/plain; name="slony_env_variables" Content-Transfer-Encoding: 7bit Content-Disposition: inline; filename="slony_env_variables" export CLUSTERNAME=slony_example export MASTERDBNAME=test export SLAVEDBNAME=testslave export MASTERHOST=localhost export SLAVEHOST=localhost export REPLICATIONUSER=postgres export PGBENCHUSER=test --------------070904090408070308070005--