Ujwal S. Setlur wrote:

>Hello,
>
>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.
>
>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.
>
>  
>
The subscribe will sub all the tables from one set in one go.  If the 
other tables are small, there is likely to be nothing gained from 
putting them into another set.

BTW, you could do this without locking data writes out of the master.  
Use the slonik command 'set drop table' to drop the big table from your 
existing set, then create a new set containing the big table.

>2. Can I take a snapshot of the master using pg_dump,
>restore it on the slave and then start replication?
>
>  
>
No, this won't work.  When you issue subscribe set, slonik will delete 
all the entries from the tables in that set (on the subscriber) and 
repopulate them.

Some things that might help ease the burdon of the subscription (do 
before issuing the subscribe):
-truncate all the tables on the new subscriber, and vacuum them.
-dropping the indexes on the big table on the subscriber will speed up 
the copy.  You'll have to recreate them manually after the subscription 
is done though.  There is a patch to do this automatically being worked 
on, but it's not quite ready for prime time.
-turning off fsync on the subscriber might help as well (I've never 
tried, so I can't say).  _Don't_ do this if you have any other databases 
running on that  backend that are important!  This may leave you with an 
unrecoverable mess if the backend crashes.  Be sure to turn it back on 
once the subscription is complete.

I would also strongly recommend locking access on the subscriber to 
everyone execpt the user that is doing the subscription.  An easy way to 
do this is to setup  dedicated slony user, and lock out all other users 
via the pg_hba.conf.

I'd expect this table to take quite a while to subscribe, but the amount 
of time is going to depend largely on the hardware you are running.

-- 
Brad Nicholson  416-673-4106
Database Administrator, Afilias Canada Corp.