Thanks for the reply. For some reason, I thought write
access to the master was locked during subscription.
Thanks for setting me straight. I can deal with the
slave not being very usable until subscription is
done.

Thanks again,

Ujwal

--- Christopher Browne <cbbrowne at ca.afilias.info>
wrote:

> "Ujwal S. Setlur" <uvsetlur at yahoo.com> writes:
> > I would like to set up slony1 (1.1.0) to replicate
> an
> > existing database that has one large table (~30
> > million rows) and several other smaller tables.
> >
> > I went throught the steps of setting up a fresh
> slave
> > and subscribing to the master. However, when I
> started
> > replication, access to tables (using select
> statemtns)
> > on the slave are locking up. I suspect it is due
> to
> > the large amount of data being transferred.
> 
> There is no surprise here.
> 
> The tables have exclusive locks on them on the
> subscriber while the
> COPY_SET event takes place.
> 
> It is fair to say that the subscriber is not in a
> usable state while
> the subscription is setting up.
> 
> > What is the best way to start replication of an
> > existing large database?
> >
> > 1. Currently all the tables are in one set. I can
> try
> > to move the large table into a different set, but
> I
> > don't want to lock the application out of
> inserting
> > new rows into the table on the master.
> 
> Nothing is locked on the master, except insofar as
> you have, during
> the COPY_SET event, some AccessShareLock locks.
> 
> For instance, see the locks outstanding during a
> COPY_SET for one of
> the ducttape tests:
> 
> /* cbbrowne@[local]/dba2 slony_test1=*/ select
> c.relname, l.transaction, l.pid, l.mode, l.granted
> from pg_locks l, pg_class c where c.oid = l.relation
> and relname in ('tellers', 'accounts', 'branches',
> 'history') order by c.relname, l.mode;
>  relname  | transaction |  pid  |       mode       |
> granted 
>
----------+-------------+-------+------------------+---------
>  accounts |             | 26149 | AccessShareLock  |
> t
>  accounts |             | 26146 | AccessShareLock  |
> t
>  accounts |             | 26147 | AccessShareLock  |
> t
>  accounts |             | 26150 | AccessShareLock  |
> t
>  accounts |             | 26149 | RowExclusiveLock |
> t
>  accounts |             | 26146 | RowExclusiveLock |
> t
>  accounts |             | 26147 | RowExclusiveLock |
> t
>  accounts |             | 26150 | RowExclusiveLock |
> t
>  branches |             | 26149 | AccessShareLock  |
> t
>  branches |             | 26150 | AccessShareLock  |
> t
>  branches |             | 26147 | AccessShareLock  |
> t
>  branches |             | 26146 | AccessShareLock  |
> t
>  branches |             | 26149 | RowExclusiveLock |
> t
>  branches |             | 26150 | RowExclusiveLock |
> t
>  branches |             | 26147 | RowExclusiveLock |
> t
>  branches |             | 26146 | RowExclusiveLock |
> t
>  tellers  |             | 26147 | AccessShareLock  |
> t
>  tellers  |             | 26149 | AccessShareLock  |
> t
>  tellers  |             | 26150 | AccessShareLock  |
> t
>  tellers  |             | 26146 | AccessShareLock  |
> t
>  tellers  |             | 26147 | RowExclusiveLock |
> t
>  tellers  |             | 26149 | RowExclusiveLock |
> t
>  tellers  |             | 26150 | RowExclusiveLock |
> t
>  tellers  |             | 26146 | RowExclusiveLock |
> t
> (24 rows)
> 
> > 2. Can I take a snapshot of the master using
> pg_dump, restore it on
> > the slave and then start replication?
> 
> No, that is not supported, and won't likely be in
> future.
> 
> At OSCON, there was discussion of a "CLONE NODE"
> notion, where raw
> filesystem copies would be taken of nodes (likely
> copied via rsync)
> one of two things would happen:
> 
>  1.  Duplicate a master node
> 
>  In this case, the master node would have to be
> locked (all sets
>  having the slonik LOCK SET operation applied to
> them) during the
>  "clone time."
> 
>  Notion:
> 
>    Repeatedly rsync the master DB to a new host,
> until the updates
>    take little time to process.
> 
>    Then LOCK SET, shut the master DB down, and do a
> final rsync.
> 
>    Start both DBs
> 
>    Submit a new "CLONE NODE" command, probably of
> the form...
> 
>     clone node (source node=1, new node=4,
> conninfo='dbname=mydb host=server2 port=5432');
> 
>    This command verifies that both nodes are locked,
> then...
>      1. Submits a STORE_NODE to add in new node #4
>      2. Submits a STORE_PATH to add a path from 1 to
> 4
>      3. Generates a set of sl_subscribe entries for
> all of the sets on 1, where 4 subscribes to 1
>      4. Renumbers things on node 4 so that it
> believes itself to be node 4
>      5. Unlocks the master node
> 
>    Now start 2 slons...
> 
>  Alas, this requires LOCK SET and some outage of the
> master DB, so it
>  definitely does NOT fit into the category of "does
> not interrupt
>  operations."  :-(
> 
>  2.  Duplicate a subscriber node
> 
>  This is analagous; hits a subscriber node.  Things
> are quite a bit
>  simpler to handle, and we don't have to interrupt
> operations.
> 
>  Notion is similar:
> 
>   Repeatedly rsync subscriber to a new host until
> the updates take
>   little time to process.
> 
>   Then stop the slon for the subscriber, call it
> node #2
> 
>   Do a final rsync
> 
>   Start both DBs
> 
>   Submit a new "CLONE NODE" command, of the form
> 
>     clone node (source node=2, new node=4,
> conninfo='dbname=mydb host=server2 port=5432');
> 
>    This command verifies that both nodes are in
> sync, then...
>      1. Submits a STORE_NODE to add in new node #4
>      2. Submits a STORE_PATH to add a path from 2 to
> 4
>      3. Generates a set of sl_subscribe entries for
> all of the sets on 2, where 4 subscribes to 2
>      4. Renumbers things on node 4 so that it
> believes itself to be node 4
> 
>   Now start 2 slons...  
> 
> Note: This is a brand new plan, and CLONE NODE has
> not yet had a
> single line of code written for it.
> 
> 
=== message truncated ===