"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.

If people have rocks to throw at this, it would be nice to hear about
before implementing code :-).
-- 
"cbbrowne","@","ca.afilias.info"
<http://dev6.int.libertyrms.com/>
Christopher Browne
(416) 673-4124 (land)